{"id":1261,"date":"2019-08-15T08:48:42","date_gmt":"2019-08-15T08:48:42","guid":{"rendered":"https:\/\/www.withinweb.com\/info\/?p=1261"},"modified":"2019-08-28T07:48:52","modified_gmt":"2019-08-28T07:48:52","slug":"analyzing-google-webmaster-statistics-spreadsheets","status":"publish","type":"post","link":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/","title":{"rendered":"Analyzing Google Webmaster Statistics with Spreadsheets"},"content":{"rendered":"<h4>The Google Tools<\/h4>\n<p>There are two Google tools that you can use to analyze the performance of your web site.\u00a0 One is Google Analytics, the other is Google Webmasters.\u00a0 There two systems give different views and statistics on your web site.<\/p>\n<p>When you set up Google Webmasters you have to make sure that the URLs are set up properly and you need to set up conical reference.<\/p>\n<p>Once Google is providing statistics into the webmaster tool, you can start analyzing the data.\u00a0 However, you may have to wait for several months before you do a proper analyses and see trends.<\/p>\n<p>Google Webmasters provide statistics on how your web site is performing with Google Search.\u00a0 In particular, it includes information on the queries that the user entered into Google to arrive at a given page, click through rates and information on the search position.\u00a0 All of these give you an understanding of search engine performance for you web site.<\/p>\n<h4>Downloading Your Data<\/h4>\n<p><a href=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone  wp-image-1496\" src=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page-300x147.png\" alt=\"\" width=\"581\" height=\"357\" \/><\/a><\/p>\n<p>The following assumes that you have already set up your web site and that data is available.<\/p>\n<p>1) Log into Google Webmasters using your Google account.<\/p>\n<p>The Search Console will be displayed.<\/p>\n<p>2 ) Click on the Website that you want to analyze.<\/p>\n<p>In the left hand navigation column, click on &#8220;Performance&#8221; and in the right hand side, scroll down to where you will see a list of &#8220;Queries&#8221; with the number of clicks and impressions.<\/p>\n<p>3) Go to the &#8220;Download&#8221; icon and click on it.<\/p>\n<p>4) Save the download as either a csv file or as a Google Sheets file.\u00a0 I normally download as Google Sheets format and then open it up in an old version of Microsoft Excel to do the calculations.\u00a0 However, you can just work with Google Sheets as it does the job just as well.<\/p>\n<p>5) Sort the complete spreadsheet in order of &#8220;Impressions&#8221;, highest to lowest.<\/p>\n<p>6) Add in a new column called %Impressions.<\/p>\n<p>We want to calculate each value as a percentage of the total impressions.\u00a0 So for each entry:<br \/>( Impression Cell Value )\u00a0 \/ SUM( All Impression Values ) * 100<\/p>\n<p>7) Add in a new column called %Cumulative<\/p>\n<p>We want to add up all the cumulative values using the formulae =<br \/>%Impression + %Previous Cumulative Value<\/p>\n<p>8) From the cumulative percentage we can look at the top 50% of queries which are the most important and do out analysis on that.<\/p>\n<h4>Doing The Analyses<\/h4>\n<p>We can now look at our listing and see which of the keywords in the top 50% have low CTR (Click Through Rate).\u00a0 We can see that these keywords are giving high number of impressions on our site, but some are not returning good CTR.<\/p>\n<p>We can look at these queries, and then try to work out how we can improve the click through rate.<\/p>\n<h4>The Other Downloads<\/h4>\n<p>From the same location we can download &#8220;Queries&#8221;, &#8220;Countries&#8221;, &#8220;Pages&#8221; and &#8220;Devices&#8221;.\u00a0 From each one we can investigate different issues.\u00a0<\/p>\n<p>The first thing to notice is that if you add up the impressions in each one, you will notice that they are different.\u00a0 So what is going on here?<\/p>\n<p>Well, Google has a page at:<\/p>\n<p><a href=\"https:\/\/support.google.com\/webmasters\/answer\/7576553\">https:\/\/support.google.com\/webmasters\/answer\/7576553<\/a><\/p>\n<p>There is a section called &#8220;Data Discrepancies&#8221; which gives some explanations for the differences.\u00a0 Essentially, the issue with the queries download is that it is limited to 1000 rows, so there will be some loss of data although these will be from queries that are rarely made.\u00a0 There is also loss of data for other reasons, such as excluding queries with &#8220;private&#8221; data, time lag differences in data processing, different processing techniques for different data sources and so on.<\/p>\n<h4>Comparing Across Dates<\/h4>\n<p>Luckily for us, Google has provided us with features to compare one set of data with another over time.<\/p>\n<p>If you click on the Date Compare button, you can select Filter or Compare. In the Compare list you can then choose the date range for comparison.<\/p>\n<p><a href=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-1507\" src=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-260x300.png\" alt=\"\" width=\"260\" height=\"300\" srcset=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-260x300.png 260w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-100x116.png 100w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-150x173.png 150w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-200x231.png 200w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-300x347.png 300w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list-450x520.png 450w, https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/compare_list.png 501w\" sizes=\"auto, (max-width: 260px) 100vw, 260px\" \/><\/a><\/p>\n<p>The resulting csv file provides with a wealth of information of changing data over time.<\/p>\n<h4>Example Spreadsheet File<\/h4>\n<p>If you click on the following link you can download and example xls spreadsheet which shows the spreadsheet formula.<\/p>\n<p><a href=\"https:\/\/www.withinweb.com\/graphics\/Sample_Webmaster_Results.xls\">Click here for example spreadsheet.<\/a><\/p>\n<p>\u00a0<\/p>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Google Tools There are two Google tools that you can use to analyze the performance of your web site.\u00a0 One is Google Analytics, the other is Google Webmasters.\u00a0 There two systems give different views and statistics on your web<span class=\"ellipsis\">&hellip;<\/span><\/p>\n<div class=\"read-more\"><a href=\"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/\">Read more <span class=\"screen-reader-text\">Analyzing Google Webmaster Statistics with Spreadsheets<\/span><span class=\"meta-nav\"> &#8250;<\/span><\/a><\/div>\n<p><!-- end of .read-more --><\/p>\n","protected":false},"author":53,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-1261","post","type-post","status-publish","format-standard","hentry","category-soe"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications\" \/>\n<meta property=\"og:description\" content=\"The Google Tools There are two Google tools that you can use to analyze the performance of your web site.\u00a0 One is Google Analytics, the other is Google Webmasters.\u00a0 There two systems give different views and statistics on your web&hellip;Read more Analyzing Google Webmaster Statistics with Spreadsheets &#8250;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/\" \/>\n<meta property=\"og:site_name\" content=\"PHP Web Applications\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-15T08:48:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-08-28T07:48:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page-300x147.png\" \/>\n<meta name=\"author\" content=\"Paul Gibbs\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Gibbs\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/\"},\"author\":{\"name\":\"Paul Gibbs\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/#\\\/schema\\\/person\\\/b4df539cfb6debde6d0373daadfeb470\"},\"headline\":\"Analyzing Google Webmaster Statistics with Spreadsheets\",\"datePublished\":\"2019-08-15T08:48:42+00:00\",\"dateModified\":\"2019-08-28T07:48:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/\"},\"wordCount\":690,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/webmasters_main_page-300x147.png\",\"articleSection\":[\"SOE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/\",\"url\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/\",\"name\":\"Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/webmasters_main_page-300x147.png\",\"datePublished\":\"2019-08-15T08:48:42+00:00\",\"dateModified\":\"2019-08-28T07:48:52+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/#\\\/schema\\\/person\\\/b4df539cfb6debde6d0373daadfeb470\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/webmasters_main_page.png\",\"contentUrl\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/wp-content\\\/uploads\\\/2019\\\/08\\\/webmasters_main_page.png\",\"width\":1546,\"height\":755},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/analyzing-google-webmaster-statistics-spreadsheets\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Analyzing Google Webmaster Statistics with Spreadsheets\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/#website\",\"url\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/\",\"name\":\"PHP Web Applications\",\"description\":\"Information and support for products of WithinWeb.com\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/#\\\/schema\\\/person\\\/b4df539cfb6debde6d0373daadfeb470\",\"name\":\"Paul Gibbs\",\"sameAs\":[\"http:\\\/\\\/www.withinweb.com\"],\"url\":\"https:\\\/\\\/www.withinweb.com\\\/info\\\/author\\\/azpg27\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/","og_locale":"en_US","og_type":"article","og_title":"Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications","og_description":"The Google Tools There are two Google tools that you can use to analyze the performance of your web site.\u00a0 One is Google Analytics, the other is Google Webmasters.\u00a0 There two systems give different views and statistics on your web&hellip;Read more Analyzing Google Webmaster Statistics with Spreadsheets &#8250;","og_url":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/","og_site_name":"PHP Web Applications","article_published_time":"2019-08-15T08:48:42+00:00","article_modified_time":"2019-08-28T07:48:52+00:00","og_image":[{"url":"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page-300x147.png","type":"","width":"","height":""}],"author":"Paul Gibbs","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Paul Gibbs","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#article","isPartOf":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/"},"author":{"name":"Paul Gibbs","@id":"https:\/\/www.withinweb.com\/info\/#\/schema\/person\/b4df539cfb6debde6d0373daadfeb470"},"headline":"Analyzing Google Webmaster Statistics with Spreadsheets","datePublished":"2019-08-15T08:48:42+00:00","dateModified":"2019-08-28T07:48:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/"},"wordCount":690,"commentCount":0,"image":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#primaryimage"},"thumbnailUrl":"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page-300x147.png","articleSection":["SOE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/","url":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/","name":"Analyzing Google Webmaster Statistics with Spreadsheets - PHP Web Applications","isPartOf":{"@id":"https:\/\/www.withinweb.com\/info\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#primaryimage"},"image":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#primaryimage"},"thumbnailUrl":"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page-300x147.png","datePublished":"2019-08-15T08:48:42+00:00","dateModified":"2019-08-28T07:48:52+00:00","author":{"@id":"https:\/\/www.withinweb.com\/info\/#\/schema\/person\/b4df539cfb6debde6d0373daadfeb470"},"breadcrumb":{"@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#primaryimage","url":"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page.png","contentUrl":"https:\/\/www.withinweb.com\/info\/wp-content\/uploads\/2019\/08\/webmasters_main_page.png","width":1546,"height":755},{"@type":"BreadcrumbList","@id":"https:\/\/www.withinweb.com\/info\/analyzing-google-webmaster-statistics-spreadsheets\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.withinweb.com\/info\/"},{"@type":"ListItem","position":2,"name":"Analyzing Google Webmaster Statistics with Spreadsheets"}]},{"@type":"WebSite","@id":"https:\/\/www.withinweb.com\/info\/#website","url":"https:\/\/www.withinweb.com\/info\/","name":"PHP Web Applications","description":"Information and support for products of WithinWeb.com","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.withinweb.com\/info\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.withinweb.com\/info\/#\/schema\/person\/b4df539cfb6debde6d0373daadfeb470","name":"Paul Gibbs","sameAs":["http:\/\/www.withinweb.com"],"url":"https:\/\/www.withinweb.com\/info\/author\/azpg27\/"}]}},"_links":{"self":[{"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/posts\/1261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/users\/53"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/comments?post=1261"}],"version-history":[{"count":52,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/posts\/1261\/revisions"}],"predecessor-version":[{"id":1518,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/posts\/1261\/revisions\/1518"}],"wp:attachment":[{"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/media?parent=1261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/categories?post=1261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withinweb.com\/info\/wp-json\/wp\/v2\/tags?post=1261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}