This is the first in my series on data driven marketing examples and case studies including step-by-step guides on how to apply simple data analysis techniques to real life marketing challenges.
In this guide I’ll describe how to use simple linear regression and formulas to forecast marketing revenue in Google Sheets. Above chart is based on (anonymized) data out of my work at Google. It’s the marketing spend from an account from one of our smaller clients. You could pull the same kind of data from your own Google Ads account (or similar data from Facebook or any other marketing channel).
I’ll go through everything step-by-step and explain it all, but I recommend having open below sheet with the example data and play along to really understand it.
So picking up above example imagine you get the following task from your superiors or a client:
If we rise our investments by 20% compared to the last 6 months, what will be the revenue from Google Ads on a weekly basis for the next 6 months?
There are some implications from this question:
- It won’t be enough to give a total estimate for the next 6 months, but we have to give a forecast for each individual week.
- We have to forecast at least 24 week into the future.
In addition to the task you have a Google Sheet containing dates, costs and revenue data from the last year.
Cleaning, formatting and preparing the data
As mentioned in my other post one of the first steps is almost always to prepare and format the data in the right way. Possible to-do’s here could be formatting the fields correctly (e.g. costs and revenue as dollar values) or deleting not needed columns. Luckily this all has been done already in the sample data sheet.
However there is still some stuff to-do.
First, we should check if costs and revenues are related. Obviously this should be the case as investing more in Ads should lead to more traffic, which should lead to more conversions – but you never know. And you might use this method on a data set, where it isn’t as obvious, which why is testing for correlation is so important (however don’t confuse correlation with causation).
It is actually quite easy in Google Sheet as there is an formula for correlation. So in a cell of your choice type in the following:
You should get a correlation coefficient of 0.81, which indicates (as expected) a fairly strong positive relationship between ad investments and overall revenue.
Next we’ll add some rows for the future weeks. You can do this quite easily by writing the following into cell A54:
It will add 7 days to above date. Now drag it down into the other cells until you have all the 24 future weeks.
Now add some additional columns headers. For E to J add the following headers in row 1:
- Costs Forecast
- Revenue Forecast
- Revenue Forecast Lower
- Revenue Forecast Upper
- Deviation Upper
- Deviation Lower
Your sheet should look like this now:
That’s it for preparation. Let’s start forecasting!
The actual forecasting
We will have to do two things here. Plug-in the future investments 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 6 months. So we will simply multiply the costs of each of the last 24 weeks with 1.2 in order to get the investments for the future 24 weeks. As such write the following into E54 and drag it down for the other weeks:
Next we’ finally do the revenue forecasting – and it is easier than you might expect as there is a formula in Sheets exactly for that. Write this into D54 and again drag it down for the other future weeks:
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 D2:D53 and existing cost values (y-values ) in cells C2:C53. You can use this function to predict revenue, traffic, and a lot of other things.
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.
All resulting in this:
Factoring in eventualities
Forecasting is never 100% accurate. You will always have a deviation upwards or downwards. Taking this into account is quite important and as such you should also show it in your analysis.
We’ll take the assumption that in our first predicted week we will have a deviation of 5% in either direction, meaning the actual revenue can be either only 95% of the predicted revenue (lower bound) or 105% of the predicted revenue (upper bound). Therefore plug in 0.95 in cell I54 and 1.05 in cell J54 .
Further we assume that our prediction will become more uncertain the further away the predicted weeks are. As such we assume that for each additional week there will be an additional uncertainty of 0.5%. This leads to a deviation of 16.5% in either direction in the final future week. You can easily calculate those number by writing
=I54–0.005 in cell I55
=J54+0.005 in cell J55
and dragging both down as usual.
Now we’ll simply calculate the upper bounds by multiplying the predicted revenue numbers with each downward and upward deviation. Write the following formulas and drag down accordingly for a last time:
Your forecast including eventualities:
You’re done! Or at least almost. Technically you are done already – you predicted revenues for your marketing channel for the next 6 months. However nobody want to read a boring table and drawing insights from that table is very difficult to impossible. 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:
Add the following four series:
Series 1 – D1:D76
Series 2 – F1:F76
Series 3 – G1:G76
Series 4 – H1:H76
and as X-axis: A1:A76.
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 F53, G53, H53:
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 more sophisticated machine learning methods. We are also not counting in any other influencing factors as for example seasonality, competition etc. when forecasting based on the last 6 month.
Nonetheless above is a valid demonstration on how simple data science techniques (linear regression in this case) can be applied to real life marketing challenges by everyone, even when non-technical. 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?
Did You Enjoy This?
Then feel free to sign up for my newsletter. Get my newest articles on career & skill development for marketers and guides on technical marketing every week.