-- touchpoints（タッチポイントログ）
-- touchpoint_id  : タッチポイントID
-- customer_id    : 顧客ID
-- channel        : 接触チャネル（'seo', 'sns', 'email', 'display', 'direct'）
-- touched_at     : 接触日時（TIMESTAMP）
-- converted      : この接触後に購買したか（1 / 0）
-- session_id     : セッションID（複数タッチを1経路として束ねるキー）

-- journey_paths（変換経路テーブル ※touchpointsから集計して作る）
-- customer_id    : 顧客ID
-- path           : 接触経路（例：'seo > sns > email'）
-- converted      : 最終的に購買したか（1 / 0）
-- touch_count    : タッチ数

-- STEP1: 顧客ごとの接触経路を生成

WITH ordered_touchpoints AS (
    SELECT
        customer_id,
        channel,
        touched_at,
        converted,
        -- 顧客内での接触順序
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY touched_at ASC
        )  AS touch_order,
        -- 最終タッチかどうか
        CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touched_at DESC) = 1
            THEN 1 ELSE 0
        END  AS is_last_touch
    FROM touchpoints
    WHERE touched_at >= TIMESTAMP '2024-01-01 00:00:00'
    AND   touched_at <  TIMESTAMP '2025-01-01 00:00:00'
),
journey_paths AS (
    SELECT
        customer_id,
        -- 接触チャネルをカンマ区切りの経路に結合
        ARRAY_JOIN(
            ARRAY_AGG(channel ORDER BY touch_order ASC),
            ' > '
        )  AS path,
        MAX(converted)        AS converted,
        COUNT(channel)        AS touch_count
    FROM ordered_touchpoints
    GROUP BY customer_id
)
SELECT *
FROM journey_paths
ORDER BY touch_count DESC, converted DESC;

-- STEP2: チャネル間の遷移確率を計算する

WITH ordered_touchpoints AS (
    SELECT
        customer_id,
        channel,
        touched_at,
        converted,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id ORDER BY touched_at ASC
        )  AS touch_order,
        COUNT(*) OVER (PARTITION BY customer_id)  AS total_touches
    FROM touchpoints
    WHERE touched_at >= TIMESTAMP '2024-01-01 00:00:00'
    AND   touched_at <  TIMESTAMP '2025-01-01 00:00:00'
),
-- 各タッチポイントに「次のチャネル」を付与
with_next AS (
    SELECT
        customer_id,
        channel                                          AS from_channel,
        -- 次のチャネル（最終タッチの次は Conversion または Null）
        COALESCE(
            LEAD(channel) OVER (
                PARTITION BY customer_id ORDER BY touch_order ASC
            ),
            -- 次がない場合（最終タッチ）
            CASE WHEN MAX(converted) OVER (PARTITION BY customer_id) = 1
                 THEN 'Conversion' ELSE 'Null' END
        )  AS to_channel,
        touch_order,
        total_touches
    FROM ordered_touchpoints

    UNION ALL

    -- Start状態からの遷移（各顧客の最初のタッチ）
    SELECT
        customer_id,
        'Start'  AS from_channel,
        channel  AS to_channel,
        0        AS touch_order,
        total_touches
    FROM ordered_touchpoints
    WHERE touch_order = 1
),
-- 遷移回数の集計
transition_counts AS (
    SELECT
        from_channel,
        to_channel,
        COUNT(*)  AS transition_count
    FROM with_next
    GROUP BY from_channel, to_channel
),
-- 遷移確率（各fromチャネルからの出口確率の合計で割る）
transition_matrix AS (
    SELECT
        from_channel,
        to_channel,
        transition_count,
        SUM(transition_count) OVER (PARTITION BY from_channel)  AS from_total,
        ROUND(
            transition_count * 1.0
            / SUM(transition_count) OVER (PARTITION BY from_channel)
        , 4)  AS transition_prob
    FROM transition_counts
)
SELECT *
FROM transition_matrix
ORDER BY from_channel, transition_prob DESC;

-- STEP3: 全経路の転換率を遷移確率から計算する

