Consumer mobile app, marketing and product analytics across acquisition, engagement and monetisation
GA4 & Ads Intelligence Dashboards: BigQuery ETL, Funnel Models
The pipeline, the BigQuery models and ten Looker Studio dashboards were built and handed over, and the published dashboard link is part of the project record. What we cannot show is verification: the repository holds the write-up and the dashboard link rather than pipeline code, and the record contains no reconciliation log against the source platforms and no before/after performance figures. Treat the dashboard count and the funnel design as evidenced, and any accuracy or impact claim as unmeasured.
- Our role
- Analytics engineering, API extraction, BigQuery modelling and the dashboard layer
- Timeline
- Built as an ETL pipeline, a modelled funnel layer and ten dashboards, then handed over
- Sector
- Consumer mobile app, marketing and product analytics across acquisition, engagement and monetisation
At a glance
- Source families unified
- Ad platforms, AppsFlyer, GA4
- Funnel modelled end to end
- Install, signup, activation, purchase, retention
- Dashboards delivered
- 10: split by audience
- Warehouse
- BigQuery, with the funnel and cohort logic in SQL
- KPIs modelled
- CPI, multi-stage conversion, cohort retention, DAU and MAU
- Delivery stage
- Built and handed over; accuracy not independently measured
- Stack
- Python, REST APIs, BigQuery, GA4, AppsFlyer, Looker Studio
What was breaking
Three systems each held one third of the answer. The advertising platforms knew what was spent and on which campaign. AppsFlyer knew which install belonged to which source. GA4 knew what the user then did inside the app. Individually each one reports competently; together they could not answer the only question that matters to a growth team, which is whether the money bought users who stayed.
The gap shows up as soon as anyone tries to compute a stage-to-stage rate. Installs are attributed in one tool and signups happen in another, so a conversion rate between them has to be assembled by hand from two exports with different definitions of the same day. Done once it is a report; done weekly it is a job nobody wants and nobody can audit.
Retention made it worse, because retention is a cohort question and cohorts need history in one place. Comparing how users acquired in different weeks behaved later is not possible when the acquisition side and the behaviour side are joined manually each time.
There was also no agreement on what the funnel was. Install, signup, activation, purchase and retention are five stages that different dashboards will define five different ways unless the definition is written down once, in SQL, where everything reads it.
Spend and behaviour could not be read together
Acquisition cost sat in the ad platforms and in-app behaviour in GA4, so the effect of a campaign past the install was invisible.
Stage-to-stage conversion was manual
Rates between funnel stages had to be assembled from separate exports with their own definitions, which makes the result unauditable.
Cohort retention had nowhere to run
Comparing the later behaviour of users acquired in different periods needs acquisition and behaviour history in one warehouse.
No shared funnel definition
Without one written definition of each stage, every chart invents its own and two dashboards disagree about the same number.
Budget decisions ran on install cost alone
Cost per install is the cheapest metric to get and the easiest to optimise in the wrong direction, because it says nothing about what happens after the install.
What we built
The pipeline is deliberately plain: Python extractors against each platform's REST API, landing raw into BigQuery on an automated schedule, with every transformation done afterwards in SQL. The reason to keep extraction dumb is that extraction is where the failures are (rate limits, schema changes, late-arriving attribution), and a step that only writes what it received can be rerun without reasoning about what it previously changed.
Each source stays at its own grain in the raw layer. Ad platform data is campaign-day; AppsFlyer is install-level attribution; GA4 is event-level behaviour. These are three different things and the common mistake is to flatten them into one table early, which produces a schema where every metric is defensible and none is exactly right. The joins happen in the model, where they can be read.
The funnel is the centre of the build. Install, signup, activation, purchase and retention are defined once in SQL as per-user stage timestamps, and every conversion rate, drop-off chart and cohort view derives from that one model. Stage definitions written once in the warehouse are the difference between a funnel that two teams can argue about with evidence and five dashboards that quietly disagree.
Attribution is then a join rather than a tool. With spend at campaign-day grain, AppsFlyer's install attribution and GA4's in-app events in the same warehouse, cost per install stops being the end of the analysis and becomes the first column of it: the same campaign can be read at install, at activation, at purchase and at week-four retention. That is what makes a channel comparison honest, because channels that look expensive per install often do not look expensive per retained user.
On top sit ten Looker Studio dashboards, split by the question being asked rather than by data source, campaign performance, funnel analysis, user behaviour, retention and cohorts, engagement, feature usage, attribution, content performance, contributor activity and monetisation. Splitting by audience matters more than it sounds: a single dashboard that serves marketing and product serves neither, because the filters each one needs are the noise the other has to ignore.
One thing we would say plainly: this build has no reconciliation layer. The models and dashboards were delivered and the funnel definitions are explicit, but there is no recorded comparison of warehouse figures against each platform's own reporting. That is the first thing we would add, and on the comparable engagements in this group it is exactly what turned a dashboard into something the business stopped double-checking.
Extract raw, model in SQL
The Python extractors write what the API returned and nothing more, so a rerun after a rate limit or a schema change is safe. All meaning lives in BigQuery.
Let each source keep its own grain
Campaign-day spend, install-level attribution and event-level behaviour are three different things. Flattening them early makes every metric approximately right.
Define the funnel once
Per-user stage timestamps in one model, with every conversion rate and cohort view derived from it, so no dashboard can introduce its own definition of activation.
Judge channels past the install
Cost per install is the easiest metric to optimise in the wrong direction. The same campaign is read at activation, purchase and retention because that is where its real cost appears.
Split dashboards by reader, not by source
Ten focused dashboards instead of one that tries to serve marketing and product at once, where each audience's filters are the other's noise.
Non-negotiables
- Each platform's data had to keep its own meaning. Attribution belongs to AppsFlyer, behaviour to GA4, and merging them into one wide table would have made both slightly wrong.
- Extraction had to run on a schedule rather than on request, so a dashboard is never read against a stale partial load.
- The funnel definition had to live in SQL, once, so that no dashboard could introduce its own version of a stage.
- The dashboard layer had to be usable by marketing and product readers who do not write queries.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Three source families to one funnel model
Python extractors land each platform's data raw at its own grain; the funnel, attribution and cohort logic is modelled in SQL; ten dashboards read the model rather than the raw tables.
- Data store or CRM
- Trigger
- Processing step
- Delivered output
- Primary path
- Flow
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Decide what each source can and cannot answer
Before any extraction, the three source families were separated by what they are authoritative for: the ad platforms for spend and campaign structure, AppsFlyer for install attribution, GA4 for in-app behaviour. That division is what stops the later model from asking a source a question it cannot answer well, which is the usual cause of two dashboards disagreeing about installs.
Extract through each platform's REST API
Python extractors call each API and write the response into BigQuery on an automated schedule. They do no transformation. Extraction is where the operational failures live (rate limits, schema drift, attribution arriving late), and a step that only writes what it received can be rerun without first working out what it previously changed.
Keeps a failed or repeated extraction from being a risk to any figure downstream.
Land each source at its own grain
Campaign-day spend, install-level attribution and event-level GA4 behaviour are kept as separate tables rather than merged into one wide model. Flattening them at ingest is the shortcut that makes every metric afterwards approximately correct. The joins are done in the model instead, where the assumption behind each one is written down.
Avoids a shared schema in which no single metric is exactly right.
Define the five funnel stages once in SQL
Install, signup, activation, purchase and retention are modelled as per-user stage timestamps in one place. Every conversion rate, drop-off view and cohort chart derives from that model rather than recomputing the stages. Without it, activation means whatever each dashboard's author decided that week, and the funnel stops being comparable across channels.
Prevents two dashboards from reporting different conversion rates for the same stage.
Join spend to behaviour so channels can be judged past the install
With spend, attribution and in-app events in one warehouse, a campaign can be read at install, activation, purchase and later retention instead of only at cost per install. This is the analysis the split systems made impossible, and it is where channel comparisons usually reverse: the cheapest installs are frequently not the cheapest retained users.
Model cohort retention and engagement
Retention is a cohort question, so users are grouped by acquisition period and their later activity measured against it, alongside DAU, MAU and session behaviour. Keeping this in the model rather than in a dashboard calculation means the retention curve on a marketing dashboard and the one on a product dashboard are the same curve.
Stops retention being recomputed per chart, which is how two teams end up with different numbers for the same week.
Build ten dashboards split by the reader
Campaign performance, funnel analysis, user behaviour, retention and cohorts, engagement, feature usage, attribution, content performance, contributor activity and monetisation each get their own dashboard in Looker Studio. The split is by audience rather than by data source, because one dashboard serving both marketing and product gives each of them the other's filters to ignore.
What changed
| Aspect | Before | After |
|---|---|---|
| Following a user from ad to retention | Impossible, spend, attribution and behaviour lived in three systems. | One warehouse where a campaign can be read at install, activation, purchase and later retention. |
| Funnel definitions | Each report defined its own stages, so rates were not comparable. | Five stages defined once in SQL as per-user timestamps, with every chart derived from that model. |
| Stage-to-stage conversion | Assembled by hand from exports with mismatched date definitions. | Modelled, so a drop-off can be compared across channels and periods. |
| Cohort retention | No history joined across acquisition and behaviour to run it against. | Cohorts by acquisition period with retention and engagement measured from one model. |
| Channel decisions | Driven by cost per install, which says nothing about what follows. | Cost readable at each later stage, so a cheap install that never activates is visible as such. |
| Dashboards | Platform-native reporting, one view per tool. | Ten dashboards split by the audience reading them, over the modelled layer. |
What it runs on
Every tool listed with the job it actually does in this build.
Data
BigQuery
The warehouse and the model layer, raw tables per source, the funnel stage model, the spend-to-behaviour joins and the cohort retention logic.
SQL
Where every definition lives: the five funnel stages, conversion rates, cohort retention and the attribution joins.
Orchestration
Python
The extractors. Each one calls a platform API and writes the response unchanged, which is what makes a rerun after a rate limit or a schema change safe.
Integration
REST APIs
The extraction surface for every source, used in preference to manual exports so the pipeline runs on a schedule rather than on request.
GA4
Authoritative for in-app behaviour: events, engagement, feature usage and session activity.
AppsFlyer
Authoritative for mobile install attribution, which install belongs to which source, which is the join that makes channel analysis possible.
Meta Ads
Paid social spend and campaign structure at campaign-day grain.
Google Ads
Paid search and app campaign spend, kept at its own grain rather than forced into a shared ad schema.
Delivery
Looker Studio
The serving layer, ten dashboards split by audience, reading the modelled tables rather than the raw ones.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
The funnel as five stage timestamps per user
Replaces each dashboard deciding for itself what activation means. One model produces the stage a user reached and when, and every conversion rate and drop-off chart reads from it.
sql
-- Illustrative. One definition of each stage, derived once per user.
CREATE OR REPLACE VIEW model.user_funnel AS
SELECT
u.user_id,
u.install_source,
u.campaign_id,
MIN(IF(e.name = 'first_open', e.ts, NULL)) AS installed_at,
MIN(IF(e.name = 'sign_up', e.ts, NULL)) AS signed_up_at,
MIN(IF(e.name = 'activated', e.ts, NULL)) AS activated_at,
MIN(IF(e.name = 'purchase', e.ts, NULL)) AS purchased_at
FROM model.users AS u
LEFT JOIN raw.ga4_events AS e USING (user_id)
GROUP BY u.user_id, u.install_source, u.campaign_id;Cost per stage, not just cost per install
Replaces a channel comparison that stops at install cost. Joining campaign-day spend to the attributed funnel lets the same campaign be priced at activation and at purchase, where the ranking often changes.
sql
-- Spend joined to attributed outcomes, so a channel is judged past
-- the install rather than only at it.
SELECT s.campaign_id,
SUM(s.spend) AS spend,
COUNTIF(f.installed_at IS NOT NULL) AS installs,
COUNTIF(f.activated_at IS NOT NULL) AS activations,
COUNTIF(f.purchased_at IS NOT NULL) AS purchasers,
SAFE_DIVIDE(SUM(s.spend), COUNTIF(f.installed_at IS NOT NULL)) AS cpi,
SAFE_DIVIDE(SUM(s.spend), COUNTIF(f.activated_at IS NOT NULL)) AS cost_per_activation
FROM model.ad_spend_daily AS s
LEFT JOIN model.user_funnel AS f USING (campaign_id)
GROUP BY s.campaign_id;Cohort retention from the same model
Replaces per-chart retention calculations. Grouping users by acquisition week and measuring later activity against it means the marketing and product dashboards show the same curve.
sql
-- One retention definition, read by every dashboard that needs it.
SELECT
DATE_TRUNC(f.installed_at, WEEK) AS cohort_week,
DATE_DIFF(a.active_date, DATE(f.installed_at), WEEK) AS weeks_since,
COUNT(DISTINCT a.user_id) AS retained_users
FROM model.user_funnel AS f
JOIN model.daily_active AS a USING (user_id)
WHERE f.installed_at IS NOT NULL
GROUP BY cohort_week, weeks_since
ORDER BY cohort_week, weeks_since;What it was worth
The thing this build changed is which questions are available. Before, the three systems could each answer a third of a question; after, the funnel is one model and a channel can be examined at every stage rather than at the install. Cost per install is the metric most app marketing is steered by because it is the easiest to obtain, and it is also the one most likely to point the wrong way, a channel that delivers cheap installs that never activate looks like the best channel until the funnel is joined.
Defining the five stages once is the less visible half of the value. Ten dashboards reading one stage model means a drop-off discussed in a marketing review and the same drop-off in a product review are the same number. Where that definition is not centralised, most of the meeting goes on reconciling the charts rather than acting on them.
We will be specific about what is not claimed here. The record for this engagement documents what was built and published, not what it was worth: there is no reconciliation of warehouse figures against each platform's own reporting, and no before and after performance measurement. The stated outcome is improved lifecycle visibility and identified funnel drop-offs. We are not going to convert that into a percentage.
If this engagement were extended, the first addition would be a reconciliation suite, installs against AppsFlyer, spend against each ad platform, sessions against the GA4 interface, logged with cause and decision for every difference. On the comparable builds in this group that is the step that moved a dashboard from being checked against the platform every week to being trusted on its own.
Looker Studio dashboards delivered
Campaign, funnel, behaviour, retention, engagement, feature usage, attribution, content, contributor activity and monetisation.
Funnel stages defined once in SQL
Install, signup, activation, purchase and retention, as per-user stage timestamps.
Source families unified in one warehouse
Advertising platforms for spend, AppsFlyer for install attribution, GA4 for in-app behaviour.
Where conversion and retention are defined
Every dashboard derives from it, so two teams cannot report different rates for the same stage.
Extraction cadence
Python extractors run automatically against each platform's REST API rather than on request.
Performance impact
The record describes improved lifecycle visibility and identified drop-offs. No before/after figure was measured, and none is claimed.
Handed over
- Python extractors against the advertising platform, AppsFlyer and GA4 REST APIs, running on an automated schedule.
- A BigQuery raw layer holding each source at its own grain, campaign-day spend, install-level attribution, event-level behaviour.
- A funnel model defining install, signup, activation, purchase and retention once, as per-user stage timestamps.
- Spend-to-behaviour joins so a campaign can be priced at activation and purchase rather than only at install.
- Cohort retention and engagement models covering DAU, MAU and session behaviour.
- Behavioural and product models for user journeys, feature usage, content engagement and contributor activity.
- Ten Looker Studio dashboards split by audience: campaign performance, funnel, user behaviour, retention and cohorts, engagement, feature usage, attribution, content performance, contributor activity and monetisation.
Questions about this build
What teams usually ask when they recognise their own process here.
Because they are three different grains. Ad spend is campaign-by-day, AppsFlyer attribution is install-level, and GA4 behaviour is event-level. A single wide table forces two of the three into a shape they do not fit, and the result is a schema where every metric is defensible and none is exactly right. Landing each at its own grain and joining them in the model keeps the assumption behind each join visible to whoever reads the SQL.
Agreement. Install, signup, activation, purchase and retention are modelled once as per-user stage timestamps, and every conversion rate, drop-off view and cohort chart derives from that. Without it, activation means whatever each dashboard's author chose, two dashboards report different rates for the same week, and review meetings are spent reconciling charts instead of deciding anything.
Because it is the cheapest metric to obtain and the easiest to optimise in the wrong direction. A channel can deliver the lowest cost per install and the worst cost per activated user at the same time, and if install cost is the only joined figure available, that channel will keep getting budget. Once spend and in-app behaviour share a warehouse, the same campaign can be priced at activation, at purchase and at later retention, and channel rankings frequently change when it is.
It was built and handed over, with the dashboards published and the pipeline running on a schedule. We are describing it as delivered rather than as a system we operate, because the record available to us holds the write-up and the dashboard link rather than pipeline code or an operations log, and we will not claim live operation we cannot evidence. The funnel design and the dashboard set are evidenced; ongoing accuracy is not.
A reconciliation layer, first and without hesitation: installs checked against AppsFlyer, spend against each ad platform, sessions against the GA4 interface, with every difference logged alongside its cause and the decision taken. After that, the items the project's own notes list as next (a streaming path, churn and lifetime-value prediction, and anomaly detection on campaign performance) all of which are worth more once the base figures are known to agree with the platforms.
It needs the extractor updated, which is why the extractors do nothing but write what the API returned. Schema drift and rate limits are the normal operating conditions of marketing APIs, not exceptions, so the step that touches them is kept as thin as possible and is safe to rerun. Because all the meaning lives in the BigQuery models, a source change is handled in one extractor rather than rippling through the funnel definitions and ten dashboards.
Capabilities this used
Read next
All product & gtm analytics workHave a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.