One of the best SEO books I’ve read in recent years is “50 ways to make Google love your website” by Steve Johnston and Liam McGee and it’s available from Amazon UK here. Cheesy name I know but don’t be fooled by it: if you’re interested in SEO then get this book.
The thing I use most often from this book is the keyword research tool. In this post I’ll explain how the tool works and give it to you to download as a ready-made Excel spreadsheet.
Keyword research tool?
At its most basic level, optimising a website is about keywords:
- Figuring out which words your target audiences search with when they’re looking for your product or service
- Creating pages on your website that are clealy themed around that keyword and similar keywords, making it as easy as possible for search engines to understand what the page is about
- Coming up with ways to attract links from high quality sites into your pages, with anchor text that’s relevant to the keyword. ‘Anchor text’ is the words in the link and using a keyword rather than ‘click here’ gives a clue about where the link might take you.
The book suggests how to build a keyword list from sources other than your own website. This is helpful if you’re building a new site or promoting a new service from the ground up. The sources include:
Much of our work is with organisations that already have websites, and they want them to perform better. As part of reviewing current performance it can be helpful to reveal the words that visitors actually use to find the site.
Analysing which keywords people use to find your website
Enter Google Analytics, the free tool that you can use to show how people found your site and what they did when they got there.
To see the keywords that people used to find your site – and once you’re logged into Analytics using the new interface:
- select ‘Traffic sources’ from the left hand nav
- chose a traffic source: overview, organic or paid
- set the date range (top right hand corner of the interface)
But this list is only semi-helpful… being able to detect search patterns amongst the keywords would be really helpful.
How to detect keyword patterns using an Excel spreadsheet
First, download the keyword list into an Excel spreadsheet. Do this by:
- Making sure you can see the full list of keywords on the screen. Do this by setting the ‘show rows’ drop-down (in the bottom right hand corner) to a number bigger than the total number of keywords in the list
Ah. Problem. There are 2095 keywords in the list in the screen-shot and Google Analytics allows a maximum view of 500. If you try to download you’ll only get the first 500 keywords.
You can get round this by changing the URL string: just change the number at the end of the string from 500 to, in this case, 2095
Using the drop-down at the top of the screen you can now export the whole lot to Excel. For me, the ‘export to CSV’ seems to work better than ‘CSV for Excel’ option.
Anyhow you should now have an Excel spreadsheet listing all your keywords together with the number of visits:
Next, delete the rows at the top and all the columns except ‘Keyword’ and Visits, so you’re left with this:
OK, thanks for being so patient, this is where it gets really interesting
Download this Excel spreadsheet, which contains the formula from the ’50 ways’ book. It should look like this:
See where this is going?
Copy your keywords and the number of visits into columns A and B, starting at row 3 so you don’t overwrite anything. The formula in cell B1 should sum up all visits from all keywords, so make sure that it includes all the rows in your list – ie rows 3 to 2098 in the above example. The formula in the other cells in row 1 should sum up all the rows below it so when you’ve tweaked B1, copy the formula across to the other cells in row B.
Now, choose a keyword that you want to test for. I’ve put together a silly example below but it shows the point:
When you put the keywords you want to test for into the cells in row 2 – starting at column C, the spreadsheet sums up how many times the word appears in all the keywords.
And finally…
My clients often have an expectation around which keywords their visitors are likely to use, or should be using, to find the site. And those expectations can be wildly off the mark. This tool provides a great way to open up a discussion critical to SEO and conversion optimisation. What other tools have you found to be effective?
Written by Ned Wells
Tags: Free SEO tools, Liam McGee, Steve Johnston