Как посчитать Multi-Touch Attribution в SQL

Зачем Multi-Touch

Last-touch overweights closing channels. First-touch overweights top-of-funnel. Multi-touch — все touchpoints получают часть credit, более fair.

Модели

Linear Attribution

WITH user_touches AS (
    SELECT
        c.user_id,
        c.amount AS conversion_value,
        t.channel,
        COUNT(*) OVER (PARTITION BY c.user_id) AS total_touches
    FROM conversions c
    JOIN touchpoints t ON t.user_id = c.user_id AND t.touched_at <= c.converted_at
)
SELECT
    channel,
    SUM(conversion_value::NUMERIC / total_touches) AS attributed_revenue
FROM user_touches
GROUP BY channel
ORDER BY attributed_revenue DESC;

Каждый touch получает conversion_value / num_touches.

U-shaped

40% first + 40% last + 20% distributed middle:

WITH ranked AS (
    SELECT
        c.user_id,
        c.amount,
        t.channel,
        t.touched_at,
        ROW_NUMBER() OVER (PARTITION BY c.user_id ORDER BY t.touched_at) AS rn,
        COUNT(*) OVER (PARTITION BY c.user_id) AS total
    FROM conversions c
    JOIN touchpoints t ON t.user_id = c.user_id AND t.touched_at <= c.converted_at
),
weighted AS (
    SELECT
        user_id,
        channel,
        amount,
        rn,
        total,
        CASE
            WHEN total = 1 THEN amount
            WHEN total = 2 AND rn = 1 THEN amount * 0.5
            WHEN total = 2 AND rn = 2 THEN amount * 0.5
            WHEN rn = 1 THEN amount * 0.4
            WHEN rn = total THEN amount * 0.4
            ELSE amount * 0.2 / NULLIF(total - 2, 0)
        END AS attributed
    FROM ranked
)
SELECT channel, SUM(attributed) AS attributed_revenue
FROM weighted
GROUP BY channel
ORDER BY attributed_revenue DESC;

Time-decay

Exponential, recent touches более weight:

WITH weights AS (
    SELECT
        c.user_id,
        c.amount,
        t.channel,
        c.converted_at,
        t.touched_at,
        EXP(-0.5 * EXTRACT(EPOCH FROM (c.converted_at - t.touched_at)) / 86400 / 7) AS weight
    FROM conversions c
    JOIN touchpoints t ON t.user_id = c.user_id AND t.touched_at <= c.converted_at
),
normalized AS (
    SELECT
        user_id,
        channel,
        amount,
        weight,
        weight / SUM(weight) OVER (PARTITION BY user_id) AS normalized_weight
    FROM weights
)
SELECT channel, SUM(amount * normalized_weight) AS attributed_revenue
FROM normalized
GROUP BY channel
ORDER BY attributed_revenue DESC;

EXP(-0.5 × days/7) — half-life 7 days (approximate).

Частые ошибки

Ошибка 1. Без lookback window. Touch 2 года назад — не influence. Cap window 30-180 days.

Ошибка 2. Same model for all conversions. B2C — time-decay. B2B — U-shaped (long cycle).

Ошибка 3. Direct excluded. Direct туч — это re-engagement или part of journey? Decision.

Ошибка 4. Не валидировать. ML-driven attribution требует validation against incrementality tests.

Ошибка 5. Compare models without context. Linear vs U-shaped give different numbers — both correct в своих assumptions.

Связанные темы

• Как посчитать First-Touch Attribution в SQL
• Как посчитать Last-Touch Attribution в SQL
• Как посчитать conversion window в SQL
• Как посчитать ROAS в SQL

FAQ

Какая модель лучше?

Зависит от business. B2C — time-decay. B2B — U-shaped (long cycle). Data-driven (ML) — most accurate but complex.

Multi-touch обязательно?

Для maturity yes. Стартап — start with first / last, потом graduate.

Lookback window?

B2C: 30-90 days. B2B: 90-365 days.

Shapley values?

Game-theory: each touchpoint's contribution based on все возможные коалиции. ML-grade.

Direct counts?

В большинстве моделей yes, но often filtered (re-engagement).