ファネル分析をSQLで行う方法｜30分以内のカゴ投入から購買を正確に測る

STEP 1: 商品ページ閲覧（page_view）
STEP 2: カート追加（add_to_cart）
STEP 3: チェックアウト開始（begin_checkout）
STEP 4: 購買完了（purchase）

-- user_events（ユーザー行動ログ）
-- event_id      : イベントID
-- user_id       : ユーザーID（顧客ID）
-- session_id    : セッションID（No.5で再構築したもの）
-- event_type    : 'page_view' / 'add_to_cart' / 'begin_checkout' / 'purchase'
-- product_id    : 商品ID（NULLの場合あり）
-- occurred_at   : 発生日時（TIMESTAMP・JST）

-- STEP1: ユーザー×セッション×ステップの最初の到達時刻

WITH first_events AS (
    SELECT
        user_id,
        session_id,

        -- 各ステップの最初の発生時刻（セッション内で複数回発生する場合は最初）
        MIN(CASE WHEN event_type = 'page_view'       THEN occurred_at END)
            AS step1_time,
        MIN(CASE WHEN event_type = 'add_to_cart'     THEN occurred_at END)
            AS step2_time,
        MIN(CASE WHEN event_type = 'begin_checkout'  THEN occurred_at END)
            AS step3_time,
        MIN(CASE WHEN event_type = 'purchase'        THEN occurred_at END)
            AS step4_time
    FROM user_events
    WHERE occurred_at >= TIMESTAMP '2024-12-01 00:00:00'
    AND   occurred_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY user_id, session_id
)
SELECT *
FROM first_events
ORDER BY user_id, session_id;

-- STEP2: 時間制約（30分）を適用したファネル通過判定

WITH first_events AS (
    SELECT
        user_id, session_id,
        MIN(CASE WHEN event_type = 'page_view'       THEN occurred_at END) AS step1_time,
        MIN(CASE WHEN event_type = 'add_to_cart'     THEN occurred_at END) AS step2_time,
        MIN(CASE WHEN event_type = 'begin_checkout'  THEN occurred_at END) AS step3_time,
        MIN(CASE WHEN event_type = 'purchase'        THEN occurred_at END) AS step4_time
    FROM user_events
    WHERE occurred_at >= TIMESTAMP '2024-12-01 00:00:00'
    AND   occurred_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY user_id, session_id
),
funnel_flags AS (
    SELECT
        user_id,
        session_id,
        step1_time,
        step2_time,
        step3_time,
        step4_time,

        -- STEP1通過：page_view があれば通過（起点なので時間制約なし）
        CASE WHEN step1_time IS NOT NULL THEN 1 ELSE 0 END
            AS passed_step1,

        -- STEP2通過：step1の後、30分以内にadd_to_cartが発生
        CASE
            WHEN step2_time IS NOT NULL
            AND  step1_time IS NOT NULL
            AND  DATE_DIFF('minute', step1_time, step2_time) <= 30
            AND  step2_time > step1_time  -- 必ず順序を守る
            THEN 1 ELSE 0
        END  AS passed_step2,

        -- STEP3通過：step2の後、30分以内にbegin_checkoutが発生
        CASE
            WHEN step3_time IS NOT NULL
            AND  step2_time IS NOT NULL
            AND  DATE_DIFF('minute', step2_time, step3_time) <= 30
            AND  step3_time > step2_time
            THEN 1 ELSE 0
        END  AS passed_step3_raw,  -- step2通過を前提とする前の中間値

        -- STEP4通過：step3の後、30分以内にpurchaseが発生
        CASE
            WHEN step4_time IS NOT NULL
            AND  step3_time IS NOT NULL
            AND  DATE_DIFF('minute', step3_time, step4_time) <= 30
            AND  step4_time > step3_time
            THEN 1 ELSE 0
        END  AS passed_step4_raw,

        -- ステップ間の所要時間（分）
        DATE_DIFF('minute', step1_time, step2_time)  AS min_step1_to_2,
        DATE_DIFF('minute', step2_time, step3_time)  AS min_step2_to_3,
        DATE_DIFF('minute', step3_time, step4_time)  AS min_step3_to_4
    FROM first_events
)
SELECT
    user_id,
    session_id,
    passed_step1,
    passed_step2,
    -- 連鎖的な通過判定：前のステップを通過していない場合は後続も0
    passed_step2 * passed_step3_raw  AS passed_step3,
    passed_step2 * passed_step3_raw * passed_step4_raw  AS passed_step4,
    min_step1_to_2,
    min_step2_to_3,
    min_step3_to_4
