Keyword Analysis Tutorial – Starting an SEO Project

By Scott

Search engine optimization is like any other form of marketing. Before you start, it’s important that you’ve done your homework. You should learn about who your customers are, what their needs are, and what your competitors do to appeal to them. But once you’ve done all of your homework and are ready to start the tactical process of properly integrating keywords into your website, you’ll want to conduct a thorough keyword analysis. Often, a good keyword analysis can be the difference between successful search engine optimization, and failed search engine optimization.

Here’s a tutorial on conducting your keyword analysis (you must be reading the full post in order to see it):

1 – Make a big list of keywords

Start out by building a list of all of the keywords you might want to target in your SEO campaign.

Sources of keywords may include:

A) Brainstorming
B) Keywords your competitors use on their site and in their meta tags
C) Keywords you use in your pay per click campaigns
D) Keywords people use to find your website currently (according to your web stats)
E) Names of important categories and product groupings on your website

2 – Acquire data for each keyword

Now that you have your big list of keywords, you’ll want to start acquiring data for each keyword that will help you prioritize your list.

I recommend opening an Excel spreadsheet and setting up the following columns:

A) Keyword
B) # of monthly searches (as reported by tools like Overture or WordTracker)
C) Relevance of each keyword to your business
D) Attainability of each respective keyword

The keyword and number of monthly searches column should be self explanatory.

As for assigning relevance for each keyword, I suggest a scale from 1 to 3.

1 – Not relevant at all
2 – Fairly relevant
3 – Very relevant

If you’ve done your homework, this should not be hard at all.
Here is an example of how you score relevance. Let’s say you sell Sirius Satellite Radios online.

Here are the relevance scores I would give to the following keywords:

Radios – 1 (the term is very broad)
Car Radios – 2 (the term is broad, but to someone searching on “Car Radios,?? a “Sirius Satellite Radio?? may be an option.
Sirius Radios – 3 (this term is a very close match to what you’re selling)

After you have assigned a relevance score to each keyword, move on to attainability. You will want to assign an attainability score for each keyword that takes into consideration how competitive the keyword is, and how likely your site is to achieve high rankings on that keyword in the near-term (6 to 10 months).

We use a fairly sophisticated method for scoring attainability of individual keywords that I’m not going to describe here. Here are two other methods that may work for you:

A) Find the KEI score for each keyword and use that to establish the attainability of each keyword (link to an article on calculating KEI – link to Keyword Discovery, a tool you can sign-up for to find out the KEI for each keyword automatically) (,

B) If you want to be less formal, just use the 1 to 3 scale (1 being not attainable, 3 being very attainable). If you don’t have a lot of experience in search engine optimization, this may not be easy for you to do, but it’s good practice.

Think of it this way. A term like “gifts?? will always be a 1, unless you are, have been around for 10 years, and are the leader in online gift giving.

A term like “executive gifts?? may be a 2 if your site has a good number of relevant links leading to it, and if your site has a good amount of valuable and unique content.

A term like “personalized executive gifts?? may be a 3 if you just have an average site, but are dedicated to long-term search engine optimization techniques (relevant link building, keyword optimization, content development).

Once you’re done, you’ll end up with an excel sheet that looks like this:

3 – Sorting the Data to make it usable

Now, you’ll want to sort the data. That will make it easier for you to work with it.

I recommend first sorting by # of Searches, then by Relevance, and then by Attainability.

To do this in Microsoft Excel:

A) Highlight all of the data (including row 1 with the header titles)
B) Click on “Data??
C) Click on “Sort??
D) Sort by “# of Searches?? and select Descending
E) Then by “Relevance?? and select Descending
F) Then by “Attainability?? and select Descending
G) Make sure “header row?? is selected and press OK

Now you have sorted data.

4 – Prioritize the Data

Here is where your judgment comes in. Go through your list of keywords and remove any keywords that you don’t think you will be targeting (especially keywords at the bottom of the list that have few searches and very little relevance).

Then, go through your list of keywords and attempt to pull out the top 20 keywords. Use your best judgment. Separate this list into a new Excel tab, or into a new Excel file.

Then, narrow down to the top 5 keywords, and put these keywords into a new Excel tab, or into a new Excel file.

You are left with 3 lists:

A) Your big list of keywords
B) Your top 20 keywords
C) Your top 5 keywords

Ok, how you have some useful data.

Now it is time to figure out how you will target these keywords throughout your website.

5 – Build your sitemap.

Build a sitemap in Excel that contains your most important pages listed across the top. For an e-commerce site, this may be 20 or 30 pages that represent your category pages. For other sites, you may have far more or far fewer.

Here is what it looks like:

6 – Distribute your keywords in hierarchal order among the sitemap pages.

The objective is to distribute your top 5 keywords, top 20 keywords, and big keyword list throughout your sitemap pages in priority order.

So start with your top 5 keyword list, and figure out the pages on your website where each one of those top 5 keywords would be a good fit for. One keyword can be assigned to multiple pages or to none at all.

Then, go to your top 20 keyword list and do the same.

Then, go to your big keyword list, and do the same.

When you are done, for each important page on your website, you will have a list of keywords you should be targeting on that page ranked in order of importance. It might look like this:

7 – Using your Keyword Analysis to start Keyword Optimization

Now that you have completed your keyword analysis, you can use this data to create your meta tags, page titles, on-page headlines, body copy, and cross-linking. You’ll know what keywords you should be targeting on each page.

If you find that you have many keywords assigned to each page of your website that may be a sign that you need to add more relevant pages to your site to spread out these important keywords. The idea is that you will target search engine rankings for many different (relevant) keywords, not just a few select ones.

This is just one method you can use for conducting your keyword analysis and beginning the process of optimizing your website for keywords.