Register
Forgot Password?
Score
-
-
-
  /100
Certificate
SEO Certificate




Share

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
  • 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!

114 comments

Add comment Hide comments

Comments page:
#270 2009-04-03 10:30:59 Dan Richmond

<b>@Donna Goodman</b>
I would probably rephrase what you're saying:
KEI is important as a quick (and in most cases helpful) way to choose keywords, but KEI should still be taken as a relevant value.
Besides KEI is really good for a beginner, while of course as long as a person is getting more experience in SEO, she will be able to make more intelligent decisions on profitability of keywords.

Answer
#254 2009-04-02 14:08:07 Donna Goodman

Hi Dan, I don't necessarily agree with the idea that the highest KEI keywords are the best choice in all instances, and I will tell you why I say that. First of all, all the great keywords have been taken with few examples, and probably have very low KEI values, and the "on point, popular" ones that don't have high KEI's don't exist, or have little or no searches done on them.

In my mind it makes no sense to make your number one key word a high KEI valued word, if none or very little searches are being preformed on it? I realize you need WordTrakker to be able to find that information out, but I have always had it.

On the other hand if you have a toy site like I do, and toy/s has a super low KEI because of the competition, I don't think that you want to omit that keyword from being on the top of your list. I think you just have to do a lot more than your competition to rise up through the ranks on that keyword.

I hope I am making my point without trying to destroy the value of the KEI variable. It certainly has its place, but I think you have to take everything into consideration when choosing your highest quality keywords, and that is just one factor of many. IMO

Donna

Answer
#150 2008-12-07 17:10:18 Aaron Wardle

Hi,

This is a great guide when using word tracker should I multiply the daily total by 30 to get a rough monthly search to use in the formula?

Aaron

Answer
#126 2008-10-12 11:32:01 Steve K.

Thank Dan!

I see now how it goes together -

Best wishes
Steve K.

Answer
#122 2008-10-08 11:45:07 Dan Richmond

Hey Steve.
I give a nay - sorry :)

Example:
You've got a site about cats.

You have 3 keywords:
1)cat (Relevance 1, Search volume 3, Competition 7)
2)house (R 2, Sv 3, C 7)
3) dog (R3, Sv 3, C 7)

Now let's count KEI.
1) cat: (4-1)/3*3/7 = 3/7 (around 0,43)
2) house: (4-2)/3*3/7 = 2/7 (around 0,29)
3) dog: (4-3)/3*3/7 = 1/7 (around 0,14)

As you can see, the relevance figure helps determine the most appropriate keyword in the case when # of searches and competition are equal. I chose other numbers equal just to make it more obvious. Without the R factor, it would look as if "cat" and "dog" are equally good for a cat-related sitem which is not true.

You don't need the R figure if you want to know KEI for just one word. The whole point here is to use KEI as a criteria to compare numerous keywords and to select the best ones. The R factor helps greatly, as it helps better compare keywords.

Try to play with real figures and you'll see how this works.

Answer
#121 2008-10-06 14:28:17 Steve K.

Dan - just tell me yay or nay if I'm close :-)

I played with your formula and these where my findings:

If the (R) Relavence chosen is 1 (one) then the formula reverts to normal KEI and the results are the same as if NO Relavence (R) had been used.

If the (R) is 2 or 3 then (R) reduces the KEI to a lesser figure in accordance with your preference.

(R) must be a positive integer between 1 and 3.

Close?

Steve K.
crazy 'bout formulas

stephan dot kassner at gmail

Answer
#120 2008-10-06 11:47:56 Dan Richmond

Hi Steve,

thanks for your comment.

<i>How do you arrive at (4-R) over 3?</i>

This all comes from my personal experience. :)

Answer
#118 2008-10-03 14:54:07 Steve K.

Hi Dan
Great piece of work here - I particularly like the revised KIE(R).
BUT :-)
How do you arrive at (4-R) over 3?

Much obliged at your insight and excellent tips

Warm regards
Steve K

P.S.
I've only just discovered your website and have been avidly devouring the info all day.
Sorry if the question has already been covered.

Answer
#104 2008-09-18 23:46:11 Anthony Curran

this makes keyword searching much easier to understand and get it down to a duplicable process

Answer
#58 2008-08-08 10:59:32 Drew Stauffer

This is great. I love the Excel tip. I've never taken the time to learn about how to write formulas, but now have one.

Answer
Comments page: