How to build marketing mix model and how to extract ROI

morglum · Posted 11-25-2016 02:13 AM

Hi everyone,

I want to come up with the return on investment of the marketing campaigns of a company.

Let's say that I have 5 years of weekly data for a handful of region with the following variables

# of product sold (the response variable)
TV GRP (Gross Rating Points)
Radio GRP
# of unaddressed mail delivered

The product is sold through the year, but sales show a seasonal pattern.

Here are the three questions I want to answer:

1- % of sales from each year were from "base", and % were incremental from TV / Radio / unaddressed mail

2- The ROI of a dollar spent in all three marketing campaign (knowing the total cost of TV , radio and mail campaigns and the profit from each sales).

3- The shape of the (S-shaped) media response curve so that we can see at what GRP levels the effect of tv campaign begins to taper off

My best find so far is this paper from an older SESUG : http://analytics.ncsu.edu/sesug/2008/ST-152.pdf

I have what he calls aggregated data (total sales by week) and he says that regression methods ( PROC REG) or time series regression methods (proc autoreg) are what is mostly used these days. I can't see myself using proc reg since it doesnt account for the fact that I am using time serie data, so I would probably use proc AUTOREG if I dont get a better suggestion.

My question is how can I answer my questions after running PROC AUTOREG? Here are my first ideas..

For question #1 (about the incremental sales from each campaign), I was thinking I could just predict the total number of sales for the past 5 years with all campaigns spending set to 0, then predict again the past 5 years with one campaign spending set to its actual value. The difference in the number of sales is the incremental number of sales from that campaign. Does that make sense, or would I be violating too many assumptions?

For question #2, I would use that incremental number of sale, multiply it by the dollar value of each sale and divide by the dollar cost of the campaign.

For question #3, I am at a loss...

Damien_Mather · Posted 12-08-2016 09:37 PM

you could try adding second and third-order polynomial effects for GRP to the linear term in the model specification and if signifiacnt use calculus on the significant polynomial GRP coefficients to identify points of minimum and maximum curvature and thus characterise the sigmoid response.