アップリフトモデリングをSQLで近似する方法｜施策が効く顧客だけを抽出する

-- ab_test_assignments（A/Bテスト割当テーブル）
-- customer_id   : 顧客ID
-- test_id       : テストID
-- ab_group      : 'treatment' / 'control'
-- assigned_at   : 割当日時

-- orders        : order_id, customer_id, order_date, total_amount, status
-- customers     : customer_id, registered_at, channel, rank

-- churn_features（作成した特徴量テーブル）
-- customer_id, recency_days, orders_12m, avg_order_value, active_months_12m など

-- STEP1: A/Bテスト全体の転換率

WITH test_results AS (
    SELECT
        a.customer_id,
        a.ab_group,
        -- 配信後7日以内に購買したか
        MAX(CASE
            WHEN o.order_date >= CAST(a.assigned_at AS DATE)
            AND  o.order_date <  DATE_ADD('day', 7, CAST(a.assigned_at AS DATE))
            AND  o.status = 'completed'
            THEN 1 ELSE 0
        END)  AS converted,
        -- 購買金額
        COALESCE(SUM(CASE
            WHEN o.order_date >= CAST(a.assigned_at AS DATE)
            AND  o.order_date <  DATE_ADD('day', 7, CAST(a.assigned_at AS DATE))
            AND  o.status = 'completed'
            THEN o.total_amount ELSE 0
        END), 0)  AS revenue
    FROM ab_test_assignments  a
    LEFT JOIN orders           o  ON a.customer_id = o.customer_id
    WHERE a.test_id = 'COUPON_20PCT_DEC2024'
    GROUP BY a.customer_id, a.ab_group
)
SELECT
    ab_group,
    COUNT(*)                                                     AS customer_count,
    SUM(converted)                                               AS converters,
    ROUND(SUM(converted) * 100.0 / COUNT(*), 2)                 AS conversion_rate_pct,
    ROUND(AVG(revenue), 0)                                       AS avg_revenue_per_customer,
    -- アップリフト（処置群と対照群の差）は後で計算
    SUM(converted) * 1.0 / COUNT(*)  AS conversion_rate_raw
FROM test_results
GROUP BY ab_group;

-- STEP2: セグメント別アップリフト（T-Learner近似）

WITH test_results AS (
    SELECT
        a.customer_id,
        a.ab_group,
        MAX(CASE
            WHEN o.order_date >= CAST(a.assigned_at AS DATE)
            AND  o.order_date <  DATE_ADD('day', 7, CAST(a.assigned_at AS DATE))
            AND  o.status = 'completed'
            THEN 1 ELSE 0
        END)  AS converted,
        COALESCE(SUM(CASE
            WHEN o.order_date >= CAST(a.assigned_at AS DATE)
            AND  o.order_date <  DATE_ADD('day', 7, CAST(a.assigned_at AS DATE))
            AND  o.status = 'completed'
            THEN o.total_amount ELSE 0
        END), 0)  AS revenue
    FROM ab_test_assignments a
    LEFT JOIN orders o ON a.customer_id = o.customer_id
    WHERE a.test_id = 'COUPON_20PCT_DEC2024'
    GROUP BY a.customer_id, a.ab_group
),
with_features AS (
    -- 特徴量テーブルと結合してセグメントを付与
    SELECT
        tr.customer_id,
        tr.ab_group,
        tr.converted,
        tr.revenue,
        -- RFMスコア的なセグメント
        CASE
            WHEN cf.recency_days <= 30  AND cf.orders_12m >= 4 THEN 'ロイヤル（高頻度・高直近）'
            WHEN cf.recency_days <= 60  AND cf.orders_12m >= 2 THEN '活性（中頻度・直近あり）'
            WHEN cf.recency_days > 60   AND cf.orders_12m >= 3 THEN '離脱リスク（高頻度・遠い）'
            WHEN cf.recency_days <= 90  AND cf.orders_12m = 1  THEN '新規（初回のみ）'
            ELSE                                                     '休眠（低頻度・遠い）'
        END  AS rfm_segment,
        -- 購買金額帯
        CASE
            WHEN cf.avg_order_value >= 15000 THEN '高単価（15,000円以上）'
            WHEN cf.avg_order_value >= 5000  THEN '中単価（5,000〜15,000円）'
            ELSE                                   '低単価（5,000円未満）'
        END  AS price_segment,
        cf.recency_days,
        cf.orders_12m,
        cf.avg_order_value
    FROM test_results    tr
    JOIN churn_features  cf ON tr.customer_id = cf.customer_id
),
segment_uplift AS (
    SELECT
        rfm_segment,
        price_segment,
        COUNT(CASE WHEN ab_group = 'treatment' THEN 1 END)  AS treatment_count,
        COUNT(CASE WHEN ab_group = 'control'   THEN 1 END)  AS control_count,

        -- 処置群転換率
        ROUND(
            SUM(CASE WHEN ab_group = 'treatment' THEN converted ELSE 0 END) * 100.0
            / NULLIF(COUNT(CASE WHEN ab_group = 'treatment' THEN 1 END), 0)
        , 2)  AS treatment_cr,

        -- 対照群転換率
        ROUND(
            SUM(CASE WHEN ab_group = 'control' THEN converted ELSE 0 END) * 100.0
            / NULLIF(COUNT(CASE WHEN ab_group = 'control'   THEN 1 END), 0)
        , 2)  AS control_cr,

        -- アップリフト（転換率の差）
        ROUND(
            SUM(CASE WHEN ab_group = 'treatment' THEN converted ELSE 0 END) * 100.0
            / NULLIF(COUNT(CASE WHEN ab_group = 'treatment' THEN 1 END), 0)
            -
            SUM(CASE WHEN ab_group = 'control' THEN converted ELSE 0 END) * 100.0
            / NULLIF(COUNT(CASE WHEN ab_group = 'control'   THEN 1 END), 0)
        , 2)  AS uplift_pct,

        -- 売上アップリフト（1顧客あたり）
        ROUND(
            AVG(CASE WHEN ab_group = 'treatment' THEN revenue ELSE NULL END)
            - AVG(CASE WHEN ab_group = 'control'   THEN revenue ELSE NULL END)
        , 0)  AS revenue_uplift_per_customer
    FROM with_features
    GROUP BY rfm_segment, price_segment
    HAVING COUNT(CASE WHEN ab_group = 'treatment' THEN 1 END) >= 30
    AND    COUNT(CASE WHEN ab_group = 'control'   THEN 1 END) >= 30
)
SELECT
    rfm_segment,
    price_segment,
    treatment_count,
    control_count,
    treatment_cr,
    control_cr,
    uplift_pct,
    revenue_uplift_per_customer,
    -- アップリフトのタイプ分類
    CASE
        WHEN uplift_pct > 5   THEN 'Persuadable（高アップリフト）'
        WHEN uplift_pct > 0   THEN 'Persuadable（低アップリフト）'
        WHEN uplift_pct = 0   THEN 'Lost Cause / Sure Thing（効果なし）'
        ELSE                       'Do Not Disturb（逆効果）'
    END  AS uplift_type
