SEO forecasting template will give you insights into 2023

Marketing budgets can be divided even in boom times among many channels and teams.

With a unstable economy likely limit many budgets, and headcounts well below optimal levels, marketers will need to make a strong case why their expertise should be given a fair share of resources going into 2023.

Also, it is vital to forecast how X resources will affect Y growth.

This is why I often get these questions from potential clients and clients:

• “How much traffic can we expect from SEO, and how long does it take?”
• What can SEO do to increase our revenue?
• “What kinda lift are we going see from this work?”

SEO’s mix of science and art is the unique beauty and challenge. SEO is not like paid performance channels that have CPC or CPM benchmarks which tell you how many impressions and clicks you’ll get for certain amounts of spend.

You cando forecasting for SEO to provide directional answers and establish traffic expectations for the next year or any other time period. This article will explain how I approach it.

How to use the SEO forecasting template 2023

We have a forecasting template I am happy to share with you.

Before we start, please note:

• You can only view the document, so you will need to download it. These ranges are not intended to be used as a guideline. You will need to create your own.
• The randbetween()formulas are recalculated with each change to the document. Numbers will not remain static. These estimates should be saved in another sheet/location to make it easier for future reference and comparisons.

Let’s look at how it works.

Benchmarking your growth data

Rows 3-14 of this SEO forecasting document give you a full year’s worth monthly traffic history. You should have at least one year of historical data to be able forecast a full year.

However, it is important to remember that forecasting can only be reliable if there are mature data. Explicit growth rates from the first 12 months of website traffic, for example, will produce highly distorted projections.

Choose a time frame that is appropriate for your brand’s traffic history. You should account for any factors that artificially depress or spike a particular month’s search.

• One-time ad campaign.
• Site migration
• Site outage for a prolonged period.
• Etc.

Once you have selected your benchmarking data, calculate a month-over-month average growth rate (and then add to cell L5). This smoothes out seasonality.

Forecasting baseline growth without resources

The forecasting starts in the 12 rows immediately after historical benchmarking data.

To get a baseline forecasting base, start with row 15, . Column B simply takes the benchmarked traffic and applies the average growth rate in L5 over the next year.

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 doesn’t produce a flat monthly growth rate. Google describes it as “calculating the expected y value for a specific x based upon a linear regression analysis of a dataset.”

Columns B and D represent forecasting models for growth as long as you don’t use any SEO resources and let the growth momentum take its course.

Forecasting growth using resources

ColumnE really gets to the heart of things. This takes your historical, known data in SEO (rows 3-14), and calculates a range of expected growth based on whatever resources you have.

You can set the ranges that we describe below. These are examples only and not recommendations for the forecasting document.

To determine the expected growth ranges, follow these steps:

• Begin by looking at the keywords that you wish to rank for in the next year.
• Take a look at the monthly search volume.
• Apply a basic CTR for total traffic if your site is ranked on Page 1 of those terms for at least nine months. (Be aware that it may take several months to reach a higher ranking).

Two ranges should be created: one for the initial three months (to give momentum for new keywords), and another for the next nine months.

After you have established your conservative range, add L6 to the sheet and M6 to the top. 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 established your aggressive range, add L7 to the sheet and M7 to the sheet. 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.

You now have your traffic forecasts without SEO resources (Column C) and traffic with search engine optimization resources (Column F).

Note: For comparison purposes, I recommend that you use Column D and not Column B. You’ll be reporting to your team monthly, not annually, so it is important to refer to the more precise monthly forecasts. Add the numbers in Column D to Column E and you will have an estimate of SEO growth you can share with your stakeholders.

Use SEO forecasting for directional insight

Because of the nature SEO, this isn’t an exact science. This data can be directional because of the frequent algorithm and SERP update that can swing traffic in one direction or another.

External factors such as a site relaunch or cuts in top-offunnel advertising spend, which may slow organic growth for brand keywords are not included.

It is, however, a guideline for teams considering whether or not to invest in SEO over the next few months.

SEO professionals are skilled at presenting a picture using data ambiguity. Use your storytelling skills and some Excel formulas for support.

Search Engine land.