Looking for an easy way to
visualize your keyword research?

In 10 simple steps and Excel 2007, you can build your own Keyword Research and Analysis Heatmap.

1. Come up with a 5-10 seed keywords to perform the research and analysis on. These will be the basic words that everyone will type in for what you are promoting (keys, car, dog, marketing, etc…)

2. Use Word Tracker’s Keyword Research Tool to expand each seed keyword into a list of up to 100 (you can get more with the paid-for version). Save all the words generated by this tool in one worksheet.

3. Copy all the words generated from step 2 and paste into Google’s Keyword Traffic Analysis research tool. Leave the CPC and the Daily Budget blank. Choose the region you will be doing business in or want to perform the research and analysis on.

4. Click continue to display the list. At the bottom of the estimated traffic list, click the button “Download as .csv” and open it in Excel 2007.

5. Remove the columns: Max CPC, Estimated CPC (lower), Estimated Ad Position (upper and lower), Estimated Clicks (upper), Estimated Costs (lower). In most cases these will not be necessary for proper research and analysis.

6. Remove the 2nd row containing the “upper” or “lower” specification. Highlight the headers and choose: Sort & Filter > Filter from the Ribbon.

7. Highlight the Search Volume column and choose: Conditional Formatting > Color Scales > Green - Yellow, from the Ribbon.

8. Highlight the CPC Column and choose: Conditional Formatting > Color Scales > Red - Yellow - Green.

9. Highlight the Clicks Column and choose: Conditional Formatting > Color Scales > Green - Yellow - Red.

10. Select All and choose: Sort & Filter > Custom Sort. Build the statement to say: Sort by [Search Volume] [values] [largest to smallest] Then by [CPC] [values] [largest to smallest]. You can use the “Add Level” button to add the “Then by” row.

Want to create this research and analysis report, but don’t have Excel 2007?
Contact us and inquire about our Keyword Research Tool Report services.

This entry was posted on Saturday, January 17th, 2009 at 9:10 pm and is filed under Keyword Research Tools, Social Marketing Tools. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.