1 минута чтения
8 октября 2020 г.
Разница между Retention на основе 24-часовых окон и календарных дней
Вчера в Telegram мне написал читатель блога:
Допустим, сегодня понедельник, я сделал 187 установок, и хочу посмотреть Retention первого дня, в какой день недели это можно сделать?
Речь идет о посте про Retention. Дам некоторые пояснения на этот счёт. Retention можно считать как на основе календарных дней, так и на основе 24-часовых окон. Retention нулевого дня в данном случае будет понедельник, а первого дня — вторник. Но здесь есть небольшая загвоздка.
К примеру, если вы начали продвижение в понедельник 5 октября в 23:59, то все установки этой минуты через пару минут будут иметь Retention первого дня. Это проблема календарного исчисления. Для решения этой проблемы некоторые аналитики измеряют Retention не только по календарю, но и по 24-часовым окнам.
Приложим эту идею к вышеописанному случаю:
- Retention нулевых суток в таком случае – все инсталлы с 5 октября 23:59 по 6 октября 23:59
- Retention первого дня: с 6 октября 23:59 по 7 октября 23:59
- И так далее со сдвигом 24-часового окна.
Как рассчитать Retention на основе 24-часовых окон с использованием SQL?
Вспомним запрос из предыдущего поста в блоге. Там мы считали разницу в днях между датой установки и датой активности пользователя. Модифицируем запрос так, чтобы активность считалась в 24-часовых окнах: заменим расчёт datediff на расчёт 24-часовых окон, обновив строки, выделенные жирным
<code class="language-postgresql">
SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
ndv(user.id) AS ret_base
FROM USER
LEFT JOIN client_session cs ON cs.user_id=user.id
WHERE 1=1
AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
AND from_unixtime(user.installed_at)>=date_add(now(), -60)
AND from_unixtime(user.installed_at)<=date_add(now(), -31)
GROUP BY 1,2
</code>
Обновленный запрос:
<code class="language-postgresql">
SELECT
cohort.date_diff AS day_difference,
avg(reg.users) AS cohort_size,
avg(cohort.ret_base) AS retention_base,
avg(cohort.ret_base)/avg(reg.users)*100 AS retention_rate
FROM
(SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
ndv(user.id) AS users
FROM USER
WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
AND from_unixtime(user.installed_at)<=date_add(now(), -31)
GROUP BY 1) reg
LEFT JOIN
(SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
ndv(user.id) AS ret_base
FROM USER
LEFT JOIN client_session cs ON cs.user_id=user.id
WHERE 1=1
AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
AND from_unixtime(user.installed_at)>=date_add(now(), -60)
AND from_unixtime(user.installed_at)<=date_add(now(), -31)
GROUP BY 1,2
) cohort ON reg.reg_date=cohort.reg_date
GROUP BY 1
ORDER BY 1
</code>
Результат:
Сравним с результатам, полученным предыдущим скриптом:
Заметно, что в первые дни Retention, посчитанный методом 24-часового окна, несколько ниже.
[ Рекомендации ]
Читайте также
[ Связаться ]
Давайте раскроем потенциал вашего бизнеса вместе
Заполните форму на бесплатную консультацию