FROM segment_uplift
ORDER BY uplift_pct DESC;

-- STEP3: アップリフトランキングと累積効果の計算

WITH uplift_ranked AS (
    -- STEP2の結果にランク付け
    SELECT
        rfm_segment,
        price_segment,
        treatment_count + control_count  AS total_customers,
        uplift_pct,
        revenue_uplift_per_customer,
        -- アップリフト降順でランク付け
        ROW_NUMBER() OVER (ORDER BY uplift_pct DESC)  AS uplift_rank
    FROM segment_uplift  -- STEP2の結果テーブル
),
with_cumulative AS (
    SELECT
        uplift_rank,
        rfm_segment,
        price_segment,
        total_customers,
        uplift_pct,
        revenue_uplift_per_customer,
        -- 累積顧客数
        SUM(total_customers) OVER (ORDER BY uplift_rank ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            AS cumulative_customers,
        -- 累積売上アップリフト（全顧客への配布と比較した効率）
        SUM(revenue_uplift_per_customer * total_customers) OVER (
            ORDER BY uplift_rank ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS cumulative_revenue_uplift,
        -- 全セグメントへの配布で得られる総アップリフト
        SUM(revenue_uplift_per_customer * total_customers) OVER ()  AS total_revenue_uplift,
        -- 全顧客数
        SUM(total_customers) OVER ()  AS total_customers_all
    FROM uplift_ranked
)
SELECT
    uplift_rank,
    rfm_segment,
    price_segment,
    total_customers,
    ROUND(uplift_pct, 1)               AS uplift_pct,
    ROUND(revenue_uplift_per_customer, 0) AS rev_uplift_per_cust,
    cumulative_customers,
    ROUND(cumulative_customers * 100.0 / total_customers_all, 1)  AS cumulative_customer_pct,
    ROUND(cumulative_revenue_uplift, 0)  AS cumulative_rev_uplift,
    ROUND(cumulative_revenue_uplift * 100.0 / NULLIF(total_revenue_uplift, 0), 1)
        AS cumulative_uplift_pct  -- 全体のアップリフトのうち何%をカバーしているか
FROM with_cumulative
ORDER BY uplift_rank;

-- STEP4: 配布戦略別のROI比較

WITH
-- クーポンのコスト仮定
coupon_cost AS (
    SELECT
        0.20   AS discount_rate,    -- 20%割引クーポン
        200    AS coupon_unit_cost  -- クーポン発行・配信コスト（1件あたり）
),
strategy_comparison AS (
    SELECT
        '全員配布' AS strategy,
        8407 AS targeted_customers,  -- 全処置群相当
        712  AS expected_converters, -- 処置群の転換者数
        712 * 2040  AS expected_revenue,  -- 転換者の売上
        -- クーポンコスト：転換者への割引 + 全員への配信コスト
        712 * 2040 * 0.20 + 8407 * 200  AS coupon_cost_total,
        -- 対照群との差分（純増売上）
        (712 * 2040) - (506 * 1440)  AS incremental_revenue

    UNION ALL

    SELECT
        '上位3セグメント（48%）',
        4040,    -- ランク1〜3の顧客数
        4040 * 0.155,  -- 加重平均転換率（約15.5%）
        4040 * 0.155 * 2200,  -- 加重平均売上単価
        4040 * 0.155 * 2200 * 0.20 + 4040 * 200,
        4040 * 0.155 * 2200 * 0.89  -- アップリフトの89%を回収
)
SELECT
    strategy,
    targeted_customers,
    ROUND(expected_revenue, 0)         AS expected_revenue,
    ROUND(coupon_cost_total, 0)        AS coupon_cost,
    ROUND(expected_revenue - coupon_cost_total, 0)  AS net_revenue,
    ROUND((expected_revenue - coupon_cost_total) * 100.0 / NULLIF(coupon_cost_total, 0), 1)
        AS roi_pct
FROM strategy_comparison;