Когортный анализ в Redash
⏱ Время чтения текста – 6 минутВ одной из прошлых заметок мы рассматривали построение Retention-отчета и в нем частично затрагивали понятие когорт.
Под когортой обычно подразумевают группу пользователей продукта или компании. Чаще всего группы выделяют на основе времени установки приложения / появления пользователя в системе.
Выходит, что используя когортный анализ, можно отследить как повлияли изменения в продукте на поведение пользователей (например, на старых и новых пользователей).
Вместе с этим когорты можно определить исходя и из других параметров: география пользователя, источник трафика, платформа устройства и другие важные параметры вашего продукта.
Мы разберемся с тем, как сравнить Retention пользователей недельных когорт в Redash, поскольку у Redash имеется специальный тип визуализации для построения такого отчета.
Определимся для начала c SQL-запросом. У нас как и прежде две таблицы — user (id пользователя и время установки приложения) и client_session — таймстемпы (created_at) активности каждого пользователя (user_id). Будем считать Retention первых семи дней для недельных когорт за последние 60 дней.
Запрос написан в Cloudera Impala, рассмотрим его.
Для начала построим общую численность когорт:
select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week,
ndv(distinct user.id) as cohort_size //считаем количество пользователей в когорте
from user
where from_unixtime(user.installed_at) between date_add(now(), -60) and now() //берем зарегистрированных за последние 60 дней
group by trunc(from_unixtime(user.installed_at), "WW")Вторая часть запроса поможет посчитать количество активных пользователей на каждый день в течение первых тридцати:
select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week,
datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
ndv(distinct user.id) as value //считаем количество активных пользователей на каждый день
from user
left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60) //берем сессии за последние 60 дней
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30 //отрезаем только первые 30 дней активности
group by 1,2Итого запрос целиком:
select size.cohort_week, size.cohort_size, ret.days, ret.value from
(select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week,
ndv(distinct user.id) as cohort_size
from user
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
group by trunc(from_unixtime(user.installed_at), "WW")) size
left join (select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week,
datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
ndv(distinct user.id) as value
from user
left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60)
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30
group by 1,2) ret on size.cohort_week=ret.cohort_weekОтлично, теперь нам доступны правильно посчитанные данные.
Создадим новую визуализацию в Redash и правильно укажем параметры:
Обязательно отметим, что у нас недельные когорты:
Вуа-ля, наша визуализация когорт готова:
Материалы по теме