WITH journey_paths AS (
    -- STEP1の結果
    SELECT
        customer_id,
        ARRAY_JOIN(
            ARRAY_AGG(channel ORDER BY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touched_at))
        , ' > ')  AS path,
        MAX(converted)  AS converted
    FROM touchpoints
    WHERE touched_at >= TIMESTAMP '2024-01-01 00:00:00'
    AND   touched_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY customer_id
),
path_stats AS (
    SELECT
        path,
        COUNT(*)           AS path_count,
        SUM(converted)     AS conversions,
        ROUND(SUM(converted) * 1.0 / COUNT(*), 4)  AS path_conversion_rate
    FROM journey_paths
    GROUP BY path
),
-- 全体の転換率
overall_stats AS (
    SELECT
        SUM(path_count)              AS total_journeys,
        SUM(conversions)             AS total_conversions,
        ROUND(SUM(conversions) * 1.0 / SUM(path_count), 4)  AS overall_conversion_rate
    FROM path_stats
)
SELECT * FROM overall_stats;

-- STEP4: チャネルを除外した場合の転換率と削除効果

WITH journey_paths AS (
    SELECT
        customer_id,
        ARRAY_JOIN(
            ARRAY_AGG(channel ORDER BY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touched_at))
        , ' > ')  AS path,
        MAX(converted)  AS converted
    FROM touchpoints
    WHERE touched_at >= TIMESTAMP '2024-01-01 00:00:00'
    AND   touched_at <  TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY customer_id
),
path_stats AS (
    SELECT path, COUNT(*) AS path_count, SUM(converted) AS conversions
    FROM journey_paths
    GROUP BY path
),
overall AS (
    SELECT SUM(conversions) * 1.0 / SUM(path_count)  AS overall_cr
    FROM path_stats
),
-- 各チャネルを除外したとき（そのチャネルを含む経路の転換数をゼロにする）
channel_removal AS (
    SELECT
        channel_name,
        -- そのチャネルを含まない経路の転換率
        SUM(CASE WHEN path NOT LIKE '%' || channel_name || '%' THEN conversions ELSE 0 END)
        * 1.0
        / SUM(path_count)  AS removal_cr
    FROM path_stats
    CROSS JOIN (
        SELECT 'seo'     AS channel_name UNION ALL
        SELECT 'sns'              UNION ALL
        SELECT 'email'            UNION ALL
        SELECT 'display'          UNION ALL
        SELECT 'direct'
    ) channels
    GROUP BY channel_name
)
SELECT
    cr.channel_name,
    ROUND(o.overall_cr, 4)                              AS overall_conversion_rate,
    ROUND(cr.removal_cr, 4)                             AS removal_conversion_rate,
    ROUND(o.overall_cr - cr.removal_cr, 4)              AS removal_effect,
    -- 削除効果を正規化してシェアに変換（全チャネルの削除効果の合計で割る）
    ROUND(
        (o.overall_cr - cr.removal_cr)
        / SUM(o.overall_cr - cr.removal_cr) OVER ()
    , 4)  AS attribution_share,
    ROUND(
        (o.overall_cr - cr.removal_cr)
        / SUM(o.overall_cr - cr.removal_cr) OVER ()
        * 100
    , 1)  AS attribution_pct
FROM channel_removal  cr
CROSS JOIN overall    o
ORDER BY attribution_pct DESC;

-- 3つのモデルを並べて比較する

WITH journey_paths AS (
    SELECT
        customer_id,
        ARRAY_JOIN(
            ARRAY_AGG(channel ORDER BY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touched_at))
        , ' > ')  AS path,
        MAX(converted)  AS converted,
        -- ファーストタッチ
        MIN(channel)  AS first_channel,
        -- ラストタッチ（最後のチャネル）
        MAX(channel)  AS last_channel  -- 厳密にはLASTの取得が必要だが簡略化
    FROM touchpoints
    WHERE touched_at >= TIMESTAMP '2024-01-01 00:00:00'
    GROUP BY customer_id
),
converted_paths AS (
    SELECT * FROM journey_paths WHERE converted = 1
)
-- ラストタッチの集計
SELECT
    'ラストタッチ'  AS model,
    last_channel    AS channel,
    COUNT(*)        AS conversions,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)  AS attribution_pct
FROM converted_paths
GROUP BY last_channel

UNION ALL

-- ファーストタッチの集計
SELECT
    'ファーストタッチ',
    first_channel,
    COUNT(*),
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)
FROM converted_paths
GROUP BY first_channel

ORDER BY model, attribution_pct DESC;