差分の差分法をSQLで実装する方法｜キャンペーン効果を因果推論で検証する

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

-- customers テーブル
-- customer_id   : 顧客ID
-- rank          : 会員ランク（'gold' / 'silver' / 'general'）
-- registered_at : 登録日

-- STEP1: 処置群・対照群の月次売上（施策前後）

WITH group_assignment AS (
    SELECT
        customer_id,
        CASE
            WHEN rank = 'gold'   THEN '処置群（ゴールド）'
            WHEN rank = 'silver' THEN '対照群（シルバー）'
            ELSE NULL
        END  AS treatment_group
    FROM customers
    WHERE rank IN ('gold', 'silver')
),
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', o.order_date)  AS sales_month,
        g.treatment_group,
        COUNT(DISTINCT o.customer_id)       AS active_customers,
        COUNT(o.order_id)                   AS order_count,
        SUM(o.total_amount)                 AS total_sales,
        ROUND(AVG(o.total_amount), 0)       AS avg_order_value,
        -- 1顧客あたりの月次売上（DIDで最も重要なKPI）
        ROUND(SUM(o.total_amount) * 1.0
            / COUNT(DISTINCT o.customer_id), 0)  AS sales_per_customer
    FROM orders             o
    JOIN group_assignment   g  ON o.customer_id = g.customer_id
    WHERE
        o.status = 'completed'
        AND o.order_date >= DATE '2024-09-01'
        AND o.order_date <  DATE '2025-01-01'
        AND g.treatment_group IS NOT NULL
    GROUP BY
        DATE_TRUNC('month', o.order_date),
        g.treatment_group
)
SELECT
    sales_month,
    treatment_group,
    active_customers,
    order_count,
    total_sales,
    sales_per_customer,

    -- 期間ラベル
    CASE
        WHEN sales_month < DATE '2024-11-01' THEN '施策前'
        ELSE                                      '施策後'
    END  AS period
FROM monthly_sales
ORDER BY sales_month, treatment_group;

-- STEP2: DiD 推定量の計算

WITH group_assignment AS (
    SELECT customer_id,
           CASE WHEN rank = 'gold'   THEN '処置群' WHEN rank = 'silver' THEN '対照群' END AS grp
    FROM customers WHERE rank IN ('gold', 'silver')
),
period_sales AS (
    SELECT
        g.grp,
        CASE WHEN o.order_date >= DATE '2024-11-01' THEN '施策後' ELSE '施策前' END AS period,
        -- 顧客1人あたりの月平均売上（2ヶ月の平均）
        ROUND(
            SUM(o.total_amount) * 1.0
            / COUNT(DISTINCT o.customer_id)
            / 2.0  -- 2ヶ月で割って月平均に
        , 0)  AS avg_monthly_sales_per_customer
    FROM orders           o
    JOIN group_assignment g  ON o.customer_id = g.customer_id
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2024-09-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY g.grp, CASE WHEN o.order_date >= DATE '2024-11-01' THEN '施策後' ELSE '施策前' END
),
pivoted AS (
    SELECT
        grp,
        MAX(CASE WHEN period = '施策前' THEN avg_monthly_sales_per_customer END)  AS pre,
        MAX(CASE WHEN period = '施策後' THEN avg_monthly_sales_per_customer END)  AS post
    FROM period_sales
    GROUP BY grp
)
SELECT
    grp,
    pre   AS pre_period_avg,
    post  AS post_period_avg,
    post - pre  AS within_group_diff,

    -- DiD: 処置群の差分 − 対照群の差分
    (MAX(CASE WHEN grp = '処置群' THEN post - pre END) OVER ()
    - MAX(CASE WHEN grp = '対照群' THEN post - pre END) OVER ())  AS did_estimate
FROM pivoted
ORDER BY grp DESC;

-- STEP3: 2×2 DiD テーブル（完成版・解釈付き）

