Analyzing Google Webmaster Statistics with Spreadsheets

The Google Tools

There are two Google tools that you can use to analyze the performance of your web site.  One is Google Analytics, the other is Google Webmasters.  There two systems give different views and statistics on your web site.

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.

Once Google is providing statistics into the webmaster tool, you can start analyzing the data.  However, you may have to wait for several months before you do a proper analyses and see trends.

Google Webmasters provide statistics on how your web site is performing with Google Search.  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.  All of these give you an understanding of search engine performance for you web site.

Downloading Your Data

The following assumes that you have already set up your web site and that data is available.

1) Log into Google Webmasters using your Google account.

The Search Console will be displayed.

2 ) Click on the Website that you want to analyze.

In the left hand navigation column, click on “Performance” and in the right hand side, scroll down to where you will see a list of “Queries” with the number of clicks and impressions.

3) Go to the “Download” icon and click on it.

4) Save the download as either a csv file or as a Google Sheets file.  I normally download as Google Sheets format and then open it up in an old version of Microsoft Excel to do the calculations.  However, you can just work with Google Sheets as it does the job just as well.

5) Sort the complete spreadsheet in order of “Impressions”, highest to lowest.

6) Add in a new column called %Impressions.

We want to calculate each value as a percentage of the total impressions.  So for each entry:
( Impression Cell Value )  / SUM( All Impression Values ) * 100

7) Add in a new column called %Cumulative

We want to add up all the cumulative values using the formulae =
%Impression + %Previous Cumulative Value

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.

Doing The Analyses

We can now look at our listing and see which of the keywords in the top 50% have low CTR (Click Through Rate).  We can see that these keywords are giving high number of impressions on our site, but some are not returning good CTR.

We can look at these queries, and then try to work out how we can improve the click through rate.

The Other Downloads

From the same location we can download “Queries”, “Countries”, “Pages” and “Devices”.  From each one we can investigate different issues. 

The first thing to notice is that if you add up the impressions in each one, you will notice that they are different.  So what is going on here?

Well, Google has a page at:

There is a section called “Data Discrepancies” which gives some explanations for the differences.  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.  There is also loss of data for other reasons, such as excluding queries with “private” data, time lag differences in data processing, different processing techniques for different data sources and so on.

Comparing Across Dates

Luckily for us, Google has provided us with features to compare one set of data with another over time.

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.

The resulting csv file provides with a wealth of information of changing data over time.

Example Spreadsheet File

If you click on the following link you can download and example xls spreadsheet which shows the spreadsheet formula.

Click here for example spreadsheet.


Posted in SOE

Leave a Reply