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.

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/

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

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.

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.

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

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

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.

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.

• 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!