How to Forecast your Site’s Organic Traffic

traffic projection tool

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

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.

Click to view sample report

traffic projection 1

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:

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

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.

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:

Monthly traffic = traffic from the same month last year * last quarter’s YoY growth rate

alt text

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.

traffic projection screens

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!

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.

Ready for BigQuery?

Get off the ground quickly, with customizable data pipeline Recipes for BigQuery.

GET COOKING