Forecast your Site's Organic Traffic

With Google Analytics, SEMrush and Supermetrics

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:

alt text

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:

  1. Metholodogy: Picking between bottom-up or top-down, based on the data available.
  2. Transparency: Clearly defining + communicating your scenarios.
  3. 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:

  1. 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

alt text

  1. 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)

alt text

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:

  1. Business as Usual
    As discussed, a top-down forecast using total organic traffic from GA.

  2. 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.

  3. 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.

alt text

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:

alt text

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.


Last chance!

Grab the forecast template over at the Blueprint, an SEO-focused collaboration between Ryan Stewart and myself.