You recently started as an intern at Vexnomics and are eager for more responsibility. Finally your boss comes to you with an exciting opportunity to build your first marketing mix model!

Hey have you got a sec?
We just got a new client, but the analysts are all at capacity.
I know you are looking for more responsibility: would you want to take this on?
I can walk you through how it works. It should be a pretty simple model and the client is an old friend of mine – it'll be a good project to learn on.
So what do you say? Are you interested?

Marketing mix modeling (MMM) is a statistical technique that helps companies analyze past marketing and sales data to determine the most effective marketing strategies for future growth. It involves using historical data to model the relationships between various marketing inputs and sales outcomes, and then using those models to simulate the impact of different marketing scenarios on future sales.

The "marketing mix" in MMM refers to the four key elements of any marketing strategy: product, price, promotion, and place. These elements are often represented by the acronym "4Ps". MMM helps companies understand the relative impact of each of these elements on sales, and how they interact with each other. For example, a company might use MMM to determine the optimal price for a product, or the most effective mix of advertising and sales promotions.

MMM typically starts with a large dataset of historical sales and marketing data, which is then cleaned and prepared for analysis. This data is used to create models that describe the relationships between the different marketing inputs and sales outcomes. These models can take many forms, including linear regression models, time series models, and econometric models.

Once the models are built, they can be used to simulate different marketing scenarios and predict the likely impact on sales. For example, a company might use MMM to evaluate the impact of a price increase on sales, or the effect of a new advertising campaign on brand awareness. This allows companies to make more informed decisions about their marketing strategies and allocate resources more effectively.

MMM can also be used for optimization, which is the process of finding the best combination of marketing inputs that maximizes a specific performance metric, such as profit or market share. This can be done by using optimization algorithms, such as linear programming or genetic algorithms, to find the optimal solution.

MMM is a powerful tool for companies of all sizes, and can be applied to a wide range of industries and products. It can be used to optimize advertising and promotional campaigns, evaluate the effectiveness of different distribution channels, or determine the optimal pricing strategy. MMM can also be used to forecast future sales and identify potential market opportunities.

However, MMM is not without its limitations. One of the main challenges is that it relies on historical data, which may not be representative of future conditions. Additionally, MMM models can be complex and difficult to interpret, which can make it hard for non-experts to understand the results. Another challenge is that MMM can be time-consuming and resource-intensive, which can make it difficult to implement in some organizations.

Marketing Mix Modeling

