Marketing Mix Modeling (MMM) Tutorial in Google Spreadsheets

Overview

What is Marketing Mix Modeling (MMM)?

Marketing Mix Modeling (MMM) is a statistical technique that gives you insight into the amount of sales contribution each media channel makes, so as to better optimize your advertising spend.

Marketing Mix Modeling made easy

Almost all of the Fortune 500 and a growing share of smaller startups rely on Marketing Mix Modeling to understand the incremental performance of each channel so that they can allocate budget between them. This technique has risen in popularity due to iOS14, as digital tracking methods using cookies or device IDs are now less reliable, and more privacy friendly techniques like MMM are being increasingly incorporated into decision making.

We made this template because honestly it’s a lot of work to do MMM manually if you don’t have the benefit of reusable code or software, and yet nothing beats the humble spreadsheet for developing an intuition for how these things work. We made it free because too many people we know were getting ripped off by consultants doing essentially a worse version of this and charging thousands of dollars for the pleasure. While we would be very careful about using this model for production decision-making (since MMM is hard), it should help to give you some more intuition for how these types of models work.

Marketing Mix Modeling example in Google Spreadsheets

In this tutorial you will learn how to predict future sales at different budget allocations with a simple (ready-to-go) template. Linear regression in Google Spreadsheets is a good start that will help you estimate the incremental performance of each marketing channel—all without the help of a data scientist. An example template using Google Sheets’ LINEST function (using the least squares method) can be seen below:

https://docs.google.com/spreadsheets/d/1kfatNU0cs6VJoRdhqUkGuOnSFNJ27rL-UWmdaRGVLP8/copy

Feel free to use the template in your own analysis by clicking the link below and creating a copy to paste your own data into. If you’re modifying this template for use in creating your own blog posts or videos, all we ask is that you please provide proper attribution back to Recast.

> Recast MMM Template
> Explainer Video

If you are interested in a more advanced analysis in code, that can help you better automate marketing mix modeling, then have a look at our Bayesian MMM tutorial on the Recast website, or talk to us about our automated MMM solution.

Table of Contents

How to build a Marketing Mix Model in Google Spreadsheets:
Equation and method
4-Step Process
Model Limitations
Frequently asked questions about MMM
Send us your feedback

How to predict marketing performance in Google Spreadsheets

Equation and method

The ordinary least-squares (OLS) method is one of the most commonly used methodologies for predicting simple linear regression models. Linear regression is a good way to get started with Marketing Mix Modeling, because it can be done in a spreadsheet, which makes it relatively accessible compared to more advanced machine learning or Bayesian models.

The model attempts to make the most accurate prediction of the dependent variable (revenue or conversions) based on already available data on the independent variables, namely media spend by channel, as well as organic drivers like seasonality or national holidays. Once a model is built and validated, it can be used to estimate the incrementality of each media channel (how many sales wouldn’t have occurred without spending on ads), as well as to forecast future allocations of advertising spend (what happens if you double your advertising budget).

The formula for a simple linear regression model, with just one media channel and one organic variable, looks like this:

Y = B2*X2 + B1*X1 + B0

Where:

• Y is the revenue from sales, or number of conversions
• B2 is the coefficient for advertising, i.e. how many sales you get for each dollar spent
• X2 is how many dollars you spend on advertising
• B1 is the coefficient for an organic variable, i.e. for example google search trends for your industry
• X1 is the value of an organic variable i.e. an index of google search trends from 0 to 100
• B0 is the intercept, or how many sales you’d get if you spent $0 on ads and had no contribution from organic variables

Note that not every marketing mix model is formulated in this way, and some more advanced techniques, like the models we build at Recast, don’t ‘control for’ organic variables in the same way as this example does. However this is a good entry level introduction to MMM.

There are three general approaches to Marketing Mix Modeling you can choose from:

Using Google Sheets – This is a basic model suitable for small businesses and startups just getting started with MMM. If you’re a Tenjin customer, then you can get access to the aggregated data you need with Tenjin’s Data Exporter using the steps mentioned in the “Process” below. If you are not a Tenjin customer, you can still use this template as long as you have the data split by day or week over 6 months to 3 years. If you’re confused about how to do this you can contact us at info@growthfullstack.com, and our customer success team will get back to you as soon as possible.

Using a Python or R Script – This method is more advanced, and not covered in this tutorial. We recommend users with larger data sets, or more campaigns, spending more than $100k per month to attempt building a custom model. You can use open-source libraries like Robyn by Meta, or LightweightMMM by Google, but you’ll likely need a Data Scientist to build and interpret your model. When building a model make sure you follow a checklist of features good modern MMMs should all have.

Working with an automated solution – This is the category we fall into with Recast, our tool which automates this process and displays the results in actionable dashboards so you can optimize your budget and eliminate wasted advertising spend. If you don’t have data science expertise in-house, or don’t want to tie them up trying to solve the complexities that make MMM hard, this can be a good option. Check out our build vs buy calculator for more information.

4-Step Process

The first step to implement this simple marketing mix model is to make a copy of this Google sheet. Take a look at the explainer video first as this will help you understand what’s going on with the template and how it works.

> Recast MMM Template
> Explainer Video

Next, you can follow the steps below to build your model:
Step 1: (For Tenjin customers): Log into the Tenjin dashboard, click on the “Data Exporter” button on the top right, select a date range, choose “Weekly” as your granularity, the app you want to use for the prediction, group by campaign and download CSV for Day 7 ad revenue.

If you’re not a Tenjin customer you need to export your daily spend from each ad platform and revenue, or app installs, or whatever conversion metric you’re trying to predict from whatever analytics tool you use. It needs to be in tabular format, meaning one column per variable and one row per week.

Step 2: Copy and paste this data into the RAW tab. If you have separate campaigns for different countries, you likely need to create a new model for each country. Tick the checkbox for each variable that represents a media campaign. You may also want to add a constant term (i.e. ‘1’ in every row) as we configured the LINEST function to not add one by default.

When using the LINEST function in Google Sheets, it won’t let you have any missing columns, which means any time you want to select or deselect a variable in your model, you normally have to do a whole lot of copying and pasting and reformatting. This sheet gets round that by doing an ARRAYFORMULA to conditionally add or remove columns in the Select-Train and Select-Test tabs, which makes it necessarily complicated. If you want a simpler introduction to LINEST check out this Econometrics Tutorial on my personal website.

Step 3: Build your model by choosing what variables you want to include, as well as what carryover and saturation rates provide the best fit with the data. This is an art in itself, and is done by looking at what makes the model more accurate as well as more plausible. Note: if you select multiple variables that are the same, the model will break, so if you run into an error that’s likely the issue.

Accuracy is measured by the model’s ability to predict future values it hasn’t seen yet, which is done in this model by splitting the data into TRAIN and TEST tabs. The MAPE is the Mean Average Percentage Error, or how wrong the prediction is on average in predicting the data we held back in the TEST tab. NRMSE is Normalized Root Mean Square Error, an alternative accuracy metric which penalizes bigger spikes and dips more.

Plausibility is measured by whether the numbers make sense, for example is the model saying that one small channel is driving an outsized contribution to sales? This is partially measured by the RSSD metric, or the Residual Sum of Squared Differences, essentially a measurement of how far from your current allocation of spend the model would recommend. There are also other statistical measures of plausibility, such as Significance (based on P values), Uncertainty (based on Margin of Error), as well as Skew and Kurtosis (how biased or spiky the errors are).

It’s likely if you didn’t study statistics you’ll need to work with a business analyst to interpret some of these figures, and find your way to a working model through trial and error. You can also take some of the Marketing Mix Modeling courses at Vexpower and start to learn yourself how to do it (or buy a subscription for someone on your team).

The CORR tab helps you identify which variables are correlated with sales (or with each other) which helps when selecting which features go in the model. If you want to look ‘under the hood’ as to what’s happening with each metric in the model, check out the Est tab where most of these metrics are calculated.

Step 4: Once you have a model you’re happy with, on the charts on the MODEL page you can visualize the implications and take action. You can see the resulting accuracy in terms of how well it predicts past and future data (top left) and decide how much to trust decisions made using insights from the model. If you find that some channels are driving a far larger contribution (top right) than expected from your digital attribution, you can invest more in those channels (or cut spend if their contribution is far lower than expected). You can see the decomposition (bottom left) and what variables were responsible for dips and spikes in sales. You can also see the saturation curves (bottom right) and choose what level to spend at in order to avoid reaching diminishing marginal returns.

If you want to forecast the results of future spending allocations, you can check out the “PLAN” tab in order to see the potential CPA (Cost per Acquisition) or ROAS (Return on Ad Spend) at different spending levels, based on your model. You can also just add new data for your dependent variables into the RAW tab, and adjust the “train split”, cell B49 in the EST tab, until you have the same number of training observations as before.

Limitations of this model

Simple linear regression assumes marketing doesn’t improve / decline in performance over time, and may show spurious results like that some channels drive negative sales. For such cases we recommend you look into more sophisticated techniques like Bayesian MMM.
It’s very possible that you don’t have all the data you need to build a valid, accurate model. This is a common outcome, particularly if you haven’t allocated enough time to collecting data (which can take weeks or months depending on your business size and structure), or if your marketing performance is driven by variables that are hard to measure.
It’s quite easy to build the wrong model and make decisions based on its findings, that turn out to lose you money. Always consult with a statistician or business analyst who understands linear regression, or learn more about MMM yourself before making important irreversible decisions with this method.

Frequently asked questions about MMM

What is MMM? Why should I use it?

Marketing Mix Modeling is a statistical technique for estimating the incrementality of each marketing channel, so you can better allocate your budget and eliminate wasted advertising spend. You can find more detailed information on when MMM makes sense to incorporate into your attribution stack here.

What is the difference between MMM and MTA?

Multi-Touch Attribution (MTA) requires user level data in order to deterministically assign attribution back to users who saw or clicked on ads. MMM requires only aggregate data and matches spikes and dips in sales to events or actions in marketing.

Send us your feedback

Your feedback helps us constantly improve and update our current use cases, or in creating new use cases as per demand. If you have any questions regarding this use case, or would like to have a further discussion on the topic, then reach out to us at info@growthfullstack.com.