Your guide to SEO forecasting with Google Sheets

SEO forecasting is essential for predicting the future performance of your strategies, measuring progress, and making data-driven decisions.

This guide will show you how to use Google Sheets to create accurate forecasts for traffic, rankings, and revenue, even if you’re not a data expert.

Forecasting: The secret to navigating SEO’s biggest hurdles

In marketing and SEO, it’s often necessary to illustrate our vision for what lies ahead. Common scenarios include:

• Getting buy-in for a new SEO or content strategy: We need to demonstrate the expected ROI of the proposed strategy. While it may not be perfectly precise, providing an estimate of the potential growth this strategy could bring is crucial for securing approval.
• Website migrations: How can we tell if a website has fully recovered post-migration? With variables like seasonality, it can be challenging. For instance, a migration in November might leave you unsure whether December’s performance reflects issues or seasonal trends. Having projected performance metrics as a benchmark is invaluable in these cases.
• Highlighting the risks of neglecting SEO: In a new role, I created an SEO strategy and showed the projected performance if no changes were made. The outlook wasn’t promising, which emphasized the need for ongoing SEO efforts and proved that SEO is far from a one-time task.

The solution? Forecasting!

We need to do some math to project the future; we can’t just say, “que sera, sera.”

What is forecasting in SEO?

Forecasting in SEO is predicting future trends in organic search traffic, keyword rankings, user behavior, and revenue using historical data, analytics, and algorithms.

Key metrics we typically forecast:

Metrics we typically want to forecast:

• Clicks: This is the bread and butter of SEO. We aim to predict how much traffic we’ll generate.
• Impressions: Often underestimated, impressions reveal trends and can measure awareness. Higher impressions mean more visibility in search results.
• Rankings: A key focus for clients, tools, and SEOs alike. (Although we may shift toward a greater emphasis on CTR in the future.)
• Conversions and revenue: Ultimately, this is what matters most. Showing ROI through conversions and revenue is the true success in SEO.

Dig deeper: How to do an SEO projection

How to forecast for SEO using Google Sheets

There are various ways and tools for forecasting, including creating your own Python scripts. 

In many cases, with some manual data collection and cleaning, you can simply use Google Sheets.

Forecasting ranges from simple tactics to more advanced ones. 

Once you understand that SEO forecasting is about estimating rather than providing precise math, you’ll likely develop your own methods.

1. The FORECAST function

The easiest, most straightforward way is to use the FORECAST function:

=FORECAST(x, data_y, data_x)

Where:

• x: This is the specific point or value you want to predict or estimate.
• data_y: This is the list of numbers representing the historical data for the SEO metric you’re forecasting.
• data_x: This is the list of dates corresponding to the historical data, showing when each value in data_y occurred.

The FORECAST function in Google Sheets predicts future values using linear regression. 

It calculates a straight line that best fits your existing data points and uses this line to estimate future outcomes based on the trend.

Here’s an example of how this may look like on a graph:

The red regression line is the forecasting line. It’s the straight line that passes through as many data points as possible on the graph, allowing it to estimate future trends.

Here’s another example:

Columns A and B have the historical clicks data by month from September 2023 to December 2024. 

I wanted to predict the clicks for January 2025, February 2025, and March 2025 and put those values in Column C. 

This method is easy to use and explain, but not all data fits neatly into a straight line, as shown in the first example above.

2. Exponential smoothing forecasting

This forecasting method emphasizes recent data more than older ones, making the forecast more responsive to recent changes and trends in data. Here’s how to get started:

• Add an index column next to your data columns as follows:

• Select the “Index” and “Clicks” columns, then go to the Insert menu and click on Insert chart.

• Customize the chart by selecting the Customize tab.

• Go to Series and adjust the settings as follows. (Notice how the graph now includes a trend line for forecasting.) You can use the formula pointed to by the arrow to fill in the remaining values in the “Clicks” column.

For row index 17, the clicks would be equal to =123*Exp(-0.0949)^A18.

Dig deeper: How to reliably predict SEO success before publishing content

3. Simple moving average (SMA)

This forecasting method calculates the forecast as the average of historical data over N periods, where N represents the number of past periods considered. 

For example, to forecast clicks for next month using the past three months’ data (100, 120, and 110 units), the calculation would be:

• Forecast = (100 + 120 + 110) ÷ 3 = 110 clicks

Here, N is 3 because we used three months of historical data.

To forecast using the SMA method in Google Sheets, simply calculate the average of the past few months. For example, to forecast Jan. 2025, Feb. 2025, and Mar. 2025, follow these steps:

• For January 2025, the forecast is the average of the last three months (October – December 2024).
• For February 2025, the forecast is the average of the previous three months (the forecasted value for November 2024 – January 2025).
• Continue the same way for each following month.

Simple Google Sheets techniques for better SEO forecasts

Google Sheets offers various forecasting models. With time and experimentation, you can determine which best suits your data. 

Keep in mind that these models provide estimates, not exact figures. 

As long as this is clearly communicated to stakeholders, any model you choose will add value.

• Use the linear regression model when your data is best represented by a straight line.
• Consider exponential smoothing to assign more weight to recent data, which is especially useful if your website has undergone significant changes, such as a design overhaul, in the past few months.
• Opt for the simple moving average to identify trends and gauge the direction of future performance.

I recommend combining these methods to achieve the most reliable forecast for your data.

Dig deeper: How to use RStudio to create traffic forecasting models