This article explains how to estimate the traffic you would receive if your site was ranked number one for a particular keyword. Knowing which keywords would bring the most traffic will help you decide which terms you should be targeting.
There are many tools which can help with keyword research; one of the most overlooked is your own analytics data. Some of my highest traffic keywords are terms that were not obvious from any keyword tool.
Initial Keyword Research
If you do not have data of your own, initial keyword research may involve brainstorming ideas with clients / colleagues and using external keyword tools such as:
(1) Google Adword keyword tool
(3) A Test Google Adwords campaign
(4) Google Related Searches
(5) Google Wonder Wheel
(6) Google Suggest
Using Your Own Data as a Keyword Tool
As Google starts to refer visitors to my website I use the data provided to adapt my keyword list. I use my analytics data to find the keywords that are actually bringing traffic to the site, and then combine this with data from Google Webmaster tools. This allows me to find terms that might be showing impressions but are not receiving clicks.
Traffic Estimation Example
The following is an example for a site that I have analytics data for: taxcodes.org
Log into your analytics program, download the keyword and visitor data and paste it into a spreadsheet.
See columns A and B below:
Take 50 - 100 keywords at a time and place them within a keyword ranking software program, such as SEObook’s Rank Checker, and let the tool tell you where you are ranking for each keyword. Paste your position for each corresponding keyword in column C. For the most accurate results, use the average ranking position over the data’s timeframe, for each keyword.
Click Through Rates
Every industry, keyword, and page will have a different Click Through Rate (CTR). However, we need to use a benchmark, and for this example I have used Red Cardinal’s CTR analysis. Paste the data into columns F and G.If your keywords are ranking in a position not shown, you may need to estimate the CTR.
Position #1 Traffic
By using the visitor data, keyword position, and CTR we can now estimate the number of visitors your site would receive if it were in position one for the corresponding keyword. Input the following formula into cell D2 and fill down:
The final step is to sort column D in descending order. The keywords that should bring the most traffic will now be listed at the top.
Compare your own data with the results from the external keyword tools and adjust your keyword list using the insight you have gained. Don’t forget that some keywords may send a great deal of traffic to your site but, if these visitors don’t convert, they may be useless. If you know the conversion rate of your keywords you can incorporate this data into the spreadsheet and then resort the list. Another point to consider is, depending on the competition, it might be very difficult to rank number one for some keywords. If, for example, a government website, Wikipedia or, an exact match domain is dominating the higher positions it may take a great deal of effort to overtake them. Take this into account when deciding which keywords to target.
Get to Work
Now that you have determined which keywords have the most potential, you can get to work. In the example provided I would begin by logically grouping the keywords and then writing an article with the title ‘Tax Codes For 2011’. After the article has been indexed by Google I would asses my rankings and start building links, if necessary, for variations of ‘tax codes 2011’.
It is important to mention that if you never change and reprioritize your keyword list you could potentially miss out on many visitors. Reassessing keywords on a routine basis could significantly increase the traffic to your website.
Do you only use the external keyword tools when constructing your keyword list or, do you use other unconventional methods? If so, I’d love to hear about them.
6 thoughts on “Excel Keyword Research: Analytics + Automated #1 Ranking Traffic Estimates”
Thank you for this informative article, David. Can I ask, when grabbing keyword data from google analytics, what kind of time range would you suggest searching?
It would depend on how much traffic your website receives. I would want enough data to make the results statistically meaningful. As a rule of thumb though I’d normally use 30 days of data.
Comments are closed.