Use this SEO forecasting template to gain insights for 2023

Even in boom times, marketing budgets are divided among a range of teams, channels, and initiatives.

Going into 2023, with a shaky economy likely to cap many budgets and headcounts far below optimal levels, it will be especially important for marketers to articulate a compelling case for why their area of expertise should get a fair share of resources.

In other words: forecasting how X resources will achieve Y growth is going to be vital.

Because of this, I frequently get the following questions from clients (and prospective clients):

• “How much traffic will we get from SEO and how long will it take?”
• “What can SEO do for our revenue?”
• “What kind of lift are we going to see from this work?”

The beauty and unique challenge of SEO is its blend of art and science. Unlike paid performance channels, where you have CPC and CPM benchmarks that tell you how many clicks and impressions you’ll get for a specific amount of spend, SEO doesn’t have a clear, quantifiable path to cause/effect.

That said, you can do SEO forecasting to give some directional answers to these questions and set traffic expectations for the year (or any specified time frame) ahead. In this article, I’ll explain my approach.

We’ve built a forecasting template that I’m happy to share with you here. 

Before we get started, note that:

• It’s view-only, so you’ll have to download your copy. The ranges are not recommendations; you’ll need to fill in your own.
• The randbetween() formulas recalculate with every change to the document, so numbers will not be static. We recommend saving these estimates in another sheet/location for posterity and comparisons.

Let’s break down how the tool works.

Benchmarking your growth data

In this SEO forecasting doc, rows 3-14 give you a year’s worth of monthly traffic history. For the purpose of forecasting a full year to come, you should be able to reference at least a year of historical data for benchmarking.

It’s important to note, though, that reliable forecasting depends on having mature data as a benchmark. Extrapolating growth rates from, say, the first 12 months of a website’s traffic will yield highly skewed projections.

Pick a time period that makes sense for your brand’s traffic history. Make sure you’re accounting for factors that artificially spiked or depressed any particular month’s search:

• A one-off ad campaign.
• A site migration.
• A prolonged site outage.
• Etc.

Once you have your benchmarking data selected, take those numbers and calculate an average month-over-month growth rate (and add to cell L5); this smooths out factors like seasonality. 

Forecasting baseline growth (with no resources)

Your next 12 rows after the historical benchmarking data are where the forecasting begins. 

Starting with row 15, Column B takes your benchmarked traffic and simply applies the average growth rate (in L5) over the next year to get a forecasting baseline.

Column D takes the previous year’s data and applies the Google Sheets “forecast” formula, which you can get by entering =round(forecast(A15,C$3:C14,A$3:A14),0) into Column D, Row 15 and dragging the formula down through all applicable cells.

This formula does not produce a flat month-over-month growth rate; as Google describes the formula, it “calculates the expected y-value for a specified x based on a linear regression of a dataset.”

The values in columns B and D are forecasting models for your growth if you applied no SEO resources at all and simply let your growth momentum continue on its own.

Forecasting growth with resources

We really get to the good stuff with Column E, which takes your historical, known SEO data (rows 3-14) and applies a range of expected % of growth given whatever SEO resources you’re projected to have on hand. 

It’s up to you to set the two ranges we’ll describe below (which are only included as examples and not as recommendations in the forecasting doc).

To calculate the expected growth ranges:

• Start by analyzing the keywords you want to rank for over the next year.
• Look at the monthly search volume.
• Then apply a basic CTR to get total traffic if you ranked on Page 1 for those terms for approximately nine months (given that it will take a few months to achieve a higher ranking). 

Create two ranges: one conservative range for the first three months (to allow momentum to build for newly in-focus keywords) and a more aggressive range for the following nine months. 

Once you have your conservative range, add the low end to L6 in the sheet and the high end to M6. Paste the formula =round(D3*((RANDBETWEEN($L$6,$M$6)/100)+1),0) into Column E, Row 15, and drag down for the first three months to get forecasts for applicable cells. 

Once you have your aggressive range, add the low end to L7 in the sheet and the high end to M7. Paste the formula =round(E6*((RANDBETWEEN($L$7,$M$7)/100)+1),0) into Column E, Row 18, and drag down for the next nine months to get forecasts for applicable cells. 

Now you have your forecasts for traffic without SEO resources (Column D) and traffic with SEO resources (Column E).

Note: I recommend using Column D, not Column B, for comparison purposes because you’ll likely report to your team by month, not by year, and should therefore reference the more accurate monthly forecasts. Subtract the number from Column D from the number in Column E, and you’ll have estimates for SEO growth that you can share with your stakeholders. 

Using SEO forecasting to gain directional insights

This is not an exact science because of the nature of SEO. With frequent algorithm and SERP updates that can swing your traffic one way or another, this data will be directional.

It also won’t account for external factors like a planned site relaunch, cuts in top-of-funnel ad spend that may stunt organic growth for brand keywords, etc. 

That said, it is a reference point for what’s at stake for teams weighing whether to invest in SEO in the coming months.

All good SEO professionals know how to paint a picture with some data ambiguity, so use those storytelling skills and some Excel formulas to support your cause.

Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.