Как да съберем кохорти потребители под формата на графики в 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