ivi — метрика «цепляемости» сериала и сегментный retention

Условие

Структура — content_watch (просмотры) + content (контент с типом монетизации SVOD/AVOD/TVOD, признаком сериала через compilation_id).

Задание 2 — спроектировать метрики и SQL-запросы:

1. «Цепляемость» и «крутость» сериала. Нужна метрика, которая при наличии 3–4 серий сериала позволит сравнить его «крутость» с другими сериалами.

2. Retention всех пользователей сервиса. Нужно сегментировать аудиторию, рассчитать ретеншн по сегментам и дать рекомендации по улучшению ретеншна каждого сегмента.

Если в тестовой базе чего-то не хватает — описать недостающее.

Решение

Подход

Это вопрос про дизайн метрик, а не SQL. Хороший ответ:

1. Чётко определяет цель метрики и что она должна различать.
2. Предлагает формулу с разбором, почему именно так.
3. Упоминает ловушки (что метрика не учитывает).
4. Признаёт отсутствие данных, если они нужны.

Часть 1. Цепляемость сериала (binge-метрика)

Цель

Различить сериалы:

• Цепляющий: посмотрел 1 серию → захотел продолжить → посмотрел все.
• Лёгкий, но не цепляющий: посмотрел 1 серию → бросил (популярный у новичков, но не удерживает).
• Тяжёлый: мало кто начинает, но кто начал — досматривает.

Метрика

«Average watch depth» — медианная глубина просмотра серии.

WITH series_starters AS (
  -- Юзеры, посмотревшие хотя бы серию 1 этого сериала
  SELECT cw.user_id, c.compilation_id
  FROM content_watch cw
  JOIN content c ON c.content_id = cw.content_id
  WHERE c.episode = 1
  GROUP BY cw.user_id, c.compilation_id
),
max_episode_per_user AS (
  -- Самая поздняя серия этого сериала, до которой дошёл юзер
  SELECT cw.user_id, c.compilation_id, MAX(c.episode) AS last_episode
  FROM content_watch cw
  JOIN content c ON c.content_id = cw.content_id
  GROUP BY cw.user_id, c.compilation_id
)
SELECT
  s.compilation_id,
  AVG(m.last_episode)                    AS avg_depth,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY m.last_episode) AS median_depth,
  COUNT(*)                                AS series_starters
FROM series_starters s
JOIN max_episode_per_user m USING (user_id, compilation_id)
GROUP BY s.compilation_id
HAVING COUNT(*) >= 100;  -- min sample size для надёжности

Это базовая метрика «как далеко смотрят», но она наивная: если сериал из 10 серий, max=10. Если из 3 — max=3. Несравнимо.

Лучший вариант — нормализованная

Completion rate(N) = доля стартеров, дошедших до серии N. Кривая сериала по сериям.

С 3–4 сериями измеримо: completion(2) / completion(1), completion(3) / completion(2) — это inter-episode retention.

Метрика «крутости»: geomean(completion(2)/completion(1), completion(3)/completion(2), ...) — геометрическое среднее inter-episode retention. Чем выше → тем больше «цепляет» от серии к серии.

WITH per_episode_users AS (
  SELECT
    c.compilation_id,
    c.episode,
    COUNT(DISTINCT cw.user_id) AS users
  FROM content_watch cw
  JOIN content c ON c.content_id = cw.content_id
  GROUP BY c.compilation_id, c.episode
)
SELECT
  compilation_id,
  episode,
  users,
  LAG(users) OVER (PARTITION BY compilation_id ORDER BY episode) AS prev_users,
  users * 1.0 / NULLIF(LAG(users) OVER (PARTITION BY compilation_id ORDER BY episode), 0)
       AS retention_to_next
FROM per_episode_users;

Усреднение по эпизодам даёт «индекс цепляемости»; высокий индекс при стабильно высоком inter-episode retention.

Усиление: учитываем «глубину просмотра серии»

Если есть show_duration и длина серии — добавляем «% серии досмотрен». Идеальная цепляемость = «90% досмотрено + переход к следующей серии».

-- Доля серий, досмотренных >80%
SELECT compilation_id, episode,
       AVG(CASE WHEN show_duration / episode_length > 0.8 THEN 1 ELSE 0 END) AS pct_completed
