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

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.

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.

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.

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.

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.

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.

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):

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!