How to forecast traffic by keyword rank

Written by and published

When you work with clients who have short budgets and limited resources, you’re forced to be extremely focused in your marketing efforts. That means only compiling reports that provide actionable analytics and next steps.

One report I have created allows me to forecast the amount of traffic I’ll be able to drive based on the keyword ranking.

Opportunity Spreadsheet

Rankings and traffic

There are three important metrics I focus on when running an SEO campaign: traffic, ranking and conversions. The two we’re focused on for this report are traffic and rankings.

We’re not talking about traffic and rankings separately, but instead as a cross-referenced pair. This is important because you can increase a site’s rankings without actually increasing traffic. How so? Marketers could rank hundreds of long-tail specific terms and say that they’ve brought their clients hundreds of first page rankings — but never have actually impacted the business’ bottom line. Thankfully, the Raven Tools platform has a tool that allows you to easily cross reference traffic from specific keywords and their rank.

Rankings and Traffic

Cross-referenced traffic and rankings create the foundation for my forecasting report. We start by compiling the data we have and then extrapolate it to guess-timate the opportunity.

Forecasting traffic

The actual theory behind the report is fairly simple. You begin by looking at keywords that drive traffic and their specific rank.

Then break it down to only keywords that rank on the first page, preferably above the 7th position. This gives us a list of high-value keywords that will have a huge opportunity.

I say huge opportunity because AOL click data has shown a 1st position site is clicked 42.13% of the time compared to 7th position site, which is only clicked 3.41% of the time. Although the AOL click data is not perfect data set, it still gives us insight into the click through rate on search results pages.

SERP Click Through Rate

Based on the data from AOL, we’re able to outline specific CTR for positions on the page. We can then look at our keywords and estimate how much traffic they would drive, if they were in the first position.

For example, assume the term “tv remotes” drove 450 visits a month in the 4th position. AOL click data shows that the 4th position receives a CTR of 6.06%. When you do the math (x=(450/0.0606)0.4213) you see that a 1st position ranking could drive 3,128 visits.

Knowing the amount of traffic that could be driven in the 1st position allows you to identify how much that term is worth to you. You can justify spending resources on that term because you know its value.

How to build the traffic forecast report

Before you can start using this spreadsheet, you’ll need to have Excel, a Raven account and a Google Analytics profile (you need to be able to track both rank and traffic by keyword).

  1. First, authorize your Raven account to access your Google Analytics profile. Not sure how? Check out the step-by-step guide in the Raven Knowledge Base.
  2. Once you’re all set up in Raven, and you have added and been tracking keywords for at least two weeks with the SERP Tracker, export your SERP Tracker data to CSV. This will give us the entire table of data: rankings, traffic, keyword, competition, etc.
  3. Download my free Traffic Forecast spreadsheet and open it in Excel.
  4. Take that table and paste it into the “Data” tab of the Traffic Forecast Excel spreadsheet. If you use the same number of keywords as in my sample sheet, the tables will auto-update and return you the forecast for 1st position traffic on the “Traffic” tab. Traffic Forecast Spreadsheet Advanced Excel users should be able to edit the tables to add more keywords. Don’t forget to re-define the name on the “Data” tab. Refer to the “Credits” tab for more information or head over to my marketing forum for support.

What’s your unique SEO report?

Online marketing is great because it gives us access to so much information. We can track time on site, keywords, total traffic, traffic from other sites, etc. This amount of information can often lead to an overload or misuse of data.

Just because we have the ability to track it doesn’t mean we should. Make sure your reporting is focused and actionable. If you’re not answering a question or setting up a next step, why are you putting together that report?

Is there a unique way you use data to create an actionable report? Feel free to share your ideas in the comments below, or link to report examples you use.