How to Marketing Mix Modeling (MMM) with EXCEL — Intro

Liked this post? Share it!

Share on facebook
Share on linkedin
Share on twitter
Share on email

This article will help you understand what Marketing Mix Modeling is and how it can help your Business Grow.

Thanks to Mike Taylor for his post on Econometrics in GSheets, the inspiration for our template

If you prefer watching instead of reading, I’ve created a free Masterclass on Youtube: >>Click Here to Access<<

What is Marketing Mix Modeling

MMM are statistical techniques applied to historical data to understand the marginal contribution that each individual factor has on our sales.

They quantify the impact that each factor, measure what’s their contribution to sales and help you to understand how much to invest on each channel to maximize Sales or Conversions.

It’s an analytical approach invented in the 1960s and used by big corporations to allocate budgets across departments in order to achieve corporate financial goals.

In 2010, with the event of social media and search engines, MMM methodology has seen a downtrend… until now 🙂

MTA vs MMM

In the eCommerce space, we are used to seeing Google analytics dashboards, that help us understand users’ behavior.

The analytical technology behind Google analytics is Multitouch Attribution (MTA).

It’s an analytical framework that uses cookies to track any user’s behavior inside our website.

It’s been incredibly powerful until April 2021 when IOS 14 have launched.

With IOS 14 there have been restrictions on sharing third-party cookies and this caused analytical tools that used cookies technology to have a high discrepancy with reality.

Facebook ads (Meta ads), Google ads, Google analytics, and many more platforms, have a really hard time tracking users’ behaviors.

While Multitouch Attribution focuses on the user’s behaviour, Marketing Mix Modeling focuses on how efficient is your investment

In fact, it’s a cookieless framework to do marketing analysis on your Marketing Mix, that hasn’t had any effect from IOS 14.

What is Marketing Mix Modeling Used for?

Marketing Mix Modeling (MMM) is used to approximate how many sales you are gonna have based on the values of your inputs:

Inputs can be:

  • How much you spend on each channel
  • What’s your product’s price
  • Seasonality
  • SEO Activities
  • Competitor’s activities
  • Offline media investments
  • Other External factors

And from them we can:

How do Marketing Mix Modeling (MMM) work in practice?

Let’s imagine we have a time series that described our sales over time

from this graph, we don’t understand enough, but what I notice are 2 spikes really close:

We are gonna use a Marketing Mix Modeling tool called MMM Decomposition graph to deduct what caused this variation.

In this graph, we have a decomposition of how Holiday, Ads Spent, TV spent, and price contributed to our weekly sales.

As we can clearly see the variation is described by the holiday’s contribution.
In the absence of “holiday”, we do 100$ less in sales.

The MMM decomposition graph it’s an incredible tool for Diagnostic Analysis and can help us understand why some variations happen.

What kind of data should I use in my Marketing Mix Modeling (MMM)?

Let’s explain the data engineering behind MMM dataset.

This is an example of a simple dataset for MMM:

  • First column: timestamp variable
  • Second column: output variable (what you are trying to predict)
    Can be either revenue, conversions, or even sessions.
  • Other columns: input variables

There exist 2 types of input variables:

  • Numerical: all the values that have an extended length from 0 to N.
    They can be budget spent, sessions, price, impressions …
  • Categorical: Input that has fixed values.
    They can be a day of the week, months, year, promotion days …
    They are generally described by boolean values (1 or 0)

If you are an eCommerce and you want to collect your data, follow this procedure:

  • CMS: order export
    Create a pivot on the orders with this framework:
    Dimension: Date
    Value: SUM(revenue) or SUM(orders)
  • Adv Platform (report section):
    Dimension: day or date (depending on the platform)
    Value: Budget spent
  • Google Analytics (report section):
    Dimensiondate
    Value: Session
    Filter: Medium: Organic
  • Email Marketing (report section):
    Dimension: date
    Value: Recipient

Then copy and paste into multiple sheets in Google Sheets.

