Как мы решили задачу визуализации когорт пользователей в сервисе Promopult c помощью Grafana.

Promopult ^[1] — мощный сервис с большим числом пользователей. За 10 лет работы число регистраций в системе перевалило за миллион. Те, кто сталкивался с подобными сервисами, знают, что этот массив юзеров далеко не однороден.

Кто-то зарегался и «уснул» навеки. Кто-то забыл пароль и зарегался еще пару раз за полгода. Кто-то несет деньги в кассу, а кто-то пришел за халявными инструментами ^[2]. И хорошо бы с каждого получить некий профит.

На таких больших массивах данных, как у нас, анализировать поведение отдельного юзера и принимать микро-решения бессмысленно. А вот отлавливать тренды и работать с большими группами — можно и нужно. Что мы, собственно, и делаем.

Краткое содержание

1. Что такое когортный анализ и зачем он нужен.
2. Как сделать когорты по месяцу регистрации пользователей на SQL.
3. Как перенести когорты в Grafana ^[3].

Если вы уже знаете, что такое когортный анализ, и как его сделать на SQL, сразу переходите к последнему разделу.

1. Что такое когортный анализ и зачем он нужен

Когортный анализ — это метод, основанный на сравнении разных групп (когорт) пользователей. Чаще всего у нас группы формируются по неделе или месяцу, в котором пользователь начал использовать сервис. Отсюда вычисляется время жизни пользователя, а это уже показатель, на основе которого можно проводить довольно сложный анализ. Например, понять:

• как влияет канал привлечения на время жизни пользователя;
• как использование какой-либо функции или услуги влияет на время жизни;
• как запуск фичи X повлиял на время жизни по сравнению с прошлым годом.

2. Как сделать когорты на SQL?

Размер статьи и здравый смысл не позволяют приводить здесь наши реальные данные — в тестовом дампе статистика за полтора года: 1200 пользователей и 53 000 транзакций. Чтобы вы могли поиграть с этими данными, мы подготовили docker-образ с MySQL и Grafana, в котором можно пощупать все это самому. Ссылка на GitHub в конце статьи.

А здесь мы покажем создание когорт на упрощенном примере.

Предположим, что у нас есть сервис. В нем регистрируются пользователи и тратят деньги на услуги. Со временем пользователи отваливаются. Мы хотим узнать, как долго живут пользователи, и сколько из них отваливается после 1-го и 2-го месяца использования сервиса.

Для ответа на эти вопросы нам нужно построить когорты по месяцу регистрации. Активность будем мерять по расходам в каждом месяце. Вместо расходов могут быть заказы, абонентская плата или любая другая активность, привязанная ко времени.

Исходные данные

Примеры сделаны в MySQL, но для остальных СУБД существенных отличий быть не должно.

Таблица пользователей — users:

Таблица расходов — billing:

Выбираем все списания пользователей и дату регистрации:

SELECT 
  b.userId, 
  b.Date,
  u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Результат:

Когорты строим по месяцам, для этого преобразуем все даты в месяцы:

DATE_FORMAT(Date, '%Y-%m')

Теперь нам нужно знать, сколько месяцев пользователь был активным — это разница между месяцем списания и месяцем регистрации. В MySQL есть функция PERIOD_DIFF() — разница между двумя месяцами. Добавляем PERIOD_DIFF() в запрос:

SELECT
    b.userId,
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Cчитаем активированных в каждом месяце пользователей — группируем записи по BillingMonth, RegistrationMonth и MonthsDiff:

SELECT
    COUNT(DISTINCT(b.userId)) AS UsersCount,
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
GROUP BY BillingMonth, RegistrationMonth, MonthsDiff

Результат:

В январе, феврале и марте появилось по одному новому пользователю — MonthsDiff = 0. Один пользователь января был активен и в феврале — RegistrationMonth = 2019-01, BillingMonth = 2019-02, так же и один пользователь февраля был активен в марте.

На большом массиве данных закономерности, естественно, видны лучше.

Как перенести когорты в Grafana

Когорты формировать мы научились, но когда записей становится много, анализировать их уже нелегко. Записи можно экспортировать в Excel и сформировать красивые таблицы, но это не наш метод!

Когорты можно показать в виде интерактивного графика в Grafana ^[3].

Для этого добавляем еще один запрос, чтобы преобразовать данные в подходящий для Grafana формат:

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## старый запрос, возвращающий когорты
  SELECT 
    COUNT(DISTINCT(b.userId)) AS Users, 
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
  FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
  WHERE
    u.RegistrationDate BETWEEN '2018-01-01' AND CURRENT_DATE
  GROUP BY 
    BillingMonth, RegistrationMonth, MonthsDiff 
) AS s
GROUP BY 
  time_sec, metric

И выгружаем данные в Grafana.

Пример графика из демо ^[4]:

Потрогать руками:

GitHub-репозитарий с примером ^[4] — это docker-образ с MySQL и Grafana, который можно запустить на своем компьютере. В базе уже есть демо-данные за полтора года, с января 2018 по июль 2019 года.

При желании можно загрузить свои данные в этот образ.

P.S. Статьи про когортный анализ на SQL:

https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/ ^[5]

https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/ ^[6]

Автор: PromoPult

Источник ^[7]