Creative Digital Media

History is recorded moments in life. People’s thoughts, either great among others or just great in their own shadow. Every glimpse of life at that just moment, deserves to be treasured. So just “Keep That Thought”…

How to do Awesome SEO Keyword Research for a Standard Website

By , November 7, 2012

Keyword research is hugely important. The success of your entire SEO engagement depends on getting this piece of work right.

Why? Because an informed client will judge you on how visible their website becomes for the terms in their SEO keyword list, and that list is the product of your keyword research.

What Constitutes Good Keyword Research?

To improve keyword research, start with a really tight definition of what successful keyword research looks like. In order to say you’re a keyword research ninja, you should be able to look over your completed keyword list and say the following:

  • “I’m confident we’ll be able to get the client’s site to rank well against all keywords in the list.”
  • “If the client’s website becomes more visible in SERPs (search engine results pages) against the keywords in my list it will have a major impact on their total traffic/conversions.”
  • “I know which keywords I should be focus on first and which are less important.”

Kind of sound like you? Here’s some further acid tests:

  • Are there keywords you’re tracking but not really making any progress against, usually because the site isn’t ranked at all for the term? This is a bad thing, and your keyword list could be improved.
  • Would you have the time to implement keyword-by-keyword strategies designed to boost ranking for each individual keyword on your list before the end of your engagement, or is your list too long? Are you fairly confident your strategy is going to get you into positions 1-5 for said term? If the answer is no – that’s a bad thing!
  • Are there keywords on your list that your client won’t refer to on their website (e.g., cheap)? If the answer is yes, that’s a bad thing!

Why is Keyword Research Not This Good All the Time?

Another prerequisite of improving keyword research is to face up to what you’re currently doing that is holding it back from being the best it can be. So much of keyword research relies on “gut feeling” so the term “dirty keyword research” is a fitting term for any process that involves making decisions in your lower digestive tract.

The classic example of a “dirty keyword research” process would be something like this:

  • Build massive list of keywords using keyword tool and PPC data.
  • Remove terms that are “no-hopers” (e.g., competitor brand terms or unrelated phrases).
  • Order by volume.
  • Keyword list is only limited by the number of keywords your rank monitoring tool can handle. Priority is any term from the top of the list where your gut instinct is you could hit the top five.

This process is bad. It doesn’t look at anything other than potential audience, it certainly doesn’t favor the long tail or secondary keywords where we all know massive gains can be made, and fundamentally it sets you up for a big fall further down the road when your client doesn’t get the results they may have expected.

Here are some of the other major pitfalls of dirty keyword research:

  • To rank well for a keyword, you need to have a page that deserves to be the first result on the web for that term. This means you will need a content strategy for almost every term. With dirty keyword research, you inevitably end up with a list of terms that is so long you have no chance of impacting upon them all. You will get lost and have to explain to the client why there are keywords in the list that aren’t moving.
  • You will be drawn into focusing on head terms where it may be impossible to generate traffic before the client’s patience runs thin. Dirty keyword research doesn’t allow you to sensibly prioritize keywords. To move beyond prioritization by volume, you have to manually assess each keyword using your gut and maybe a cursory study of the SERP. This is manual, subjective, and means you’re essentially gambling that you’ve picked the right terms.
  • Dirty keyword research doesn’t allow for a regular reassessment of which keywords should be a priority, as search volume in Google’s keyword tool rarely changes dramatically outside of seasonal terms.

It’s understandable why keyword research is compromised in many instances; you need to get it right but can’t spend forever on it – a client is unlikely to spend large amounts of money which enables you to spend 16 hours on keyword research for a normal site. Giving junior staff keyword research projects that will take them forever is pretty boring – the longer someone spends on something they aren’t enjoying, the more the quality drops off.

The Keyword Research Dream

Imagine a keyword research process that:

  • Does away with the need for an SEO practitioner to manually assess keywords and make judgment calls on which keywords to include in the final list.
  • Puts Google Keyword Tool data into context.
  • Can be completed in half a day.
  • Can be quickly refreshed once movement in keyword position has taken place.

Make the Keyword Research Process Easy Enough for Your Grandma to Understand

This is important, because you can’t sell something your customer doesn’t understand.

Keyword research should always be broken down into two phases:

  • Keyword discovery
  • Keyword list refinement

There is lots of information on keyword discovery; people love posting about this stuff as soon as new tools become available. However, keyword list refinement is all too often neglected, or it’s undertaken subjectively. The key to building a great keyword list is applying the same rigorous tests evenly to every single keyword in order to truly understand their worth.

The light bulb moment: Great keyword research involves making keyword list refinement robust and efficient through the use of an algorithm based on several keyword metrics.