Use the “=VLOOKUP” function to centralize every value into the right cell in a unified sheet.

Repeat this process with all the sheets and data sources you have until you have one unified sheet with all your data.


MMM Transformation

Once we centralized everything we need to transform all advertising data with 2 transformations:

  • AdStock: transformation that measures the impact of past advertisements on present sales.

    It’s also called “Carryover effect”, and states that if you spend 10.000 € today, not all the people that see the ad will buy the same day, someone will buy the next day and someone else will buy the next week.

    With the Adstock transformation, we approximate the marketing investment effect over time.
  • Diminishing Returns: returns on investments are not linear, the more you increase your budget the less will be the marginal returns.

    For this reason, we use a power function to approximate the response on advertising investments based on the volume invested.

    >> See this EXCEL TEMPLATE to check all the formulas<<

Learning Algorithms

Once we transformed all the variables we can start using the function =Linest(‘output column’, ‘input columns’, 1, 1) (linear regression).

to get our model function.

Keep in mind that the linear regression creates a function similar to this one:

y = b1x1 + b2x2 + b3x3 + b0

It’s gonna give us the coefficients “b” of the function.

This is a Linear Regression’s output example on Excel

if you want to check out the template >>Click Here<<

What this model is saying to us is:

Intercept = 3, which means if we shut down every media investment, we would still get 3 sales.

TV Coefficient = 0.19, means that every dollar spent on TV results in 0.19 incremental transactions

To generate 1 transaction with TV Spent, we would have to spend 5$.

Same School of thought for the other coefficients.

Hyperparametrization

After Training, the goal is to find the best performer hyperparameter for AdStock and diminishing returns that will increase our R²

The values colored in yellow correspond to Beta and Alpha in the transformations we did before.

Transforming each media variable according to this formula:

media_transformed = (Adstock (t-1)*Beta + Spent(t))^Alpha

You can change them until you find all the values that maximize R².

I created a template that you can copy, so you’ll have the logic under your hand >> Click here to access it<<


Post-training analysis

After modeling, we need to generate our predictions and see graphically how accurate is our model.

The formula in excel would be something like this:

=Coef_FB$*B2+Coef_TV$*C2+Coef_Radio$*D2+Intercept$

You’ll find the actual formula in the excel template.

Results are gonna show our model predictions.

Copy the “actual sales” column next to predictions and create a line plot that shows how accurate are your predictions over time:

X-Axis = Time

Y-Axis = Prediction, Actual Sales

This would be the result

Conclusions

You created your first model on EXCEL, congratulations!

This would be just the introduction to Marketing Mix Modeling, in the next post we will cover:

  • How to create a Forecaster Scenario Simulator
  • How to create an automatic budget allocator
  • How to do Diagnostic Analysis with a MMM Decomposition Graph
  • How to see Statistical attributions from your model

Do you want to start now with Marketing Mix Modeling?

We deliver a Marketing Mix Modeling service that help you understand how much each investment is contributing to your sales.

It’s a 2 weeks program that allows you to unlock all the Marketing Mix Modeling insights for your business and help you make better Data Driven Decisions in the future.

Book a call through this link: >>Click Here<<

Do you want to install Advanced Marketing Mix Modeling and Machine Learning in your Business?

I introduce to you Cassandra: an Advanced MMM Service that uses AI to predict what to spend on each channel to maximize ROI

Service: https://cassandra.app/

It’s a service that allows your company to:

  • Centralize your data in a central data warehouse
  • Train advanced Machine Learning models that will predict what to spend on each channel to maximize ROI
  • Display all the insights you need from 1 single dashboard

Source: https://www.saxifrage.xyz/post/econometrics-gsheets

More Free resources for you

Reducing CPA by 30% with Spedire.com

How to optimize CPO by identifying marketing budget waste Spedire.com is one of the biggest sellers in the shipping industry in Italy.  It had an

Find out how Cassandra can help you

Click below to book a call with one of Cassandra's founders to learn more about how it works and get the most out of your data