FROM ...

Чего не хватает в тестовой базе

• Длина серии в секундах — без неё нельзя считать «досмотренность».
• Лайки / рейтинги пользователя на серию — субъективная оценка.
• Время между сериями (binge vs гошёл смотреть раз в неделю).

Часть 2. Retention пользователей сервиса

Сегментация

Возможные сегменты:

1. По monetization usage: только AVOD / SVOD-подписчики / TVOD-покупатели / mixed.
2. По типу контента: фильмолюбы / сериаломаны / детский контент / спорт / документалки.
3. По частоте: ежедневные / еженедельные / редкие.
4. По sources: organic / paid UA / referral.
5. По платформе: TV / mobile / desktop.

Самая полезная — по типу контента + по типу монетизации.

Расчёт retention по сегментам

WITH first_watch AS (
  SELECT user_id, MIN(DATE(show_date)) AS first_dt
  FROM content_watch
  GROUP BY user_id
),
user_segment AS (
  SELECT
    cw.user_id,
    -- Тип контента: какой смотрят больше
    CASE
      WHEN SUM(CASE WHEN c.compilation_id IS NOT NULL THEN 1 ELSE 0 END) >
           SUM(CASE WHEN c.compilation_id IS NULL     THEN 1 ELSE 0 END)
      THEN 'series_lover' ELSE 'standalone_lover'
    END AS content_type_seg,
    -- Тип монетизации
    MAX(c.paid_type) AS paid_seg
  FROM content_watch cw
  JOIN content c USING (content_id)
  GROUP BY cw.user_id
),
retention AS (
  SELECT
    f.user_id,
    s.content_type_seg,
    s.paid_seg,
    f.first_dt,
    DATE(cw.show_date) - f.first_dt AS day_after,
    1 AS active
  FROM first_watch f
  JOIN content_watch cw USING (user_id)
  JOIN user_segment s USING (user_id)
)
SELECT
  content_type_seg,
  paid_seg,
  day_after,
  COUNT(DISTINCT user_id) AS active_users
FROM retention
WHERE day_after IN (1, 7, 30)
GROUP BY content_type_seg, paid_seg, day_after;

Рекомендации по сегментам

Чего не хватает в тестовой базе

• Категория контента (жанр) — не сказано в условии, важно для рекомендаций.
• Время посещения — пиковые часы для push.
• Платёжки и подписки — отдельная таблица. По текущим данным мы знаем тип контента, но не активный план юзера.
• Поисковые запросы и клики — разница «активно ищет» и «случайно зашёл».

Анализ / интерпретация

Главное в ответе:

1. Метрики «цепляемости» сериала строятся на completion rate per episode + inter-episode retention.
2. Retention аудитории сегментируем по типу контента и типу монетизации — это позволяет давать дифференцированные рекомендации.
3. Для полноты нужны длина серии, жанр, подписки — отметить это явно.

Подводные камни

1. Размер выборки. На малопопулярных сериалах метрика «цепляемости» — шум. Min 100 starter-ов.
2. Сериалы разной длины. 3-серийный «мини» и 50-серийный шоу — разные паттерны. Нормировать на относительную глубину.
3. Cohort effect. Юзер, начавший сериал в день релиза, имеет другие условия, чем месяц спустя. Учитывайте «freshness».
4. Пропуски серий. Юзер может посмотреть серии 1, 2, 5 — пропустить 3 и 4. Считаем last_episode = 5 или connected_completion = 2?
5. Re-watch. Просмотр одной серии 3 раза. Считаем 1 user, не 3.
6. Сегментация по «что смотрит» — chicken-and-egg. Юзер смотрит то, что ему рекомендуют. Если детектируем «серияломан», это может быть следствием рекомендации.
7. Retention метрика чувствительна к границе дня. Часовые пояса — обязательно учесть.

Эталонный ответ

Цепляемость сериала: inter-episode retention = completion(N) / completion(N-1), geomean по всем парам. Дополнительно — % досмотренной серии (если есть длина).

Retention аудитории: сегментация по (content_type, monetization_type, platform), расчёт D1/D7/D30 по сегментам, дифференцированные рекомендации.

Чего не хватает в данных: длина серии (для досмотренности), жанр, подписки, поисковые события.