FROM funnel_flags
ORDER BY user_id, session_id;

-- STEP3: ファネルサマリ（完成版）

WITH first_events AS (
    SELECT
        user_id, session_id,
        MIN(CASE WHEN event_type = 'page_view'       THEN occurred_at END) AS step1_time,
        MIN(CASE WHEN event_type = 'add_to_cart'     THEN occurred_at END) AS step2_time,
        MIN(CASE WHEN event_type = 'begin_checkout'  THEN occurred_at END) AS step3_time,
        MIN(CASE WHEN event_type = 'purchase'        THEN occurred_at END) AS step4_time
    FROM user_events
    WHERE occurred_at >= TIMESTAMP '2024-12-01 00:00:00'
    AND   occurred_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY user_id, session_id
),
funnel_flags AS (
    SELECT
        user_id, session_id,
        step1_time, step2_time, step3_time, step4_time,
        1  AS passed_step1,
        CASE
            WHEN step2_time IS NOT NULL AND step1_time IS NOT NULL
            AND  DATE_DIFF('minute', step1_time, step2_time) BETWEEN 0 AND 30
            THEN 1 ELSE 0
        END  AS passed_step2,
        CASE
            WHEN step3_time IS NOT NULL AND step2_time IS NOT NULL
            AND  DATE_DIFF('minute', step2_time, step3_time) BETWEEN 0 AND 30
            THEN 1 ELSE 0
        END  AS raw_step3,
        CASE
            WHEN step4_time IS NOT NULL AND step3_time IS NOT NULL
            AND  DATE_DIFF('minute', step3_time, step4_time) BETWEEN 0 AND 30
            THEN 1 ELSE 0
        END  AS raw_step4,
        DATE_DIFF('minute', step1_time, step2_time)  AS min_1_2,
        DATE_DIFF('minute', step2_time, step3_time)  AS min_2_3,
        DATE_DIFF('minute', step3_time, step4_time)  AS min_3_4
    FROM first_events
),
funnel_final AS (
    SELECT
        user_id, session_id,
        passed_step1,
        passed_step2,
        passed_step2 * raw_step3  AS passed_step3,
        passed_step2 * raw_step3 * raw_step4  AS passed_step4,
        min_1_2, min_2_3, min_3_4
    FROM funnel_flags
)
SELECT
    'STEP1_商品ページ閲覧'   AS funnel_step,
    SUM(passed_step1)       AS users,
    100.0                   AS step_rate_pct,
    NULL                    AS drop_rate_pct,
    NULL                    AS avg_time_to_next_min

UNION ALL

SELECT
    'STEP2_カート追加',
    SUM(passed_step2),
    ROUND(SUM(passed_step2) * 100.0 / NULLIF(SUM(passed_step1), 0), 1),
    ROUND(100.0 - SUM(passed_step2) * 100.0 / NULLIF(SUM(passed_step1), 0), 1),
    ROUND(AVG(CASE WHEN passed_step2 = 1 THEN min_1_2 END), 1)
FROM funnel_final

UNION ALL

SELECT
    'STEP3_チェックアウト開始',
    SUM(passed_step3),
    ROUND(SUM(passed_step3) * 100.0 / NULLIF(SUM(passed_step1), 0), 1),
    ROUND(
        SUM(passed_step2 - passed_step3) * 100.0 / NULLIF(SUM(passed_step2), 0)
    , 1),
    ROUND(AVG(CASE WHEN passed_step3 = 1 THEN min_2_3 END), 1)
FROM funnel_final

UNION ALL

SELECT
    'STEP4_購買完了',
    SUM(passed_step4),
    ROUND(SUM(passed_step4) * 100.0 / NULLIF(SUM(passed_step1), 0), 1),
    ROUND(
        SUM(passed_step3 - passed_step4) * 100.0 / NULLIF(SUM(passed_step3), 0)
    , 1),
    ROUND(AVG(CASE WHEN passed_step4 = 1 THEN min_3_4 END), 1)
FROM funnel_final;

-- STEP4: カート追加後の離脱時間分布（カゴ落ちタイミング）

