"Ain't no use jokin', everything is broken The performance numbers for last week look wrong. Facebook ads is reporting less conversions and there's a rise in organic... could this be the dreaded iOS14 update?
Bank of Yu Office – Morning
Apple pushed live their iOS14 update last week, and you're now looking at the data. What you see isn't pretty – it's immediately clear that tracking is broken...
The Dreaded Update You’re an analyst in the marketing team at Bank of Yu, a promising FinTech app that helps you do more with your savings by lending money to your friends and earning rewards on their purchases. This market is fast moving and highly regulated, but Yu has so far managed to side-step competitors to grow virally through word of mouth.\nAfter testing paid ads on Facebook, the marketing team had just started scaling when iOS14 hit – suddenly the value of these campaigns is in question. With iPhone users opting out of tracking, the mechanism for attributing conversions back to advertising campaigns is starting to break. How do you measure performance when you can’t rely on user-level data?
iOS14 Impact\nHey just sending you the latest data: it looks like around 20-30% of our conversions are being attributed to direct instead of Facebook.\nCan you take a look?\nthanks,
iOS14 Impact Analysis It can be hard to tell how much an event impacted performance – there are many other variables to consider, as well as the natural noise you get in all performance data. This impact looks on the surface to be relatively large, and your boss certaintly thinks it's important(!), but we only have a week's worth of data since the update, and it could just be statistical noise.\nIt is suspicious that overnight the share of installs from Facebook dropped at the exact same time the share of installs from Organic rose, so it's worth investigating, but we shouldn't invest in expensive in-depth analysis until we triage the size of the issue first.
I sat in the dark and thought: There's no big apocalypse. Just an endless procession of little ones.
Before considering more advanced techniques, it's usually helpful to do a simple before and after analysis, to calculate the size of the problem you're facing. If you jump straight into marketing mix modeling, or setting up an experiment, before you've even determined the size of the problem, you risk wasting time on the wrong issues.
Rather than dropping everything, you decide to finish your existing work for the day, then do some simple analysis tomorrow to determine the size of the problem, and if this is really an anomaly we're seeing, or just random noise.
Is this a real problem, or just an anomaly Before anyone starts to panic, you need to triage the problem and estimate the size of the issue. We only have a week's worth of data, could this just be an anomaly?
Bank of Yu Office – The Next Day
Let's do some analysis to determine if this really is a problem, and if so, calculate the scale of the problem. We need to do a before and after analysis and some anomaly detection.
Before and After Organic Take the data in the file below and calculate the difference between the number of organic downloads before and after the iOS14 update on April 26th. Compare the previous two weeks data we have versus the 1 week post iOS14.\nWork out how many installs we have gained that we wouldn't have expected, if for the last week we got the average before the event. Create a chart to help you visualize the change over time. Add a series for the average before and average after, as well as a label for the date of iOS14.
You can add a horizontal line to a chart by adding a new series where every row in that period has the same value (for example the average before). Annotations can be added by adding a label to the Date on the X Axis, then choosing a column that has the label you want in the row for that date and nothing else.
Describe your process for creating the chart, adding the before and after series and adding the label.
How many extra installs did we get attributed to Organic in the week since the update?
Before and After Facebook Now you've done Organics, do the same for Facebook before and after the iOS14 update on April 26th. Compare the previous two weeks data we have versus the 1 week post iOS14 with regards to cost per install.\nWork out how many installs we have gained that we wouldn't have expected, if for the last week we got the average cost per install before the event. Create a chart to help you visualize the change over time. Add a series for the average before and average after, as well as a label for the date of iOS14.
Don't average an average! When you take the average of a column that is already an average, for example cost per install, you end up with the wrong result. This happens because the average function weights each value of the already averaged column evenly, misrepresenting the underlying data. Instead you should always only sum the components of the function (i.e. cost and installs), then use a calculated field in your pivot table to calculate the true average.
What was different in this task versus doing the before and after analysis for Organic?
What percent of installs did we potentially lose from Facebook in the week since the update?
Anomaly Detection One simple way to determine if an event was significant or just noise, is to do some basic anomaly detection. This works by finding the quartile ranges of the data prior to the event, then seeing which quartile the value falls within. If it's in the upper or lower quartile, it's likely to be an anomaly.\nUsing quartile ranges instead of guessing or eyeballing the data gives you a reliable, consistent method for determining how important a deviation a worrysome new data point is.\nThis is key if you want to avoid continuously chasing your tail as an analyst, because it can tell you when to dive in to solve a problem, and when it makes sense to just relax and wait for more data. It's likely that if something isn't a true anomaly it will revert back to the mean given a few more days or weeks.
The quartiles of a data set are found by dividing up the data into four buckets. The QUARTILE function in Excel and GSheets gives you the upper and lower bounds of those buckets. So something is in the 4th quartile if it's above the upper bound of quartile 3, or it's in the 1st quartile if it's below the upper bound of quartile 1.\nThe quartiles are closely related to standard deviation - the interquartile range, which is the difference between the 3rd and 1st quartile, is approximately 1.35 times the standard deviation.\nTo determine if a value is an anomaly, simply add the interquartile range to the 3rd and 1st quartile boundaries - if it's above or below those bounds, it's an anomaly.
Did your calculations show iOS14 was an anomaly?
Explain your process for calculating if iOS14 represented an anomaly in the data.
Trying to be less wrong about attribution No model is 100% accurate, but business is all about making decisions under uncertainty. All we need to do is find clever ways to decrease that uncertainty – Marketing Mix Modeling is one such method.
Bank of Yu Office – Two Weeks Later
Your before and after analysis was well received, and you've been looking for solutions. Marketing Mix Modeling is what everyone is recommending, so that's what you'll focus on.
Marketing Mix Modeling (MMM) A technique that rose to prominence in the 1980s to track the performance of TV campaigns, marketing mix modeling is seeing a resurgance. As user privacy protections like GDPR, Adblockers and iOS14 inhibit marketer's ability to get accurate user level data for attribution, the industry is moving to more probabilistic techniques like MMM. This technique uses aggregate data, making it future proof and privacy friendly.\nIt works by matching spikes and dips to actions and events that occured in marketing and the wider business, to build a model of what's driving conversions. By picking up on natural variations in the data, MMM can distinguish what impact each variable had on the business, and allow you to forecast forward what will happen under different scenarios.\nPerformance marketers most commonly use it to determine the true incremental impact of ads. From an annual or quarterly project, they arrive at an underlying coefficient for the true value of each ad channel, then use those coefficients to apply a rule of thumb calculation when estimating performance for optimization. This was previously important to fight over-counting from the ad platforms, but post-iOS14 is now more commonly defending the impact of ads from under-counting due to lack of users opted into tracking.\nTo learn more about MMM and to see a template you can use, visit the following blog post on Saxifrage.xyz
Media mix models are the future of mobile advertising
You spend a couple of hours gathering data
Over the past few hours you've been gathering data to use in your model, and have it all ready in an 'ABT' or Analytical Base Table. Now all the data is in one place in the right format, it's time to build your model.
Build Your First Model Use the data in the ABT file to build a marketing mix model. Include all the variables in the model and don't worry about any of the statistics. All we care about are the model coefficients for this exercise. In particular we want to see what the model predicts the coefficient for Facebook Ads is – we can use this to find the incremental Cost per Install (CPI) for Facebook ads, by dividing 1 by the coefficient (1/coefficient=CPI when y is installs and x is ad spend).
The LINEST function in excel is all you need to create a marketing mix model. It works like this: LINEST(known_y, known_x, 1, 1) where the known_y is the variable you're trying to predict, like sales or installs, and known_x are the columns you think predict y, such as ad spend, app store ranking and PR. The result is the coefficients for each variable, the r squared (or closeness of fit with the data) and various other statistics.\nNote that the variables come out backwards and have an additional intercept variable at the end. So if you built a model including variable_a, variable_b and variable_c, the coefficients from LINEST would be in the following order: variable_c, variable_b, variable_a, intercept.
What was the coefficient of Facebook Ads (to 2 decimal places)?
What's the implied incremental CPI of Facebook ads based on the coefficient?
The average CPI since iOS14 has been 6.38. What should we multiply this by to get the true incremental CPI when optimizing our ads?
Great work on that MMM analysis\nIt's reassuring to know that the underlying performance of Facebook is still there even if we can't see it\nWe'll use that rule of thumb of an 0.81 multiplier when doing our reporting ongoing.\nOf course this is just a simple model, it'd be good to keep learning more so we can defend the accuracy of the model and keep improving it...
" 
