Forgot Password?
SEO Certificate


1.2.2 Shortlist your keywords

Now you have a long and versatile list of keywords and keyphrases to choose from.

So let′s brush aside all stuff and shortlist your search terms. Again, you can choose between a free but long way to do this, or use Rank Tracker tool you already know to make this task a snap.

Option 1: Sweep away the keyword trash, by hand

Calculating KEI

Here we go with the magic wand of SEOs. That′s the SEO know–how called KEI, or Keyword Efficiency Index. This index shows you how good a keyword is for bringing traffic to your site.n

You need keywords with high KEI.

Here′s what the importance of your keywords depends on:

  • Relevance (R): it is how closely your keyword is related to what you offer to your customers.

We use the three–grade scale to estimate relevance. So now you need to make a separate column for Relevance in your spreadsheet, and put one of the following numbers next to each word or phrase:

  • 1 for Excellent
  • 2 for Good
  • 3 for Poor
  • Search Volume (SV): it is how many queries are made for a keyword per month.

Here′s how you can check Search Volume for each word or phrase in Google AdWords. Let′s say you want to check it for "free ecard":

  • go to Google AdWords Keyword Tool or use Google Keyword Tool by Rank Tracker
  • copy and paste the selected keywords
  • select the desired match type on the left (I′ll explain the options in a second)
  • select your region, language and devices you wish to scan the traffic for, wait a little bit and VOILA! you are now armed with the monthly search volumes for your keywords.

As promised, below is the description of the available match types. If you′ve already used Google Keyword Tool or simply know how they differ from each other, just skip this paragraph.

  1. So, broad match type is the default option. For example, for the query "free ecard" you′ll see the number of searches, containing either or both words "free" and "ecard" in any order, possibly along with other relevant terms. This number may also include singular/plural forms queries and synonyms.
  2. Choosing [Exact] match you′ll see how many times per month the keyphrase is searched exactly as it′s entered by a user.
  3. And, finally, the "Phrase" match will reflect the number of queries where words are in the same order as in your keyphrase – even if other words are present in that query. For example, the number of searches for "free ecard", will also include keywords like "free ecard happy birthday" or "free ecard funny".

If you are not an international business or by any reasons are not interested in the numbers of search queries from all over the world, select the necessary country in the Local monthly searches column. Note, that these figures may not be exactly accurate, but reflect general trends.

Now, in your spreadsheet, make a column for Search Volume and enter the number of search values for each phrase.

  • Competition (C): that′s how many websites try to rank for this keyword. Generally, all sites that have the same keyword as you are your online competition. And the fewer websites are optimized for the same keyword, the better KEI this keyword has.

The way to check competition is simple. Enter the keyword in Google and click to search. Now look at the image below to see where you find the Competition value:

Google competition for the free ecard keyword Google competition for the free ecard keyword

In a separate column for Competition in your spreadsheet, enter values for each keyword.

Here′s an example of how your keywords table may look like:

Relevance, Search Volume and Competition Values in Excel worksheet
Relevance, Search Volume and Competition Values in Excel worksheet

So what makes a KEI better? Higher relevance, higher search volume, and smaller competition.

Now, let′s count the magic KEI number for your keywords.

The classical formula for KEI looks like this:

Keyword Efficiency Index formula
Keyword Efficiency Index formula

Sometimes I also use an improved formula for better estimation. It takes into account real relevance of your keywords.

Keyword Efficiency Index with Relevance
Keyword Efficiency Index with Relevance

Now make a column for KEI in your Excel spreadsheet and apply the second formula to calculate KEI for each phrase.

Keyword Efficiency Index in Excel worksheet Keyword Efficiency Index in Excel worksheet

Let′s choose the best keywords

First, sort all keywords in your spreadsheet by KEI. As I already said, the higher KEI, the better your keywords.

You do not need many keywords to optimize your site for, so look at your list and choose top 5 keywords. Mark them as the major ones (say, color them green.) Then, choose the following 30 and mark them as important (you can color them yellow.) Some more 65 or so keywords should be marked as supplemental (e.g. red.)

Now you′ve got a nice list. We′ll target the green–marked keywords in the first turn, make use of the yellow ones as alternative and mind the red ones just in case.

Looks complicated? It definitely does, that′s why I′ll provide you with a quicker and easier way to perform the same check automatically. Stay tuned!


Add comment Hide comments

Comments page:
#3040 2012-05-28 06:38:16 Wouter - Simple SEO Agency Amsterdam

Hi Dan I am using the in title competition to check my "real competitors". I like the way you use keyword relevance in your KEI calculation. Is there any change this can be calculated within Rank Tracker itself? By the way I mentioned your method on my site which explains KEI:

