Complete Cohort Analysis: Retention, Revenue, and Product Behavior

Build a complete cohort analysis framework for [PRODUCT NAME], a digital product with [NUMBER] users and [PERIOD] of data.\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Core Definitions for [PRODUCT]:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Activation event (cohort start): [e.g., first login, first transaction, account creation]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Retention event (measures active usage): [e.g., opens app, completes a transaction, generates a report]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Cohort period: [week/month]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Observation window: [NUMBER] periods\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 1 — Retention Matrix (Retention Cohort):**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSQL query to generate the matrix:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nWITH first_events AS (\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    SELECT\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        user_id,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        DATE_TRUNC('month', MIN(event_date)) AS cohort_month\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    FROM events\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    WHERE event_type = '[ACTIVATION_EVENT]'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    GROUP BY user_id\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n),\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nmonthly_activity AS (\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    SELECT\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        e.user_id,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        DATE_TRUNC('month', e.event_date) AS activity_month,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        p.cohort_month,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        DATEDIFF('month', p.cohort_month, DATE_TRUNC('month', e.event_date)) AS period\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    FROM events e\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    INNER JOIN first_events p USING (user_id)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    WHERE e.event_type = '[RETENTION_EVENT]'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSELECT\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    cohort_month,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    COUNT(DISTINCT CASE WHEN period = 0 THEN user_id END) AS period_0,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    ROUND(COUNT(DISTINCT CASE WHEN period = 1 THEN user_id END) * 100.0 /\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n        NULLIF(COUNT(DISTINCT CASE WHEN period = 0 THEN user_id END), 0), 1) AS retention_1,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    -- Repeat for periods 2 to 12\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nFROM monthly_activity\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nGROUP BY cohort_month\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nORDER BY cohort_month;\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Matrix Interpretation:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Row (horizontal): cohort retention over time → measures product lifecycle quality\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Column (vertical): retention of different cohorts in the same period → measures product improvement\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Diagonal: retention in the period immediately after each cohort's activation\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Part 2 — Revenue Cohort (how much each cohort generates over time):**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`sql\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSELECT\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    cohort_month,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    SUM(CASE WHEN period = 0 THEN revenue END) AS revenue_month_0,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    SUM(CASE WHEN period = 1 THEN revenue END) AS revenue_month_1,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    -- ... through period 12\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n    SUM(revenue) AS cumulative_ltv\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nFROM revenue_per_user\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nINNER JOIN first_events USING (user_id)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nGROUP BY cohort_month;\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Calculate payback period per cohort (when average CAC is recovered)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Project 24-month LTV using decay curve\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Identify \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\