コホート分析をSQLで行う方法｜月次リテンション率を可視化するクエリ

-- orders テーブル
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日（DATE型）
-- total_amount : 注文金額
-- status       : 'completed' / 'cancelled' など

-- STEP1: 顧客ごとの初回購買月を特定

WITH first_purchase AS (
    SELECT
        customer_id,
        -- 初回購買日
        MIN(order_date)  AS first_order_date,
        -- 初回購買月（年月だけ取り出す）
        DATE_TRUNC('month', MIN(order_date))  AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT *
FROM first_purchase
ORDER BY cohort_month, customer_id;

-- STEP2: 購買記録に経過月数を付与

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date))  AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
all_purchases AS (
    SELECT
        o.customer_id,
        DATE_TRUNC('month', o.order_date)  AS purchase_month
    FROM orders  o
    WHERE o.status = 'completed'
),
cohort_data AS (
    SELECT
        f.cohort_month,
        a.purchase_month,
        a.customer_id,
        -- 経過月数: 購買月 - 初回購買月（月単位）
        DATE_DIFF('month', f.cohort_month, a.purchase_month)  AS month_number
    FROM all_purchases   a
    JOIN first_purchase  f  ON a.customer_id = f.customer_id
)
SELECT *
FROM cohort_data
ORDER BY cohort_month, customer_id, month_number;

-- STEP3: コホート×経過月ごとの購買者数

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date))  AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
all_purchases AS (
    SELECT
        o.customer_id,
        DATE_TRUNC('month', o.order_date)  AS purchase_month
    FROM orders  o
    WHERE o.status = 'completed'
),
cohort_data AS (
    SELECT DISTINCT  -- 同月内複数購買の重複を排除
        f.cohort_month,
        a.purchase_month,
        a.customer_id,
        DATE_DIFF('month', f.cohort_month, a.purchase_month)  AS month_number
    FROM all_purchases   a
    JOIN first_purchase  f  ON a.customer_id = f.customer_id
),
cohort_size AS (
    -- 各コホートの初月人数（M+0の人数）
    SELECT
        cohort_month,
        COUNT(DISTINCT customer_id)  AS cohort_customer_count
    FROM first_purchase
    GROUP BY cohort_month
),
cohort_monthly_buyers AS (
    -- コホート×経過月ごとの購買者数
    SELECT
        cohort_month,
        month_number,
        COUNT(DISTINCT customer_id)  AS buyers
    FROM cohort_data
    GROUP BY cohort_month, month_number
)
SELECT
    b.cohort_month,
    s.cohort_customer_count,
    b.month_number,
    b.buyers,
    -- リテンション率 = その月の購買者数 ÷ 初月の購買者数
    ROUND(b.buyers * 100.0 / s.cohort_customer_count, 1)  AS retention_rate
FROM cohort_monthly_buyers  b
JOIN cohort_size             s  ON b.cohort_month = s.cohort_month
ORDER BY b.cohort_month, b.month_number;

-- STEP4: ピボット形式のコホートテーブル（完成版）

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date))  AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
all_purchases AS (
    SELECT DISTINCT
        o.customer_id,
        DATE_TRUNC('month', o.order_date)  AS purchase_month
    FROM orders  o
    WHERE o.status = 'completed'
),
cohort_data AS (
    SELECT
        f.cohort_month,
        a.customer_id,
        DATE_DIFF('month', f.cohort_month, a.purchase_month)  AS month_number
    FROM all_purchases   a
    JOIN first_purchase  f  ON a.customer_id = f.customer_id
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT customer_id)  AS cohort_count
    FROM first_purchase
    GROUP BY cohort_month
),
cohort_monthly_buyers AS (
    SELECT
        cohort_month,
        month_number,
        COUNT(DISTINCT customer_id)  AS buyers
    FROM cohort_data
    GROUP BY cohort_month, month_number
),
retention AS (
    SELECT
        b.cohort_month,
        s.cohort_count,
        b.month_number,
        ROUND(b.buyers * 100.0 / s.cohort_count, 1)  AS retention_rate
    FROM cohort_monthly_buyers  b
    JOIN cohort_size             s  ON b.cohort_month = s.cohort_month
)
-- ピボット（M+0〜M+5まで横展開）
SELECT
    cohort_month,
    MAX(cohort_count)                                                            AS cohort_size,
    MAX(CASE WHEN month_number = 0 THEN retention_rate END)  AS "M+0",
    MAX(CASE WHEN month_number = 1 THEN retention_rate END)  AS "M+1",
    MAX(CASE WHEN month_number = 2 THEN retention_rate END)  AS "M+2",
    MAX(CASE WHEN month_number = 3 THEN retention_rate END)  AS "M+3",
    MAX(CASE WHEN month_number = 4 THEN retention_rate END)  AS "M+4",
    MAX(CASE WHEN month_number = 5 THEN retention_rate END)  AS "M+5"
FROM retention
GROUP BY cohort_month
ORDER BY cohort_month;