There is tremendous Solution for this problem ...
This is called SEO (Search engine Optimization )
Note: This article is part of a series. To understand this piece I recommend you read part 1, which can be found here. 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.
Methodology
- 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.
Don't forget to download that Excel template to get started on your awesome keyword research!
