"What's with the delay? When running a new brand campaign, you noticed that results take a few days to come in. Is there a delayed effect of advertising?
GoolyBib Office – Monday Morning
You launched a new brand campaign, and it seems to be going really well. However you noticed that when you increase spend it takes a few days to see it in the results.
Baby Steps in Branding As the Head of Growth at GoolyBib, a fast-growing DTC startup in the wearable tech for babies market, you just launched a brand campaign on Facebook. Unlike your other Facebook ad campaigns this one uses video and optimizes to reach, so it's more like a TV ad campaign than normal performance marketing. You notice it's taking longer to see results than usual when you scale up spend, so you hypothesize there must be a lagged effect – but how do you measure that?
Heya\nYes I know exactly what you mean.\nThat's called the 'lagged effect' and the way you solve it is with Adstocks.\nThey touch on it in this article:
Adstocks Advertising doesn't usually have all of its effect on the same day you spend. For example you might see an ad today and then buy the product a few days later. Adstocks are a way to capture that impact in your model, and quantify it so you can forecast when to expect your ads to take effect. To do this you must transform the spend data to make it non-linear: the most common transformation is a Geometric transformation, which you can do easily in Excel or GSheets. Read the article linked to below for more information on how to do that.
The Geometric function is straightforward to apply in Excel or GSheets because it only has a single parameter, the adstock rate. You simply take the adstock for the last period (or 0 if this is the first), then multiply it by the adstock rate, before adding this period's spend. For example if you spent $100 today and yesterday's adstock was $150, with an adstock rate of 0.2, your adstock for today would be 0.2 * $150 + $100 = $130.
Truth is confirmed by inspection and delay; falsehood by haste and uncertainty.
I agree there's definitely some lag effect from our brand campaign\nIt's great that you might have a good way to model it\nI think it's worth spending some time on
Lags in a spreadsheet In order to model the lagged effect of your campaigns, you turn to a spreadsheet template.
GoolyBib Office – Monday Afternoon
You decide to analyse the lagged effect within Google Sheets based on what you learned this morning from the blog post you read.
Plotting spend and conversions First before we do any modeling, let's look at the data in visual form. Plotting spend and conversions on the left and right axis respectively will help you see whether they move together or not. This should help you identify if there is evidence of a lag.
Plot spend and conversions as a line chart, with spend on the left Y axis and conversions on the right Y axis. Does there look to be a lag?
Adstock Curve In order to see what type of a lag adstocks will add to our data, let's calculate it for a hypothetical one time spend of $100. Limiting spend to one period at the beginning like this can help generate an easier to understand chart where we can see the shape of the curve representing the lag.
Create an adstock curve chart and set the rate to 0.2. How quickly does the adstock drop below 1?
Modeling Adstocks Now we have a function to work out adstocks, let's model it and see which adstock is the most accurate. To do this we need to use the LINEST function in GSheets / Excel, which lets us run a linear regression. The accuracy of that model will be at its highest when we've guessed the right adstock value, so we can just keep changing the value until we find the best fit model.\nIn order to create the model we need to use the LINEST function in GSheets / Excel, then calculate the MAPE or Mean Absolute Percentage Error. To do this we need the coefficients from the model, which we multiply by the transformed spend to get a prediction. Subtract the prediction for each day and make it absolute, then find the mean of that column to get Mean Absolute Error. Then you divide that by the mean of the original conversions column to get MAPE.
When using LINEST you first choose the y variable, in our case conversions, by selecting the full range from B2 to B31. Then we select X, which is the adstocked variable, not our normal untransformed spend. We they put a 1 or True for both of the other parameters. To get a prediction for each day, we multiply the adstocked coefficient by the value of transformed adstock for that day, then add the constant.
To calculate MAPE we need the prediction from the model, so we can calculate the error. To get absolute error, we can use the abs() formula and subtract our predictions from our conversions column. If you average this column you have the MAE or Mean Absolute Error. To express it as a percentage (Mean Absolute Percentage Error). You take the average of the absolute errors divided by the average of the conversions column.
Follow the prior steps and calculate the MAPE for an adstock of 0.2. What percentage error are we seeing?
Try the adstock rates from 0.1 to 0.9. What rate has the lowest MAPE in the model?
In your own words, why might this adstock not be a good representation of the real lagged effect of your ads?
Who's got the better curves The Geometric Adstocks you got in your spreadsheet don't add up: surely it doesn't drop off that fast? Time to get scripting.
GoolyBib Office – Monday Evening
You're working late because you want to solve this. You have the adstocks in GSheets working, but the curve doesn't look realistic. You go back to your friend.
Have you heard of Weibull Adstocks?\nThey look a little curvier and there's some evidence of accuracy gains in using them
Weibull Adstocks For some channels it's unlikely that the bulk of the impact of an ad is felt on the day it runs. For direct response traditional geometric adstocks (like we have been using) are suitable, but for top of funnel branding campaigns we can make a better assumption. Weibull adstocks create more of a smooth curve and offer more flexibility by adding one more parameter. They are however more complex, because they can't be easily calculated in spreadsheets due needing to apply a Weibull distribution to a rolling window.
Scan the paper we just linked to - in a Weibull adstock, what does the 'k' parameter control?
Weibull Code We've provided for you a script where you can apply Weibull adstocks. Feel free to try a few different shape and window parameters to see how it affects the plot. Your task will be to add the code that estimates the linear regression model, which you can adapt and copy across from another script.
Google Colab is a hosted Python notebook that lets you run code as you write it in different cells. To run a cell press the play button or shift + enter. This lets you easily think through a problem and keep trying code until you get something working, then share that solution easily afterwards... all without having to set up a development environment on your computer!
Try making a copy of the colab notebook and running it on your own data. Did you get it working?
From the notebook above, figure out how to run a linear regression on the transformed data. Did you get it working?
Use the SKLearn documentation above to calculate the mean absolute percentage error. What is the MAPE for a window of 6 and shape of 3?
Awesome, I'm glad the MAPE was lower for Weibull\nGlad it worked out for you\nNow you can use it in any new model you build!
" 