Keyword research is usually required in two client scenarios:

  • A standard website needs a keyword list building for a relatively straightforward SEO engagement. In this standard keyword research engagement you build a keyword list and then optimize individual pages for individual terms.
  • A brand has a huge volume of templated content (products, recipes, or articles are the usual suspects) and much of their site is based off a handful of page layouts. You need to drag up as many pages to a decent level of SEO best practice as quickly as possible with this programmatic keyword research, but there is no way you could ever monitor all the possible keywords in one tool.

The light bulb moment 2: You don’t have to have just one keyword research methodology. Different approaches should be used to get to the same great quality keyword list depending on the type of site you’re working with.

It’s so simple you can sum it up in one diagram!


Here’s a downloadable Excel template to help you execute the process described in the article. Here’s the process in a diagram.

How do you build a keyword list for a relatively normal site with limited amounts of template content? In these engagements you build a keyword list and then optimize individual pages for individual terms. Brand websites are a good candidate for this kind of keyword research.

But First, a Note on Search Volume

Search volume data from the Google keyword tool is bullshit. You know it, I know it, the whole industry knows it. You look up the search volume for a term, then log in to the tool and do the same thing again – the search volume is completely different!

The simple truth is: you shouldn’t rely on individual datapoints coming out of Google’s keyword tool. It’s really there for illustrative purposes only.

On the other hand, it’s the only real source of data on any keyword; Analytics is only going to give you data on stuff you already rank for. So you’re stuck with the Google keyword tool as your comprehensive and easily-accessible data source.

You can use a simple equation to try and reduce our reliance on individual data points that come from the Google keyword tool; we call it “relative search volume”.

relative search volume = individual keyword search volume / total search volume for all keywords

By dividing the search volume for the individual term by the total volume, you get down to focusing on the proportion of the total audience rather than the raw number of searches. It allows us to place the search volume in context, which is crucial for properly judging how important it is to target a keyword.

How to Undertake Keyword Discovery – aka Building Your Big-Ass Keyword List

Please note, in both forms of keyword research, it’s assumed that you have already been through the keyword discovery phase; as stated previously there are tons of good resources on this topic – check them out! Discovery can generally be broken down into discovery through the client or discovery through tools.

Discovery Through Your Account Manager/Client

Who better than the client themselves to help kick off the keyword research process? Just as we know our SEO, clients know their websites and the products and services they offer. They also know their brand and hopefully their customers and what they’re looking for when they visit the site.

In a discovery session you could cover these potential topics to aid keyword research:

  • High margin products/services.
  • Offline media initiative keywords/phrases
  • Previous SEO/keywords for pages

Discovery Through Tools

You know the form here, we’re talking about the usual sources: Google Instant searches, Google Adwords Keyword Tool, paid search data, paid-for discovery tools (e.g. SearchMetrics Essentials).

Once we have the big-ass list we can get to work refining our keyword list to a useful size, with a priority order built in.

Standard Keyword Research

Standard keyword research is all about subjecting your long list of keywords to a series of tests based on keyword metrics. Tests have various scoring systems, the scoring of which can be weighted to reflect your opinion of how important a factor is.

When coming up with keyword metrics there is one golden rule: you have to be able to get all the necessary data for all your keywords in one sheet. If you have to query each keyword in turn to generate the data then it’s not a suitable keyword metric.

For instance, “difficulty of ranking on page 1” would be a great metric, available from several sources, the most well-known being SEOmoz. Unfortunately this data can only be fetched by querying each keyword in turn. This disqualifies this metric on the grounds of it being too manual, until such time that a tool is developed for getting this data for keywords in bulk (hint hint, developers!)

Here are some examples of tests we can run. Does the client:

  • Rank for the keyword?
  • Rank in a position where there is opportunity for rapid traffic growth?
  • Have links with anchor text matching this term?
  • Enjoy a significant number of conversions from the keyword?
  • Make a large margin on the product / service described by the keyword?

And finally, does the keyword in question make up a large proportion of the total search audience?

The beauty of running several tests on your keywords is that it if a keyword isn’t suitable from one point of view, it can be from another – but everything is done in a consistent manner so you don’t need to argue with your team or your client over which keywords you’re going to target; they just rise to the top of the list naturally.

Even better, the structure of the table doesn’t need to change if you believe the system isn’t working, you can simply change the scoring system.


  • Paste your long keyword list in alphabetical order. Always work with the list in alphabetical order until the final stage.
  • Create a column for each test to the right. The formulas which work out the score for each test should sit in these columns, referencing the data on other sheets. Explanations for how you can run the tests in the table follow.
  • Run as many of the tests below as possible. I’ve tried to be exhaustive, but it isn’t hard to extrapolate this methodology to create test for keywords metrics not covered here.

Ranking and Opportunity Based on Ranking

Setup: In a seperate tab paste your alphabetical keyword list in Column A, and the corresponding rank in Column B. If it’s not ranking, make sure the rank = 0.

If you’re ranking for a term, that’s a good thing! It means your site has a page that’s relevant for the term – so you don’t need to spend time creating something from scratch, and you may well be able to improve ranking through some simple optimization.

In this test we reward terms that rank. This is done through a simple if statement. If ranking > 0, score 1, if not, score 0.

We also run another test on this data – how much of an opportunity is there to get major traffic? We know traffic from a keyword increases exponentially as ranking approaches the top positions above the fold – spots 1, 2 and 3.

Therefore, we should prioritize keywords ranking just outside these spots; a small ranking improvement here could generate a big traffic increase. Using nested if statements, you can score keywords; 5 points for ranking 4 – 10, 4 for 11 – 15, 3 for 16 – 20, 2 for 21-30 and 1 for beyond position 30. All other positions get 0 points – including those in positions 1 – 3, as we feel there isn’t a huge opportunity to move the needle on these terms; either it’s too competitive to change rank or there are bigger gains to be made from getting above the fold for other terms.

Competitor Ranking

Setup: exactly the same as for your own ranking data above.

In this scenario, we look at the competitor’s ranking and apply a negative score if a competitor is present on the first page of results. This is because we consider a keyword more of an opportunity if a direct competitor doesn’t rank on the first page. We usually choose the most visible site in the client’s sector as the competitor, so you know that in general you’re going to have more of a struggle on your hands if they’re on the first page.

In this scenario, if competitor rank > 0, we score the keyword -1, if not we score it 0.

Existing Anchor Text

Setup: paste a list of all the anchor text phrases used in external links to your site into a column in a separate sheet.

If someone is already linking to you using the exact same anchor text as the keyword you’re assessing, that’s a great signal that your site is relevant for that term, and that as a result it will be relatively easy to get more links with that keyword as the anchor text. Use a match formula to check if the keyword you’re assessing is an existing anchor text phrase. If it is, award 3 points, if not, the keyword gets 0 points.

vs. On-Site Search Terms or Paid Search Conversions

Setup: Same as for anchor text – grab the top terms which people searched within your site, or the terms from your paid account that drive conversions (you made need to exclude really low values) and paste them into new sheets.

If a user searches for a term once they have arrived at your site using internal search, that’s a pretty good indication the term is relevant. If one of our potential keywords appears in the top internal search terms, we award it 1 point.

It’s vitally important keywords that drive conversion are prioritized – if any of our long list keywords also appear in the top converting terms from paid search (exact match basis) we award 3 points. You could improve this further by penalising terms with a -1 score if the conversion rate for that term is low, as this would indicate the user’s searching for the keyword are not particularly relevant.

vs. Time on Site

Setup: Enable the non-paid search segment in your analytics. Download the top 500 keywords driving visits, and make a note of average time on site for the non-paid segment.

If the average time spent on site for a keyword is above the site-wide average for organic traffic, we score the keyword 1 point.

To find if the keyword you are testing exists within your user behaviour data and return the corresponding time on site or bounce rate figure use a vlookup formula, then in an adjacent column use an if statement to determine the keyword’s score based on its value relative to the average values you noted earlier.

If the keyword isn’t present in the user behaviour data, we score the keyword 0.

  • Test vs CPC from paid search: Paste all your paid search keywords & CPCs into a worksheet. In your title sheet use vlookup to return the CPCs for the keywords you are testing. The CPC test is negative: the larger the CPC for the keyword, the less relevant you are, the less you want to prioritise the term. Make sure your CPC is pasted as a number, e.g. 1.21 for $1.21.
  • Create relative search volume for each keyword. You do this by dividing each terms search volume into the total search volume of all your keywords.
  • Combine the total of all your previous tests with the CPC and the relative search volume. This is always the final test before we work out the total score per keyword. Our formula to work out the total keyword score follows:

total keyword score = ((sum of scores from tests)-CPC from paid search)*relative search volume

By multiplying all the previous by the relative search volume you ensure that the most important factor in your keyword refinement algorithm is audience size. The great thing is though, you’re not focussing on absolute number of searches, but on the proportion of your total audience that will search the term in question.

The final step in producing your table should be to order all of the data by total score, and there you have it; a prioritized keyword list for your client. You can proudly state to your client that the reason you want to focus on the terms at the top of your list is because they are:

  • Relevant.
  • Popular amongst your potential audience.
  • Representative of quick-win opportunities.

Even better, you can point to a robust methodology to back this up.

If you need to do a keyword list refresh after a few months, another benefit of this process appears: all you need to do is fetch fresh keyword metric data and rerun the formulas in your mastersheet – you’ll find the order of terms in your list has changed naturally as your algorithm reflects the fact that you have managed to get some terms into a great position.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


This entry was posted on November 9, 2012 by in Informative, SEO and tagged , .