Как посчитать Time on Site в SQL

Зачем Time on Site

Time on Site (session duration) — измеряет engagement. Длинная session = много контента / value. Короткая = quick bounce или quick task complete (зависит от продукта).

Формула

Time on Site = MAX(event_time) - MIN(event_time)  -- per session

Limitation: last event time не = exit time. Real exit time неизвестно — fallback to last interaction.

Базовый расчёт

WITH session_times AS (
    SELECT
        session_id,
        user_id,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end,
        EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time))) AS duration_seconds,
        EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time))) / 60 AS duration_minutes,
        COUNT(*) AS events
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY session_id, user_id
)
SELECT
    COUNT(*) AS sessions,
    AVG(duration_seconds) AS avg_duration_sec,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_duration_sec,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY duration_seconds) AS p90_duration_sec,
    COUNT(*) FILTER (WHERE events = 1) AS single_event_sessions
FROM session_times;

Time on page

Estimate per-page time via LEAD on event timestamps:

WITH page_times AS (
    SELECT
        session_id,
        page_url,
        event_time,
        LEAD(event_time) OVER (PARTITION BY session_id ORDER BY event_time) AS next_event_time
    FROM events
    WHERE event_name = 'page_view'
)
SELECT
    page_url,
    COUNT(*) AS visits,
    AVG(EXTRACT(EPOCH FROM (next_event_time - event_time))) AS avg_seconds_on_page,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (next_event_time - event_time))) AS median_seconds_on_page
FROM page_times
WHERE next_event_time IS NOT NULL  -- last page IN session has no exit time
GROUP BY page_url
ORDER BY visits DESC
LIMIT 50;

Last page не measured (no next event).

Average vs Median

SELECT
    AVG(duration_seconds) AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median,
    AVG(duration_seconds) - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS skew_indicator
FROM session_times;

Right-skewed → mean > median. Reading marathon (1-2% users) inflate AVG. Use median.

По device

SELECT
    device_type,
    COUNT(*) AS sessions,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_sec,
    AVG(duration_seconds) AS mean_sec
FROM session_times
JOIN devices USING (user_id, session_id)
GROUP BY device_type;

Desktop sessions обычно longer чем mobile.

Bounce sessions exclusion

Bounce sessions have 1 event → duration = 0. Excludes для clean metric:

SELECT
    AVG(duration_seconds) AS avg_engaged_duration
FROM session_times
WHERE events > 1;  -- exclude bounces

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

Ошибка 1. Last event = exit. Real user closed tab N seconds after last interaction. Unknown N.

Ошибка 2. AVG pulled by whales. Marathon sessions skew. Use median.

Ошибка 3. Bounce sessions = 0 duration. Pulls average down. Decide: include или not.

Ошибка 4. Server-side timestamps. Latency adds to time. Use client timestamps если available.

Ошибка 5. Time-zone mismatch. event_time в local. Comparing — convert to UTC.

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

• Как посчитать sessionization в SQL
• Как посчитать page views в SQL
• Как посчитать bounce rate в SQL
• Как посчитать engagement rate в SQL

FAQ

Какой Time on Site ok?

E-com: 2-4 min. News: 1-3 min. SaaS dashboard: 5-15 min. Зависит от purpose.

Time on last page?

Unknown. Approximate by avg session duration / number of pages. Crude.

AVG или Median?

Median. Long tail сильно искажает AVG.

Bounce exclude?

Yes для «engaged duration». Both report — meaningful comparison.

Inactivity timeout?

GA — 30 min. Means session_end = last event если > 30 min from next.