March 16th 2021

Forecasting gets a bad rap in the industry because it can end up being abstract. Unfortunately, there are elements outside ones control when it comes to implementing SEO campaigns so the idea of predicting outcomes can be daunting. Some of the dependencies are;

  • The speed at which the client implements your recommendations is often outside of your control.
  • You can’t predict what search engines would do.
  • Algorithm updates can change everything. 
  • The timeline for above the line campaigns and new product launches.

So Why Forecast?

Investment decisions are based on expected profitability. Including expected profitability in your campaign proposal takes it from a nice to have to the priority list making it easier to secure buy-in.  Calculating the expected profitability of your campaign also helps you show what ROI can be achieved. Forecasting also helps you set an expectation and create anticipation for your campaign. Other benefits of forecasting include.

  • It provides you with the data to challenge pre-set targets to assess if it is realistic or attainable. Often, organic traffic and sales targets are based on the general targets set for the marketing team and the budget assigned to the campaign is not reflective of the expected growth. Forecasting can help you determine if the campaign will need more budget to achieve its goals.
  • Forecasting enables you to challenge the assumptions used when the targets where set. Is the data used to create the data robust? What dependencies have been applied to the target? For example, 80% of the Google Analytics account we see are set up incorrectly, that is why data hygiene is an essential part of effective forecasting.
  • It shifts the narrative from being “a technical or Google problem” to a commercial one which is a language everyone on the team can relate to regardless of their experience and knowledge level.
  • It gets the C-suites interested and they are the ones who sign off the money, so you defiantly need them on board.

Accepted Framework for Forecasting?

An acceptable framework for forecasting within the industry is forecasting based on a set of keywords. For this forecast, you need a list of keywords your site is already ranking for and or keywords you want your site to rank for. Next, you estimate the position increase factoring in the expected outcome of the campaign you are running. You then input the organic click-through rate (CTR) for each ranking position (this can be done using data from your Google Search Console account or if these are keywords you do not rank for you can use AWR’s CTR tracker). Divide average search volume for each keyword by its CTR to estimate the amount of traffic each keyword would drive this should give you your traffic forecast.

To get your revenue forecast you can take a granular approach and calculate the conversion rate (CR) and average order value (AOV) for each keyword based on the performance of the page you have mapped the keyword to, or you can use the CR and AOV for the entire site. CR and AOV should be extracted from GA. Divide the estimated traffic with the conversion rate and the multiple the AOV to get your revenue forecast.


In my opinion, this type of forecasting is better suited for estimating the ROI for content creation because it is based entirely on keyword predictions.  Some other limitations are: 

  • Difficulty in predicting keyword trends.
  • Personalisation and localisation make ranking positions a moving target.
  • It is not holistic as it is impossible to predict all the keywords a site will rank for.
  • Due to Google not providing keyword data in Google Analytics, it is impossible to accurately predict the exact keywords that are driving conversions on your site.

Forecasting using statistical models.

At Erudite, we forecast using statistical models based on linear regression and exponential smoothing.

What is Linear Regression?

