Когортный анализ в Redash

В одной из прошлых заметок мы рассматривали построение 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

Отлично, теперь нам доступны правильно посчитанные данные.