Как посчитать page views в SQL

Зачем page views

PV — фундаментальная web metric. Reach traffic, content performance, ad inventory. Не всегда optimize for (engagement deeper signal), но базовая в реportingе.

Total vs Unique PVs

Total PVs = COUNT(*)
Unique PVs = COUNT(DISTINCT (user_id, page_url, session_id))

Unique PV — same URL same session counted once. Total — every refresh / re-visit counted.

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

SELECT
    DATE_TRUNC('day', event_time) AS day,
    COUNT(*) AS total_pvs,
    COUNT(DISTINCT user_id) AS unique_visitors,
    COUNT(DISTINCT session_id) AS sessions,
    COUNT(*) / NULLIF(COUNT(DISTINCT session_id), 0) AS pvs_per_session
FROM events
WHERE event_name = 'page_view'
  AND event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По URL

Top performing pages:

SELECT
    page_url,
    COUNT(*) AS pvs,
    COUNT(DISTINCT user_id) AS unique_visitors,
    COUNT(DISTINCT session_id) AS sessions,
    AVG(time_on_page_sec) AS avg_time_sec
FROM events
WHERE event_name = 'page_view'
  AND event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY page_url
ORDER BY pvs DESC
LIMIT 50;

По сегментам

SELECT
    u.country,
    u.device_type,
    COUNT(*) AS pvs,
    COUNT(DISTINCT u.user_id) AS visitors
FROM events e
JOIN users u USING (user_id)
WHERE e.event_name = 'page_view'
  AND e.event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY ROLLUP(u.country, u.device_type)
ORDER BY u.country, u.device_type;

ROLLUP для subtotal + grand total.

Page paths (sequences)

WITH sequence AS (
    SELECT
        session_id,
        page_url,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) AS step
    FROM events
    WHERE event_name = 'page_view'
)
SELECT
    page_url AS step1_url,
    LEAD(page_url, 1) OVER (PARTITION BY session_id ORDER BY step) AS step2_url,
    LEAD(page_url, 2) OVER (PARTITION BY session_id ORDER BY step) AS step3_url
FROM sequence
WHERE step = 1
LIMIT 100;

Common path analysis для UX.

PV trends

SELECT
    DATE_TRUNC('week', event_time) AS week,
    COUNT(*) AS weekly_pvs,
    LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('week', event_time)) AS prev_week_pvs,
    (COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('week', event_time)))::NUMERIC * 100
    / NULLIF(LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('week', event_time)), 0) AS wow_change_pct
FROM events
WHERE event_name = 'page_view'
  AND event_time >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY 1
ORDER BY 1;

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

Ошибка 1. URL with query params. /page?utm=x и /page?utm=y — different URLs в COUNT. Strip query strings для page-level analytics.

Ошибка 2. Bot traffic. Crawlers могут составлять 30%+ PVs. Filter.

Ошибка 3. Single page apps (SPA). React routing — не reload page. Track router events not pageload.

Ошибка 4. PV count vs Unique count confusion. Stakeholders ожидают unique. Default total — overcounts.

Ошибка 5. Reloads. F5 = additional PV в analytics. Treat as same или not — define.

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

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

FAQ

Total vs Unique PVs?

Total = все. Unique = unique combos. Report both.

Query params strip?

Yes. /page?utm=x ≈ /page. Use REGEXP_REPLACE.

Bot filtering?

User-agent filter, IP blacklists, rate-limit thresholds (>100 PV/min = bot).

SPA tracking?

Hook to router. Track virtual page views.

PV vs unique user?

PV — events. Unique user — counted once independent of repeated views.