WITH group_assignment AS (
    SELECT customer_id,
           CASE WHEN rank = 'gold'   THEN '処置群' WHEN rank = 'silver' THEN '対照群' END AS grp
    FROM customers WHERE rank IN ('gold', 'silver')
),
customer_counts AS (
    -- 各グループの顧客数
    SELECT grp, COUNT(*) AS customer_count
    FROM group_assignment
    GROUP BY grp
),
period_sales AS (
    SELECT
        g.grp,
        CASE WHEN o.order_date >= DATE '2024-11-01' THEN 'post' ELSE 'pre' END AS period,
        SUM(o.total_amount)                              AS total_sales,
        COUNT(DISTINCT o.customer_id)                    AS active_customers,
        COUNT(o.order_id)                                AS order_count,
        ROUND(
            SUM(o.total_amount) * 1.0
            / COUNT(DISTINCT o.customer_id)
            / 2.0
        , 0)  AS avg_monthly_sales_per_customer
    FROM orders o
    JOIN group_assignment g ON o.customer_id = g.customer_id
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2024-09-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY g.grp,
             CASE WHEN o.order_date >= DATE '2024-11-01' THEN 'post' ELSE 'pre' END
),
pivoted AS (
    SELECT
        grp,
        MAX(CASE WHEN period = 'pre'  THEN avg_monthly_sales_per_customer END)  AS pre_sales,
        MAX(CASE WHEN period = 'post' THEN avg_monthly_sales_per_customer END)  AS post_sales,
        MAX(CASE WHEN period = 'pre'  THEN active_customers END)                AS pre_active,
        MAX(CASE WHEN period = 'post' THEN active_customers END)                AS post_active,
        MAX(CASE WHEN period = 'pre'  THEN order_count END)                     AS pre_orders,
        MAX(CASE WHEN period = 'post' THEN order_count END)                     AS post_orders
    FROM period_sales
    GROUP BY grp
),
with_did AS (
    SELECT
        grp,
        pre_sales,
        post_sales,
        post_sales - pre_sales  AS sales_diff,
        pre_active,
        post_active,
        pre_orders,
        post_orders,
        -- DiD（ウィンドウ関数で両グループの差分を計算）
        (post_sales - pre_sales)
        - MIN(post_sales - pre_sales) OVER ()  AS did_sales,
        -- 相対的な施策効果（%）
        ROUND(
            ((post_sales - pre_sales)
            - MIN(post_sales - pre_sales) OVER ())
            * 100.0
            / NULLIF(MAX(CASE WHEN grp = '処置群' THEN pre_sales END) OVER (), 0)
        , 1)  AS did_relative_pct
    FROM pivoted
)
SELECT
    grp,
    pre_sales                    AS "施策前（月平均）",
    post_sales                   AS "施策後（月平均）",
    sales_diff                   AS "変化額",
    ROUND(sales_diff * 100.0 / NULLIF(pre_sales, 0), 1)  AS "変化率%",
    did_sales                    AS "DiD推定値（施策純効果）",
    did_relative_pct             AS "施策前比の純効果%",
    pre_active                   AS "施策前アクティブ顧客",
    post_active                  AS "施策後アクティブ顧客",
    pre_orders                   AS "施策前注文数",
    post_orders                  AS "施策後注文数"
FROM with_did
ORDER BY grp DESC;

-- STEP4: 施策前の月次トレンド比較（平行トレンド仮定の確認）

WITH group_assignment AS (
    SELECT customer_id,
           CASE WHEN rank = 'gold'   THEN '処置群' WHEN rank = 'silver' THEN '対照群' END AS grp
    FROM customers WHERE rank IN ('gold', 'silver')
),
monthly_pre AS (
    SELECT
        DATE_TRUNC('month', o.order_date)  AS sales_month,
        g.grp,
        ROUND(
            SUM(o.total_amount) * 1.0
            / COUNT(DISTINCT o.customer_id)
        , 0)  AS sales_per_customer
    FROM orders o
    JOIN group_assignment g ON o.customer_id = g.customer_id
    WHERE o.status = 'completed'
    -- 施策前の複数ヶ月を確認（ここでは6ヶ月前まで）
    AND o.order_date >= DATE '2024-05-01'
    AND o.order_date <  DATE '2024-11-01'
    GROUP BY DATE_TRUNC('month', o.order_date), g.grp
),
with_growth AS (
    SELECT
        sales_month,
        grp,
        sales_per_customer,
        -- 前月比成長率
        LAG(sales_per_customer) OVER (
            PARTITION BY grp
            ORDER BY sales_month
        )  AS prev_month_sales,
        ROUND(
            (sales_per_customer
            - LAG(sales_per_customer) OVER (PARTITION BY grp ORDER BY sales_month))
            * 100.0
            / NULLIF(LAG(sales_per_customer) OVER (PARTITION BY grp ORDER BY sales_month), 0)
        , 1)  AS mom_growth_pct
    FROM monthly_pre
)
SELECT
    sales_month,
    grp,
    sales_per_customer,
    mom_growth_pct,
    -- 処置群と対照群の成長率の差
    mom_growth_pct
    - AVG(mom_growth_pct) OVER (
        PARTITION BY sales_month
    )  AS growth_diff_from_avg
