Return to All Templates

Forecasting Revenue from Google Search Console Data

Google Search Console provides search history source data from the top search provider in the world... Google. The data is very useful if you're running a business in which garnering online eyeballs is of importance. Even with other SEO tools at your disposal (like Ahrefs, SEMRush, Serpstat... there are so many solid tools), you'll never have a more accurate version of the truth than working directly with Google's data.

If you're new to the world of SEO or don't have a data engineering background, creating a forward looking perspective based on historical search results can be intimidating. The nice thing about organic search is that, if you're on to something (i.e. showing up in search, retaining visitors, ranking adequately for search terms, etc.), the results tend to compound over time. Following the lead of Tom Capper and others in the search field, we built a very simple model to project gross profit and revenue impacts of growing (or declining) organic search visibility. We based it all on data directly from Google Search Console (and then, as usual, a bunch of business assumptions).

Use Google Search Console Data to Forecast Revenue and Gross Margin Contributions from Search

With this model, we are trying to project the future revenue and gross profit impacts of increasing search visibility on Google via organic search efforts. This is based on a twelve month Google Search Console extract, which includes a 'dates' tab on impressions and clicks for your domain. With that data and some assumptions on conversion rates, we can extrapolate future revenue and gross profit contributions directly from search.

The result of this model may be a scenario analysis, a budget conversation justifying return on investment, or an update for the executive team. The ability to create a sound (albeit assumption laden) perspective on organic search efforts could be used in many different ways.

Forecasting Revenue from Google Search Console Data

Template Inputs & Forms

Website Impressions and Clicks

A twelve month daily summary of impressions and clicks from Google Search Console -> Google Sheets.

Compound Monthly Growth Rate for Clicks

The growth in monthly clicks derived from the aforementioned historical data (or tweaked to show a scenario analysis).

If you're unfamiliar with calculating compound monthly growth or you're unsure of your assumption, pivot the 'date' data in Google Sheets by month to see the sum of all clicks each month for the trailing twelve months. Use the following equation to get a compound growth rate from your own historical data: CMGR = [(Latest Month/First Month)^(1/# of Months)] - 1

Conversion Rate

The rate at which visitors convert to paying customers. Based on business data (or set as an assumption).

Average Revenue per User/Customer

The average revenue for a user converting from visitor to paid. Based on business data (or set as an assumption).

Gross Margin

Used for gross profit calculation. Gross profit is useful in determining how much cash is contributed back to the business for reinvestment or funding future efforts and growth. Based on business data (or set as an assumption).

Example Inputs for Revenue Forecasting from Google Search Console History

Data Sources

Google Search Console

Use the export functionality from Google Search Console under Performance -> Search Results. Make sure to change the date range to 12 months. Pull that bad boy into Google Sheets or Excel. Use some data formulas and a pivot table to convert the 'Dates' tab into sum totals of clicks and impressions by month.

Set Date Range of Google Search Console Data
Pull Google Search Console Data to Google Sheets

Conversion Stats

There's no single source for this info. If you have a way to sign up for free, what percentage of website visitors convert into free users? And what percentage of free users convert into paying customers? For example, we used some basic stats from our internal BI tools to get to the following data.

June '22
Clicks - 14,327
New Paid Customers from Clicks ~ 19
Conversion Clicks -> Paid ~ 0.13%

Basic Profit and Loss (P&L)

If you have access to a basic twelve month P&L, pull it in to base the gross profit calculations. If not, you can use a historical or average gross margin percentage for gross profit calculations. It may be useful to have additional data sets for more complex assumptions or projections, but this will get you going in the right direction.

Setup and Methodology

Columns

The model assumes a monthly time series, starting with the last full month of clicks and conversion to paid users (use actuals). The model will assume a twelve month forward projection.

Rows

For each month in the time series, the model will calculate the following: 

  • Clicks: Starting with the latest full month of click data, the model applies a monthly growth rate (input) to project clicks from search over the time series. 
  • Clicks -> Paid: For each month, the model multiplies the number of clicks by the conversion rate of clicks -> paid to get a number of new customers/users. 
  • Revenue: For each month, the model multiplies the new customers/user count by the average revenue per customer/user to project revenue forward.
  • Gross Profit: Lastly, the model multiplies the revenue for each month by the gross margin percentage input to see gross profit over the forecasted periods.

Output

By using this model, you should arrive at a projection of future revenue and gross profit contributions that can be attributed to organic search. It's certainly not bullet proof, but it gives you the beginning of a model that can grow in complexity and accuracy over time.

Visualizations to Consider

  • A line chart showing different scenarios for Revenue growth/decline
  • A bar chart showing number of new paid users/customers by month
  • A funnel chart showing the cumulative new clicks, paid users/customers by month
  • A line chart combining historical and projected data for clicks

Key Metrics to Consider

  • Revenue contribution from organic search (trailing twelve months)
  • Projected revenue contribution from organic search (next twelve months)
  • Projected gross profit contribution from organic search (next twelve months)

Conclusion

Determining how much to invest or how much time to spend improving SEO can be tough. With this model and some basic assumptions, you can show the potential opportunity for revenue and gross profit contribution attributable to SEO. This can drive the right conversations and business decisions for those less familiar with the data driving SEO decision making.

Superdeck is a no-code app builder that makes it easy to build beautiful, lightweight web apps to automate processes, create interactive plans and prototype ideas without any code. Join our waitlist to access this template.