Как посчитать customer journey в SQL

Зачем customer journey

Customer journey — упорядоченная последовательность touchpoints, через которые юзер прошёл до конверсии (или отвалился). В SQL обычно сводят к строке вида landing → blog → pricing → checkout. Используется для атрибуции, оптимизации воронки, поиска «странных» сценариев типа pricing → support → refund.

Структура данных

Минимум — user_id, event_name, event_timestamp. Иногда channel (email, organic, paid). Один юзер = много строк, упорядоченных по времени:

user_id | event_timestamp     | event_name | channel
42      | 2026-05-01 10:00:00 | landing    | organic
42      | 2026-05-01 10:03:00 | blog       | direct
42      | 2026-05-02 14:30:00 | pricing    | direct
42      | 2026-05-02 14:35:00 | checkout   | direct
42      | 2026-05-02 14:38:00 | purchase   | -

Путь до конверсии

Строим путь как строку через STRING_AGG:

WITH user_events AS (
    SELECT
        user_id,
        event_timestamp,
        event_name,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS event_rank
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
),
converted AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event_name = 'purchase'
),
journey AS (
    SELECT
        ue.user_id,
        STRING_AGG(ue.event_name, ' -> ' ORDER BY ue.event_rank) AS path,
        COUNT(*) AS path_length,
        MAX(ue.event_timestamp) - MIN(ue.event_timestamp) AS journey_duration
    FROM user_events ue
    WHERE ue.user_id IN (SELECT user_id FROM converted)
    GROUP BY ue.user_id
)
SELECT path, COUNT(*) AS users
FROM journey
GROUP BY path
ORDER BY users DESC
LIMIT 20;

Топ-путь обычно простой: landing → pricing → checkout → purchase. Длинные пути часто означают confusion.

Длина пути и время

WITH journey AS (
    SELECT
        user_id,
        COUNT(*) AS path_length,
        EXTRACT(EPOCH FROM (MAX(event_timestamp) - MIN(event_timestamp))) / 3600 AS hours_to_convert
    FROM events
    WHERE user_id IN (SELECT user_id FROM converted)
    GROUP BY user_id
)
SELECT
    path_length,
    COUNT(*) AS users,
    AVG(hours_to_convert) AS avg_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours_to_convert) AS median_hours
FROM journey
GROUP BY path_length
ORDER BY path_length;

Аномалии: «путь из 30 шагов» = bot или баг трекера, выкидывайте.

Сегментация по типу пути

WITH journey AS (
    SELECT
        user_id,
        STRING_AGG(event_name, ' -> ' ORDER BY event_timestamp) AS path
    FROM events
    GROUP BY user_id
)
SELECT
    CASE
        WHEN path LIKE '%pricing%checkout%' THEN 'direct_intent'
        WHEN path LIKE '%blog%pricing%' THEN 'content_to_paid'
        WHEN path LIKE '%support%' THEN 'pre_sale_question'
        WHEN path LIKE '%trial%' THEN 'trial_user'
        ELSE 'other'
    END AS journey_type,
    COUNT(*) AS users
FROM journey
GROUP BY journey_type
ORDER BY users DESC;

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

Ошибка 1. Не отсортировать события. Без ORDER BY event_timestamp агрегация даёт случайный порядок — путь становится бессмыслицей.

Ошибка 2. Один юзер = много анонимных сессий. До логина у юзера может быть несколько device_id. Не идентифицировав — посчитаете каждую сессию отдельным путём.

Ошибка 3. Сжимать одинаковые последовательные шаги. landing → landing → landing обычно нужно свернуть в landing. LAG + фильтр.

Ошибка 4. STRING_AGG без ORDER BY внутри. В Postgres STRING_AGG(event_name, ' -> ') без ORDER BY даёт недетерминированный порядок.

Ошибка 5. Считать на всех событиях. Если в events 50 типов событий — путь становится мусором. Фильтруйте до значимых (page_view верхне-уровневых, не каждый клик).

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

• Как посчитать funnel в SQL
• Как посчитать funnel drop-off в SQL
• Как посчитать time-to-convert в SQL
• Как посчитать sessionization в SQL

FAQ

Сколько событий в пути — норма?

Для SaaS — 5–15. Для e-com — 3–8. > 20 — обычно bot или потерянный юзер.

Как обработать множественные конверсии?

Возьмите первую (first conversion) или все и анализируйте отдельно (retention loop).

Что включать в события?

Только значимые page views и actions: landing, blog, pricing, signup, trial_start, checkout, purchase. Не каждый клик.

Как обработать длинные пути?

Сжать одинаковые соседние, обрезать после конверсии, выкинуть юзеров с n > 50 событий.

Customer journey vs funnel?

Funnel — фиксированные шаги. Journey — реальный путь, любой порядок. Funnel — частный случай.