A mobile app was buying installs on Meta and Google without knowing what happened after the download. This project connects ad spend to what users actually do — week by week.
MOBILE // FIREBASE + META + GOOGLE → BIGQUERY → LOOKER
LTV 90D: $6.20 · FREE→PAID 4.4%
The grid is the main view: each row is a week of installs, each column a day of retention.
The funnel shows how many users make it from install to paying.
The donut shows which channel brings installs, and at what cost.
Meta had one set of numbers, Google another, and Firebase the truth — but nobody saw all three together. Retention was checked once a month, in a manual export.
We joined the ad data with the app data in BigQuery. The main page is now a retention grid: every week of installs, tracked from day 0 to day 30.
Firebase app data is joined with Meta and Google ad spend in BigQuery. SQL builds the retention and revenue views, shown in Looker Studio with alerts for drops.
Installs, events, retention, revenue events.
Spend, installs, creative detail.
Daily joins keyed on install cohorts.
Cohort retention, activation and LTV-by-channel models.
Cohort-hero page + activation, channels, creatives, monetisation.
Retention-dip and CPI-drift alerts to Slack.
Two more pages: the creative table and the alerts page. Built in HTML with sample data.
MOBILE // STICKY BEATS CHEAP
| CREATIVE | INSTALLS | CPI | D7 | LTV 90D | ST |
|---|---|---|---|---|---|
| UGC — ROUTINE HOOK | 6,420 | $1.42 | 24% | $7.10 | ● |
| DEMO — 15S | 4,180 | $1.71 | 22% | $6.40 | ● |
| STATIC — RATINGS | 3,940 | $1.96 | 19% | $5.20 | ● |
| MOTION — FEATURES | 2,110 | $2.60 | 14% | $3.90 | ● |
AUTOMATION // RETENTION + CPI RULES → SLACK
Cohort D7 landed 2pts under trend. Correlates with the onboarding A/B arm B. Arm paused.
Blended CPI up 9% WoW on Google; Meta steady. Suggest: rebalance 10% toward Meta.
Spend ↔ Firebase join ran 07:00. 24,806 installs matched. 0 errors.
UGC — Routine Hook stays the compounding winner: cheapest install AND stickiest user. Motion — Features fails both tests.
The onboarding drop between permissions and profile is the biggest lever on the board — worth more than any bid change.
The retention grid updates every morning instead of once a month.
What an install costs now sits next to what a user is worth.
Winning ads are the ones whose users stay — not just the cheapest installs.
NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS
If installs are cheap but you don’t know what happens after, this dashboard shows you.