January 18th, 2011One tricky part of SEO is figuring out which keywords should be used in your SEO strategy that you don’t know about. Tools like Rank Checker require you to know which keywords you want to rank well for before you can plug them into the tool and measure where your site ranks for them. But what if you don’t know what new keywords you should use to begin with?
Using Excel and Google Analytics, you can discover new keywords by comparing one time period to another in the keywords report. The end result is a list of keywords that were used for the first time between your two reporting periods. In Google Analytics go to Traffic Sources > Keywords. In the URL add &limit=50000 to the end of the URL and hit enter (the page will not display any differently, but when you export to a csv file, you will export 50,000 rows instead of 500). Export the report and then do the same thing with the previous month. Combine these two reports together then do some conditional formatting to highlight the duplicates in Excel. (To do this in excel 07, highlight the column, click on the conditional formatting button below the Home tab, then choose Highlight Cell Rules > Duplicate Values.)
The keywords that are not highlighted mean that they are not duplicates and were used for the first time between this reporting period and the last. Take this list of keywords and see what their potential is in popularity using Google’s Keyword Tool. Also look at outcome metrics from these keywords in Google Analytics to measure their potential – did they convert? What if you had a lot more traffic from these newly discovered keywords, what would that mean to the bottom line?
Entry Filed under: SEO