Как посчитать Exit Rate в SQL

Зачем Exit Rate

Exit Rate = % сессий с page X как last visited page. Используется для funnel optimization. Pages с high exit rate в важных flows — leak points.

Формула

Exit Rate (page) = sessions_ended_on_page / total_sessions_visited_page × 100%

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

WITH page_visits AS (
    SELECT
        session_id,
        page_url,
        event_time,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time DESC) AS rn_from_end
    FROM events
    WHERE event_name = 'page_view'
      AND event_time >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT
    page_url,
    COUNT(*) AS total_visits,
    COUNT(*) FILTER (WHERE rn_from_end = 1) AS exits,
    COUNT(*) FILTER (WHERE rn_from_end = 1)::NUMERIC * 100 / COUNT(*) AS exit_rate_pct
FROM page_visits
GROUP BY page_url
HAVING COUNT(*) >= 50
ORDER BY exits DESC
LIMIT 50;

Exit Rate vs Bounce Rate

Bounce — entry page only. Exit — any page в sequence.

-- Bounce: session has 1 PV total
WITH session_pv_count AS (
    SELECT session_id, COUNT(*) AS pvs
    FROM events
    WHERE event_name = 'page_view'
    GROUP BY session_id
)
SELECT
    COUNT(*) FILTER (WHERE pvs = 1)::NUMERIC * 100 / COUNT(*) AS bounce_rate_pct
FROM session_pv_count;

-- Exit: last page in any session
-- (already shown above)

Top exit pages

Pages с unusually high exit rate:

WITH stats AS (
    SELECT
        page_url,
        COUNT(*) AS total_visits,
        COUNT(*) FILTER (WHERE rn_from_end = 1)::NUMERIC * 100 / COUNT(*) AS exit_rate
    FROM (
        SELECT
            page_url,
            ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time DESC) AS rn_from_end
        FROM events
        WHERE event_name = 'page_view'
          AND event_time >= CURRENT_DATE - INTERVAL '30 days'
    ) e
    GROUP BY page_url
    HAVING COUNT(*) >= 100
),
avg_stats AS (
    SELECT AVG(exit_rate) AS avg_exit
    FROM stats
)
SELECT
    s.page_url,
    s.total_visits,
    s.exit_rate,
    a.avg_exit,
    s.exit_rate - a.avg_exit AS diff_from_avg
FROM stats s, avg_stats a
WHERE s.exit_rate > a.avg_exit * 1.5
ORDER BY s.exit_rate DESC;

По step в funnel

WITH funnel_pages AS (
    SELECT
        session_id,
        page_url,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) AS step,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time DESC) AS rn_from_end
    FROM events
    WHERE event_name = 'page_view'
      AND page_url IN ('/landing', '/signup', '/checkout', '/confirmation')
)
SELECT
    page_url,
    COUNT(*) AS visits,
    COUNT(*) FILTER (WHERE rn_from_end = 1)::NUMERIC * 100 / COUNT(*) AS exit_rate
FROM funnel_pages
GROUP BY page_url
ORDER BY visits DESC;

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

Ошибка 1. Confusing с bounce. Bounce — single-page sessions. Exit — last page (multi-page session).

Ошибка 2. Filter low-volume. Pages с 5 visits, 80% exit — noise. Min sample.

Ошибка 3. Goal pages. «Thank you» / confirmation pages — high exit OK. Achievement = exit.

Ошибка 4. Session timeout как exit. User idled out = exit. But maybe тwitched away, returns. Define.

Ошибка 5. Multiple devices. Started checkout on mobile, completed on desktop — looks like exit, but not.

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

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

FAQ

Exit vs Bounce?

Bounce — 1-page session. Exit — last page in any session.

High exit OK?

На goal pages (confirmation) — yes. На funnel mid-step — no.

Cross-device exit?

If user_id tracked across devices, look at user journey. Otherwise per-device.

Exit rate всегда 100% где-то?

Каждая session кончается на каком-то page. Sum exits = sum sessions. But % per page varies.

Time-out как exit?

GA defines 30 min inactivity = session end. Standard.