Как посчитать video completion rate в SQL

Зачем completion rate

Video completion rate (VCR) — главный индикатор качества контента. Низкий VCR = boring, плохой производительности, или плохая обложка. У YouTube это «audience retention», у TV — «AMA» (average minute audience). Для marketing video оптимизируют на 25-50% completion (короткий attention span). Для long-form — 60%+.

Формула

completion_rate = views_to_end / views_started

«Started» обычно — view past first 3 seconds. «To end» — past 95% длительности.

Completion в SQL

WITH video_views AS (
    SELECT
        view_id,
        video_id,
        max_watched_seconds,
        video_duration_seconds
    FROM video_view_events
    WHERE view_started_at >= CURRENT_DATE - INTERVAL '30 days'
      AND max_watched_seconds >= 3  -- past 3 seconds = "started"
)
SELECT
    video_id,
    COUNT(*) AS views,
    COUNT(*) FILTER (WHERE max_watched_seconds >= video_duration_seconds * 0.95) AS completed,
    COUNT(*) FILTER (WHERE max_watched_seconds >= video_duration_seconds * 0.95)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS completion_pct
FROM video_views
GROUP BY video_id
HAVING COUNT(*) >= 100
ORDER BY completion_pct DESC;

VCR 70%+ — high engagement. VCR < 20% — content drops viewers быстро.

По длительности видео

SELECT
    CASE
        WHEN video_duration_seconds <= 30  THEN '0-30s'
        WHEN video_duration_seconds <= 60  THEN '30-60s'
        WHEN video_duration_seconds <= 180 THEN '1-3 min'
        WHEN video_duration_seconds <= 600 THEN '3-10 min'
        ELSE '10+ min'
    END AS duration_bucket,
    AVG(CASE WHEN max_watched_seconds >= video_duration_seconds * 0.95 THEN 1.0 ELSE 0 END) * 100 AS avg_completion_pct
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY MIN(video_duration_seconds);

Короткие видео обычно 70-85% completion. Long-form 30-40%.

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

SELECT
    device_type,
    AVG(CASE WHEN max_watched_seconds >= video_duration_seconds * 0.95 THEN 1.0 ELSE 0 END) * 100 AS completion_pct,
    COUNT(*) AS views
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_type
ORDER BY completion_pct DESC;

Mobile completion обычно ниже (отвлекаются), TV — выше (sit-back experience).

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

Ошибка 1. Completion = 100% playback. Видео в 100% не reach из-за credits / pre-roll. 95% — стандарт «completed».

Ошибка 2. Включать 1-second views. Случайный click → 1 second. Filter max_watched_seconds >= 3.

Ошибка 3. Не учитывать skips. Юзер skip-нул кусок → max_watched может быть 100% без actual viewing. Track engaged minutes.

Ошибка 4. Один cutoff для всех. Short-form 95% normal. Long-form 80% реалистично.

Ошибка 5. Сравнивать VCR на ad vs organic. Ads inflate (autoplay), organic clicks — другое поведение.

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

• Как посчитать video watch time в SQL
• Как посчитать engagement в SQL
• Как посчитать bounce rate в SQL
• Как посчитать tutorial completion в SQL

FAQ

Какой VCR хороший?

Short-form (TikTok): 70-90%. Mid-form (YouTube): 40-60%. Long-form (Netflix): 30-50%.

75% или 95% cutoff?

95% — strict. 75% — relaxed. Чаще publishers используют 75%.

Skips влияют?

Без проверки watched_seconds не учтены. Track «engaged seconds» отдельно.

Mobile vs desktop?

Mobile attention shorter (15-30%). Desktop / TV outdo.

Видео без metadata?

Если duration unknown — нельзя считать VCR. Track completion event explicitly.