クーポン施策の効果をSQLで検証する方法｜割引の売上貢献を正しく測る

-- coupons（クーポンマスタ）
-- coupon_id        : クーポンID（主キー）
-- coupon_code      : クーポンコード
-- campaign_id      : 紐づくキャンペーンID
-- discount_type    : 'fixed'（固定額）/ 'rate'（割引率）
-- discount_value   : 割引額（fixed）または割引率（rate、0〜1の小数）
-- issued_at        : 発行日時
-- customer_id      : 発行対象の顧客ID（NULL なら汎用クーポン）
-- expires_at       : 有効期限

-- orders（注文テーブル）
-- order_id         : 注文ID
-- customer_id      : 顧客ID
-- order_date       : 注文日
-- total_amount     : 注文金額（割引後）
-- original_amount  : 注文金額（割引前）
-- coupon_id        : 使用したクーポンID（NULL なら未使用）
-- status           : 'completed' / 'cancelled'

-- campaigns（キャンペーンマスタ）
-- campaign_id      : キャンペーンID
-- campaign_name    : キャンペーン名
-- start_date       : 開始日
-- end_date         : 終了日

-- STEP1: クーポンの発行・使用状況を把握する

WITH coupon_usage AS (
    SELECT
        c.coupon_id,
        c.coupon_code,
        c.campaign_id,
        c.discount_type,
        c.discount_value,
        c.customer_id          AS issued_to,
        CAST(c.issued_at AS DATE)   AS issued_date,
        CAST(c.expires_at AS DATE)  AS expires_date,

        -- 注文テーブルと紐づける（使用された注文）
        o.order_id,
        o.order_date,
        o.original_amount,
        o.total_amount,

        -- 割引額を計算（fixedはそのまま、rateは元値から計算）
        CASE
            WHEN c.discount_type = 'fixed'
                THEN c.discount_value
            WHEN c.discount_type = 'rate'
                THEN ROUND(o.original_amount * c.discount_value, 0)
            ELSE 0
        END  AS discount_amount,

        -- 使用フラグ
        CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END  AS is_used
    FROM coupons  c
    LEFT JOIN orders  o
        ON  c.coupon_id  = o.coupon_id
        AND o.status     = 'completed'
)
SELECT *
FROM coupon_usage
ORDER BY issued_date, coupon_id;

-- STEP2: キャンペーン別クーポン効果サマリ

WITH coupon_usage AS (
    SELECT
        c.coupon_id,
        c.campaign_id,
        CAST(c.issued_at AS DATE)  AS issued_date,
        o.order_id,
        o.order_date,
        o.original_amount,
        o.total_amount,
        CASE
            WHEN c.discount_type = 'fixed' THEN c.discount_value
            WHEN c.discount_type = 'rate'  THEN ROUND(o.original_amount * c.discount_value, 0)
            ELSE 0
        END  AS discount_amount,
        CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END  AS is_used
    FROM coupons  c
    LEFT JOIN orders  o
        ON  c.coupon_id = o.coupon_id
        AND o.status    = 'completed'
)
SELECT
    cu.campaign_id,
    ca.campaign_name,

    -- 発行数・使用数・使用率
    COUNT(cu.coupon_id)                              AS issued_count,
    SUM(cu.is_used)                                  AS used_count,
    ROUND(SUM(cu.is_used) * 100.0
        / NULLIF(COUNT(cu.coupon_id), 0), 1)         AS usage_rate_pct,

    -- 売上・割引額
    COALESCE(SUM(cu.original_amount), 0)             AS gross_sales,
    COALESCE(SUM(cu.discount_amount), 0)             AS total_discount,
    COALESCE(SUM(cu.total_amount), 0)                AS net_sales,

    -- 回収率（売上 ÷ 割引額）
    ROUND(
        COALESCE(SUM(cu.original_amount), 0) * 1.0
        / NULLIF(COALESCE(SUM(cu.discount_amount), 0), 0)
    , 1)                                             AS recovery_rate,

    -- 使用1件あたりの平均注文金額（割引前）
    ROUND(
        COALESCE(SUM(cu.original_amount), 0)
        / NULLIF(SUM(cu.is_used), 0)
    , 0)                                             AS avg_order_value_per_use

