Our job as data people is to reduce anxiety.
To soothe the concerns of colleagues or customers who are worried about the future.
That’s where forecasting comes in.
Of course reality never plays out as you forecast it – BUT – having a solid framework for thinking about potential scenario paths will make it easier for colleagues or customers to support your plan.
They’ll thank you for working to soothe their anxiety.
So let’s investigate a common source of digital business stress:
What could my website’s organic traffic be in a year?
The answer to that question will drive everything for an organic-dependent online business: potential revenue, hiring, comp, you name it.
So to help answer it, we built a template that does some basic forecasting math in Google Sheets (using data from Google Analytics + SEMrush) and summarizes the findings into a 5 page Google Data Studio report.
It’s an approach that’s specifically designed to help SEO agencies when pitching new clients, but you could adapt this forecast methodology for any project.
IMO forecasting is built on 3 pillars, which we’ll talk about below:
Let’s dive into the nitty gritty of how we implemented these 3 for the Traffic Projection Tool.
BIG NEWS: We originally built this model in Google Sheets + Data Studio. It worked great, but it was cumbersome to setup. So we decided to build this forecasting tool as software. We’ve completed it, and it’s amazing – check out the automated SEO traffic projection tool here.
Let’s first define what we mean by these two.
Top-down forecasts take some broad macro trend, like top-line revenue growth, and forecast forward based on that trend. All of the subcomponents of revenue (COGS, gross profit, etc) are then calculated based off of that top-line forecast.
Bottom-up (aka ‘sum of the parts’ forecasts) look at the trend of each individual component, and then sum them up to forecast how the whole entity will move.
For this traffic forecast, we used a combination of bottom-up and top-down forecasting:
Monthly traffic = traffic from the same month last year * last quarter’s YoY growth rate
Monthly traffic = sum of (keyword-level ranking * assumed CTR for that position * search volume)
Since we had access to both top-down and bottom-up datasets (thanks to Supermetrics, the Google Sheets Add-on), we decided to combine the two.
But in your own forecasting work your methodology choice may be confined by what data you can access.
If people can’t understand how you built out a forecast, they won’t trust the results.
So it’s on you to communicate your forecast scenarios with crystal clarity.
We ended up with 3 forecast scenarios:
For the report template in Data Studio, Ryan added detailed context to each chart – so that a new colleague or customer could pick up the report and read it without confusion.
This is why I love Google Sheets.
You can build a template for stuff like forecasting, make as many copies as you like, and you know that the math will remain the same throughout.
And like any Sheets template we publish at CIFL, we include a ‘Getting Started’ tab containing all assumptions, so that you can quickly double-check that your assumptions are matching between forecasts:
To see how these principles come together into an organic traffic forecast, check out this video walkthrough of the setup process.
BIG NEWS: We originally built this model in Google Sheets + Data Studio. It worked great, but it was cumbersome to setup. So we decided to build this forecasting tool as software. We’ve completed it, and it’s amazing – check out the automated SEO traffic projection tool here.
Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.
GET COOKING