This is part of my SEO forecasting template from my course, Forecasting for SEO, which includes a simple linear regression + seasonality and additive Holt-Winters model. 🚀
Check out the video for how to use the template 🎥
This is a short extract from my 2 hour Forecasting for SEO course.
In the Forecasting For SEO course, there is an additional scenario forecasting sheet and there will soon be a Facebook Prophet model.
About the SEO Forecasting template
1. Prerequisites
1.1 Google Analytics Add-On
The template forecasts using Google Analytics data. To automate the retrieval of Google Analytics data you’ll need to install the Google Analytics add-on for Sheets.
1.2 Solver Add-On
The Solver add-on by Frontline Systems Inc. certainly isn’t perfect but unless you’re a mathematical genius, you’ll need it to run the calculations for the Holt-Winters model.
2. The Linear Regression Model
This model forecasts the linear trend of historical data (2 years) and then applies a seasonal component to it. It’s pretty neat for when you’re starting out with forecasting as there’s no complicated inputs but arguably it’s not a model at all (I mention this in the video). That’s because it doesn’t really fit the data, it kinda assumes a default fit for the data.
Nevertheless, it does work across quite a lot of SEO datasets (in my experience) but will generally only work with data that is both linear and seasonal.
2.1 Inputs
The model has 1 input – seasonality.
This allows you take the seasonal indices from either year 1, year 2 or an average of both. This is useful for example, if there’s a lot of anomalous monthly values in a specific year which doesn’t accurately reflect historical seasonality.
2.2 Outputs
- Forecast summary vs last year (%).
- 12 month forecast with predicted monthly values vs last year (%).
- Time series chart with linear trendline.
3. The Holt-Winters Additive Model
Holt-Winters, also known as triple exponential smoothing, is a classic statistical model that forecasts using exponentially decreasing weights over time. In simple terms, this is an assumption that the most recent historical data is a truer reflection of what will happen in the future.
I’m a big fan of the Holt-Winters model and use it regularly. You’ll need to watch the video for this one, as the inputs won’t make much sense otherwise!
This is the additive version of the Holt-Winters model, so if your data is multiplicative, meaning your seasonal variance increases/decreases over-time, this won’t work very well.
3.1 Inputs
For the Holt-Winters model you’ll really need at least three year’s of historical data.
Alpha, gamma what you say? The inputs sound confusing but they’re really not. They are the model parameters, and provide the weightings for our data. To make sense of them think of them as the base factor, trend factor, and seasonality factor. Values nearer to 1 give more weight to recent historical values, and values nearer to 0 have the opposite effect.
Testing
You can also select ‘Test Data’ as TRUE and this will only train the model on 80% of the historical data, so you can test with the remaining 20%.
Forecast Accuracy
MAPE is my forecasting accuracy measure of choice and is the average of the absolute percentage errors of the forecast. The template provides a measure both for the test and the training data.
3.2 Outputs
- Forecast summary vs last year (%).
- 12 month forecast with predicted monthly values vs last year (%).
- Time series chart with forecast values.
- Forecast errors chart which can be used to validate how accurate the forecast might be.
Thanks for stopping by 👋
I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓
Questions? Get in touch with me on social or comment below 👇