CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    revenue DECIMAL(10,2)
);

WITH first_purchase AS (
    SELECT 
        customer_id,
        MIN(DATE_TRUNC('month', order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),

monthly_activity AS (
    SELECT 
        o.customer_id,
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        COUNT(DISTINCT o.order_id) AS orders,
        SUM(o.revenue) AS revenue
    FROM orders o
    JOIN first_purchase fp ON o.customer_id = fp.customer_id
    GROUP BY o.customer_id, fp.cohort_month, activity_month
),

cohort_data AS (
    SELECT 
        cohort_month,
        activity_month,
        DATEDIFF('month', cohort_month, activity_month) AS month_number,
        COUNT(DISTINCT customer_id) AS active_customers,
        SUM(revenue) AS total_revenue
    FROM monthly_activity
    GROUP BY cohort_month, activity_month, month_number
),

SELECT 
    cohort_month,
    month_number,
    active_customers,
    FIRST_VALUE(active_customers) OVER (PARTITION BY cohort_month ORDER BY month_number) AS cohort_size,
    ROUND(100.0 * active_customers / 
          FIRST_VALUE(active_customers) OVER (PARTITION BY cohort_month ORDER BY month_number), 2) AS retention_rate
FROM cohort_data
ORDER BY cohort_month, month_number;

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

# Sample data
data = pd.read_csv('saas_logins.csv')
data['login_date'] = pd.to_datetime(data['login_date'])
data['signup_date'] = pd.to_datetime(data['signup_date'])

# Step 1: Cohort month
data['cohort_month'] = data['signup_date'].dt.to_period('M')
data['activity_month'] = data['login_date'].dt.to_period('M')

# Step 2: Group
cohort_data = data.groupby(['cohort_month', 'activity_month']).agg(
    active_users=('user_id', 'nunique')
).reset_index()

# Step 3: Month number
cohort_data['month_number'] = (
    cohort_data['activity_month'] - cohort_data['cohort_month']
).apply(lambda x: x.n)

# Step 4: Cohort size
cohort_size = cohort_data[cohort_data['month_number'] == 0][['cohort_month', 'active_users']]
cohort_size = cohort_size.rename(columns={'active_users': 'cohort_size'})

# Merge
final = cohort_data.merge(cohort_size, on='cohort_month')
final['retention'] = final['active_users'] / final['cohort_size']

# Pivot for heatmap
pivot = final.pivot_table(
    index='cohort_month', 
    columns='month_number', 
    values='retention'
)

# Plot
plt.figure(figsize=(10, 8))
sns.heatmap(pivot, annot=True, fmt='.0%', cmap='Blues', cbar_kws={'label': 'Retention %'})
plt.title('Monthly User Retention by Signup Cohort')
plt.ylabel('Cohort Month')
plt.xlabel('Month Number')
plt.show()

Month →  0      1      2      3
Cohort ↓
2025-01  100%   45%    28%    19%
2025-02  100%   52%    35%    —
2025-03  100%   48%    —      —

WITH installs AS (
    SELECT 
        user_id,
        MIN(event_date) AS install_date
    FROM events 
    WHERE event_type = 'install'
    GROUP BY user_id
),
daily_active AS (
    SELECT 
        i.user_id,
        i.install_date,
        e.event_date,
        DATEDIFF(e.event_date, i.install_date) AS day_number
    FROM events e
    JOIN installs i ON e.user_id = i.user_id
    WHERE e.event_type = 'session_start'
),
retention AS (
    SELECT 
        install_date,
        day_number,
        COUNT(DISTINCT user_id) AS dau
    FROM daily_active
    WHERE day_number IN (0, 1, 3, 7, 14, 30)
    GROUP BY install_date, day_number
),
cohort_size AS (
    SELECT install_date, dau AS cohort_size
    FROM retention
    WHERE day_number = 0
)
SELECT 
    r.install_date,
    r.day_number,
    r.dau,
    c.cohort_size,
    ROUND(100.0 * r.dau / c.cohort_size, 2) AS retention_pct
FROM retention r
JOIN cohort_size c ON r.install_date = c.install_date
ORDER BY install_date, day_number;

-- Add revenue to e-commerce cohort
SELECT 
    cohort_month,
    month_number,
    SUM(total_revenue) AS revenue,
    SUM(total_revenue) / FIRST_VALUE(SUM(total_revenue)) OVER (PARTITION BY cohort_month ORDER BY month_number) AS revenue_index
FROM cohort_data
GROUP BY cohort_month, month_number;

-- Use DATEDIFF, GROUP BY install_date, day_number
-- Filter day_number IN (0,1)
-- Find cohort with max(1 - retention_d1)

cohort-analysis/
├── sql/
│   ├── ecommerce_retention.sql
│   ├── gaming_dau.sql
│   └── ltv_cohort.sql
├── python/
│   ├── saas_heatmap.ipynb
│   └── cohort_class.py
├── data/
│   └── sample_orders.csv
└── README.md

# Cohort Analysis Toolkit

## Features
- SQL: E-commerce, Gaming, SaaS retention
- Python: Heatmaps, LTV, segmentation
- Reusable: Works with any user-event data

## Run
```bash
# SQL
sqlite3 data.db < sql/ecommerce_retention.sql

# Python
jupyter notebook python/saas_heatmap.ipynb