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

Зачем Time on Page

В блоге pageviews растут, но bounce rate высокий. Time on page показывает: 80% страниц закрывают за 5 секунд (контент не зацепил) или за 3 минуты (читают, но не идут дальше). Это разные проблемы с разными решениями.

Что такое Time on Page

Time on Page — время между загрузкой текущей страницы и следующего pageview (на этом же сайте).

TOP = next_pageview_time - current_pageview_time

Проблема: для last pageview в сессии нет следующего pageview → TOP = 0 в классическом GA.

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

Данные: pageviews(user_id, session_id, page_url, pageview_time).

WITH next_pv AS (
    SELECT
        user_id,
        session_id,
        page_url,
        pageview_time,
        LEAD(pageview_time) OVER (
            PARTITION BY user_id, session_id ORDER BY pageview_time
        ) AS next_pv_time
    FROM pageviews
)
SELECT
    page_url,
    AVG(EXTRACT(EPOCH FROM (next_pv_time - pageview_time))) AS avg_time_on_page_sec,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (next_pv_time - pageview_time))
    ) AS median_top_sec
FROM next_pv
WHERE next_pv_time IS NOT NULL  -- исключаем last page IN session
GROUP BY page_url
HAVING COUNT(*) >= 100
ORDER BY avg_time_on_page_sec DESC;

Проблема последней страницы

На last page в сессии нет следующего pageview. Стандартный TOP = 0, что занижает среднее.

Решение 1: Исключать last pageview (WHERE next_pv_time IS NOT NULL). Но тогда блог-посты (часто last) недопредставлены.

Решение 2: Использовать heartbeat-events (см. ниже).

Решение 3: Использовать session end из beacon API.

Engaged Time vs Time on Page

Современный подход: heartbeat-events каждые 15 секунд если страница в фокусе.

-- Допустим, есть events with type = 'heartbeat'
SELECT
    page_url,
    COUNT(*) * 15 AS total_engaged_sec,
    COUNT(*) * 15.0 / COUNT(DISTINCT session_id) AS avg_engaged_per_session
FROM events
WHERE event_type = 'heartbeat'
  AND event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY page_url
ORDER BY total_engaged_sec DESC;

Engaged Time — точнее, но требует JS-инструментации.

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

Ошибка 1. Outliers. Юзер ушёл на обед с открытой вкладкой → TOP 60 минут. Cap в 30 мин или используйте median.

Ошибка 2. Last page problem. Игнорировать last pageview занижает blog-метрики. Используйте engaged time.

Ошибка 3. Cross-tab. Юзер открыл 5 вкладок — на каждой есть pageview, но активна одна. Heartbeat решает.

Ошибка 4. Timezone. pageview_time в UTC, но привязка пользователя — местное время.

Ошибка 5. Bot-trafic. Боты дают мгновенные навигации между страницами. Фильтруйте.

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

• Как посчитать bounce rate в SQL
• Как посчитать sessions в SQL
• Как посчитать funnel в SQL
• LAG и LEAD в SQL

FAQ

Какой Time on Page считается хорошим?

Контентный блог: 2-5 минут. E-com category page: 30-60 сек. Лендинг: 30 сек - 2 мин.

Time on Page или engaged time?

Engaged — точнее. Time on Page — проще считать.

TOP падает — что делать?

Проверьте качество контента (вышел плохой пост?), скорость загрузки (медленная страница?), мобайл UX.

Cap outliers где?

30 минут — стандарт. Для long-read контента — 60 минут.

TOP коррелирует с CR?

Часто да, но не всегда. Юзер может долго листать перед покупкой, или быстро завершить опытную покупку.