Разница между Retention на основе 24-часовых окон и календарных дней

Thu, 08 Oct 2020 13:55:14 +0300

Вчера в 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-часовых окон, обновив строки, выделенные жирным


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

Обновленный запрос:

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

Результат:

Сравним с результатам, полученным предыдущим скриптом:

Заметно, что в первые дни Retention, посчитанный методом 24-часового окна, несколько ниже.

Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: retention

Разница между Retention на основе 24-часовых окон и календарных дней

Как рассчитать Retention на основе 24-часовых окон с использованием SQL?

Результат: