Cohort & Retention Analysis

1-- Step 1: First purchase month = cohort month
2WITH customer_cohort AS (
3    SELECT
4        customer_id,
5        MIN(order_date) AS first_order_date,
6        FORMAT(MIN(order_date), 'yyyy-MM') AS cohort_month
7    FROM fact_orders
8    WHERE status = 'Delivered'
9    GROUP BY customer_id
10),
11
12-- Step 2: Gắn cohort month vào mỗi order
13orders_with_cohort AS (
14    SELECT
15        o.customer_id,
16        o.order_date,
17        c.cohort_month,
18        c.first_order_date,
19        DATEDIFF(MONTH, c.first_order_date, o.order_date) AS month_number
20    FROM fact_orders o
21    JOIN customer_cohort c ON o.customer_id = c.customer_id
22    WHERE o.status = 'Delivered'
23),
24
25-- Step 3: Đếm unique customers ở mỗi month_number
26cohort_data AS (
27    SELECT
28        cohort_month,
29        month_number,
30        COUNT(DISTINCT customer_id) AS customers
31    FROM orders_with_cohort
32    GROUP BY cohort_month, month_number
33),
34
35-- Step 4: Lấy cohort size (month_number = 0)
36cohort_sizes AS (
37    SELECT
38        cohort_month,
39        customers AS cohort_size
40    FROM cohort_data
41    WHERE month_number = 0
42)
43
44-- Step 5: Retention Rate Table
45SELECT
46    d.cohort_month,
47    s.cohort_size,
48    d.month_number,
49    d.customers AS retained_customers,
50    ROUND(d.customers * 100.0 / s.cohort_size, 1) AS retention_rate_pct
51FROM cohort_data d
52JOIN cohort_sizes s ON d.cohort_month = s.cohort_month
53WHERE d.month_number <= 12
54ORDER BY d.cohort_month, d.month_number;

1-- Retention Rate theo customer segment
2WITH customer_cohort AS (
3    SELECT
4        o.customer_id,
5        c.customer_segment,
6        MIN(o.order_date) AS first_order_date,
7        DATEDIFF(MONTH, MIN(o.order_date), o.order_date) AS month_number
8    FROM fact_orders o
9    JOIN dim_customer c ON o.customer_id = c.customer_id
10    WHERE o.status = 'Delivered'
11    GROUP BY o.customer_id, c.customer_segment,
12        DATEDIFF(MONTH, MIN(o.order_date), o.order_date)
13)
14SELECT
15    customer_segment,
16    COUNT(DISTINCT CASE WHEN month_number = 0
17        THEN customer_id END) AS m0_customers,
18    COUNT(DISTINCT CASE WHEN month_number = 1
19        THEN customer_id END) AS m1_customers,
20    COUNT(DISTINCT CASE WHEN month_number = 3
21        THEN customer_id END) AS m3_customers,
22    ROUND(
23        COUNT(DISTINCT CASE WHEN month_number = 1 THEN customer_id END) * 100.0
24        / NULLIF(COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 0)
25    , 1) AS m1_retention_pct
26FROM customer_cohort
27GROUP BY customer_segment
28ORDER BY m1_retention_pct DESC;

1-- Monthly Churn Rate
2WITH monthly_active AS (
3    SELECT
4        FORMAT(order_date, 'yyyy-MM') AS month,
5        COUNT(DISTINCT customer_id) AS active_customers
6    FROM fact_orders
7    WHERE status = 'Delivered'
8    GROUP BY FORMAT(order_date, 'yyyy-MM')
9),
10monthly_new AS (
11    SELECT
12        FORMAT(MIN(order_date), 'yyyy-MM') AS first_month,
13        COUNT(DISTINCT customer_id) AS new_customers
14    FROM fact_orders
15    WHERE status = 'Delivered'
16    GROUP BY customer_id
17)
18SELECT
19    a.month,
20    a.active_customers,
21    COALESCE(n.new_customers, 0) AS new_this_month,
22    a.active_customers - COALESCE(n.new_customers, 0) AS returning_customers,
23    ROUND(
24        (a.active_customers - COALESCE(n.new_customers, 0)) * 100.0
25        / LAG(a.active_customers) OVER (ORDER BY a.month), 1
26    ) AS returning_rate_pct
27FROM monthly_active a
28LEFT JOIN (
29    SELECT first_month, SUM(new_customers) AS new_customers
30    FROM monthly_new
31    GROUP BY first_month
32) n ON a.month = n.first_month
33ORDER BY a.month;