Plain math to make a forecast for a marketing plan based on Google Analytics data

Google+Tweet about this on TwitterShare on FacebookShare on LinkedIn

This post will demonstrate one of the possible solutions for creating a marketing plan with Google Analytics data using just Google Sheets and plain math.

Hopefully, this is what you will get if you take on board the solution (a gs table actually) that is described in this post.

marketing plan with google analytics data: result

Why do it?

First off, analytics in general (and web-analytics in particular) is not only about gaining and structuring data. Obviously,  it’s more about “torturing” data you gained so to unfold the precious insights by any means. Google Analytics metrics are good at representing basic state of things, but oftentimes that’s not enough.

That is why tools like Data Studio (allowing you to make you own metrics & work with different kinds of data) and Google Bigquery (big data processing, fast and affordable) must be ranked with GA (if not higher) in importance.

(a standard analytics gent’s set)

marketing plan with google analytics data: set

Secondly,  your marketing department must have a certain plan containing numbers that are based on data, not on managers’ guess-work or competitors’ achievements.

Finally, you may explore something new about our users/site/conversion etc. while comparing plan figures with the actual data.

So what’s the plan?

We’re going to use two simple methods for making a forecast based on metrics data we collect in Analytics. It can be anything you like: transactions, certain events, bounce rate. In my case for demonstration purposes I’ll use a simple metric like number of sessions.

Using this forecast, we can build a marketing plan with Google Analytics data.

Methods to make a forecast for a marketing plan with Google Analytics data

We are going to use the following methods:

  1. Ordinary least squares
  2. Moving average

Plus, we’ll have a formula for measuring observational error. In brief, here are formulae for both methods.

Least squares

    \[ F_t_+_1 = ax + b$ \]


Ft + 1 = forecast for the period, ab = coefficients, x = number of the time period.

The coefficients (a and b) are calculated according to the following equations:


    \[ a =\frac{\sum_{i=1}^{n}{D_a * x} - (\sum_{i=1}^{n}{D_a} * \sum_{i=1}^{n}{x}) / n}{\sum_{i=1}^{n}{(x^2)} -(\sum_{i=1}^{n}{(x)^2) / n}} \]


Da = actual data you possess, n = number of periods in time range


    \[ b =\frac{\sum_{i=1}^{n}{D_a}}{n}-\frac{a * \sum_{i=1}^{n}{x}}{n} \]


Moving average

The formula for this method looks like this


    \[ F = M_t_-_1 + \frac{1}{n} * (D_a - D_t_-_1) \]


F = forecast, Mt-1 = the value of moving average for the period before last. There’s also one more formula for measuring observational error:


    \[ Err =\frac{1}{n}\sum_{i=1}^{n}\frac{D_a - D_f}{D_а} \]


Df = Forecast data

Though these formulae may seem to be complicated, trust me, they are easy to use. You will soon see it.

Forecast calculation

I’ve prepared a table that uses formulae above for making a forecast with Google Analytics data. You can easily access it by following the link below.

All you have to do is to paste your data to the B column. Feel free to leave comments where necessary. Now let’s have a closer look at the table.

Least square tab – makes a forecast using the least squares method

Moving average tag – makes a forecast using the moving average method

Comparison of methods – demonstrates the results for both methods.

Now, when you have a forecast, you can try making a marketing plan, say, like this. Plan = month forecast value + 10%


Obviously, the described methods have certain weak points. For instance, they are only applicable for forecasting short periods (especially, least squares method). However, they work perfectly for almost any kind of data.

Feel free to ask your questions in the comment section below.

Google+Tweet about this on TwitterShare on FacebookShare on LinkedIn