FROM coupon_usage      cu
JOIN campaigns         ca  ON cu.campaign_id = ca.campaign_id
GROUP BY cu.campaign_id, ca.campaign_name
ORDER BY recovery_rate DESC;

-- STEP3: クーポン起因の「新規行動」と「元々買う予定だった行動」を分離

WITH coupon_usage AS (
    SELECT
        c.coupon_id,
        c.campaign_id,
        c.customer_id       AS issued_to,
        o.order_id,
        o.customer_id       AS purchaser_id,
        o.order_date,
        o.original_amount,
        CASE
            WHEN c.discount_type = 'fixed' THEN c.discount_value
            WHEN c.discount_type = 'rate'  THEN ROUND(o.original_amount * c.discount_value, 0)
            ELSE 0
        END  AS discount_amount,
        CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END  AS is_used
    FROM coupons  c
    LEFT JOIN orders  o
        ON  c.coupon_id = o.coupon_id
        AND o.status    = 'completed'
),
-- クーポン使用日の30日前までに購買歴があるかを確認
prior_purchase_check AS (
    SELECT
        cu.coupon_id,
        cu.order_id,
        cu.purchaser_id,
        cu.order_date,
        cu.original_amount,
        cu.discount_amount,
        -- 30日以内に購買歴があれば「元々買う予定だった可能性が高い」
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM orders  prev
                WHERE prev.customer_id = cu.purchaser_id
                AND prev.status        = 'completed'
                AND prev.order_date    >= DATE_ADD('day', -30, cu.order_date)
                AND prev.order_date    <  cu.order_date
            ) THEN '直近購買あり（自然購買の可能性）'
            ELSE   '直近購買なし（クーポン誘引の可能性）'
        END  AS purchase_likelihood
    FROM coupon_usage  cu
    WHERE cu.is_used = 1
)
SELECT
    purchase_likelihood,
    COUNT(order_id)              AS order_count,
    SUM(original_amount)         AS gross_sales,
    SUM(discount_amount)         AS total_discount,
    ROUND(AVG(original_amount), 0)  AS avg_order_value
FROM prior_purchase_check
GROUP BY purchase_likelihood
ORDER BY purchase_likelihood;

-- STEP4: 割引額帯別のROI比較

WITH coupon_usage AS (
    SELECT
        c.coupon_id,
        c.discount_type,
        o.order_id,
        o.original_amount,
        CASE
            WHEN c.discount_type = 'fixed' THEN c.discount_value
            WHEN c.discount_type = 'rate'  THEN ROUND(o.original_amount * c.discount_value, 0)
            ELSE 0
        END  AS discount_amount,
        CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END  AS is_used
    FROM coupons  c
    LEFT JOIN orders  o
        ON  c.coupon_id = o.coupon_id
        AND o.status    = 'completed'
),
with_bucket AS (
    SELECT
        *,
        CASE
            WHEN discount_amount < 500          THEN '〜499円'
            WHEN discount_amount < 1000         THEN '500〜999円'
            WHEN discount_amount < 2000         THEN '1000〜1999円'
            WHEN discount_amount < 5000         THEN '2000〜4999円'
            ELSE                                     '5000円以上'
        END  AS discount_bucket
    FROM coupon_usage
    WHERE is_used = 1
)
SELECT
    discount_bucket,
    COUNT(order_id)                            AS order_count,
    ROUND(AVG(original_amount), 0)             AS avg_order_value,
    ROUND(AVG(discount_amount), 0)             AS avg_discount,
    ROUND(AVG(original_amount) * 1.0
        / NULLIF(AVG(discount_amount), 0), 1)  AS avg_recovery_rate,
    SUM(original_amount)                       AS total_sales,
    SUM(discount_amount)                       AS total_discount
FROM with_bucket
GROUP BY discount_bucket
ORDER BY
    CASE discount_bucket
        WHEN '〜499円'      THEN 1
        WHEN '500〜999円'   THEN 2
        WHEN '1000〜1999円' THEN 3
        WHEN '2000〜4999円' THEN 4
        ELSE                    5
    END;