Как посчитать Retention?

В этой заметке разберём как правильно построить отчет по Retention с использованием Redash и языка SQL.

Для начала, в двух словах, что это за метрика Retention rate, почему она важна, какой бывает и каким образом считается.

Retention rate

Метрика Retention rate довольно широко распространена и особенно известна в мобильной индустрии, поскольку позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. Вспомним (или узнаем) как рассчитывается Retention:

Retention дня X — это N% процентов пользователей, которые вернутся к продукту в день X. Иными словами, если в какой-то конкретный день (день 0) пришло 100 новых пользователей, а на 1-ый день вернулось 15, то Retention 1-го дня составит 15 / 100 = 15%.

Чаще всего выделяют Retention дней 1, 3, 7 и 30 как наиболее описательные метрики продукта, однако полезно в целом рассматривать кривую Retention и делать выводы исходя из нее.

Кривая retention

В конечном итоге нас интересует построение такой кривой, которая показывает удержание пользователей с 0-го дня до 30-го.

Кривая Retention rate c 0-го по 30-ый день

Rolling Retention (RR)

Кроме классического Retention rate выделяют также Rolling Retention (далее RR). При расчете Rolling Retention помимо дня X учитываются также все последующие дни. Таким образом RR 1-го дня — количество пользователей, которые вернулись в 1-ый и последующие дни.

Сравним Retention и Rolling Retention 10-го дня:

Retention₁₀ — количество пользователей, вернувшихся в 10-ый день / количество пользователей, установивших приложение 10 дней назад * 100%.

Rolling Retention₁₀ — количество пользователей, вернувшихся в 10-ый день или позже / количество пользователей, установивших приложение 10 дней назад * 100%.

Гранулярность (retention временных отрезков)

В некоторых отраслях и соответствующих задачах полезно понимать Retention конкретного дня (чаще всего в мобильной индустрии), в других случаях полезно понимать удержание пользователя на разных временных интервалах: например, недельные отрезки или месячные (часто полезно в e-commerce, ретейле).

Пример когорт по месяцам и соответствующий им месячный Retention

Как построить Retention отчет на языке SQL?

Выше мы разобрали как посчитать Retention в формулах. Теперь приложим это к языку SQL.

Допустим, что у нас есть две таблицы: user — храним данные об идентификаторах пользователей и мета-информацию, client_session — информация о посещениях пользователями мобильного приложения.

В запросе будут фигурировать только две эти таблицы, поэтому вы с легкостью сможете адаптировать запрос под себя.
примечание: в рамках данного кода я использую Impala в качестве СУБД.

Собираем размер когорт

<pre class="sql">

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
</pre>

Считаем количество вернувшихся пользователей по каждой когорте

<pre class="sql">

SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,

          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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 datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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
</pre>

В этом запросе ключевая часть содержится в команде datediff: теперь мы считаем для каждой когорты и для каждого datediff количество уникальных пользователей все той же командой ndv() (фактически, количество пользователей которые вернулись в дни от 0-го до 30-го).

Отлично, теперь у нас есть размер когорт и количество вернувшихся пользователей.

Собираем все вместе

<pre class="sql">

SELECT reg.reg_date AS date_registration,

       reg.users AS cohort_size,

       cohort.date_diff AS day_difference,

       cohort.ret_base AS retention_base,

       cohort.ret_base/reg.users 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,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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 datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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
    ORDER BY 1,3
</pre>

Мы получили запрос, который для каждой когорты считает Retention, в итоге результат можно отобразить в таком виде:

Retention rate, посчитанный для каждой когорты пользователей

Построение единственной кривой Retention

Несколько модифицируем наш запрос и получим требуемые данные для построения одной кривой Retention:

<pre class="sql">

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,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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 datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) 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
</pre>

Теперь у нас для каждого дня посчитан средний по всем когортам Retention rate.

Больше по теме

- Как создавать продукты, формирующие привычки?

- Top 3 Ways To Calculate User Retention Rate With Formulas