In the past, Google Analytics and Webmaster Tools did not like working together. Today, they must learn to play nice with one another to change an industry’s culture on reporting trends.
Both Google Analytics and Webmaster Tools provide keyword-specific data. Combining keyword data from both sources, and grouping like-terms together, can provide a much more complete picture of your website keyword activity.
SEO is harder and more complicated
Grouping keywords for reporting is nothing new. Most tracking tools offer some sort of tagging feature, but you need to add in the actual keywords and group them yourself. This method is focused on what you want to see, which is not always capturing the full picture of online keyword activity coming to your website.
We have developed a Lethal Weapons Spreadsheet (.xlsm, 4.6 M) that you can use to simply copy and paste keyword data from both Analytics and Google Webmaster Tools to provide a more comprehensive, group picture of search engine activities. Below is an example of a final trending report.
How does this thing work?
Collecting the data
Collecting the data
The first step is to gather your data from Google Analytics and Webmaster Tools. Save each data set for use later.
For Google Analytics, all you need are the Keywords and Visits. You can find this information in the “Organic” Traffic Source report. Remember you will need to show “all” rows in the table before you export to CSV. Google Analytics will only export what is visible on the page.
For Webmaster Tools, we are only using the Impressions and Clicks. This info is under Search Queries, and you will need to select “Download this table”. Keep in mind that Webmaster Tools only stores the past 3 months of data.
Important – As you are pulling from two different data sources, make sure your date ranges match.
The next step is to understand how the spreadsheet, you just downloaded, works and how to setup keyword groups. In the spreadsheet you will see several “Tabs” (or Worksheets). You should only care about the Tabs named “Original Data”, “Summary” and all the “Page#”.
Original data tab
This is where you copy and paste your Google Analytics and Webmaster Tools keyword data.
It’s a good idea to copy over one of the data sets into the spreadsheet before setting up keyword groups. This will allow you to see data appear while you are setting up the keyword groups on the Page# tabs.
Note – The spreadsheet does make a small assumption in regards to Webmaster Tools data. Anything that has a volume of <10, the spreadsheet assigns a value of “2”. This is needed to be able to sum up the numbers.
This is where initial hard work comes into play; setting up your keyword groups. The spreadsheet allows for 100 groups. (Once you have this setup, future trending is a snap!)
Add a keyword and you should see a number appear in the Impressions/Visits column (& Clicks for you are look at GWT data).
Important Note – The Keyword column is setup to pull back “broad” match data. Meaning, if you add in the keyword “red”, you will see all the data for keywords with “red” in the phrase. You should assume there will be a small keyword cross-over in this type of setup.
The Summary Tab is the one-stop place for all the Page# Tabs. You will need to “Tag” each of your Page#s with the related Keyword Group. This allows you to easily copy and paste the results into your formal report in Word or Excel.
Once you are comfortable with the keyword group setup, the spreadsheet can be used over and over. Simply gather the data, copy it into the Original Data Tab and pull the numbers from the Summary Tab.
Lethal Weapon possibilities
If you are implementing a content marketing strategy, grouping keyword data together from Google Analytics and Webmaster Tool is a powerful trending tool. You are missing a large chunk of long-tail keyword activity to your website if you are only providing your clients (or management) keyword rankings reports as the gauge to determine success or failure of your efforts.
The data in this tool shows the real search engine traffic cycle to your website and allows you to change the conversation from rankings to overall results.
There are additional possibilities that we are testing for the future with this tool. We are adding in Average Position and CTR from Webmaster Tools and Goals and Ecommerce data from Google Analytics. Also, looking into incorporating Bing Webmaster Tools data.
The evolution of this tool could very well be registered as a Lethal Weapon as we trend and communicate success.
Download the spreadsheet.(.xlsm, 4.6 M) Give it a try and tell us what you think?
Chris Bross is a partner of EverEffect, specializing in Pay Per Click Account Management, SEO Consulting and Web Analytics. Chris has over 18 years of Internet experience with Fortune 500 companies and expertise directing and implementing online experiences to promote business, products and services. Feel free to connect with Chris through Twitter or on Google+.