The KISSmetrics Guide To Keyword Research – Part II: Analyzing and Choosing the Best Keywords

Keyword research is a vital part of any online marketing campaign. In the first part of this keyword research series, you were given a base spreadsheet to use to start entering data including estimated search volume and approximate CPC next to keyword ideas and phrases you generated using various tools including SEMRush, Google Suggest, and the Google AdWords Keyword Tool. Now we will look at getting even more data about each keyword to further narrow down the results.

The following is the second part of the keyword research series. It will help you continue to record important data into your keyword research spreadsheet which will help you narrow down the list of keyword ideas to the best keywords / phrases for both your main website’s search engine optimization and topics for content development.

Please note that this data will take a bit of time to compile, so you might want to run through the keyword list and remove any obvious ones that you don’t want. I usually go with no more than 100 keywords for this part of the process.

Spreadsheet Recap

The spreadsheet for this post is located at http://bit.ly/kwrgoogledoc. This document has columns for data using all tools mentioned in the first two parts of this series. You can add or delete columns as you wish in order to match your keyword research needs. The columns included are as follows.

  • Keyword
  • GAKT – Competition (Google AdWords Keyword Tool)
  • GAKT – Global Monthly Searches (Google AdWords Keyword Tool)
  • GAKT – Local Monthly Searches (Google AdWords Keyword Tool)
  • GAKT – Approximate CPC (Google AdWords Keyword Tool)
  • SEOmoz KA – Difficulty (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 1 (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 1 DA (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 1 RDLRD (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 2 (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 2 DA (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 2 RDLRD (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 3 (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 3 DA (SEOmoz Keyword Analysis)
  • SEOmoz KA – Competitor 3 RDLRD (SEOmoz Keyword Analysis)

Columns for keyword ideas and the Google AdWords Keyword Tool were discussed in the previous post.

Keyword Analysis with SEOmoz

If you have a Pro membership with SEOmoz (you can always get a 30 day free trial if you have just one keyword research project to do), then you have access to a very nice little tool called Keyword Difficulty & SERP Analysis.

This tool will show you the percentage of difficulty for each keyword, giving you an idea of how hard it will be to rank highly for it. You will also see the top ten sites ranked for each keyword, each site listed with its domain authority and the number of root domains linking to it. The latter is really the information that will help you decide which keywords to target.

You can put in five words at a time and choose which search engine to do the analysis for – Google, Bing, or Yahoo. You can also select a country.

keyword research difficulty tool

Using a sampling of 5 keyword phrases from the ones previously discovered by the AdWords Keyword Tool, you can see the results of the difficulty assessment in conjunction with search volume.

keyword research difficulty tool results

SEOmoz tools discontinued pulling search volume data from Google Adwords as of November 11, 2011.

While this information is nice (you’ll want to enter the percentages into your spreadsheet), the real competitor information can be seen when you click on the full analysis for each keyword, such as these results for social media company. I like to open them in new tabs for convenience.

keyword research difficulty tool full analysis

This is the part where you can learn a few things. First off, you can see who you will be trying to beat. If your website comes up in this list of the top ten, then congratulations – you’re on the first page for a keyword phrase with a highly competitive keyword difficulty of 66%.

If you’re not in these results, you can compare your own website’s domain authority and linking root domains to the stats of the sites ranking in the top ten. Domain authority is a score from 1 to 100. The higher a website’s domain authority, the more likely it is to rank for the keywords it is optimized for. The linking root domains is the number of unique domains linking to a website.

You can see that the site with the highest authority and linking root domains doesn’t necessarily win the top spot. But if you want to make #1, your site may need a domain authority of 28 or higher and hundreds of linking root domains to beat the competition.

Another thing you can learn from this is whether a keyword is drawing commercial or informational results. This means that you will learn whether your keyword is being searched for commercial purposes (meaning that someone wants to find a product or service) or whether it is being searched for informational purposes (meaning that people are looking for information, not a product or service).

For social media company, the results include six actual social media consulting companies and agencies. The other four results are about rankings and comparisons of social media companies.

Comparatively, if I do a full analysis of the keyword phrase what is social media, you will see that the first page of results are purely informational, and specifically the top five are from highly authoritative domains.

So if you are a social media company, you will probably want to target the phrase social media companies for your main business homepage. You will also want to create some content based around the phrase what is social media and place it on highly authoritative domains such as YouTube and SlideShare to see if you can get it into the rankings for a phrase searched potentially by a million people monthly.

Alternative to the Pro SEOmoz Tool

If you’re not interested in a premium tool, then what you can do is simply look up each keyword phrase and look at websites ranking in the first page of the results.

If you’re interested in the domain authority stats, you can get some using OpenSiteExplorer.org (a limited amount for free accounts) or the free version of the SEOmoz toolbar – just use that and compare it to your own website’s domain authority. You can grab overall backlink statistics from Yahoo Site Explorer (until it is retired) and use the Inlinks number for each domain.

At least with this information, you can still determine the commercial vs. informational usage of a keyword plus some basic stats to compare against your own website.

Putting It Together in the Spreadsheet

The final part of the process is taking the spreadsheet and interpreting the data. I like to use the Conditional Formatting in Excel to highlight cells a certain color based on the numerical value in the cell. You simply highlight the cell range you want to compare, then go to Conditional Formatting > New Rule.

KISSmetrics Keyword Research

Select the 3-Color Scale Format Style, and change the Lowest Value / Highest Value color when applicable. Certain fields in your spreadsheet will need to display the fact that a higher number is good whereas a lower number is bad. For those fields (global & local monthly searches), you will want to leave the color settings as default, as shown below.

Keyword Research in MS Excel

Other fields in your spreadsheet will need to display the fact that a higher number is bad whereas a lower number is good. For those fields (GAKT – Competition, GAKT – Approximate CPC, SEOmoz KA – Difficulty, SEOmoz KA – Competitor DA, SEOmoz KA – Competitor RDLRD), you will want to change the lowest value color setting to green and the highest value color setting to red, as shown below.

keyword research excel conditional formatting

Once you’re finished, you can highlight the top row and add a filter by applying the Sort & Filter > Filter option. I like to start with selecting the filter for the SEOmoz KA – Difficulty column and sorting it from smallest to largest. This way you get the following (click on the image below to see a larger view):

keyword research spreadsheet results

As you can imagine, having the highlighting and sorting options would be very valuable when looking at a spreadsheet with up to 100 keywords. You could quickly see which keywords have the most searches and least competition or the least competition but most searches. You could then move over and look at the actual competition for each keyword if you were shooting for #1 rankings.

Making the Final Keyword Choices

My suggestion when making the final choice on keywords is as follows.

  • If your website is commercial (offering products or services), go with keywords that seem to have the most commercial intent based on the currently ranking websites. If your website is informational (blogs come to mind), go with keywords that have the most informational intent.
  • Look for a “sweet spot” of high search volume in conjunction with low difficulty / competition.
  • Look at keywords with high search volume with top competitors that have lower domain authority and backlinks.
  • If your website is already ranking on the first page, but not in the top 5 spots (or beneath other search result areas such as images, local results, etc.) and the keyword has good search volume, focus on getting those keywords and phrases to move up.

Sometimes, it’s not the actual decision that is the most difficult – it is actually compiling the data that takes the most time! Also, don’t forget to take the keywords that are more informational and use them for content topic ideas!

This concludes our method of discovering keyword ideas, analyzing them, and then choosing the best for your website. We have noticed that there are many various ways to perform keyword research, and you have to try out different analysis methods to see which ones speak to you the best when deciding on keywords. With that in mind, part three of this series will be a compilation of the top posts on keyword research. Stay tuned!

Next Up: Part III – 40 Top Keyword Research Posts of 2011

Click here to read the third post in this series.

About the Author: Kristi Hines is a freelance writer, blogger, and social media enthusiast. Her blog Kikolani focuses on blog marketing, including social networking strategies and blogging tips.

  1. Excelent SEO Guide for Keyword Research… can’t wait for part three !

  2. Loved Part II also, Looking forward for Part III now!

  3. Great articles -
    Small point – Google SERPs info is no longer available thru SEOMoz – see excerpt from their FAQ below:

    The Keyword Difficulty Tool used to show search volume. Where can I find that data?

    Google made some changes to their AdWords API that prevents us from displaying search volume data at this time, though we’re working on finding an alternative! You can still get this data by going to Google’s Keyword Tool HERE. Fortunately, we don’t use search volume to calculate Keyword Difficulty scores, so the tool is still 100% alive and well!

    • Hi Jim,

      Gotta love it when someone changes their tools overnight – I grabbed that screenshot the night before this went live!

      I actually just use the Google AdWords Keyword Tool to pull the search volume. It’s a lot easier, especially since you can export the data for all of your keywords at once vs. doing it five at a time. I just use the SEOmoz tool to quickly see the difficulty percentage and top ten competitors per keyword.

  4. Nice, but the Keyword Analysis and Keyword Difficulty Tool in SEOMoz doesn’t show Google data anymore.

    • I know, gotta love the changes. I noticed the numbers were unstable the last several times I used it – that must have been why. I still like using Google AdWords Keyword Tool to get the search volume since you can export it for all of your keywords at once.

  5. Ok, big tip for the ones reading this… skip all this and use http://www.secockpit.com
    I’m a customer, not owner or so, and this tool gives you an advantage. a big one.
    Saves a lot of time.

  6. Thanks very much for this! I’ve just done it and I think it’s a great way at quickly highlighting the keywords where there is a good opportunity.

    I have a question for though. What happens if one of the listings that comes up is a huge competitor? For example, Amazon.co.uk comes up in the top 3 of every single keyword I’ve analysed for a client. It’s skewing the data because they have a DA of 88 and over 150k RDLRD! Would you suggest omitting Amazon and instead focusing on the top competitors from position 2-4 or just ignoring Amazon?

  7. Great Post. I’ve got a question though.

    Are you referring to page linking root domains or root domain linking root domains in your analysis? From your spreadsheet and your wording you seem to be referring to the later.

    However, just want to clarify with you:
    1) Is this correct?
    2) Why is this a better measure than the page linking root domains? SEOmoz only shows the page linking domains in it’s open site explorer so thought that would be more applicable.

    Look forward to you response.

    Thanks!

  8. it’s a great way at quickly highlighting the keywords where there is a good opportunity.Great post thanks for such a wonderful article.

  9. Great post, thanks so much. Can’t wait to try some of the tools you mentioned.

  10. This is a great way to analysis the keywords… I love the tool very much

  11. Nice to have an alternative to the Adwords keywords tool though as a sanity check I always recommend running a small PPC campaign to analyse what is being clicked on. You should also link your analytics and webmaster tools account to compare clicks, visibility and average position.

  12. Hi Kristi, thanks for the great information. I have couple of questions, please help me on these:

    1. Alexa also shows top queries which are giving traffic to a website. Are these the top results of should I use above tools to get the top10 keywords

    2. Many often I come across keywords which are important but at the same time has high competition also. Can I still go ahead and use them ? If I put them in title + URL+ heading of the page, I should stand out of competition

Comments are closed.

← Previous ArticleNext Article →