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, Ryan Stewart and I built a template that does some basic forecasting math in Google Sheets (using data from Google Analytics + SEMrush), and summarizes the findings in a 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.
Ryan and I built this forecast process as part of our new SEO agency training, the Blueprint. If you’re eager to dive straight in, pick up the Google Sheets + Data Studio templates over at the Blueprint.
IMO forecasting is built on 3 pillars, which we’ll talk about below:
- Metholodogy: Picking between bottom-up or top-down, based on the data available.
- Transparency: Clearly defining + communicating your scenarios.
- Repeatability: The same inputs + dials should output the same forecast, ever time.
Let’s dive into the nitty gritty of how we implemented these 3 for the Traffic Projection Tool.
Top-down vs bottom-up forecasting
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:
- Top-down to map a “Business as Usual” scenario by calculating your site’s current organic traffic growth from GA, and rolling it forward 12 months:
Monthly traffic = traffic from the same month last year * last quarter’s YoY growth rate
- Bottom-up to define potential keyword-level paths to traffic growth, whether by boosting your existing keyword rankings or by going after new keywords:
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.
Communicating your forecast scenarios
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:
Business as Usual
As discussed, a top-down forecast using total organic traffic from GA.
Improve Existing Keywords
A bottom-up forecast using your site’s existing keyword rankings from SEMrush, with the traffic boost coming from a 50% assumed ranking improvement over the next year.
New Keyword Growth
A bottom-up forecast using your competitors’ keyword rankings from SEMrush, with traffic picked up by matching their rankings for a list of keywords over the next year.
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.
Bake repeatability into your forecast
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:
Putting it all together
To see how these principles come together into an organic traffic forecast, check out this video walkthrough of the setup process.
Grab the forecast template over at the Blueprint, an SEO-focused collaboration between Ryan Stewart and myself.