Keyword Research Spreadsheet Tips

Posted by Peter Rastello

Jun 2, 2010 6:56:00 PM

Free blogging planner A big part of keyword research is to make sense of the data that you have. Many tools provide statistics on keywords, but one thing I've run into a number of times is the difficulty in seeing the forest for the trees. For example, the Hubspot Keyword Grader tool is an excellent utility for helping find viable keywords for your online presence. It can be used in multiple ways to suggest keywords both from seed entries that you provide and by evaluating particular websites themselves and churning out words that they may be ranking for.

 One of the approaches that many researchers like to take is to export the data generated by Keyword Grader out to a spreadsheet. This can be achieved by clicking the "Export" button on the bottom of the page. It can be a good idea to do this because it is easier to manipulate fields, formats, and sort options in excel. The trouble is that the presentation of the data dump that gets exported to Excel can make it difficult to interpret, that is, you may not see the forest for the trees, without some additional format massaging. That brings me to the point of this article, which is to share my approach to cutting back the forest a little.

 Un-modified, a typical data dump from the Competitor View looks like this:

 Raw Keyword Data

After a few global commands and a little conditional formatting, the same spreadsheet looks like this, making it considerably easier to view, sort, and recognize patterns:

 Formatted Keyword Data


My objective is to end up with performance results from 1 to 99 only, with color coding for 1 (green), 2 (yellow), and 3 to 10 (orange). Just doing this cleans up the presentation a great deal making it considerably easier to interpret the data. Here are the steps I take to accomplish the above:

 

1.      Find and replace (CTRL+F) "100+" with nothing

2.      Find and replace (CTRL+F) "Low" with nothing

3.      Highlight the cell in the top left corner of the field of data in the column after ‘Monthly Searches' and:

a.       Pick the ‘Conditional Formatting' menu selection

b.      Format the cell to be colored green if it contains a ‘1'

c.       Format the cell to be colored Yellow if it contains a ‘2'

d.      Format the cell to be colored orange if it contains the range ‘3 to 10'

4.      Grab the lower right ‘handle' of the cell you just formatted and stretch it down to the lower right corner of the data field.

 

This is an easy and quick way to present the data; hopefully you will find it useful too. Your feedback and questions are welcome, happy spread sheeting!

Topics: targeted web traffic, Inbound Marketing Methodology, keywords