メルマガの購買転換率をSQLで計測する方法｜配信効果を可視化するクエリ

-- メルマガ配信ログテーブル（mail_send_log）
-- send_id      : 配信ID（主キー）
-- campaign_id  : キャンペーンID
-- campaign_name: キャンペーン名
-- customer_id  : 配信対象の顧客ID
-- sent_at      : 配信日時（TIMESTAMP型）

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

-- STEP1: 配信顧客と購買の突き合わせ

WITH campaign_sends AS (
    -- 対象キャンペーンの配信リスト
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        sent_at
    FROM mail_send_log
    WHERE campaign_id = 'C001'  -- 分析対象のキャンペーンIDを指定
),
post_send_orders AS (
    -- 配信後に購買した記録を引っ張る
    SELECT
        s.customer_id,
        s.campaign_id,
        s.campaign_name,
        s.sent_at,
        o.order_id,
        o.ordered_at,
        o.total_amount,
        -- 配信から購買までの時間（時間単位）
        DATE_DIFF('hour', s.sent_at, o.ordered_at)  AS hours_after_send
    FROM campaign_sends  s
    LEFT JOIN orders     o
        ON  s.customer_id = o.customer_id
        AND o.status      = 'completed'
        -- 配信後から72時間以内の購買のみ
        AND o.ordered_at >= s.sent_at
        AND o.ordered_at <  s.sent_at + INTERVAL '72' HOUR
)
SELECT *
FROM post_send_orders
ORDER BY customer_id, ordered_at;

-- STEP2: 顧客単位に集約して転換フラグを付与

WITH campaign_sends AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        sent_at
    FROM mail_send_log
    WHERE campaign_id = 'C001'
),
post_send_orders AS (
    SELECT
        s.customer_id,
        s.campaign_id,
        s.campaign_name,
        s.sent_at,
        o.order_id,
        o.ordered_at,
        o.total_amount,
        DATE_DIFF('hour', s.sent_at, o.ordered_at)  AS hours_after_send
    FROM campaign_sends  s
    LEFT JOIN orders     o
        ON  s.customer_id = o.customer_id
        AND o.status      = 'completed'
        AND o.ordered_at >= s.sent_at
        AND o.ordered_at <  s.sent_at + INTERVAL '72' HOUR
),
customer_conversion AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        sent_at,
        -- 72時間以内に1件でも購買があれば転換フラグ=1
        CASE WHEN COUNT(order_id) > 0 THEN 1 ELSE 0 END  AS is_converted,
        -- 転換した場合の購買件数・購買金額
        COUNT(order_id)                                    AS order_count,
        COALESCE(SUM(total_amount), 0)                     AS total_amount,
        -- 初回転換までの時間
        MIN(hours_after_send)                              AS hours_to_first_purchase
    FROM post_send_orders
    GROUP BY
        customer_id,
        campaign_id,
        campaign_name,
        sent_at
)
SELECT *
FROM customer_conversion
ORDER BY is_converted DESC, hours_to_first_purchase;

-- STEP3: キャンペーン転換率サマリ（完成版）

WITH campaign_sends AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        sent_at
    FROM mail_send_log
    WHERE campaign_id = 'C001'
),
post_send_orders AS (
    SELECT
        s.customer_id,
        s.campaign_id,
        s.campaign_name,
        s.sent_at,
        o.order_id,
        o.ordered_at,
        o.total_amount,
        DATE_DIFF('hour', s.sent_at, o.ordered_at)  AS hours_after_send
    FROM campaign_sends  s
    LEFT JOIN orders     o
        ON  s.customer_id = o.customer_id
        AND o.status      = 'completed'
        AND o.ordered_at >= s.sent_at
        AND o.ordered_at <  s.sent_at + INTERVAL '72' HOUR
),
customer_conversion AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        CASE WHEN COUNT(order_id) > 0 THEN 1 ELSE 0 END  AS is_converted,
        COUNT(order_id)                                    AS order_count,
        COALESCE(SUM(total_amount), 0)                     AS total_amount,
        MIN(hours_after_send)                              AS hours_to_first_purchase
    FROM post_send_orders
    GROUP BY customer_id, campaign_id, campaign_name, sent_at
)
SELECT
    campaign_id,
    campaign_name,

    -- 配信数
    COUNT(customer_id)                                     AS send_count,

    -- 転換数・転換率
    SUM(is_converted)                                      AS converted_count,
    ROUND(SUM(is_converted) * 100.0 / COUNT(customer_id), 2)
                                                           AS conversion_rate_pct,

    -- 転換顧客の購買金額
    SUM(total_amount)                                      AS attributed_sales,
    ROUND(AVG(CASE WHEN is_converted = 1 THEN total_amount END), 0)
                                                           AS avg_sales_per_converter,

    -- 配信1件あたりの売上貢献（ROAS的な指標）
    ROUND(SUM(total_amount) * 1.0 / COUNT(customer_id), 0)
                                                           AS sales_per_send,

    -- 転換までの中央値時間（Prestoの場合）
    APPROX_PERCENTILE(hours_to_first_purchase, 0.5)        AS median_hours_to_purchase

FROM customer_conversion
GROUP BY campaign_id, campaign_name;

-- STEP4: 全キャンペーンの転換率比較

WITH all_sends AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        sent_at
    FROM mail_send_log
),
post_send_orders AS (
    SELECT
        s.customer_id,
        s.campaign_id,
        s.campaign_name,
        o.order_id,
        o.total_amount
    FROM all_sends  s
    LEFT JOIN orders  o
        ON  s.customer_id = o.customer_id
        AND o.status      = 'completed'
        AND o.ordered_at >= s.sent_at
        AND o.ordered_at <  s.sent_at + INTERVAL '72' HOUR
),
customer_conversion AS (
    SELECT
        customer_id,
        campaign_id,
        campaign_name,
        CASE WHEN COUNT(order_id) > 0 THEN 1 ELSE 0 END  AS is_converted,
        COALESCE(SUM(total_amount), 0)                     AS total_amount
    FROM post_send_orders
    GROUP BY customer_id, campaign_id, campaign_name
)
SELECT
    campaign_id,
    campaign_name,
    COUNT(customer_id)                                          AS send_count,
    SUM(is_converted)                                          AS converted_count,
    ROUND(SUM(is_converted) * 100.0 / COUNT(customer_id), 2)  AS conversion_rate_pct,
    SUM(total_amount)                                          AS attributed_sales,
    ROUND(SUM(total_amount) * 1.0 / COUNT(customer_id), 0)    AS sales_per_send
FROM customer_conversion
GROUP BY campaign_id, campaign_name
ORDER BY conversion_rate_pct DESC;