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).

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.

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‍

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:

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.

Superchart is the easiest way to create professional charts and embed them online. Join our waitlist to create live, interactive charts that can be embedded into your website or blog within a matter of minutes.

Originally published on the Superchart site.