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

Зачем path analysis

Path analysis выявляет, какие последовательности действий приводят к конверсии чаще остальных. Если 80% покупателей прошли через pricing → faq → checkout, а 20% — напрямую, то страница FAQ полезна. Если из 100 путей landing → demo → support только 2% покупают, а landing → blog → demo даёт 30% — что-то сломано в первом сценарии.

Топ путей до конверсии

WITH paths AS (
    SELECT
        user_id,
        STRING_AGG(event_name, ' -> ' ORDER BY event_timestamp) AS path
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
    HAVING SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) > 0
)
SELECT
    path,
    COUNT(*) AS users,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS share_pct
FROM paths
GROUP BY path
ORDER BY users DESC
LIMIT 20;

Биграмные переходы

Считаем переходы «событие i → событие i+1»:

WITH ordered AS (
    SELECT
        user_id,
        event_name,
        LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS next_event
    FROM events
),
transitions AS (
    SELECT
        event_name AS from_event,
        next_event AS to_event,
        COUNT(*) AS transitions
    FROM ordered
    WHERE next_event IS NOT NULL
    GROUP BY event_name, next_event
)
SELECT
    from_event,
    to_event,
    transitions,
    transitions * 100.0 / SUM(transitions) OVER (PARTITION BY from_event) AS share_from_pct
FROM transitions
ORDER BY from_event, transitions DESC;

Видно, куда чаще уходят с конкретной страницы. pricing → close_tab — плохой сигнал.

Конверсия по пути

Какая доля юзеров с данным путём купила:

WITH user_paths AS (
    SELECT
        user_id,
        STRING_AGG(event_name, ' -> ' ORDER BY event_timestamp)
            FILTER (WHERE event_name <> 'purchase') AS path_before,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS converted
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
),
agg AS (
    SELECT
        path_before,
        COUNT(*) AS users,
        SUM(converted) AS conversions,
        SUM(converted) * 100.0 / COUNT(*) AS cr_pct
    FROM user_paths
    WHERE path_before IS NOT NULL
    GROUP BY path_before
)
SELECT *
FROM agg
WHERE users >= 50
ORDER BY cr_pct DESC
LIMIT 20;

users >= 50 отсекает single-юзер пути — иначе CR 100% будет у любого случайного юзера.

Find high-converting sequences

Ищем 3-граммы, которые часто встречаются перед конверсией:

WITH numbered AS (
    SELECT
        user_id,
        event_name,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS step
    FROM events
),
trigrams AS (
    SELECT
        n1.user_id,
        n1.event_name || ' -> ' || n2.event_name || ' -> ' || n3.event_name AS trigram,
        MAX(CASE WHEN nc.event_name = 'purchase' THEN 1 ELSE 0 END) AS converted
    FROM numbered n1
    JOIN numbered n2 ON n2.user_id = n1.user_id AND n2.step = n1.step + 1
    JOIN numbered n3 ON n3.user_id = n1.user_id AND n3.step = n1.step + 2
    LEFT JOIN events nc ON nc.user_id = n1.user_id AND nc.event_name = 'purchase'
    GROUP BY n1.user_id, n1.event_name, n2.event_name, n3.event_name
)
SELECT
    trigram,
    COUNT(*) AS occurrences,
    SUM(converted) AS conversions,
    SUM(converted) * 100.0 / COUNT(*) AS cr_pct
FROM trigrams
GROUP BY trigram
HAVING COUNT(*) >= 100
ORDER BY cr_pct DESC
LIMIT 20;

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

Ошибка 1. Считать пути на разнородных событиях. Если в events смешаны page views и backend events — путь становится мусором. Фильтруйте до значимых.

Ошибка 2. Игнорировать длинный хвост путей. Топ-20 покрывает 30% юзеров, остальные 70% — уникальные. Анализируйте биграммы вместо полных путей.

Ошибка 3. CR без статистической значимости. Путь «A → B → C» с CR 60% при 10 юзерах — шум. users >= 50 минимум.

Ошибка 4. Pre/post-конверсия в одном пути. Если конверсия в середине, дальнейшие события — это уже post-покупка (поддержка, refund). Обрезайте после первой purchase.

Ошибка 5. Не учитывать sessionization. Один юзер ↔ много сессий с разрывом в дни. Объединять в один путь — спорно. Иногда лучше split по 30-минутным окнам.

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

• Как посчитать customer journey в SQL
• Как посчитать funnel в SQL
• Как посчитать sessionization в SQL
• Как посчитать multi-touch attribution в SQL

FAQ

Биграммы или полные пути?

Биграммы дают больше данных и стат-значимости. Полные пути — для качественного анализа топ-сценариев.

Сколько событий минимум?

Триграмма — минимум 3 события. Для CR-сравнения — 100+ повторений сетапа.

Path analysis в Postgres медленный?

На 10M+ событий — да. ClickHouse / DuckDB подходят лучше.

Как обработать петли?

landing → blog → landing → pricing — циклы можно свернуть или оставить как есть. Зависит от вопроса.

Path vs Markov chain?

Markov даёт transitional probabilities. Path analysis — фактические наблюдения. Markov делает предсказание.