Matching Google Webmaster Tools and Analytics keywords data

This is the first version of the SEO dashboard or the first one of the set, if I finally decide to have them separated for a more easy usage.

While working on ideas for the Ultimate SEO dashboard and having in mind that keyword analysis is getting harder thanks to Google reporting 'not provided' keywords in a scandalous percentage that question came back to me again: How can we merge keywords data from WMT and Google Analytics to pull more insight from it?

Google knows this information is relevant for SEOs and they don't want to make it easy for you. Unless they change how WMT information is integrated in Google Analytics or the GA API is updated to merge both sources it is impossible by now as far as I know

So I had one of those Vicky the Viking moments (my European fellows will understand the reference): NextAnalytics Excel plugin to the rescue! And it works like a charm although it requires with a bit of manual work. Follow instructions ahead.

How to make the SEO Keywords Tool work

1. Download the keywords tool dashboard excel file.
(What you see there is a sample of data from this web, nothing relevant).

2. Get the NextAnalytics Excel plugin, required to make it work.
Download a trial for free or buy it, it is the best money I ever paid for a tool helping me analyse SEO and Social Media. (Thanks Mike and Ward, amazing customer support, for enduring patiently my insidious questions)

3. Decide the period to analyze selecting dates, let’s say past month.
Google Analytics > Traffic Sources > Search Engine Optimization > Queries

4. Exporting the WMT data.
You will find out that you have more keywords than the ones you can list but there is a trick for that.

Export Webmarter Tools data from Google Analytics

Select any number higher than 10 at "Show rows:" drop down selector. Then go to the URL input of your browser and change the last number between the D and the / to match number of total keywords, 1102 in this case.

explorer-table.rowCount%3D50/ -> explorer-table.rowCount%3D1102/

Export Webmarter Tools data from Google Analytics

Then you can export CSV for Excel and save it to your computer.

Export Keywords from Google Analytics

5. Import WMT data into the dashboard.
Clear "keywords-WMT" tab or worksheet of the dashboard. Important, this is the worksheet where we want the data coming from WMT or it won’t work.

At Data menu in Excel, select Get External Data > From text and follow instructions to get keyword’s data there. Delimiter is Comma, not Tab.

Import WMT keywords data into Excel

Delete first 6 rows to move everything up and leave Query, Impressions, Clicks, Average Position, and CTR at the first row. Also delete last row having totals.

Remove irrelevant rows from keywords imported data

Manual import finished.

6. Configure Google analytics connection at "GoogleData_actions" worksheet.
Enter the email and password you use to access Google Analytics. Enter the Profile ID.
IMPORTANT, it is not the typical UA-9415164-2. Two ways to get it:

a) Navigate to any report in Google Analytics, take a look at the URL and find the number between p and /

trafficsources-overview/a9210124w20511295p18438502/%3F_.date

Find Google Analytics prodife ID in URL

b) You can either use the Next Analytics plugin to reveal it.

NestAnalytics Google Analytics Excel plugin

Google Analytics Profile ID in Next Analytics

7. Configure details
Enter the dates of the period to analyse, first and last day matching the period you selected at step 3 while exporting WMT data.

If you wish to remove some keywords, branded ones for example, use the filter the same way you would be doing it in Google Analytics, otherwise leave it blank. This will filter the keywords you already have from WMT and the ones to come from GA.

8. Hit "Refresh" button and let the magic happen.

NextAnalytics refresh Excel data

What data and insights you get from the keywords

Data from Google Analytics and Webmaster Tools is matched by keyword so you have several columns ordered by visits.

SEO dashboard with Keywords

  • Query or Keywords
  • Impressions, Clicks, Average position and CTR, numbers in red, from WMT
  • Visits, Pageviews per Visit, Avg Time on Site, Bounce Rate, and All Goals Conversion Rate from GA (if you have goals properly set conversion rate numbers will appear) in blue

Insights you can get from keywords

Bad news first: keywords bringing traffic from organic not reported in WMT. We knew WMT is providing inaccurate or partial data. Here you are going to find how much.

Now the good ones.
The whole picture, from impressions to conversion and everything in between. Again, not perfect but at least you have both sides of the story, what happens at SERPs and after the click.

