New! Local SEM Competitive Intelligence
Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

Lethal Weapons – Keyword Insights From Google Analytics & Webmaster Tools

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

SEO (or Inbound Marketing) today is a mix of several tactics, including: technical SEO, social media and content marketing to name just a few. These tactics place more emphasis on long-tail keywords and cast a much wider keyword range within the search engines. It can be challenging communicating keyword trends and success on the different tactics that are implemented. Plus, Google search results are much more personalized to an individual’s tastes and location.

Old school SEO largely consisted of picking target keywords and an agency’s success or failure was based on achieving high rank on the selected terms. To achieve rank an agency would provide on-page optimization recommendations and then go to town on building backlinks. At the end of the day, a SERP report was provided showing ranking position on the target keywords. Pretty simple!

The problem with SERP ranking reports

A basic SERP ranking report lists all the target keywords and their associated rank/position within the search engines. Over time you could be following hundreds of individual keywords and the SERP report could be 20 pages long.

Everyone has seen this – boring!

Lethal-4Everyone has seen this – boring!
Everyone has been in “that” meeting where the client (or management) scrolls to page 9 and points out that no progress has been made on one or two specific keywords.

A few years ago Hitwise (now Experian) published a study stating that 70% of all searches are long-tail (it’s probably more now). It’s tough to demonstrate keyword success considering rankings reports provide such a narrow view of the activities implemented today.

The problem with ranking reports is that they only show what people want to see, not actual numbers reflecting efforts on your content marketing and long-tail keyword strategies.

Keyword groups are the real deal


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

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 spreadsheet

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#”.

The “Data” and “Keyword List” tabs are the control and functional aspects of the spreadsheet. We do not go into the explanation of code or macro setup as part of this blog post (sorry).

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.

Page# tabs

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

Lethal-7Add 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.

Summary tab

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.

On-going maintenance

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?


Guest Blogger Chris Bross
Guest Blogger Chris Bross
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 .