Linear regression is the most widely used statistical techniques. It is a way to model a relationship between two sets of variables (source – It is predicting the outcome of an event based on previous outcomes.

What is exponential smoothing?

According to, exponential smoothing forecast future values using a weighted average of all previous values in the series. This function gives more weight to recent values in the series even though it considers past trends when creating the forecast. It also works to remove outliers by analysing the data for errors, trends, and seasonality to smoothing the calculation and determine the strength of predictors.

Why use linear regression and exponential smoothing.

The impact of implementing SEO changes are cumulative and not standalone. This method of forecasting looks at the site as a whole enabling you to forecast for both content and technical changes.

What do you need for your forecast?

#1 2 – 3 years of historical data

This helps to ensure statistical significance creates greater accuracy and increases the chances of spotting and discounting outliers.  It also enables the model better estimate seasonality and YoY growth trends. You can use Google Analytics or Search Console data. We use GA data as it is a more controlled data set increasing the chances of accuracy. The data should contain dates and the information you are trying to forecast i.e., traffic, conversion rate etc. It should also be in chronological order.

#2 Excel forecast sheet.

Excel Forecast Sheet Function
Excel Forecast Sheet Function

Training your data.

It is important to train your data to ensure the outcome is as accurate as possible. In an ideal pre-pandemic scenario training your data was a straightforward process. 

For example, if you have data for 2019 – 2020 and want to forecast for 2021. I recommend training the 2019 data to replicate 2020 results and then using the function settings to forecast for 2021.  However, due to the pandemic, this can be quite tricky as there were lots of outliers in 2020. To exclude the effect of the pandemic, I would train 2018 data to replicate 2019 results and use the function settings to forecast 2020 and 2021. This would enable you to exclude or quantify the impact of the pandemic.

Functions for training your data.

Create Forecast Worksheet
Create Forecast Worksheet
  • Confidence Interval – This is an estimation of how well the data sample truly represents the values you are analysing.  The default confidence interval is set to 95% however, you can change this depending on how confident you are in that the data hygiene process.
  • Seasonality – This calculates the length of the seasonal patterns. Does it peak up in the summer? Is there a massive sale every 3 months? Excel detects this automatically, but you can set it manually and change the values to better suit your needs. To set it manually, count the number of up-down cycles in your data (the number needs to be at least 2 or higher). Keep an eye on the trend lines when you train the data to mimic previous years.
  • Interpolate missing points – This is used to specify how you want Excel to calculate missing points in the data set. It can be calculated using the weighted average of the previous months or it can be left at zero. I usually set it to use the weighted average.

Other Functions.

  • Timeline range – Change the time range for the forecast.  This needs to match the data on the values range.
  • Value range – Change the data you want forecasting. This needs to match the dates on the timeline range.
  • Forecast end – You to select how many months you want the forecast to extend for
  • Forecast start – Where you want the forecast data to start from.
  • Duplicate aggregates using – You can have duplicates if you are doing an hourly forecast you might come across multiple values within the same timestamp. We use monthly data, so this is not applicable however, you have the option of using count, average or median calculation method to work out the value for duplicate entries. If you do not change this, it defaults to averages.
  • Include forecast statistics – if you want additional information on the statistical formula used to create the forecast, tick this box to get it included in the worksheet.

Forecast Output.

Forecast Output (Video)
Simple Forecast Output with lower and upper confidence bound.
Simple Forecast Output with lower and upper confidence bound.
Forecast can be a line graph or bar chat.
Forecast can be a line graph or bar chat.

To calculate the revenue forecast, multiple the projected traffic by the projected CTR to get the transaction forecast then multiple the transaction forecast by the average order value to get the revenue forecast.

Example of a comprehensive forecast - Forecasting the impact of the weather on organic traffic.
Example of a comprehensive forecast – Forecasting the impact of the weather on organic traffic.

Calculating the Impact of Your Work

Calculating the impact of your work is the non-scientific part as it is down to your experience and your knowledge of the competitive landscape. We tend to model it closer to a scenario analysis. Do not forget to factor in any above the time activity that the client is planning to undertake. I.e., new product launch, price increases, offline campaigns etc. (if you have supporting data to measure the impact),other media spend. The more data points you have the better the prediction.  Also, do not forget that some SEO activities will affect other channels. For example, site speed improvements will lead to improved conversions across all channels so even if you do not factor this in your forecast it is worth highlighting as an additional benefit.

Creating a Model line

After we have determined a percentage increase, we create 4 model lines.

Forecasting Model Line
Model Lines
  • The first line is the do-nothing model. It is most likely that if the client does any other marketing activity and has some visibility (except they have some form of an algorithm or manual penalty) they will continue to grow even if it is slow-paced. We like to communicate this to clients as this also help them quantify the impact of our work and helps to build trust.
  • Unfortunately, things don’t always go as planned on all projects. Except you have control of the entire process from, diagnosis, sign-off to implementation there will be factors outside of your control that can impact the speed at which recommendations get implemented and this will affect the results of the campaign.  Due to this, we create three additional trendlines: cautious, moderate, and optimistic, which reflect the impact of implementing different levels of the recommendations.

Calculating ROI on SEO investment

Once you have estimated the impact of your campaign you can now calculate the ROI of the campaign using this formula.

[(GI-CI)/CI] X 100

GI = gains investment (how much additional revenue, or “return”, and CI = cost of investment or client investment (spend to date).

Hopefully, this gives you the confidence to create projections and show projected returns for your SEO campaigns. If you need any help outlining your SEO strategy to ensure you hit your target, please reach out to us.

This article was authored by former Erudite team member Miracle Inameti-Archibong