KPI dashboard and data pipeline for a mobile app

Scattered sources

Downloads were in App Store Connect, usage in Google Analytics 4, subscriptions in RevenueCat and website search in Google Search Console. Comparing anything meant opening four tabs.

Numbers that disagreed

Each source counts a "new user" differently, uses different time zones and finalises data with a different delay. My own test installs were also counted as real users.

Questions without answers

Do people who start a trial come back? Which onboarding step loses the most users? Do social media posts bring downloads? No single tool could answer these.

One KPI over time: day-1 return rate

Weekly value against baseline and target. The dotted lines mark when each phase shipped.

How the KPIs were chosen

• One KPI tree, not a list: stability and first-session reading feed day-1 return, which feeds trial starts and payment. A KPI lower in the tree is not judged until the ones above it move.
• Leading before lagging: "subscribers who reopen the app" was added because payments lag by weeks. It shows early whether a change is working.
• Every KPI has an owner phase: a paywall change is judged on paywall-to-trial, not on retention, so one change cannot take credit for another.
• Snapshots, not just "now": each run is saved with its date, so the plan has a before-and-after record instead of a dashboard that only shows today.

New-user funnel

Users who installed in the selected range. The arrow shows how many reached the step from the step before.

Retention by weekly install cohort

Share of a cohort active again on day 1, 7 and 30. A dash means the day has not happened yet, not zero.

Downloads per day by source

App Store Connect is the final number but arrives two days late, so its last two days are empty here, not zero.

Checks built into the pipeline

• Source priority per day: App Store Connect when it has the day, otherwise RevenueCat minus test installs, otherwise GA4. Each figure is labelled Final, Provisional or Live.
• Test installs removed: my own simulator and screenshot runs are detected by pattern and excluded from every figure.
• Missing is not zero: when a source fails, the value is stored as "not measured". A run where no source answered stops with an error instead of reporting a quiet day.
• Unsettled days are re-loaded: GA4 can still revise a day for 48 hours, so the job loads those days again until they settle.
• Small samples flagged: a rate with fewer than 10 users in the denominator is marked and never highlighted.

Example: Day-1 retention

Definition

Share of users who were active again one day after their first session

Numerator

Cohort members active on day 1

Denominator

Users whose first session was on the cohort date

Grain

Daily cohort; weekly and monthly values are recomputed from summed numerator and denominator, never averaged

Caveats

A cohort younger than one day has no value yet. Denominator under 10 is flagged as a small sample.

Source

GA4 cohort report, table fct_retention

Example query: a rate by country

Ratios are summed first and divided once, so a big country is not weighted the same as a small one.

SELECT x.key                    AS country,
       SUM(x.value)             AS trials,
       SUM(y.value)             AS new_users,
       ROUND(100.0 * SUM(x.value)
             / SUM(y.value), 1) AS trial_rate
FROM   fct_daily_dim x
JOIN   fct_daily_dim y
  ON   y.date = x.date
 AND   y.dimension = x.dimension
 AND   y.key = x.key
 AND   y.metric = 'new_users'
WHERE  x.dimension = 'country'
  AND  x.metric = 'trial_users'
  AND  x.date BETWEEN :from AND :to
GROUP BY x.key
HAVING SUM(y.value) >= 10
ORDER BY trial_rate DESC;