WITH first_events AS (
    SELECT
        user_id, session_id,
        MIN(CASE WHEN event_type = 'add_to_cart'     THEN occurred_at END) AS step2_time,
        MIN(CASE WHEN event_type = 'begin_checkout'  THEN occurred_at END) AS step3_time,
        MIN(CASE WHEN event_type = 'purchase'        THEN occurred_at END) AS step4_time
    FROM user_events
    WHERE occurred_at >= TIMESTAMP '2024-12-01 00:00:00'
    AND   occurred_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY user_id, session_id
),
cart_abandonment AS (
    SELECT
        user_id,
        session_id,
        step2_time,
        step3_time,
        step4_time,
        -- カート後の行動
        CASE
            WHEN step4_time IS NOT NULL
            AND DATE_DIFF('minute', step2_time, step4_time) <= 30
            THEN '購買完了'
            WHEN step3_time IS NOT NULL
            AND DATE_DIFF('minute', step2_time, step3_time) <= 30
            THEN 'チェックアウト止まり'
            ELSE 'カゴ落ち'
        END  AS cart_outcome,
        -- カート後の最終行動までの時間（分）
        COALESCE(
            CASE WHEN step4_time IS NOT NULL THEN DATE_DIFF('minute', step2_time, step4_time) END,
            CASE WHEN step3_time IS NOT NULL THEN DATE_DIFF('minute', step2_time, step3_time) END,
            NULL  -- 記録なし（セッション終了）
        )  AS minutes_after_cart
    FROM first_events
    WHERE step2_time IS NOT NULL  -- カート追加があるセッションのみ
)
SELECT
    cart_outcome,
    COUNT(*)                                              AS session_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)   AS pct,
    ROUND(AVG(minutes_after_cart), 1)                     AS avg_minutes,
    ROUND(APPROX_PERCENTILE(minutes_after_cart, 0.5), 0)  AS median_minutes,
    ROUND(APPROX_PERCENTILE(minutes_after_cart, 0.9), 0)  AS p90_minutes
FROM cart_abandonment
GROUP BY cart_outcome
ORDER BY pct DESC;

-- STEP5: デバイス別ファネル転換率の比較

WITH first_events AS (
    SELECT
        e.user_id,
        e.session_id,
        e.device_type,  -- 'mobile' / 'desktop' / 'tablet'
        MIN(CASE WHEN e.event_type = 'page_view'      THEN e.occurred_at END) AS s1,
        MIN(CASE WHEN e.event_type = 'add_to_cart'    THEN e.occurred_at END) AS s2,
        MIN(CASE WHEN e.event_type = 'begin_checkout' THEN e.occurred_at END) AS s3,
        MIN(CASE WHEN e.event_type = 'purchase'       THEN e.occurred_at END) AS s4
    FROM user_events  e
    WHERE e.occurred_at >= TIMESTAMP '2024-12-01 00:00:00'
    AND   e.occurred_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY e.user_id, e.session_id, e.device_type
),
funnel AS (
    SELECT
        device_type,
        1  AS p1,
        CASE WHEN s2 IS NOT NULL AND s1 IS NOT NULL
             AND DATE_DIFF('minute', s1, s2) BETWEEN 0 AND 30 THEN 1 ELSE 0 END  AS p2,
        CASE WHEN s2 IS NOT NULL AND s1 IS NOT NULL
             AND DATE_DIFF('minute', s1, s2) BETWEEN 0 AND 30
             AND s3 IS NOT NULL
             AND DATE_DIFF('minute', s2, s3) BETWEEN 0 AND 30 THEN 1 ELSE 0 END  AS p3,
        CASE WHEN s2 IS NOT NULL AND s1 IS NOT NULL
             AND DATE_DIFF('minute', s1, s2) BETWEEN 0 AND 30
             AND s3 IS NOT NULL
             AND DATE_DIFF('minute', s2, s3) BETWEEN 0 AND 30
             AND s4 IS NOT NULL
             AND DATE_DIFF('minute', s3, s4) BETWEEN 0 AND 30 THEN 1 ELSE 0 END  AS p4
    FROM first_events
)
SELECT
    device_type,
    COUNT(*)                                            AS sessions,
    SUM(p2)                                             AS cart_adds,
    ROUND(SUM(p2) * 100.0 / NULLIF(COUNT(*), 0), 1)   AS cart_add_rate,
    SUM(p4)                                             AS purchases,
    ROUND(SUM(p4) * 100.0 / NULLIF(COUNT(*), 0), 1)   AS purchase_rate,
    -- モバイルとの差分を出すためのリファレンス値（WINDOWで計算）
    ROUND(
        SUM(p4) * 100.0 / NULLIF(COUNT(*), 0)
        - AVG(SUM(p4) * 100.0 / NULLIF(COUNT(*), 0)) OVER ()
    , 1)  AS vs_avg_pct
FROM funnel
GROUP BY device_type
ORDER BY purchase_rate DESC;