The Head of Performance wants proof that Facebook ads drives organic app installs. If you can provide that proof you'll help him secure a bigger budget.

The Head of Performance is coming over to your desk. I wonder what they want so late on a Friday?

Hey
Sorry to disturb.
I have a small favor to ask.
I'm trying to get this budget proposal over the line, and one thing I need proof of is that Facebook is driving a significant amount of our organic app installs
I mean it has to be, right? Organic goes up everytime we spend.
Do you think you could take a look?

As a marketing analyst at a promising FinTech app in the social banking space, it's your job to help the team figure out what's performing. One key assumption that your Head of Performance has made, is that spending money on advertising also helps drive organic installs. If you can help him prove there's a Halo Effect of ads, you should be able to secure more budget, and help your start-up achieve even faster growth.

Halo Effect of Ads

Facebook ad installs might be attributed to organic for a variety of reasons. One major reason is iOS14, which allowed users to opt out of tracking. Even before this release, tracking was blocked manually by users with Adblockers. In addition some installs might happen outside of the conversion window of 1 day view, 7 day click.

Orion CKB

In our experience, the last-click channel in this situation is almost always direct or organic search and the Facebook ad contributes to what we call the Halo Effect.

Is this really organic?

If Facebook spend drives organic installs, they at least need to be correlated. How do we do linear regression to calculate that?

You first need to see if Facebook spend is correlated with organic app installs, and you know that's fairly easy to do.

If we want to prove that ad spend is correlated with organic installs, we need to first establish correlation. Do they move up and down at the same time? We're looking for a positive correlation, because we believe spend positively influences how many installs we get from organic.
Our first task is to get all the data into one place. We have the data downloaded from the App Store that shows installs for organics, and the data downloaded from Facebook, which shows ad spend. To join them together, we have to make sure the Facebook ads data has a column that contains the year and month, then build a pivot table to summarize spend by month, before doing a VLOOKUP to join everything together. Once you have the data in place, you can simply use the CORR() function to find the correlation.

Finding Correlation

If you want to format a date in GSheets or Excel, for example to make our Year and Month lookup column, you can use the TEXT formula. For example: TEXT(A2,"YYYYMM") where A2 is the date column.

Join the data together into the same sheet as described in the brief. What is the correlation between Facebook Spend and App Installs?

We know that Facebook ad spend is correlated with organic installs, but do we know how much a move in one affects the other? For that we need to use Linear Regression, which lets us model the relationship between spend and installs. The output will be an equation that we can plug numbers into to predict how many organic installs we'll get for our budget.

Linear Regression

The LINEST function in GSheets and Excel lets you do a linear regression, by inputting the variable you're trying to predict (installs), the variable you think affects it (facebook spend) and then putting TRUE for the other two parameters. Then the top two numbers from the output are the coefficients - multiply the first by your spend and add the second, and you can predict organic installs.

Use the LINEST function to do a linear regression of spend on app installs. What is the coefficient for app installs?

The output of a single variable linear regression is the same thing you get by adding a trendline to a scatterplot chart. To do this go to series > app installs > trend line > linear > show r2 > use equation. This will display the same numbers you got from your LINEST function.

Create a scatter plot chart between spend and app installs, and add the trendline with the equation. What is the R2 value?

Do the same exercise for page views and spend rather than app installs. What can you tell us about the correlation and linear regression function?

How much do you need to spend on ads to get an extra install via organic?

Thanks so much for staying late for this
I'm happy with the result
To me it looks clear that we get more app page views whenever we advertise, and some of those convert to installs
That extra install for every $10 spent is going to go a long way to making the case for new budget!

Baby, I can see your halo

The Head of Performance wants to know: do Facebook ads drive organic app installs too? As a marketing analyst at an early-stage FinTech start-up, you’re the one tasked with finding that out. The ad budget hangs in the balance.

Vexpower Learning Certificate of Completion

Congratulations troubled-drone-18 💪