Keywords making your site appear at SERPs but not bringing any traffic (bottom of the worksheet) what means room to improve your content optimization. Some of those will have quite high Average Position values but a bunch of them are probably having positions in third, fourth page that could yield some visits/conversions with a bit of effort.

Relevant keywords quite related to your business core with a high Average Position and no traffic. Sorry, this means something is quite bad in your optimization for those.

Keep on digging on your data playing as much as you want with more advanced Excel features like filters or add other columns with the calculations you would like to include.

I hope you find it useful. Any constructive comment is appreciated and will be added in later versions of this SEO Dashboard. Have a great content optimization!

Nov 29, 2011
Written by:
Filed under: Analytics






8 comments
Nov 30, 2011
Posted by:
Atniz #1

Good introduction to export and import in excel on data collected from WMT or Analytics.

Nov 30, 2011
Posted by:
Martijn Beijk #2

Hi Ani!

Any experience with exporting the data from WMT (either via interface or if even possible API?) instead of using GA to do so? I want to do a similar trick, but ruling out GA in the process.

Nov 30, 2011
Posted by:
Ani Lopez #3

Hi guys, thanks for dropping by and comment.

@Martijn no API, WMT or GA, lets you get that data (as far as I know). Exporting manually from WMT was more painful than getting the same numbers from GA > Search Engine Optimization > Queries.

No way amigo; that's the reason why you have to do it manually.

Dec 01, 2011
Posted by:
Martijn Beijk #4

Hi Ani,

Let's just say I am not using GA. Getting the numbers from WMT is something I should focus on then. Let me know if you have heard or found a way how to do this automatically.

Dec 16, 2011
Posted by:
Ani Lopez #5

Hi Martijn, you can export the WMT numbers too Your Site on the Web> Search queries > Download this table but no more than a month while in GA you get 2 months data.

Feb 16, 2012
Posted by:
Lois #6

Hi,

Thanks for the post, but... I am trying to export 23.000 keywords from GA, I've tried to change the number between "D" and "/" in the url as you said, but it just don't work. Itjust export me the number of keywords that I can see on my screen
Any idea why is that?

Feb 19, 2012
Posted by:
Ani Lopez #7

Hi Lois
Take into account that the browser has to render/show in screen all the keywords you asked when changing the end of the URL to indicate amount of keywords 'explorer-table.rowCount=20000/' before you can export them.

Handling 20,000 rows or more it is not an easy task for any browser.

Firefox (I'm using now V10) tends to crash over 8,000 keywords

Chrome does it pretty well between 10,000 y 15,000. A bit wonky between 20,000 and 25,000 but finally manages to do it so you are able to export.

The question is, what are you planing to do with such a large amount of keywords? it's insane!

Jul 19, 2012
Posted by:
najrellim #8

Thank you so much for a great tutorial and tool. Since WMT can pull the actual keywords from my pages while GA only pulls search queries, is there a way to use WMT and GA to get the same traffic data but match to my actual keywords from my content?

Have your say
Submit
twitter @anilopez

Articles I write for other sites

On Paella and Semantic Markup for recipies

In plain words, it does not work fine most of the cases. It's a bad idea. I'll explain why while I teach you how to cook an authentic Paella.

Analytics Tribulations Of An SEO

The art of measure is never easy but when it comes to SEO it's even worst

Some decisions to take beforehand on multilingual SEO @ Cardinal Path blog

Hold on! Sure, you’re excited to get your content online, but stop asking for a site to be built, and think about its audience especially when it comes to international SEO.

Challenges of Spanish Language on Search Marketing @ Multilingual Search

'Standard Spanish' is something that I don’t buy into when it comes to international scenarios. I'll explain to you why and some tips to start facing correctly your Spanish strategy.

50 feeds keeping me updated on SEO

Besides the feeds listed here I follow around 30 more for topics like Analytics, UX, Link building, IA or any other SEO related discipline

Handling Multilingual Sites for Humans & Search Engines @ Bruce Clay Blog

The logic behind the scenes to show all content to bots and the right language to users

Mobile detection issues & Google Instant Previews @ Cardinal Path blog

Mobile web represents the bigger headache ever for those wanting to target the small but growing audience they represent nowadays. check your Instant Previews for possible indexation issues.