FROM with_growth
ORDER BY sales_month, grp;

-- STEP5: 属性別の DiD 推定（異質な処置効果の検出）

WITH group_assignment AS (
    SELECT
        c.customer_id,
        CASE WHEN c.rank = 'gold'   THEN '処置群' WHEN c.rank = 'silver' THEN '対照群' END AS grp,
        -- 属性の分割（ここでは過去の購買頻度で高頻度・低頻度に分類）
        CASE
            WHEN order_hist.annual_orders >= 6 THEN '高頻度購買者'
            ELSE                                    '低頻度購買者'
        END  AS frequency_segment
    FROM customers c
    LEFT JOIN (
        SELECT customer_id, COUNT(*) AS annual_orders
        FROM orders
        WHERE status = 'completed'
        AND order_date >= DATE '2023-09-01'
        AND order_date <  DATE '2024-09-01'
        GROUP BY customer_id
    ) order_hist  ON c.customer_id = order_hist.customer_id
    WHERE c.rank IN ('gold', 'silver')
),
period_sales AS (
    SELECT
        g.grp,
        g.frequency_segment,
        CASE WHEN o.order_date >= DATE '2024-11-01' THEN 'post' ELSE 'pre' END AS period,
        ROUND(
            SUM(o.total_amount) * 1.0
            / COUNT(DISTINCT o.customer_id)
            / 2.0
        , 0)  AS avg_monthly_sales_per_customer
    FROM orders           o
    JOIN group_assignment g  ON o.customer_id = g.customer_id
    WHERE o.status = 'completed'
    AND o.order_date >= DATE '2024-09-01'
    AND o.order_date <  DATE '2025-01-01'
    GROUP BY g.grp, g.frequency_segment,
             CASE WHEN o.order_date >= DATE '2024-11-01' THEN 'post' ELSE 'pre' END
),
pivoted AS (
    SELECT
        grp,
        frequency_segment,
        MAX(CASE WHEN period = 'pre'  THEN avg_monthly_sales_per_customer END)  AS pre,
        MAX(CASE WHEN period = 'post' THEN avg_monthly_sales_per_customer END)  AS post
    FROM period_sales
    GROUP BY grp, frequency_segment
),
with_diff AS (
    SELECT
        grp,
        frequency_segment,
        pre,
        post,
        post - pre  AS diff
    FROM pivoted
),
did_by_segment AS (
    SELECT
        frequency_segment,
        MAX(CASE WHEN grp = '処置群' THEN diff END)  AS treatment_diff,
        MAX(CASE WHEN grp = '対照群' THEN diff END)  AS control_diff,
        MAX(CASE WHEN grp = '処置群' THEN diff END)
        - MAX(CASE WHEN grp = '対照群' THEN diff END)  AS did_estimate,
        MAX(CASE WHEN grp = '処置群' THEN pre END)    AS treatment_pre
    FROM with_diff
    GROUP BY frequency_segment
)
SELECT
    frequency_segment,
    treatment_diff  AS "処置群の変化",
    control_diff    AS "対照群の変化",
    did_estimate    AS "DiD純効果",
    ROUND(did_estimate * 100.0 / NULLIF(treatment_pre, 0), 1)  AS "施策前比純効果%"
FROM did_by_segment
ORDER BY did_estimate DESC;