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:

image

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:

=(B2/VLOOKUP(C2,$F$2:$G$24,2,1))*$G$2

Sort

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.

image

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.

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

You May Also Like

6 Responses to “Excel Keyword Research: Analytics + Automated #1 Ranking Traffic Estimates”

  1. sherry says:

    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?

    • Hi Sherry,

      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.

  2. [...] Excel Keyword Research: Analytics + Automated #1 Ranking Traffic Estimates – SEP [...]

  3. [...] This information can be solid gold when creating a  content development plan since you already know the volume of traffic and an estimate of how well it will convert when you rank for it. (Use this automated Excel setup to to estimate traffic for number one positions) [...]

  4. [...] Excel Keyword Research: Analytics + Automated #1 Ranking Traffic Estimates how to estimate the traffic you would receive if your site was ranked number one for a particular keyword [...]

  5. [...] you haven't, consider yourself lucky (for now). If you have,  you know that searching for the best keywords to target can be really tough. Like getting dressed in the [...]