SEO

How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

We have that handy “Search Queries” link in Google Webmaster Tools that is supposed to show for which keywords our website does well on Google. Besides, the tools shows “Average position” for each keyword. The option has been widely discussed – and more often than not it arouses some kind of a doubt: people seem to see few words that actually generate search traffic.

Today’s (somewhat geeky) Excel tutorial will help you estimate how Google Webmaster Tools data is close to the reality: today we will learn to merge that data with your Google Analytics “Google search traffic” column.

In this article, I will show you how to merge two Excel documents the way that your “real” traffic numbers (from Google Analytics) are added to keyword list generated from Google Webmaster Tools (for you to compare the numbers from two tools)

Attention: you can use this tutorial to merge any type of statistics data: Keyword Rankings and Keyword Volume, Google Rankings data and Traffic data, Backlinks and Traffic Sources, etc, etc – so I strongly recommend to give me a few minutes to try to explain everything in an easy-to-understand language.

Step 1: Export Your Data

This step is the easiest one: go to Google Webmaster Tools, select your site, click “Your site on the web” to expand it and select “Search queries”. Now scroll down to the bottom of the page and click “Download this table”.

In your Google Analytics, go to “Traffic Sources” and select “Google”. There click on “Export” and select CSV.

You are almost done. Depending on which Excel version you are using, you may also choose “Text to column” option in “Data” menus to break the data into columns. Now, delete those rows you don’t need.

Step 2: Create 2 Lists of Data

Now you need both tables in one Excel document (to be able to apply the formula). So: Create two sheets and name them accordingly to be able to identify each one in the formula

I ended up with two sheets / lists: GWT (for Google Webmaster Tools data) and GA (for Google Analytics Data).

excel merge 01 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

Now just copy paste the whole tables in the corresponding sheets / lists (you may call them tabs as well).

Step 3: Apply the Formula

This is the complex step: start following!

First, let’s make it clear again what we are going to do: we want to add visit numbers from GA (Google Analytics) sheet to the corresponding lines in the GWT (Google Webmaster Tools) sheet.

This means that the formula we are creating will:

  • Look up a keyword in the GWT sheet;
  • Find the exactly same phrase in the GA sheet;
  • Grab the number next to it in the GA sheet;
  • Add it next to that phrase in the GWT sheet.

Still following?

So what you should do next is to create a new column (where the visit number is going to be pasted) in the GWT sheet:

excel merge 02 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

The formula we are going to use: VLOOKUP

So in your new column (D2 cell on the screenshot) start typing

=VLOOKUP(

and you will be promptly suggested the syntax:

excel merge 03 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

Note: depending on the Excel versions you may see commas between the arguments.

So let’s quickly describe each argument and build the formula:

  1. lookup_value: What value do you want to look up? In our case, we need to look up the word in A2 cell;
  2. table_array: Where is the lookup table? What I did here was just going to GA sheet and select the whole table there. The formula would automatically add: GA!A2:B101
  3. Important: If you use an absolute reference ($A$2:$B$101), instead of a relative reference (A2:B101), it will be easier to copy the formula to other cells. To use the absolute reference, just click F4 – very easy!

  4. col_index_num: Which column has the value you want returned? In my case, that’s 2:
  5. excel merge 04 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

  6. [range_lookup]: Do you want an approximate match? In our case, you are likely to want the exact match, so just type FALSE here.

So we ended up with the following formula:

=VLOOKUP(A2;GA!$A$2:$B$101;2;FALSE)

Click ENTER and you should see either the numeric value (that stands for the number of visits from the GA tab) or #NA (if there’s no such a phrase in the GA tab). Now just copy the cell with the formula and paste it to the whole column “Visits”.

Here’s my result (as you can see only 11 phases from the Google Webmaster Tools report generate some actual traffic to my website):

excel merge 05 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)
Ann Smarty is the blogger and community manager at Internet Marketing Ninjas. Ann's expertise in blogging and tools serve as a base for her writing, tutorials and her guest blogging project, MyBlogGuest.com.
 How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

You Might Also Like

Comments are closed.

8 thoughts on “How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

  1. Great post Ann. I also found it useful to measure the “real” click through rate by calculating impressions against Analytics visits. Gives a better idea for each market niche, and it's probably a good measure for testing different titles/meta descriptions to see if CTR is affected.

  2. I like the extra data, but it was also nice to have an average position displayed without making any extra clicks. Any way I can do this without clicking individual terms?