Analyze Keywords Using Adwords Keyword Tool and Excel Macro Function

If you are looking for a free keyword analyzer, read this article and learn how to analyze keywords using the Google Adwords Keyword Tool and the Macro Function in Microsoft Excel.

Identifying what keywords to use for your web site or blog can mean the difference between success and failure so it's important to get it right. The problem that most new business owners face is that they do not have extra money to spend on a fancy research keyword tool, like Wordtracker.

So what to do? Use what you already have at your disposal and maximize your free resources of course!

Google Adwords Keyword Tool

If you are not familiar with this tool, simply do a search and you will find it. Its only purpose is to help you identify a list of potential keywords. You will still have to analyze them to determine their worth. We will do that later in Microsoft Excel.

• Input your general keyword idea into the white box under "Enter one keyword or phrase per line." Fill in the security code and hit "Get Keyword Ideas." By default, four columns of data will appear. To add additional columns, select them from the drop down menu under "Choose columns to display." If you plan on using Google Adsense to monetize your site or blog, add "Estimate Avg. CPC."
• You should now have five columns of data. For definitions of each, click on the question marks next to the column heading. They five columns are:
  • Keywords
  • Estimated Avg. CPC
  • Advertiser Competition
  • Local Search Volume: Month
  • Global Monthly Search Volume
• Normally you will get two lists of keywords. The top list is where you will find "Keywords related to term(s) entered." The bottom list is the "Additional keywords to consider." Under each list you will find links to download the data, including ".csv (for Excel)." Download both lists into an Excel Document, pasting one under the other.

Analyze Keywords in Microsoft Excel

You can sort through keywords in a number of ways but the goal is always the same: find keywords that are high in search volume and low in competition. In other words, lots of searches but very few people answering the call.

Those keywords are the keepers, the golden nuggets.

In addition to ease of use, Excel really is the perfect tool for this job because it offers you the ability to:

• Filter out keywords that have very little value
• Sort keywords in the order that you prefer
• Categorize and format keywords for easier consideration
• Display subsets of keywords on individual spreadsheets
• Save workbooks on your own computer for reference

Let's get to work on our list of keywords from Adwords. You should have one spreadsheet with five columns. Our goal is to quickly eliminate less useful keywords and get pare down our list to only the top contenders. We'll do that by using filters.

First we'll go through the manual process and then the macro function which will cut processing time substantially.

Using Excel Filters To Analyze Keywords

• Select the top row with the headings. Choose Data-Filters-AutoFilters to turn on the filters. You should have drop down menus in each of the columns. Using the drop down menus you can sort out whatever you do not want to see, such as, the keywords with high competition and/or those with virtually no search volume.
• For each column decide what, if anything, you want to keep or eliminate. A high level sort might look something like this:
  • Keywords--keep all
  • Estimated Avg. CPC--keep only those with CPC of $1.00 or more
  • Advertiser Competition--keep only those with competition of less than .74
  • Local Search Volume: Month--keep all. Will use next column instead
  • Global Monthly Search Volume--keep only those with at least 200 searches
• Now that you have determined acceptable thresholds for each column, set up your custom filters. In the CPC column click on the drop down menu and choose "Custom." Here is where you will define exactly what you want to keep. There are 4 white boxes each with their own drop down menu. Typically you will only use the top two boxes to analyze keywords.
  
  Using the suggestions above, in the first box choose "is greater than", type in "1.00" in the second box and click OK. Do the same for any other columns you want to set thresholds for (see suggestions for each column above). NOTE: You do not have to set a threshold for every column.
• At this point you should have eliminated all of the low quality keywords. Your list may still be quite long so a second round of filtering may be needed. Use Data-Sort and formatting, such as fill colors and bolding, to help simplify your list.
• The final decision about what keywords to keep and get rid of can only be made by manually considering a combination of CPC, Competition and Search Volume. In addition to using these variables to help make your decision, keep in mind that the keyword or phrase must somehow be weaved into your content for maximum search engine friendliness.

Using Macros To Improve The Process

The process of using filters in Excel can be tedious especially if you have several columns of data and multiple spreadsheets of keywords that you are considering. That's where the Macro Function comes in.

A macro is defined as "a series of program commands or instructions which are stored in a file and can be recalled when necessary." Basically, we will record each of the steps that we did above and then save the macro and use it each time we process keywords.

Reading this brief macros tutorial will give you a high level view of how it's done so that in the future, you can analyze keywords in a couple of mouse clicks.

• With your spreadsheet of keywords open, choose Tools-Macro-Record New Macro. Name the macro something you will remember when you need to recall it, like "KeywordResearch." Do not use dashes, slashes or spaces. Click okay. In Excel 2003, a small box will appear in your spreadsheet with a "Stop Recording" button visible.
• Every step you take from this point forward will be recorded. Go through the exact steps above and any others that you might like to perform and then hit the "Stop Recording" button.
• The next time you want to use the macro function, click anywhere in the spreadsheet and then choose Tools-Macro-Macros and find the named macro in the list. Hit "Run" and your steps will automatically be performed.

The process of analyzing keywords using the Google Adwords Keyword Tool and the Microsoft Excel Macro Function will not only save you money but a huge amount of time.