Как собрать когорты пользователей в виде графиков в Grafana [+docker-образ с примером]

    Как собрать когорты пользователей в виде графиков в Grafana [+docker-образ с примером]

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


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


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


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


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


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

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


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


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


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

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


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


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


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


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


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


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


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


    userId RegistrationDate
    1 2019-01-01
    2 2019-02-01
    3 2019-02-10
    4 2019-03-01

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


    userId Date Sum
    1 2019-01-02 11
    1 2019-02-22 11
    2 2019-02-12 12
    3 2019-02-11 13
    3 2019-03-11 13
    4 2019-03-01 14
    4 2019-03-02 14

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


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

    Результат:


    userId Date RegistrationDate
    1 2019-01-02 2019-01-02
    1 2019-02-22 2019-01-02
    2 2019-02-12 2019-02-01
    3 2019-02-11 2019-02-10
    3 2019-03-11 2019-02-10
    4 2019-03-01 2019-03-01
    4 2019-03-02 2019-03-01

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


    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
    

    userId BillingMonth RegistrationDate MonthsDiff
    1 2019-01 2019-01 0
    1 2019-02 2019-01 1
    2 2019-02 2019-02 0
    3 2019-02 2019-02 0
    3 2019-03 2019-02 1
    4 2019-03 2019-03 0
    4 2019-03 2019-03 0

    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
    

    Результат:


    UsersCount BillingMonth RegistrationMonth MonthsDiff
    1 2019-01 2019-01 0
    1 2019-02 2019-01 1
    2 2019-02 2019-02 0
    1 2019-03 2019-02 1
    1 2019-03 2019-03 0

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


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


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


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


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


    Для этого добавляем еще один запрос, чтобы преобразовать данные в подходящий для 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.


    Пример графика из демо:


    Как собрать когорты пользователей в виде графиков в Grafana [+docker-образ с примером]


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


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


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


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


    https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/


    https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/

    • +11
    • 3,5k
    • 2
    PromoPult
    45,22
    Автоматизированная рекламная платформа
    Поделиться публикацией

    Комментарии 2

      0
      Классная статья спасибо.
      Например в продвижении мобильных приложений без такого анализа вообще никак.
        0
        Спасибо. Интересная статья. Кагортный анализ это действительно то что Must Have на сегодняшний день.

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

        Самое читаемое