00:01 Hey, let's learn how to build a really simple marketing mix model. So, you know, I say really simple. I mean really simple compared to the normally complex task.
00:10 You know, you could take months to build a marketing mix model. We're gonna build a very, very simple dumb one just so you understand the mechanics.
00:17 And once you understand the mechanics of it then you'd be able to build them yourself and, and layer in some more complexity later.
00:23 Cool. So we already have one built, we call this the kitchen sink model. And it's just a list of different variables that we're using in our model.
00:32 And then it tells us a few different things. So one is what's the share of revenue? So we're saying Facebook drove 65% of all of our revenue.
00:42 It also tells us the coefficient.  So in this case saying Facebook gave us a dollar, you know, $4 18 cents for every $1 invested.
00:53 And then it also tells us the p value. So are there variables significant or not? Here we have the r squared as well.
01:02 And that tells us like, is the model a good fit to the data? So again, lots of simplification here. There are, there are many, many ways to do this <laugh>, but one way that people use when they're doing econometrics, when they're doing really simple stuff like this, is they build a kitchen sink model, right?
01:18 Which is all the data that we have we're trying to predict, but then they economize on the number of variables.
01:24 So we have lots of variables in here for not that much data, and we can see that a lot of our variables are not statistically significant.
01:31 So if it's above 0.05, it's not significant. Affiliate costs in here Monday and Wednesday as days are not significant. So, you know, if you kind of look here we know at least from this model, what they're saying is, you know, the coefficient from Monday is minus 1000.
01:48 And saying if you're trying to predict any given day, if that day happens to be a Monday, deduct $1,000 from that daily revenue total.
01:56 And that's gonna get you closer to prediction. Same thing for, you know, Friday. It's saying on Fridays, we're gonna make on average $4,500 more than, than the typical day.
02:06 So so, you know, it knows more about you know, Sunday and it knows more about Thursday and Friday than it does about Monday and Wednesday Here.
02:16 These are statistically insignificant. Therefore, you know, it doesn't, you know, it's like, at least with the data we have, it can't figure these out.
02:24 And having too many variables in the model is bad. Generally you know, you should try and predict it with as few variables as possible.
02:31 That still gives you an accurate re result. The reason is accuracy can be measured either based on the data you give it, right?
02:39 Which is one thing. And if, and, you know, on that measure, adding more variables is always gonna improve accuracy. But the real goal of Maxi Mix modeling is to be able to predict the future and say like, if I keep spending on Facebook, what's gonna happen?
02:52 So you want the model to generalize. Well meaning you want it to predict data it hasn't seen yet. And if You add too many variables, it's gonna have to do really badly on that, and you'll be overconfident in your model.
03:05 Cause you're just gonna use all those extra variables to memorize the existing data it's seen. And that's not gonna, you know, make very accurate predictions.
03:13 Cool. So what we're gonna do and you'll learn how this template works in this exercise, is basically just decrease this model by removing anything that's statistically insignificant.
03:26 So the way that Lin s works, which is what is powering this it's a linear estimator and we just need to put a known data y and then, you know, we need to put a bunch of columns.
03:37 The known data X, sorry, so known data y is revenue. That's what we're trying to predict. Known data X is all these variables, right?
03:45 Facebook cost, affiliate cost, et cetera. And unfortunately, it's a pretty simple formula. So it doesn't let us include columns individually.
03:52 You kind of have to make a new copy of the data and, and make sure they're, you know, kind of sequentially next to each other.
03:59 So I'm just gonna duplicate this and this, you know, kind of makes it a little bit of a pain, which is why it's usually good to eventually learn how to code if you're buildings because then you don't have to kind of do this.
04:12 But I'm gonna go in, I'm gonna delete the fill cost. What are the other insignificant variables? I'm gonna delete Monday, Wednesday, and payday, right?
04:22 So let's delete Monday, let's do delete Wednesday, and then let's delete payday because they were all statistically insignificant in the first model.
04:34 So we're gonna build a new model now, and I'm just gonna call that rule two. And you know, now we want to kind of do the latest function on our data two instead.
04:47 So we're still predicting revenue. So I'm just gonna select that, but get it from data two, right? It's actually just gonna be exactly the same cause we've copied it across.
04:59 Okay? So that's dated two, and then we're gonna get our new values. So our new variables so this is our slum down version.
05:13 Cool. So you're gonna select that there. And then don't worry about the rest. You know, that just kind of tells you whether you're gonna add a constant and et cetera.
05:22 So and yeah, what the output looks like. Cool. So now we have the new role model output, but we don't, we need to kind of update these things up here.
05:32 So first thing we need to do is we need to get the right column headers.  So we're gonna go to data two, we're click on Facebook cost, and then we're just gonna drag that across and here we go.
05:46 So one of the weird things that happens with Lins is that the first thing that comes out is like the last thing that goes in.
05:55 So yeah, we basically need to reverse that. And that's kind of why we have these headers separate. And then the final one is always constant.
06:05 Okay? So constant. Cool. So in order to reverse it properly, we need to do this. So we need to select here and then do the same thing again, select here.
06:20 So don't ask me to explain how this works. It's a, a transpose within a transpose but basically all, all it, literally all it does is just like reverse these columns, right?
06:31 So they come out in the right, in the right way.  So lemme just check this is correct. So Facebook cost the coefficient should be four.
06:42 Oh, okay. Yeah, actually I just realized content is always at the end. So what we need, so this one needs to be Facebook cost and then strang that across.
07:03 And then this is the cool and the content is basically just something you add which provides a baseline is how many sales you get if you didn't invest anything in advertising or you didn't know what day it was.
07:15 So the coefficient here is 11,000. It means like on an average day we're gonna get 11,000 no matter what happens.
07:22 Cool. So now that that has been reversed from this function below now let's, should do the same thing for the standard error, cuz the standard error is literally just the row below.
07:33 So these ones are the coefficients and these ones are the standard error. Then this thing here is the R squared.
07:39 So you can see it's pulling 0.75, 0.75. The other thing we need is cystic degrees of freedom 1.73. And that goes into this function.
07:48 So this is how you work out whether something statistically significant. You get the absolute value of the coefficient divided by the standard error put it into this t disk function and use, you know, input the degrees of freedom.
08:03 Again, you know, this isn't a statistics lesson, this is just practical, kind of like how to do it. You know how, how it all works under the hood is, you know, a much, much bigger course <laugh>.
08:15 Cool. So now we have a model and we've kind of updated model two but we are actually wrong about the share and the con contribution.
08:23 They come from somewhere else. They come from the pred formula here. So so what do we need to do? We need to make a pred two.
08:32 Let me just make that pred two. All right? What does pred two do? Okay so the error is, you know, basically how much the prediction differs from the actual values.
08:49 And you know, we just need to update this cuz it's not data anymore, it's data too. So this is data two, B2 and data two b2, okay?
09:06 So that down, cool. So now that's looking at the right place. But now the prediction is the sum of all these things.
09:18 And we are looking at the wrong columns Now because this is looking at the data tab should be looking at data too.
09:27 So we're gonna say Facebook cost and then we're just gonna drag across and we see that, okay, a lot of these don't have don't have column names cuz we decreased the number of variables.
09:40 Okay? So the fun thing we need to do is we need to multiply the model coefficient by the input by the actual data, right?
09:48 So Facebook cost has a coefficient of 4.24, right? And if you multiply that, buy data two, what's the first day we spent $5,800?
10:04 You know, then we know here that Facebook is gonna drive, you know, $4 for every 1000, you know, $1 that we invested.
10:14 That's what the coefficient is telling us. That's what the model is telling us. So that gives us our prediction for Facebook, right?
10:20 For every day. And let me just check that that is working. Oh, I know what I did wrong. So you need to make this an absolute reference.
10:33 So when you drag it down, it's not gonna just like drag off somewhere weird. So I pressed a four here, I'm on pc, but but basically you just put a dollar sign in front of the seven.
10:44 What that's gonna do is you can drag it this way, but you can, when you drag it this way, it's still gonna stay on row seven to get the coefficient.
10:52 So let me just drag that down again. Here we go. So now that gives us our prediction for every day for Facebook.
11:01 And then we're gonna just drag that across and it's doing the same thing, you know, but for different variables. And let me explain that.
11:10 So you can see here that like, you know, this is a Thursday, so we've got the plus 2,400 on Thursday.
11:16 This is also a Thursday. It's got another plus 2,400. And if you sum all these different columns up you know, here we don't need it all the way across.
11:24 We just need it to, I cuz we have less variables. Now if you sum all 'em across, that is the prediction of the model, right?
11:32 So that's how it all works. And now all we need to do is we need to make sure the contribution is correct.
11:41 So here we're summing up the contribution here we go. Yeah, contribution from data two, instead of data one of Facebook normally you can press command shift down.
11:59 I'm having an issue with that. Okay? So that is all the revenue that the model says we're getting from Facebook.
12:12 So yeah, just that. Yeah. So so here that's all the contribution we get from oh, that's from the constant, right?
12:22 I'm sorry, I kind of messed up the format a little bit here. So the constant, oh, we need to work out the constant, well, actually the constant is just, if you think about it this way, the constant is just the coefficient for the constant, right?
12:39 This for each day, right? So multiply by count of the days that's like another way to do it if you want, if the, you know, if, if like there's just some vary, like the constant, then you can just multiply by count of the days.
12:58 Cool. So that is how much revenue the constant makes. And then Facebook is just the sum of pre two, this column, right?
13:09 And then now we can just drag that across. Again, I'm kind of showing you the bare bones of this as you can see.
13:16 But then this is you know, this, this share is kind of updated now. So it's looking at this, the, how much that revenue is divided by the total.
13:27 So we have a model updated all of 'em, all of our p-values are statistically significant now you know, we have a coefficient.
13:37 So now it's saying that Facebook is like $4.23. TV was 40 cents on the dollar. In terms of roi. It tells us that Facebook actually drove 66% of of revenue.
13:51 So it's like slightly different from this. And you know, TV was only what, 2% for us. It tells us that, you know, Thursdays are eight, six you know, the fact that like, you know, Thursdays drove eight $63,000 worth of revenue, right?
14:10 You know, we can also kind of interpret this as like the day a week trend doesn't like make that much of a difference.
14:14 You know, it's only small variations. The vast majority of everything came from Facebook. Cool. So that is how you build a model and interpret it.
14:25 The way you would apply this is, you know, if your last click was saying that Facebook only drove $2 in revenue you know, then you might be underestimating Facebook, right?
14:36 Like Facebook's potentially because iOS 14 Facebook is not you know, it is not really $2. The marketing mix model is saying it's actually driving $4 worth of value based on the data.
14:50 So as long as You, you trust the model, then you can kind of use that as that second opinion. And then adjust your expectations and adjust your budgets accordingly.
14:59 You know, this is saying that your TV is not working, Facebook's working very, very well. So you know, perhaps you need to find a way to improve your tv or, you know, maybe account for the long term impact of tv or you, you know you just keep investing more budget in Facebook.
15:17 Cool, all right. That's marketing mix modeling and again, lots of oversimplifications here. But you know, as long as you understand the general idea that like, you know, the model takes is trying to predict y revenue with x the different variables, you input the variables in this fashion, right?
15:35 In this kind of tabular format where we have one rope per day or one rope per week. And then you make predictions, you sum up those predictions, and that kind of gives you the accuracy.
15:46 Cool. All right. Hopefully that was helpful.

Time to brush up on Linear Regression. Do some Googling and explain in your own words how it works.

Create a linear regression model with LINEST following the template provided. Include all variables in your model. Explain what modifications you had to make to the template.

Which variables are statistically significant (P Value is less than 0.05)

What share of total revenue for the time period did Facebook contribute?

Was TV profitable based on return on ad spend? (ROAS > 1)

What days had the highest and lowest impact on sales, according to the model?

Imagine you’ve got an opportunity to prove yourself with your first marketing mix modeling project. It’s a big topic: what’s the minimum viable thing to do first?

Vexpower Learning Certificate of Completion

Congratulations Corina Best 💪