Как да съберете потребителски кохорти под формата на графики в Grafana [+docker образ с пример]

Как решихме задачата за визуализация на потребителски когорти в услугата Promopult с помощта на 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

Смятаме активираните потребители всеки месец — групираме записите по 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.

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

Да я видим на живо:

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/

Източник: habr.com