Когорты и удержание читателей
Воронка «Событий» описывает переход от просмотра к завершению материала. Другой вопрос редактора — возвращаются ли читатели позже. Сгруппируем анонимные браузеры по первому наблюдаемому дню и посмотрим активность на следующий календарный день. Результат урока — отчёт удержания D1 с явными границами наблюдения.
Удержание не равно числу новых аккаунтов: мы используем anon_id из браузерного контракта. Запросы не выполнялись, а ожидаемые результаты относятся к четырём вымышленным идентификаторам. Из небольшого набора нельзя сделать вывод о качестве настоящего курса или поведении аудитории ProfessorWeb.
Когорта и день активности
Когорта объединяет объекты по общему началу. В нашем варианте начало — первый наблюдаемый день просмотра урока в доступной истории. Не первый день жизни человека, не дата регистрации и не первый запрос сервера вообще. Такое определение нужно сохранить возле отчёта, чтобы слова «новый читатель» не означали больше, чем доступные данные.
Активностью тоже считаем просмотр. Завершение без просмотра в конкретный день не добавляет возвращение по этому правилу. Можно выбрать любое учебное действие, но тогда изменится метрика. Согласованность важнее одинакового результата между разными определениями.
Сначала получим уникальные дни просмотра браузера. Повторные просмотры в тот же день не создают несколько возвращений. Затем найдём минимальную дату каждого браузера и свяжем её с активными днями.
WITH activity AS
(
SELECT DISTINCT anon_id, toDate(occurred_at) AS day
FROM events_lab.logical_events
WHERE event_name = 'lesson_view'
), cohorts AS
(
SELECT anon_id, min(day) AS cohort_day
FROM activity GROUP BY anon_id
), reader_flags AS
(
SELECT c.anon_id, c.cohort_day,
max(a.day = c.cohort_day + INTERVAL 1 DAY) AS returned_d1
FROM cohorts AS c INNER JOIN activity AS a ON c.anon_id = a.anon_id
GROUP BY c.anon_id, c.cohort_day
)
SELECT cohort_day, count() AS cohort_size,
countIf(returned_d1) AS readers_d1,
readers_d1 / nullIf(cohort_size, 0) AS retention_d1
FROM reader_flags
GROUP BY cohort_day
ORDER BY cohort_day;Это полный учебный запрос D1 без ограничения доступной истории. При внешнем использовании нужно отдельно задать начальные когорты и достаточный период наблюдения. WITH здесь именует промежуточные выражения; не следует считать его автоматическим сохранением результатов как таблиц. Семантика WITH описана в документации ClickHouse.
Что показывают наши даты
a впервые просматривает урок первого октября и возвращается второго. b впервые приходит первого и возвращается третьего, поэтому не достигает D1, хотя достигает D2. Когорта первого числа содержит два браузера и один возврат следующего дня: ожидаем 50 процентов.
c впервые наблюдается второго числа и возвращается четвёртого. Для D1 ожидаем ноль. d впервые наблюдается третьего, а последующего просмотра в наборе нет. Если данные доступны только до четвёртого включительно, D1 третьей когорты уже можно наблюдать, но вывод ограничен полнотой именно этого дня.
Позднее завершение b не меняет текущую метрику, потому что активность определена просмотром. Если выбрать любую учебную активность, его получение могло бы уточнить другой срез. Такое различие хорошо показывает, почему название «удержание» без определения события, календаря и начала недостаточно.
Календарный D1 и двадцать четыре часа
В запросе D1 означает следующий календарный день UTC. Просмотр вечером и возвращение утром через десять часов засчитаются. Возвращение через двадцать часов в тот же календарный день не засчитается. Это не ошибка: календарное удержание и окно в секундах отвечают разным вопросам.
Восьмой урок использовал сутки в воронке как 86400 секунд. Здесь день вычисляется через дату. Если редактор ожидает другую временную зону, её нужно применить последовательно к первому дню и активности. Иначе две части отчёта будут использовать разные границы полуночи.
Не называйте D1 «вернулся в течение суток», если считаете календарный следующий день. Такие подписи часто кажутся близкими, но меняют результаты. Лучше писать «просмотр на следующий календарный день, UTC». Для учебного dashboard точное определение важнее короткого маркетингового заголовка.
Незрелая когорта
Если браузер впервые появился сегодня, следующий день ещё не закончился. Ноль возвратов нельзя объявлять окончательным D1. Нужно либо исключить незрелую когорту, либо показать, что значение предварительное. Аналогично D7 требует доступных данных до седьмого последующего дня и договора о поздней доставке.
При отчёте на фиксированную дату можно ограничить cohort_day так, чтобы весь день D1 был полностью наблюдаем. Дополнительный буфер поздних событий зависит от политики потока. Пятый октябрь в фикстуре является датой получения, но не доказывает полноту всех просмотров до четвёртого; это отдельное соглашение учебного набора.
Полный набор не содержит историю до первого октября. Поэтому первый наблюдаемый просмотр может принадлежать давно существовавшему браузеру. Для настоящего отчёта нужна накопленная таблица первого появления или достаточно полная история. Обрезание данных TTL способно сместить начало, если вычислять min заново по оставшемуся хвосту.
Удаление идентификатора и связь устройств
Очистка браузерного значения создаёт новый anon_id, даже если человек продолжает тот же курс. Второе устройство тоже выглядит новым браузером. Поэтому отчёт не должен обещать точное удержание людей. Связывание устройств с аккаунтом является отдельным решением по данным и доступу, а не невидимой поправкой SQL.
Стабильные идентификаторы облегчают измерение возвращений и увеличивают связанность истории. Минимизация данных должна учитываться вместе с полезностью отчёта. Иногда редактору достаточно недельной агрегированной активности, без долгой индивидуальной истории. Решение должно исходить из конкретного вопроса, а не желания собрать максимум.
D7 и неполная доступная история
Заменить единицу интервала на семь технически легко, но данные фикстуры не содержат восьмой день для первой когорты. Значение D7 поэтому нельзя получить как окончательный ноль. Отчёт должен сообщить недостаток периода наблюдения или исключить такую когорту из зрелого результата.
Если нужна таблица D1, D2 и D7, каждый столбец имеет собственную границу зрелости. Одна строка когорты может содержать подтверждённый D1 и ещё предварительный D7. Общая отметка «данные за октябрь» не объясняет это различие.
Также различайте возврат в конкретный день и возврат хотя бы однажды до дня семь. Второй показатель накопительный и обычно не уменьшается при расширении окна, первый описывает отдельный календарный день. Оба могут быть полезны, но должны иметь разные названия и собственные SQL-правила.
В следующем уроке подготовим агрегаты для частого чтения. Когорты показывают, что агрегирование нельзя выбирать только по скорости: если потерять дни отдельного браузера или первую наблюдаемую дату, некоторые вопросы уже невозможно восстановить из общего количества событий.