Analytics Vidhya
Published in

Analytics Vidhya

Marketing Mix Model Guide With Dataset Using Python, R, and Excel

1.0 Introduction

Common Data Types for MMM

  1. Seasonality: For example, sales during the Christmas season are generally high than average sales.
  2. Macroeconomic Data: CCI, inflation, unemployment rate, GDP, etc.
  3. Product Sales Price: base price, Avg. sales price
  4. Distribution: No. of stores or No. of locations the product is available. The distribution chain can impact business outcomes. For example, the stock product may cause sales to decrease.
  1. Advertising data: TV / Radio/ NewsPpapers / Magzines / Search/ Display / Social Media / sponsorship / Affiliation / Content Marketing etc.
  2. Promotion data: No. of offers/days for which offers are provided. e.g. a price promotion like free delivery, 0% APR or cashback, etc is not supported by any advertisement that would also drive sales.
  1. Sales: It is impossible to build a MMM model without the sales variable. is generally considered as a dependent variable in MMM. Sales can be in volume in units as well as revenue($).
  2. Advertising spends data: Get the spend data from the internal marketing team or through an external marketing agency.

2.0 Data Preparation and Data Transformation

The Lag, Carryover Effect, and Shape Effect

Exhibit 1: Lag Effects
Exhibit 2: Decay = 1-Carryover Effect
Exhibit 3: Shapes of different sales response functions — Credit for

3.0 Variable Selection Process

Exhibit 8: VIF Scores
Exhibit 8: VIF Scores
Exhibit 4: The Raw Data
Exhibit 5: First Transform — The Lag
Exhibit 6: Second Transform — Power Curve Applied
Exhibit 7: Third Transform — Calculate the Decay Value
  1. Find out the correlations of the media with sales
Exhibit 8: Top 9 Radio transformations
Exhibit 9: Top 9 TV transformations
Exhibit 10: Top 9 Newspaper transformations
Exhibit 11: All Iterations.csv
Exhibit 12: Matrix
Exhibit 13: Apply ‘All Iterations’ columns name to generate 27 formulas
Exhibit 14: 27 Model Results

4.0 Model Findings (Actual vs. Model)

Exhibit 15: Actual vs Model

5.0 Media Incremental Contribution

Exhibit 16: Contribution
  1. To make it easier to visualize in Tableau, I changed the ‘week 1’ to ‘01/06/2016’, and so on.
  2. Intercept activity can be set as 1, and spend is $0.
  3. Let’s assume the cost per activity is: TV $3000/activity, Radio $2000/activity, Newspaper $1000/activity.
Exhibit 17: Media Contribution Impact % YOY
Exhibit 18: Media key Metrics
Exhibit 19: Media Effectiveness & ROI

6.0 Budget Optimization Solution

Exhibit 20: Optimized ROI
Exhibit 21: Radio — actual vs optimized spend
  1. The objective is to maximize ROI
  2. We are going to change the media spend(optimized spending)
  3. Optimized spend no more than or less than 20% of the original media budget
  4. Spend total should keep the same as original

7.0 Avoiding common pitfalls

  1. Bad data in, bad data out: First, A good MMM requires sales and marketing data collected at similar frequencies. The common mistake is to record marketing efforts less frequently than sales. Second, sometimes the data itself is bad, it may conclude outliers or missing values. Third, media units may incompatible. For example, TV media will be counted as a Gross Rating Point (GRP) while not equivalent to digital world display ads which use impressions.
  2. Collinearity: As we mentioned in the previous chapter, it is important to check multicollinearity. Because it is common for advertisers to simultaneously increase/decrease/pause the amount of their marketing spends on multiple channels. For example, TV and radio spending at the same time and by a similar amount, this collinearity makes it difficult for MMM to determine whether TV or radio spending was more impactful on sales.
  3. Omitted Variable Bias: For example, when the price change of the product decrease or increase sales substantially, however, if we remove the price factor in the model, its impact may falsely be attributed to a simultaneous change that occurred in some marketing spending, leading to an incorrect conclusion about the advertising ROI and furthermore, may mislead the marketing strategy.

8.0 Summary



Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jacky Yuan

I am a Marketing Data Scientist focused on Advertising Effectiveness, MMM, MTA, Causal Inference, and product growth.