Excel Keyword Research: Analytics + Automated #1 Ranking Traffic Estimates

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

(2) SEO Book Keyword Tool

(3) A Test Google Adwords campaign

(4) Google Related Searches

(5) Google Wonder Wheel

(6) Google Suggest

(7) Wordtraker

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

Download Data

Log into your analytics program, download the keyword and visitor data and paste it into a spreadsheet.

See columns A and B below:


Keyword Position

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.

About the Author: David de Souza

David de Souza is the founder of matching donations, a website that allows your charitable donations to go twice as far. He is also the SEO strategist for the International Professors Project, a non profit that encourages professors to volunteer for teaching opportunities abroad.

Matching Donations

Additional Posts

The Power of Social Media Monitoring in Competitive SEO

The Importance of SEO Education Within a Company

Facebook Places offers big brand Deals to Europe

Read previous post:
The Power of Social Media Monitoring in Competitive SEO

As more and more marketers begin to take a holistic approach to their online marketing, we run into multiple instances...Read...