Как посчитать session depth в SQL

Зачем session depth

Session depth (events per session или pages per session) — насколько глубоко юзер «погружается» в продукт за один заход. Низкий depth + высокий traffic = плохой content / UX, юзеры уходят сразу. Высокий depth + низкий traffic = вовлекают тех, кто пришёл, но мало приходящих.

События за сессию

Сначала нужна сессия. Простейшая определяется как 30-минутный gap inactivity:

WITH events_with_session AS (
    SELECT
        user_id,
        event_timestamp,
        SUM(CASE
            WHEN EXTRACT(EPOCH FROM (event_timestamp - LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp))) > 1800
            THEN 1 ELSE 0
        END) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS session_id
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
),
session_depth AS (
    SELECT
        user_id,
        session_id,
        COUNT(*) AS events_in_session
    FROM events_with_session
    GROUP BY user_id, session_id
)
SELECT
    AVG(events_in_session) AS avg_depth,
    COUNT(*) AS total_sessions
FROM session_depth;

Среднее 4-6 событий — типично для consumer apps.

Перцентили

SELECT
    AVG(events_in_session) AS avg_depth,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY events_in_session) AS p25,
    PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY events_in_session) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY events_in_session) AS p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY events_in_session) AS p95
FROM session_depth;

Median 2-3, mean 5 — heavy long tail. Это норма (большинство сессий короткие, мало супер-длинных).

По cohort и устройству

SELECT
    DATE_TRUNC('month', u.created_at)::DATE AS cohort_month,
    s.platform,
    COUNT(DISTINCT s.session_id) AS sessions,
    AVG(sd.events_in_session) AS avg_depth,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sd.events_in_session) AS median_depth
FROM session_depth sd
JOIN sessions s USING (session_id)
JOIN users u USING (user_id)
WHERE u.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY DATE_TRUNC('month', u.created_at), s.platform
ORDER BY cohort_month, s.platform;

Сравните iOS vs Android: разница в depth обычно ~20%. Резкая разница = UX-проблема на одной платформе.

Bot-фильтр

Сессии с depth 50+ — часто bots или scraping:

SELECT
    user_id,
    COUNT(*) AS suspicious_sessions
FROM session_depth
WHERE events_in_session > 100
GROUP BY user_id
HAVING COUNT(*) >= 5
ORDER BY suspicious_sessions DESC;

Эти юзеры — кандидаты на исключение из общей статистики.

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

Ошибка 1. Average на skewed данных. Distribution depth long-tail. Mean даст 5, median 2 — большая разница. Сообщайте оба.

Ошибка 2. Session gap 30 минут — слишком жёстко. Для long-read контента типичная пауза 1+ час. Калибруйте gap.

Ошибка 3. Считать только page_view events. Если интересна глубина взаимодействия, считайте все events (clicks, scrolls).

Ошибка 4. Bots не отфильтрованы. Bot session с 1000 hits раздует mean. Filter > 95th percentile или by user-agent.

Ошибка 5. Сравнение depth с конкурентом без context. Контентный сайт vs SaaS app — несравнимы. Depth metaning разный.

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

• Как посчитать sessions в SQL
• Как посчитать sessionization в SQL
• Как посчитать time-on-site в SQL
• Как посчитать engagement в SQL

FAQ

Какой depth «хороший»?

Editorial site: 1.5-3. SaaS dashboard: 5-15. Социалка: 10+.

Считать unique pages или total events?

Unique pages для content (avoid scroll inflation). Total events для apps.

Bounce — это depth = 1?

Часто да. Bounce rate = % sessions с depth 1.

Депт ниже — плохо?

Не всегда. Сильно зависит от типа продукта.

Sessionization gap какой?

30 минут — стандарт. Для контента иногда 60+.