Marketing mix modeling.

John Wanamaker was a 19th century United States industrialist, politician and philanthropist and he served as US Postmaster General in the cabinet of President Benjamin Harrison. He is also regarded by many as a trailblazer in advertising and a pioneer in marketing. A quote that is often attributed to him, goes
 

"Half the money I spend on advertising is wasted; the trouble is I don't know which half."
 

This quote nicely sums up the problem that has tormented marketers for as long as the industry has been around: what is the return on investment of my marketing spend? Which part of my customers would have come to my shop regardless, and which have been enticed, persuaded and convinced by well placed, timed and executed messages?  Is that TV campaign really worth the millions that I am spending on it, or do I reach more people with my outdoor billboards and my full page newspaper ad?

Nowadays a lot of the marketing is digital and the advantage of that is that you can measure its outcomes. Everything about these campaigns is tracked, the amount of times the ad was shown to individual users, where it was shown, the clicks it accrued, the behavior on the website or app of the people who clicked it, and the amount of revenue that was made following those clicks. Even though that gives a whole new set of problems determining the contribution to the bottom line of each individual visit, it still gives a lot more to work with than traditional offline forms of marketing.

Still, media agencies have over the years developed advanced models, that are able to accurately predict the outcome of advertisers’ campaigns, based on historic performance. According to these, with sufficient data points on all influencing factors and enough variability in the data these campaigns produce, what has happened in the past can be a good predictor for what will happen in the future.

Not only that, they claim to be able to prescribe the optimal combination of channels for maximization of success metrics like visitors, sales or revenue.

This practice popped to mind, when in class (Managerial Decision Analysis, OneMBA 2017) we were working with the solver table in Excel, to determine optimal combinations of product types to be produced, given certain restraints in terms of revenue per product type and production time and capacity. Marketing mix modeling seemed like a comparable problem, where the metric to optimize would be the revenue and the restraints formed by the cost of the various campaigns, and modifying factors like seasonality and budget.

Marketing Mix Modeling.

However, as various literature will also tell you, Marketing Mix Modeling uses time series data to apply statistical analysis on. More concretely, multivariate regression analyses, that calculate the correlation between various time series: multiple marketing campaigns, that are supposed to influence a success factor, say revenue, (online) store visits, or sales. The influencing factors can be many, actually the more the better and they do not all need to be marketing related. Many of these models incorporate general economic trends, weather, seasonality  and other circumstantial factors, to create a model that is as accurate as it can be.  It makes sense that marketing mix modeling would use time series instead of stale numbers, as the variability in the spends in marketing lead to insight about how effective they are to influence the successfactor.

The textbook used for class (Albright, et al, 2012, p.861) discusses the methodology in detail, although this is related to multiple products and their combined influence on total cost instead of cost related to their return, but for the example that should work just fine.

I created a data set based on 3 marketing channels and seasonality leading to the success metric of Revenue. See images for visualisations based on those. As the textbook (Albright, p. 863) suggests, I created scatterplots of the datasets separately first, to determine each channel’s individual correlation to the success metric. With an R-squared of .12 to .17, none of the channels individually had a decisive effect on the outcome. Seasonality, with an R-squared of .53 was clearly the biggest indicator of success. However, putting the marketing spend of each of the campaigns together, led to an R-squared of .48 and together with the seasonality, that seems to be accounting for pretty much all of the fluctuations in the Revenue (supposing there is no causation between the two).

Above visualisations were created within Excel without applying any particular statistical package, but I used an add-on, available to anyone. As I am working on a Mac, I used StatPlus:Mac. I ran the regression analysis of this data set and the formula this produced was the following:

Revenu =  0,52108 * Spend Channel A + 0,94501 * Spend Channel C + 1,21649 * Spend Channel B + 6722,82385 * Seasonality

Alternatively, I had also set up a Windows based system and I ran the same test on there as well using a comparable add-on, called StatTools. Interestingly, the result of that regression is very different. Apparently, also in statistics, there are multiple ways leading to Rome.

Revenu = 4004,4416924 + 0,35048737 Spend Channel A + 0,78684997 Spend Channel C + 0,69909951 Spend Channel B + 3170,36530367 Seasonality

