If you do a quick Google search for SEO forecasting, you’ll find some confusing, and often unhelpful tools and articles. As it turns out, predicting the future is hard to do. 

That’s why we developed our own method and SEO forecasting template to help. As a growth marketing agency, we’ve used this Google Sheet to forecast organic traffic growth for several SEO partners at Tuff, and it’s been eerily spot on with its predictions—as little as 10 or 20 visits off some months. 

What is SEO forecasting? 

SEO forecasting is the process of using data—like keyword volume, click-through rates, and monthly organic traffic—to predict the impact of your SEO efforts. 

In general, all kinds of industries analyze trends and patterns in past data to predict what will happen in the future. We’re applying similar methods but focusing on search engine optimization. 

Since no one can truly predict the future (that I know of), there is some margin or error to be expected. However, forecasting for SEO can help you set realistic expectations and measurable goals that are grounded in data. 

If you invest in SEO content, you want to know how much it will increase your organic traffic growth. SEO forecasting can help you with that and more. 

Want to learn more about how we forecast SEO at Tuff? Check out this video! 👇

SEO forecasting models

There are two basic ways to forecast SEO or predict organic traffic growth. 

1. Keyword forecasting

Keyword forecasting models use keyword search volume and average click-through rate (CTR) to determine website traffic. 

To use this method, you need to know the estimated search volume a target keyword gets each month and the average CTR for each ranking position. 

For example, people are most likely to click the top search result, so that CTR is higher. The CTR drops significantly as you go from position one to two to three and so on. You can pull the average CTR by position from a tool like Advanced Web Ranking’s Organic CTR history. 

Generally, in keyword forecasting, you take the search volume and multiply it by the CTR. 

For instance, let’s say you have a travel blog, and you are ranking number one for the keyword “what to bring on a road trip”. According to Semrush, the average monthly search volume is 880. 

Then, if you want to pull the CTR in the US for only the travel industry, you can do that by going to Advanced Web Ranking. According to their data, the average CTR for the top position is 33.1%. 

• 880 (search volume) × 33.1% (CTR) = 291 monthly organic traffic

2. Statistical forecasting 

The statistical forecasting method uses historical data and mathematical formulas to predict what your traffic will be in the future, based on your growth trend in the past. Specifically, it uses linear regression and exponential smoothing. 

You don’t really need to worry about the exact mathematical terms. The purpose is the same for each function—to predict the future by using data from the past. 

It is typically more accurate than keywords alone because it is custom to your website. It looks at how your site has performed in the last two to three years and uses that information to estimate your organic traffic. 

How to Forecast SEO in a Google Sheet

The method that we use combines both keyword and statistical models. You can make a copy of this SEO forecasting template and use it for your website. The step-by-step how-to guide is below, but first, you’ll want a few tools and data handy. 

What tools you’ll need:

• Google Analytics – to pull your monthly organic traffic numbers
• Google Search Console – if you are using it for your keyword positions and CTR
• Semrush or Ahrefs – to conduct keyword research and get search volume

Data that you’ll need: 

• Your organic website traffic for the last two to three years
• CTR for each ranking position (or you can use the one in the template)
• Keyword research – a list of your target keywords, current position, and monthly search volume

1. Pull your historical data 

Once you have all this, open up the forecasting template. Go to the “historical data” tab and update the dates as well as the organic traffic for each month. 

You’ll pull this from your Google Analytics account under Organic Search. Make sure to adjust it for each month, and remove bot traffic. 

Ideally, you’ll have two to three years of data. This will help you create an accurate forecast. 

If it’s a newer site, you may not have this much data to pull. In that case, pull for however many months you have. It won’t be as accurate, but it’s a starting point.

2. Identify outliers and seasonality

To make your forecast as accurate as possible, you need to look at your historical data numbers for seasonality and outliers. 

Seasonality 

Does the website have defined busy and slow months? For example, a tax business sees a spike in traffic around the beginning of the year through tax time in April. This is seasonality because it happens during the same period of time every year, and it’s predictable.

Outliers 

Are there any months when the website traffic is unusually high? Outliers are data points that are much higher or lower than the rest of the data set. For example, many online businesses saw spikes in traffic during the COVID pandemic.

You might have outliers in your data if: 

• You ran a one-off campaign that inflated your traffic
• Your website was hit with bot traffic
• Your site went through a redesign or overhaul

You’ll want to remove outlier data because it is a one-off event and not representative of your overall performance. It will also skew your forecasting and make it inaccurate.

You can use forecasting to replace it with a number that it would be if there wasn’t a random spike.

3. Get a no-change forecast 

Once you’re happy with your historical data set, go to the “Forecast” tab. Here, you’re going to get a no-change forecast. This tells you what your future organic traffic might look like without any SEO marketing or other changes.  

First, copy over your dates and traffic from the “historical data” tab into the “Forecast” tab for reference. 

Then, go to the “No change forecast” column, and adjust the forecast function so that A29 matches up with the first month you are forecasting. 

For example, it is currently (=FORECAST(A29,’historical data’!B:B,’historical data’!A:A), but the A29 will change based on your date and row. Everything else will stay the same. Drag down to get a forecast for the following months. 

4. Add the average CTR and keyword volume

The no-change forecast uses statistical forecasting. Now we are going to add keyword forecasting. 

You can either use the click-through rates that are listed in the “CTR” tab of the forecasting template or update them with your own. You can estimate your click-through rate with your website’s data or you can use Advanced Web Ranking’s organic CTR data year-over-year or by category.

Either way, you want an average CTR for positions one to twenty. 

5. Add your keyword research

Next, you’ll update the “CTR” tab with your list of target keywords. You’ll also include the monthly search volume and your current position in those columns. 

6. Forecast your traffic for different ranking positions 

Now, you’ll forecast what your organic traffic will be if you start ranking for target keywords. You’ll do this by multiplying the volume for each keyword by the CTR for each position.

I used top twenty, ten, and three but you can use any that you want. More positions can smooth out the growth trend when you add in your keyword movement. If you are currently ranking in the top twenty for one of your keywords, you may want to also add that into the forecast so it’s a little bit more accurate.

This will give you the estimated traffic numbers for each keyword, depending on the SERP. You’ll add those together and get the total estimated traffic for each position.

7. Add the total potential traffic from keywords to the no-change forecast.

Jump back to the “Forecast” tab and go to the column labeled “Keyword movement”. Add the total keyword movement to the no-change forecast number. You decide what this is—if you think most of your keywords will be in the top twenty in the first one to two months, then add that. 

If you think you’ll move to the top ten after three to six months, then use that. You’ll noticed that the chart in the template will update automatically as you add your forecast and keyword movement. 

You’ll adjust this based on how quickly keywords appear in the search results and in what position.

Note, as you add more keywords, you’ll need to update your forecast for keyword movement. You may also want to make adjustments as you get a better sense of your organic click-through-rate. 

A Growth Marketing Spreadsheet to Help Predict the Future

This SEO forecasting Google Sheet focuses on predicting your future organic traffic growth. However, you can go a step further and predict how increasing your organic traffic will affect conversions and revenue. If you want to do this, you’ll need to know your organic conversion rate and average order value. 

Predicting organic traffic can be tricky. After all, you are trying to predict the future. However, by using historical data and keyword movement, you can set benchmarks and expectations for your SEO marketing. Hopefully, this forecasting sheet will help you get started.