Originally published on my personal blog August 4, 2014.
I worked on a website generating leads across the country and when I go into Google Webmaster tools and check out search queries, I have over 4,000 keyword phrases to dig through.
In general I don’t find this to be too much of an issue, but sometimes I need to dig around in the data to find some answers and displaying 500 queries at a time without being able to filter them is a bit difficult.
Luckily Google Webmaster Tools allows you to export the data to a spreadsheet which makes working with the information a bit easier. I exported the search queries into a spreadsheet then removed the change columns and the click-through rate column since I can calculate that easily enough myself. What I was left with is the search term someone used in Google, the number of times the term brought up the site, the number of times someone clicked on the site, and the average position the site has in the search engine.
Talking in abstract is a bit annoying so let’s assume my lead generating website is getting applications for unicorn kisses. The site is optimized so when someone searches for location and unicorn kisses, it ranks. For example, a search for Illinois unicorn kisses, unicorn kisses in Denver, or online unicorn kisses the site shows up somewhere in the search engine results pages (SERPs) … ideally on page one, but that is a lofty goal since there are so many sites fighting for real estate on page one.
When the company needs to pull in more leads from specific areas, I find it helpful to know what keyword phrases I am already ranking for in those areas so I know what I can work on for a quick win.
With the data in Excel, I make two adjustments to the sheet. I first create an input cell where I can enter whatever word I want to search all the queries from Google Webmaster’s for and in a column at the end of the exported data labeled Phrases, I add this formula: =IF(ISNUMBER(SEARCH($A$1,A3)),”Keyword Phrase”,””)
The resulting spreadsheet looks something like this:
Query | Impressions | Clicks | Avg. position | Phrases |
online unicorn kisses | 1402 | 166 | 2 |
I can then filter the entire 4,000+ queries for “Keyword Prase” to find the search queries people have used which brought the site up in Google. I can then see how often the term was used and what the average position of my site is for that search term.
With this information I can answer a few questions about the site’s performance. For instance, if I notice a lot of impressions and the site is on the first search result page, but not getting any clicks, then I know there is something lacking with the title or metadescription (or the competition is just crushing it with the perfect site for the query).
Let’s walk through an example. What if we want to increase our leads for unicorn kisses in Arizona? My first step is to pull the most current search query data from Google Webmaster’s and set up the spreadsheet. (Okay, honestly, I’m probably a bit lazy and use the spreadsheet from the previous week, but you will be more diligent and get the latest data.)
I then search the terms for the word “Arizona” and get the following results.
Arizona | ||||
Query | Impressions | Clicks | Avg. position | Phrases |
winged unicorn kisses arizona | 1 | 0 | 4 | Keyword Phrase |
white unicorn kisses tucson arizona | 14 | 0 | 22 | Keyword Phrase |
online unicorn smile arizona | 21 | 0 | 27 | Keyword Phrase |
unicorn hugs arizona | 1 | 0 | 28 | Keyword Phrase |
unicorn kisses online arizona | 70 | 0 | 31 | Keyword Phrase |
silver unicorn kisses tucson arizona | 13 | 0 | 31 | Keyword Phrase |
silver unicorn kisses in tucson arizona | 39 | 0 | 33 | Keyword Phrase |
online unicorn kisses arizona | 41 | 0 | 34 | Keyword Phrase |
online unicorn kisses in arizona | 56 | 0 | 35 | Keyword Phrase |
online registration kisses in arizona | 21 | 0 | 35 | Keyword Phrase |
unicorn smiles arizona | 16 | 0 | 35 | Keyword Phrase |
unicorn kisses in tucson arizona | 14 | 0 | 35 | Keyword Phrase |
unicorn kisses tucson arizona | 14 | 0 | 35 | Keyword Phrase |
arizona unicorn hugs | 4 | 0 | 35 | Keyword Phrase |
unicorn kisses in arizona no alignment check | 1 | 0 | 38 | Keyword Phrase |
how do unicorn kisses work in arizona | 14 | 0 | 40 | Keyword Phrase |
silver unicorn kiss tucson arizona | 9 | 0 | 40 | Keyword Phrase |
Normally at this point I would take a look at the phrases on the second results page (average position 11 to 20), but you can see, this site has nothing ranking on the second page for the term. It is obvious why this site isn’t getting many leads for unicorn kisses in Arizona.
Looking at the terms bringing the site up on the third results page (average position 21 to 30) we can see there are three: white unicorn kisses tucson arizona, online unicorn smile arizona, and unicorn kisses online arizona.
I can perform these searches in Google and find out exactly which of the site’s pages are ranking for them. I can then tweak the page to try to boost the position in Google. If the page were on the second page, it might only take a rewriting of the title tag or H1 tag to get it to tip onto the first search result page. Since these are on the third search result page, something a bit more drastic might be called for. I might have to rewrite the page, adding more to the page to flesh it out.
This is just the first pass through in the process. I would next go through and find where each major city is showing up in my search query data and optimize or tweak the optimization on the pages to improve the page’s ranking in Google. Using this technique, I can even find gaps in the site. Maybe there are cities where the site doesn’t rank at all and new content will have to be added. I might discover I am ranking a bit too well for unicorn smiles and unicorn hugs which aren’t the site’s core product and thus getting traffic which won’t convert.
Is this useful to you? I’d like to know. I shared the spreadsheet with a coworker who saw value in processing the data in this way, especially when drilling down to specific locations and what terms are producing results. I’d like to know if anyone else is able to get use out of this simple technique for playing with the Google Webmasters’ search query data.