My name is Jim Kensicki, and I’m a spreadsheet addict.
When doing keyword research for a client, I often find myself working in three or four tools simultaneously, on two monitors, exporting data like there’s no tomorrow. #dataoverload
Over time, and with the help of many Excel forums, I’ve put together a spreadsheet that helps me distill all of that data into a focused list.
Rome wasn’t built in a day. While you can’t be No. 1 for the keyword “car” overnight, you might be skipping over a small set of high-volume keywords that will get you to the top of search engine results pages. Here’s how I find them:
I start in Google’s Webmaster Tools (WMT), but you could also use your trusted tool of choice for keyword rankings. I like WMT because it gives me a massive data set to work with and long-tail keywords that I wouldn’t have known about otherwise.
Next, I export the top 2,500-5,000 non-branded terms. You can exclude your brand name within the filter in the middle of the page. Keep misspellings and variations in mind. Export and throw that into a newly created Excel spreadsheet with a WMT Data tab. At this point, I usually filter out keywords where the avg ranking is > 30. I want low-hanging fruit that I can attack and get results with immediately, but you can leave those in for now if you want.
Now, you need to find your little keyword gems. Go into Google Analytics > Traffic Sources > Overview and click on “view full report” at the bottom. You want a big data set to work with, so adjust the date range accordingly. I’ll generally pull data from the most recent full quarter. I tend to include Paid traffic too, but it’s not absolutely necessary. If you want to exclude it, make sure you’re working in the Organic page within the Search section.
The top keyword driving traffic will be (not provided). Thank you, Google. Tremendously helpful. In most cases, the next few keywords will be branded terms. We’re going to do the same thing here as we did in WMT – exclude your brand terms using the search function in the middle of the page or via an advanced segment.
This is where it gets interesting. If you track conversions or have ecommerce tracking setup, you can click on Goal Set 1, 2 or ecommerce at the top to look at correlating data. If you don’t have conversions setup (SHAME ON YOU!), you can still find your money keywords. As part of your advanced segment or search filter, we’re going to use the dimensions bounce rate and time on site. So it looks like this:
What you choose for the actual filter is up to you. For this example, I filtered keywords where visits exceeded 3 minutes and bounce rate was lower than 40 percent. Depending on the amount of traffic you get, you should still have a decent data set to work with. You can also pull out keywords with high conversions, or ones that went to core landing pages as part of their visits. Get creative.
Let’s export this into a CSV and dump it in the KW behavior tab in the Excel tool.
If you’re still following along, this is where we’re going to start combining that data in our master sheet. The first formula I use looks like this:
=IF(ISERROR(MATCH(A1,’KW Behavior’!A:A,0)),”NO”,”YES”)
What I want to do is find keywords that were in the first spreadsheet we exported from Webmaster Tools that also exist in our “money keywords” tab. Once you’ve done this, you can filter for keywords that have a “YES” in this column – or terms that are in both data sets.
Now our list of 2,500 is down to 21. If you still have a very long list, you can narrow it down by filtering for keywords with average positions between 8-15. If you’re at the bottom of page 1, top of page 2, you’re on the cusp of getting exponentially more traffic (potentially) by moving up only a few positions.
Now we need search volume. Throw the remaining keywords into the Google Keyword tool. I like using Phrase versus Exact Match to give me an idea of volume around related long-tail versions of select keywords. Make sure to click “only provide terms related to your search phrase.” If not, you’re going to get a massive list of irrelevant keywords. You could just export the 20 or so terms, but I like to pull everything out and put it in the Google KW Tool tab. I’ll likely end up referring back to the longer list of 100 terms that I exported later for possible long-tail targets.
You can now pull that data into the first tab we were working in by using a VLOOKUP code. The formula looks like this:
=VLOOKUP(A1,’Google KW Tool’!A:C,2,FALSE)
Basically, the formula is searching for the keyword in A1 of our Google KW spreadsheet and when it finds a match, it returns whatever is in column 2 (search volume in this case).
You can then filter for search volume- I’ve filtered out anything with fewer than 500 searches per month.
After running this process, I narrowed down my keyword list to five terms, in positions 7 to 12, that result in extremely low bounce rates and very high average visit durations. This will help me develop an SEO content marketing campaign that drives organic traffic back to my site and using keywords naturally within each blog article update.
