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.

**Core Definitions for [PRODUCT]:**
- Activation event (cohort start): [e.g., first login, first transaction, account creation]
- Retention event (measures active usage): [e.g., opens app, completes a transaction, generates a report]
- Cohort period: [week/month]
- Observation window: [NUMBER] periods

**Part 1 — Retention Matrix (Retention Cohort):**

SQL query to generate the matrix:
```sql
WITH first_events AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(event_date)) AS cohort_month
    FROM events
    WHERE event_type = '[ACTIVATION_EVENT]'
    GROUP BY user_id
),
monthly_activity AS (
    SELECT
        e.user_id,
        DATE_TRUNC('month', e.event_date) AS activity_month,
        p.cohort_month,
        DATEDIFF('month', p.cohort_month, DATE_TRUNC('month', e.event_date)) AS period
    FROM events e
    INNER JOIN first_events p USING (user_id)
    WHERE e.event_type = '[RETENTION_EVENT]'
)
SELECT
    cohort_month,
    COUNT(DISTINCT CASE WHEN period = 0 THEN user_id END) AS period_0,
    ROUND(COUNT(DISTINCT CASE WHEN period = 1 THEN user_id END) * 100.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN period = 0 THEN user_id END), 0), 1) AS retention_1,
    -- Repeat for periods 2 to 12
FROM monthly_activity
GROUP BY cohort_month
ORDER BY cohort_month;
```

**Matrix Interpretation:**
- Row (horizontal): cohort retention over time → measures product lifecycle quality
- Column (vertical): retention of different cohorts in the same period → measures product improvement
- Diagonal: retention in the period immediately after each cohort's activation

**Part 2 — Revenue Cohort (how much each cohort generates over time):**
```sql
SELECT
    cohort_month,
    SUM(CASE WHEN period = 0 THEN revenue END) AS revenue_month_0,
    SUM(CASE WHEN period = 1 THEN revenue END) AS revenue_month_1,
    -- ... through period 12
    SUM(revenue) AS cumulative_ltv
FROM revenue_per_user
INNER JOIN first_events USING (user_id)
GROUP BY cohort_month;
```

- Calculate payback period per cohort (when average CAC is recovered)
- Project 24-month LTV using decay curve
- Identify