В этой заметке разберём как правильно построить отчет по 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-го дня:
Retention10 — количество пользователей, вернувшихся в 10-ый день / количество пользователей, установивших приложение 10 дней назад * 100%.
Rolling Retention10 — количество пользователей, вернувшихся в 10-ый день или позже / количество пользователей, установивших приложение 10 дней назад * 100%.
Гранулярность (retention временных отрезков)
В некоторых отраслях и соответствующих задачах полезно понимать Retention конкретного дня (чаще всего в мобильной индустрии), в других случаях полезно понимать удержание пользователя на разных временных интервалах: например, недельные отрезки или месячные (часто полезно в e-commerce, ретейле).
Пример когорт по месяцам и соответствующий им месячный Retention
Как построить Retention отчет на языке SQL?
Выше мы разобрали как посчитать Retention в формулах. Теперь приложим это к языку SQL.
Допустим, что у нас есть две таблицы: user — храним данные об идентификаторах пользователей и мета-информацию, client_session — информация о посещениях пользователями мобильного приложения.
В запросе будут фигурировать только две эти таблицы, поэтому вы с легкостью сможете адаптировать запрос под себя.
примечание: в рамках данного кода я использую Impala в качестве СУБД.
Собираем размер когорт
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
Разберем этот довольно несложный запрос: по каждому дню мы считаем количество уникальных пользователей для отрезка [60 дней назад; 31 день назад].
Чтобы не лезть в документацию: команда ndv() в Impala аналог команды count(distinct).
Считаем количество вернувшихся пользователей по каждой когорте
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
В этом запросе ключевая часть содержится в команде datediff: теперь мы считаем для каждой когорты и для каждого datediff количество уникальных пользователей все той же командой ndv() (фактически, количество пользователей которые вернулись в дни от 0-го до 30-го).
Отлично, теперь у нас есть размер когорт и количество вернувшихся пользователей
Собираем все вместе
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
Мы получили запрос, который для каждой когорты считает Retention, в итоге результат можно отобразить в таком виде:
Retention rate, посчитанный для каждой когорты пользователей
Построение единственной кривой Retention
Несколько модифицируем наш запрос и получим требуемые данные для построения одной кривой Retention:
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
Теперь у нас для каждого дня посчитан средний по всем когортам Retention rate.
Больше по теме
.png)
.png)
