Customer Cohort Analysis Framework (AI Prompt)

## Cohort Definition Strategy

**Recommended Cohort Types:**

1. **Monthly Acquisition Cohorts**
   - Definition: All users who signed up in the same calendar month
   - Use when: Analyzing overall retention trends, seasonal patterns
   - Example: "January 2024 Cohort" = all users with signup_date between Jan 1-31, 2024

2. **Channel-Based Cohorts**
   - Definition: Users grouped by acquisition source
   - Use when: Comparing marketing channel quality
   - Example: "Organic Search Q1 2024" vs "Paid Ads Q1 2024"

3. **Behavior-Based Cohorts**
   - Definition: Users who completed a specific action
   - Use when: Analyzing activation impact
   - Example: "Completed Onboarding" vs "Skipped Onboarding"

## Retention Analysis SQL

**Monthly Retention Table Query:**

```sql
WITH cohort_users AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', created_at) as cohort_month
  FROM users
  WHERE created_at >= '2023-01-01'
),

monthly_activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', activity_date) as activity_month
  FROM user_activity
  WHERE activity_date >= '2023-01-01'
  GROUP BY 1, 2
),

cohort_size AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) as cohort_users
  FROM cohort_users
  GROUP BY 1
),

retention_data AS (
  SELECT
    c.cohort_month,
    DATE_DIFF('month', c.cohort_month, a.activity_month) as months_since_signup,
    COUNT(DISTINCT c.user_id) as active_users
  FROM cohort_users c
  LEFT JOIN monthly_activity a ON c.user_id = a.user_id
  WHERE a.activity_month >= c.cohort_month
  GROUP BY 1, 2
)

SELECT
  r.cohort_month,
  s.cohort_users,
  r.months_since_signup,
  r.active_users,
  ROUND(r.active_users * 100.0 / s.cohort_users, 1) as retention_rate
FROM retention_data r
JOIN cohort_size s ON r.cohort_month = s.cohort_month
ORDER BY r.cohort_month, r.months_since_signup;
```

**Output Table:**

| Cohort | Size | M0 | M1 | M2 | M3 | M6 | M12 |
|--------|------|-----|-----|-----|-----|-----|-----|
| Jan 24 | 500 | 100% | 65% | 52% | 47% | 38% | 32% |
| Feb 24 | 480 | 100% | 68% | 55% | 49% | 40% | - |
| Mar 24 | 520 | 100% | 72% | 58% | 52% | - | - |

**Key Insight:** March cohort showing 7% improvement in M1 retention - investigate what changed (new onboarding? different traffic source?)

## LTV Calculation

**Simple LTV Formula:**

```
LTV = ARPU × Average Customer Lifespan

Where:
- ARPU = Average Revenue Per User (monthly)
- Average Lifespan = 1 / Monthly Churn Rate
```

**Example:**
- ARPU = $50/month
- Monthly Churn = 5%
- Average Lifespan = 1 / 0.05 = 20 months
- LTV = $50 × 20 = $1,000

**Cohort-Based LTV SQL:**

```sql
WITH cohort_revenue AS (
  SELECT
    DATE_TRUNC('month', u.created_at) as cohort_month,
    DATE_DIFF('month', u.created_at, p.payment_date) as months_since_signup,
    SUM(p.amount) as revenue
  FROM users u
  JOIN payments p ON u.user_id = p.user_id
  GROUP BY 1, 2
),

cumulative_ltv AS (
  SELECT
    cohort_month,
    months_since_signup,
    SUM(revenue) OVER (
      PARTITION BY cohort_month 
      ORDER BY months_since_signup
    ) as cumulative_revenue,
    (SELECT COUNT(DISTINCT user_id) 
     FROM users 
     WHERE DATE_TRUNC('month', created_at) = cohort_month) as cohort_size
  FROM cohort_revenue
)

SELECT
  cohort_month,
  months_since_signup,
  ROUND(cumulative_revenue / cohort_size, 2) as ltv_per_user
FROM cumulative_ltv
ORDER BY cohort_month, months_since_signup;
```

## Action Framework

**Insight → Action Mapping:**

| Finding | Implication | Action | Success Metric |
|---------|-------------|--------|----------------|
| M1 retention dropped 10% for May cohort | Something changed in May | Review: new traffic sources, product changes, seasonality | Return M1 to baseline |
| Organic users have 2x LTV vs paid | Paid acquisition may not be profitable | Calculate payback by channel, reallocate budget | Improve blended LTV:CAC |
| Users who complete onboarding have 3x retention | Onboarding is critical | Invest in onboarding optimization | Increase completion rate 20% |
| 40% churn happens in week 2 | Week 2 is danger zone | Add engagement triggers in week 2 | Reduce week 2 churn by 25% |