This of course begged for the question which model is more accurate. For this, I let them both predict the outcome of our data set. The result is two scatterplots with an R-squared that is remarkably similar (0.68147 vs 0.68876), however, with a total revenue calculation over 36 months that is a lot more accurate for the StatTool model (1% off vs 6% off for StatPlus).  Looking at the scatterplot, we can clearly see the difference between the prediction of the two models.

 

From the theory back to the real world.

With above exercise, we have proven that using the regression analysis, we are able to predict the total revenue, given combinations of channel spends and seasonality. However what we are looking for is a maximization of the revenue by having the optimal mix of channels, given budget restraints.

So how do we get from this equation that describes the relation between these variables, to the prescriptive model that helps us divide our marketing budget over the marketing channels?

First, if we look the equations, we see that the statTools formula starts with a number (4004,44...) that is not related to any of the spends. This could describe the real life situation in marketing, that part of the revenue is not to be explained by marketing, but revenue that you will make regardless of any marketing activity.

Secondly, it makes sense that one would invest as much money in the channel with the highest score as the inventory (available advertising space) in that channel allows, to maximize the outcome of the revenue. However, we see that the formulas do not agree on which channel is the better driver of revenue. In the StatTools model it is channel C with 0.78, in StatPlus it is Channel B with 1.21. I ran Solver to see which model gave me the better maximum Revenue.

For this, I took the first month of January, that had a total maximum spend of 3300 and a seasonality of 70%. In my data set that resulted in a revenue of 8000, having spent the budget as follows: 800 in channel A, 2000 in channel B and 500 in Channel C.

In Solver, Total Spend is set as the value to maximize, calculated as the Sumproduct of all channel values as defined by Solver and the seasonality on one hand and the contribution of each of them defined by the model on the other, constrained by the maximum inventory per channel, that I now added as an extra element.

With the constraints set as the image shows (Max inventory in channel A 1200, channel B 2000 and channel C 1400) Stattools predicts 8653 as revenue, investing 1400 in channel C and 1900 in channel B. Statplus comes out a bit lower at 8367, investing 2000 in B and 1300 in Channel C. Channel A is discarded in both models, as that channel gives the lowest returns.

The even more real world.

The model created with the two tools  is a gross simplification of the real world. There are many elements missing from the model that in reality affect the results of marketing campaigns and revenue as a whole.

Linear regression

Firstly, the regression we created is a linear one. That translates in the real world to a situation in which the more money we invest in marketing, the more return we can expect. This is of course not realistic. It is likely that the results will taper off at some point, limited by the maximum demand for a product. In a real data set that would be visible, but then we would be looking for something of an S-curve.

Secondly, this marketing mix model is designed on historical data and therefore applicable to situations that are the same or at least very comparable as they have been during that period. If the landscape changes, the model needs to be adjusted. This goes for example for the following elements in that landscape:

Competition

If competitors change their strategy, new competitors stand up or existing ones leave the market, this changes the possibilities for success of the product and campaign

Quality of product and campaign

The quality of the product is the be-all and end-all of commercial success. If the quality is low, then no matter the campaign, it will fall flat. Also, if the quality of the campaign is low, for instance it is targeted at a wrong audience, or it touts qualities of the product that don’t resonate with the audience, then little effect is to be expected.

News / PR

If the company gets itself in a PR situation for instance because of social, environmental or corporate governance issues, this may have serious consequences for the success of the product that are not accounted for in the model

Channels

And finally, the model only accounts for the marketing channels that you have historic data for. The model will not tell you what other channels to invest your data in, as it cannot gauge the expected return there. In a market that is rapidly developing, with constantly new ways of advertising, having three years of back data gets ever more difficult.

Conclusion.

Using the methods and tools used available free of charge to anyone with Excel, we can make a start with solving the problem of Media Mix Modeling and to me it was an eye opener how much is possible with relatively easily accessible tooling. The combination of linear regression and the solvertable to define the optimal distribution of funds seems to work ok, on a month by month basis and on a basic level.

The devil, however is like so often in the details and I don’t for a moment pretend to have cracked the problem indefinitely. The amount of influencing factors can be endless and with every new factor, the calculation gets more complicated.  

Advanced solutions, like the ones offered by my friends at Objective Partners are designed to take in all possible elements and provide you with a reliable prediction. However, for getting a basic understanding of your media spend, this can be a great starting point.  And especially if you are unfamiliar with the workings of the more advanced types of tools, I highly recommend you trying out these exercises yourself.

And don’t forget to let me know what you think!