https://analyticalmarketer.io/wp-content/uploads/2019/06/Forecasting-advertising-channel-revenue-with-Google-SheetsHeader.png 423 1210 Martin Weiss https://analyticalmarketer.io/wp-content/uploads/2021/09/AM-Logo-1.png Martin Weiss2019-06-17 12:39:202019-10-15 18:50:25Forecasting advertising channel revenue with Google Sheets
This is the fourth lesson of my Google Sheets for Marketers mini-course. A typical task every marketer has to face at one point or the other is to forecast things such as sales, revenue, conversions or something similar. Often this comes up when you have to create a robust marketing plan. Or when you have to present forecasts to superiors and stakeholders to secure budgets.
One challenge that usually comes up during the forecasting process is how you can predict the numbers as accurate as possible without using too complicated and time intensive methods.
This tutorial describes how to use simple linear regression (no worries, no math needed. There is a simple formula in Google Sheets for that) and formulas to forecast marketing channel revenue. As usual I’ll go through everything step-by-step and explain it all. But I recommend having open the mini-course workbook (please make a copy) to work along to really understand everything.
What you’ll learn:
The basics of the Google Sheets QUERY function, FORECAST formula.
In the past lessons of this course we analyzed the data from three advertising channel and learned that Twitter Ads are performing quite well. You think it would be a good idea to raise the budget of those ads to improve revenue. However you first have to convince your stakeholders and superiors of this to secure the budget. Part of this you have to forecast the potential additional revenue to prove the impact of more Twitter Ads budget. As such the task is the following:
If we rise our investments by 20% compared to last year, what will be the revenue from Twitter Ads on a monthly basis for the next year?
There are two implications from this question:
It won’t be enough to give a total estimate for the next year, but we have to give a forecast for each individual month.
We have to forecast at least 12 months into the future.
Preparing the data
Again I highly recommend to work along in the workbook, you’ll also find a solution sheet to this lesson there. As a first step we want to get the data we are going to work with into our worksheet. In our case the Twitter Ads data.
Of course we could simply copy the data from the Raw Data sheet and delete everything besides the Twitter Ads data. However this approach would get messy very fast, when working with large Data Sets. Instead we are going to work with the Google Sheets Query function. This function is probably one of the most powerful functions overall. It is kind of a pseudo SQL, which let’s you query, manipulate and analyze very large data sets in Google Sheets.
We won’t go into depth here and you’ll only learn a simple case on how to use the formula. However believe me, knowing this function will probably make you stand out among 97% of all marketers and it will seem like magic for those who don’t know it.
Put the following formula into cell A1 in your new sheet:
=QUERY(‘Worksheet – Raw Data’!A1:J118, “SELECT A, B, H, I WHERE A = ‘Twitter Ads'”,1)
The function =QUERY() has three input parameters. The first one ‘Worksheet – Raw Data’!A1:J118 defines the dataset you want to work with and which you want to query. In our case all the raw data. “SELECT A, B, H, I WHERE A = ‘Twitter Ads'” is the actual query. What you are doing here is you are selecting columns A, B,H and I. The WHERE statement introduces a condition and lets you only select those rows, where the cell in column A equals Twitter Ads. The last parameter 1 simply indicates that there is a header row, which we want to display as well.
As such we build a simple very fast filter to only select the data we need. Feel free to play around a little bit with the Query function. You can add more conditions with an AND behind the WHAT the statement. E.g. SELECT A, B, H, I WHERE A = ‘Twitter Ads’ AND H >1000 would give you the same as above, however only those rows, which have a revenue higher than $1000.
We are almost done with preparing the sheet. Just add Cost Forecast and Revenue Forecast as headers in cell E1 and F1. Write Twitter Ads in A41 and =B40+31 in B41. Copy both down until row 52.
The actual forecasting
We will have to do two things here. Plug-in the future potential budgets and forecast the resulting revenues.
The first one is quite easy. As the task states we will have a 20% higher budget compared to the last 12 months. So we will simply multiply the costs of each of the last 12 months with 1.2 in order to get the investments for the future 12 months. As such write the following into E41 and drag it down for the other months:
Next we’ll do the revenue forecasting itself with linear regression. It is easier than you might expect as there is a formula in Sheets exactly for that. Write this into F41 and again drag it down for the other future months:
What the FORECAST formula does is it predicts through linear regression a future value by using existing values.
In our case the predicted value is revenue (y-value) for a given cost (x-value). The existing revenue values (y-values) are in cells C2:C40 and existing cost values (y-values ) in cells D2:D40.
I added $ dollar signs in above formula in order create an absolute references. So when you drag the formula down it won’t change the referenced existing values.
You’re done! Or at least almost. Technically you are done already – you predicted revenues for Twitter Ads for the next 12 months. However nobody wants to read a boring table and drawing insights from that table is very difficult. So the last step to take is to build a chart on top of your analysis.
Insert a chart from the menu: Insert > Chart and change the chart type to Linear chart (Need a refresher on charts? Check out lesson 3):
Add the following two series:
Series 1 – C1:C52
Series 2 – F1:F52
and as X-axis: B1:B52.
You’ll probably notice now that there is an ugly and confusing gap between the lines of the historic data and the forecast. In order to get rid of that add the following to cells F40:
This will level the start of the forecasts to the actual revenue.
That’s it! Obviously you can change the colors of the lines (which I did in above screenshot and the example sheet) to make it look prettier and more organized.
Certainly above is a very simple solution for forecasting revenue as there are a lot more advanced techniques such as the moving average forecasting method. We are also not counting in any other influencing factors such as seasonality, competition etc..
Nonetheless above is a valid demonstration on how a simple data science technique (linear regression in this case) can be applied to real life marketing challenges by everyone. You could use above methods for all kind of marketing forecasting, e.g.:
How will the the next days outside temperature affect my sales?
How will the number of blog posts affect my website traffic?
Will conversions go up or down during winter months?
In the last lesson of this mini-course we will build a dynamic reporting dashboard, which pulls its data automatically from Google Analytics. So be sure to don’t miss out lesson number 5.