Funnel and cohort retention

Business question

Where do users drop out between signup, activation and payment? Which acquisition channels differ in week-4 retention? The intended user is a product analyst choosing which segment and onboarding step to investigate next.

Data model

The input repeats cohort size at each observed age. Loading checks that those repeated attributes agree and stores signups in a separate fact table. Summing them across W0–W8 would otherwise count each signup nine times.

Metric definitions

Observation ends on October 4, 2026, inclusive. W0 is the signup week; W4 is four weeks later. This is exact-week retention, not rolling retention. Aggregate rates use summed numerators and denominators.

Findings

The eligible W4 base contains 12,152 users, of whom 6,538 returned. Weighted W4 retention is 53.8%.

Paid ads have lower activation and retention than the partner channel. A useful next step is to investigate audience mix and whether advertising promises match the onboarding experience.

This is a descriptive difference in synthetic data, not a causal channel effect. Activation and W4 use different eligible populations. No spend, user-event history or randomized assignment is available, so CAC, LTV and A/B-test conclusions are outside scope.

SQL and evidence

-- A separate signup fact prevents counting each person once per retention age.
-- W0 eligibility confirms that the seven-day observation window has completed.
SELECT s.channel, SUM(s.signups) AS signups,
       SUM(s.activated) AS activated, SUM(s.paid) AS paid,
       SUM(s.activated) * 1.0 / NULLIF(SUM(s.signups), 0) AS activation_rate,
       SUM(s.paid) * 1.0 / NULLIF(SUM(s.signups), 0) AS paid_rate
FROM fact_cohort_signup s
JOIN fact_cohort_retention r USING (cohort, channel)
WHERE r.age = 0 AND r.mature = 1
  AND (:channel IS NULL OR s.channel = :channel)
GROUP BY s.channel ORDER BY paid_rate DESC;

-- NULL = not yet observed. A measured zero remains numeric zero.
-- :age is 4 for W4; for combined rates only mature cohort denominators qualify.
SELECT s.channel, COUNT(*) AS mature_cohorts,
       SUM(s.signups) AS eligible_signups, SUM(r.retained) AS retained,
       SUM(r.retained) * 1.0 / NULLIF(SUM(s.signups), 0) AS retention_rate
FROM fact_cohort_signup s
JOIN fact_cohort_retention r USING (cohort, channel)
WHERE r.age = :age AND r.mature = 1
  AND (:channel IS NULL OR s.channel = :channel)
GROUP BY s.channel ORDER BY retention_rate DESC;

Validation

Primary keys, foreign keys and CHECK constraints prevent invalid loads. SQL rules are in 08_quality_checks.sql; Python adds independent reconciliation checks.

These checks establish internal consistency of the synthetic fixture. They do not establish the completeness of real business events or a causal effect.

Reproduce the analysis

python3 analytics/run_analysis.py

Python 3 only; no external dependencies or network requests. The script rebuilds the local database and result files in analytics/results/.

The source contains prepared aggregates, not raw transactions or user events. Source dimensions retain their original labels; English display labels are mapped without changing the numerical data. The website loads verified extracts and recalculates filters in the browser, without a live SQL server.

Skills demonstrated

Funnel definitions, cohort modeling, maturity rules, NULL semantics, denominator control and descriptive interpretation.

Created with Codex. Charts and controls use the standard Data Analytics runtime. The data model, SQL, metric definitions and analytical logic are available for inspection and independent adaptation.