#3047 2012-05-29 04:03:58 Dan Richmond

Actually this very formula is currently used in Rank Tracker to calculate KEI.You can learn more about KEI feature in Rank Tracker here:

#3066 2012-06-01 09:55:26 Wouter - Simple SEO Agency Amsterdam

Hi Dan I cannot find the option to add keyword relevance in the KEI calculation. Is it possible?

#3140 2012-06-08 06:36:08 Dan Richmond

Actually “Relevance” is a relative factor which defines how closely your keyword is related to the theme of your website and it is for you to determine this metric. That is why this factor is not taken to account calculating KEI in Rank Tracker.

#3153 2012-06-11 08:06:27 Wouter - Simple SEO Agency Amsterdam

Would be nice if we could manually add this factor into RT.

#3001 2012-05-02 19:45:07 Jansen Wendlandt

Excellent suggestion of including the relevance factor. That always seems to be the linchpin of a word being worth marketing to.

#2851 2012-04-07 18:03:25 Cheryl Marko

Hi All...Okay i have a big problem that I hope one of you can help me with. I have no idea how to use excel or even formulas. Can Someone help me with putting this formula into excel and what and how to do this please...sorry newbie here...really need help asap...thanks ...Cheryl

#2755 2012-02-11 14:26:40 Producer Pack

Hi Dan

I have used the Google keyword tool to generate most of my keywords. Within that tool, it shows the 1. keyword 2. Competition (High,medium & low) 3. Global Monthly sales 4. Local monthly sales.

When i export the keywords from google keyword tracker to my excel sheet, it shows the competition figures as (0.24, 0.40, 0.29 and so on and so on)

My question is, what does those figures mean? are they percentages? or actual competition figures? for example.. 0.24 = 24,000 or 24,000,000 etc

Will i stll have to physically type in every keyword into google one by one to get my competition numbers?
Or has the word tracker been updated since you wrote this book so i wouldnt have to do all of that.. I have over 1000 keywords.

Hope that makes sense, and great book too ;)

#3044 2012-05-29 03:51:22 Dan Richmond

Those numbers are the competition levels in Google Adword Tool where 0.00 is the lowest competition and 1 is the highest. Please note, that these numbers can not be converted to the competition score you see performing the search in Google since is Google Adwords own metric.

#2742 2012-02-07 08:02:58 SEO Consultant

Hi Dan,

I hope that the keyword tool is going to be upgraded, so we can see more indicators like domainage, pagerank, dmoz and yahoo listings (like marketsamurai does) when we do keyword research.

All best,

I have bought the Powersuit Enterprise edition and am making money with. A great tool!

#2582 2012-01-04 18:05:23 Kitty Kraze

So it seems many of you already own word tracker and the other programs. Do you think it is worth it? Can you just use these programs and have great success? I use IBP but it does not do everything these do. Please tell me what you all think.

#2483 2011-10-30 20:00:27 Dana Haenner

Being an seo geek i like the Keira formula do any tools calc this for you, hope not the harder to do the fewer that do it...most are too lazy. Thanks again

#2434 2011-09-30 07:54:27 Renate van Laarhoven

Hi Dan,

My compliments on this SEO-guide! I haven't been able to collect the right keywords yet, because the Google Keyword Tool only gives statistics (competition and local monthly searches) about 363 out of 563 keywords. Why is this and what do I do?

Regards, Renate.

#2447 2011-10-11 09:02:51 Dan Richmond

Do you mean in Rank Tracker or in the browser? A good idea would be to get a Google AdWords account and use it in both cases (it is free).

#2460 2011-10-12 07:24:24 Renate van Laarhoven

Hi Dan,

What I mean is: when I use the Google Keyword Tool (Rank Tracker also uses this, I think?) and I would like to know how often people search a certain keyword per month, I don't get any statistics of this word, even when I think it has to be a very popular keyword, like 'sale babyfashion online' (I use the Dutch words). And I have this problem with 200 out of 563 keywords, I only have statistics for the less specific words. I have a lot of competition in children's clothing, so I think I have to use more specific words. So I spent two days to think of good words, but now I can't use your steps to pick out the best ones. I have a Google AdWords account, but I don't think this is helpful with this problem. Do you have any other suggestions?


Renate van Laarhoven

#2305 2011-07-26 22:25:44 aidt zun

I was very impressed and pleased to read this book, Trimakasih have shown them all, so petrified for a beginner like me, I will immediately practice what is suggested in the book thank you ^ ^

#1998 2011-05-12 05:42:00 Valentin Popa

This is a very good advice, many times I have problems with keyword research but your study here helps me a lot, I will apply it soon. Thank you

Comments page: