September 21, 2020

A Forecasting Template with SEO Algorithm Updates

I recently read a very interesting post from Patrick Stox on the Ahrefs blog. Since it concerns itself with SEO forecasting I wanted to write something covering two main points:

  1. How to do this in Forecast Forge
  2. Different (perhaps better?) methods for dealing with algorithm updates

Patrick uses a forecasting algorithm which is part of an open source project called Prophet. Prophet was created by Facebook and it is the main algorithm used by Forecast Forge (at the time of writing). It is possible to produce a very similar forecast to what Patrick/Ahrefs have done using Forecast Forge.

You can see an example template of this in Google Sheets

What makes this special compared to a simple Forecast Forge forecast is that I have copied Patrick’s approach for handling algorithm updates.

Patrick uses a forecasting technique called “holiday effects” to account for changes that happen around core algorithm updates. In his code notebook you can see that he has entered the dates of all Google’s core algorithm updates and that he uses a 2 week (14 day) window for the effect of the algorithm update.

In Forecast Forge you can achieve a similar effect using a helper column. You can see this in column C of the example sheet.

The above shows the holiday effect around the 8th March update in 2017 (I think this was called “Fred”). There are zeroes in the run up to the update and then ones on the day of the update and for 13 days afterwards. This pattern is repeated for the other updates.

This column of algorithm updates is then used as a helper column in the FORGE_FORECAST function.

Plot of the forecast


I think it is not a good idea to model a Google algorithm update like it is a two week holiday. There are two ways of thinking about it that can be modelled with Forecast Forge:

  1. The effect of an update persists from the date of the update onwards
  2. The effect of an update lasts until the next core update

I don’t know which one of the above two is best; it is likely that it will be different depending on the website and the core update. You can try both approaches and see which is better for your site.

You can see an example of the first approach on the second tab of the Google Sheet

There is one column for each algorithm update (columns C through P). The columns contain zeros up until the date of the core update and then ones afterwards.

Then all these columns are used as helper columns in the formula.

You can look at the third approach in the third tab of the Google Sheet.

This is very similar to the previous approach; there is one helper column for each core algorithm update and the values in these columns are zero before their respective algo update takes place.

But after the update, the ones only continue until the next algorithm update.

This is telling the forecasting algorithm that the effect of a search algorithm update lasts only until the next algorithm update.


The fourth tab has a chart where you can compare the different methodologies.

For this site, there is little to choose between my two ways of encoding algorithm updates; but these methods give a very different prediction to Patrick’s approach (the red line).

Doing better with code

There is a third way to handle Google algorithm updates but it isn’t possible to do it in Forecast Forge (yet); so you’ll have to wait for me to figure out how to include it or you’ll have to do a bit of coding.


Prophet models your data as a combination of weekly seasonality, annual seasonality and the underlying trend. The trend is assumed to be piecewise linear; i.e. it is made up of a series of straight line segments.

“Changepoints” are where these line segments meet and the gradient of the trend can change. By default Prophet inserts potential changepoints evenly through the first 80% of the data. Then when the algorithm is fitted only the most important of these are allowed to actually influence the trend; this means that there needs to be strong evidence in the data for a trend change.

The dotted lines are potential changepoints
After fitting, only some of the changepoints are used

You can also specify changepoints manually which enables you to specify the dates of algorithm updates as changepoints. I recommend using a similar approach to the default; have 25 changepoints in total with some of them being the dates of core algorithm updates and the rest being spread between them.

Then the forecast algorithm can learn if the traffic trend for your site changed around the time of an algorithm update.

Another advantage of this approach is that Prophet will use the frequency and magnitude of changepoints in the past as part of the predictions for the future. So if you have a site that is rarely influenced by Google algorithm updates you’ll see a narrower predictive interval in the future than if your site bounces around every update.

Note that there are two weaknesses with this approach:

  1. It models the impact of an algorithm update as a trend change. This might not be realistic - if you’re site is penalised in an update it won’t be like the gradient of a linear trend changed, it will be more like a major discontinuity.
  2. Algorithm updates aren’t treated differently to any other trend change; the forecasting algorithm doesn’t know the difference between the date of a search algorithm update and the date your optimised title tags went live. So the frequency and magnitude of historical trend changes won’t just be the influence of search algorithm updates.

Both of these are fixable problems but they involve quite in depth changes to the Prophet forecasting model. Speak to me if you are interested in learning more about how to do this.

If you've read this far and understood everything then you might not be in the target audience for the Forecast Forge Google Sheets Addon.

However, you probably have collegues who are! I would like to talk to them so please consider hooking me up with an introduction (

And feel free to email me with any other questions/comments you may have; I'd love to hear from you.

Never miss a post