13 мая 2026 г.·3 мин чтения

Как посчитать touchpoint analysis в SQL

Закрепи формулу touchpoint analysis в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать touchpoint analysis в Telegram

Содержание:

Зачем touchpoint analysis

Touchpoint — любое касание юзера с продуктом (visit, email_open, ad_click, blog_view). Маркетинг хочет знать, через какие касания проходит юзер до конверсии, и какие из них «двигают» сделку. Стандарт перед attribution-моделями.

Среднее число касаний

Сколько raw-касаний нужно юзеру для покупки:

WITH touches AS (
    SELECT
        user_id,
        COUNT(*) AS touch_count
    FROM marketing_touchpoints
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),
converted AS (
    SELECT DISTINCT user_id
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    COUNT(*) AS users,
    AVG(touch_count) AS avg_touches,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY touch_count) AS median_touches,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY touch_count) AS p95_touches
FROM touches
WHERE user_id IN (SELECT user_id FROM converted);

Для B2C 3–7 касаний типично, B2B — 10–20.

Top touchpoints перед deal

Какой канал чаще всего стоит непосредственно перед покупкой:

WITH last_touch AS (
    SELECT
        o.user_id,
        o.created_at AS purchase_at,
        (
            SELECT channel
            FROM marketing_touchpoints mt
            WHERE mt.user_id = o.user_id
              AND mt.event_timestamp < o.created_at
            ORDER BY mt.event_timestamp DESC
            LIMIT 1
        ) AS last_channel
    FROM orders o
    WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    last_channel,
    COUNT(*) AS conversions,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS share_pct
FROM last_touch
WHERE last_channel IS NOT NULL
GROUP BY last_channel
ORDER BY conversions DESC;

Если last-touch — direct у 60% — это «brand» juice от других каналов, не direct сам по себе.

Канальный микс конверсий

Сколько каналов уникальных задействовано перед покупкой:

WITH user_channels AS (
    SELECT
        o.user_id,
        COUNT(DISTINCT mt.channel) AS channels_used
    FROM orders o
    JOIN marketing_touchpoints mt
      ON mt.user_id = o.user_id
     AND mt.event_timestamp < o.created_at
    WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY o.user_id
)
SELECT
    channels_used,
    COUNT(*) AS users,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS share_pct
FROM user_channels
GROUP BY channels_used
ORDER BY channels_used;

channels_used = 1 — single-channel конверсии. ≥ 3 — multi-channel путь, нужна multi-touch attribution.

Закрепи формулу touchpoint analysis в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать touchpoint analysis в Telegram

Эффективность канала

CR по каналу первого касания:

WITH first_touch AS (
    SELECT
        user_id,
        channel,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS rn
    FROM marketing_touchpoints
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
),
exposed AS (
    SELECT channel, COUNT(*) AS users_exposed
    FROM first_touch
    WHERE rn = 1
    GROUP BY channel
),
converted AS (
    SELECT
        ft.channel,
        COUNT(DISTINCT o.user_id) AS converted_users
    FROM first_touch ft
    JOIN orders o ON o.user_id = ft.user_id
    WHERE ft.rn = 1
    GROUP BY ft.channel
)
SELECT
    e.channel,
    e.users_exposed,
    COALESCE(c.converted_users, 0) AS converted,
    COALESCE(c.converted_users, 0) * 100.0 / NULLIF(e.users_exposed, 0) AS first_touch_cr_pct
FROM exposed e
LEFT JOIN converted c USING (channel)
ORDER BY first_touch_cr_pct DESC;

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

Ошибка 1. Last-touch как единственная атрибуция. Подавляет вклад «top of funnel» каналов. Используйте также first-touch и multi-touch.

Ошибка 2. Считать touchpoints без de-dup. Один email_open и пять кликов в нём — это 5 касаний? Зависит от определения. Поправляйте: 1 email_open = 1 касание в день.

Ошибка 3. Touchpoints без attribution window. Если юзер кликнул year ago и купил вчера — не атрибутируйте этому клику. Window 30/60/90 дней.

Ошибка 4. Считать direct как канал. Direct = «не знаем». Часто это последствие предыдущей кампании. Не сравнивайте «direct CR» с «paid CR» наивно.

Ошибка 5. Игнорировать non-converted в knowledge base. Top touchpoints перед deal без сравнения с top touchpoints не-конвертнувших — не показывают causation.

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

FAQ

Сколько touchpoints типично?

B2C 3–7, B2B 10–20.

Что считать touchpoint?

Любое идентифицированное касание: visit, email_open, ad_click, blog_view. Не каждый scroll или impression.

Direct как обработать?

Отдельная категория. Обычно «previous brand exposure» — поэтому атрибутируется не direct сам по себе.

Window для attribution?

30 дней для B2C, 90 для B2B, 180+ для enterprise.

Touchpoint vs interaction?

Touchpoint — момент касания каналом. Interaction — действие юзера внутри касания. Touchpoint крупнее.

Ещё по теме