Jak zbierać kohorty użytkowników w postaci wykresów w Grafana [+ obraz docker z przykładem]

Jak rozwiązaliśmy problem wizualizacji kohort użytkowników w serwisie Promopult z wykorzystaniem Grafana.

Promopult — potężny serwis z dużą liczbą użytkowników. Przez 10 lat działalności liczba rejestracji w systemie przekroczyła milion. Ci, którzy mieli do czynienia z podobnymi serwisami, wiedzą, że ten ogromny zbiór użytkowników nie jest jednorodny.

Niektórzy zarejestrowali się i „zapadli w sen” na wieki. Niektórzy zapomnieli hasło i zarejestrowali się jeszcze kilka razy w ciągu pół roku. Niektórzy przynoszą pieniądze do kasy, a inni przyszli po darmowe narzędzia. I dobrze byłoby, gdyby z każdego można było uzyskać jakiś zysk.

Na tak dużych zbiorach danych, jak nasze, analizowanie zachowania pojedynczego użytkownika i podejmowanie mikro-decyzji jest bezsensowne. Natomiast uchwycenie trendów i praca z dużymi grupami – jest możliwa i potrzebna. Co zresztą robimy.

Streszczenie

1. Czym jest analiza kohortowa i po co jest potrzebna.
2. Jak tworzyć kohorty według miesiąca rejestracji użytkowników w SQL.
3. Jak przenieść kohorty do Grafana.

Jeśli już wiesz, czym jest analiza kohortowa i jak ją przeprowadzić w SQL, przejdź od razu do ostatniej sekcji.

1. Czym jest analiza kohortowa i po co jest potrzebna

Analiza kohortowa to metoda oparta na porównywaniu różnych grup (kohort) użytkowników. Najczęściej grupy te formowane są według tygodnia lub miesiąca, w którym użytkownik zaczął korzystać z serwisu. Stąd oblicza się czas życia użytkownika, a to już wskaźnik, na podstawie którego można przeprowadzać dość skomplikowaną analizę. Na przykład zrozumieć:

• jak kanał pozyskania wpływa na czas życia użytkownika;
• jak korzystanie z jakiejkolwiek funkcji lub usługi wpływa na czas życia;
• jak wprowadzenie funkcji X wpłynęło na czas życia w porównaniu z ubiegłym rokiem.

2. Jak stworzyć kohorty w SQL?

Wielkość artykułu i zdrowy rozsądek nie pozwalają przytaczać tutaj naszych rzeczywistych danych – w testowym zbiorze danych statystyki za półtora roku: 1200 użytkowników i 53 000 transakcji. Abyście mogli pobawić się tymi danymi, przygotowaliśmy obraz dockerowy z MySQL i Grafana, w którym można to wszystko samodzielnie przetestować. Link do GitHub na końcu artykułu.

A tutaj pokażemy tworzenie kohort na uproszczonym przykładzie.

Załóżmy, że mamy usługę. Użytkownicy rejestrują się w niej i wydają pieniądze na usługi. Z czasem użytkownicy odpadają. Chcemy wiedzieć, jak długo żyją użytkownicy i ilu z nich rezygnuje po 1. i 2. miesiącu korzystania z usługi.

Aby odpowiedzieć na te pytania, musimy zbudować kohorty według miesiąca rejestracji. Aktywność będziemy mierzyć według wydatków w każdym miesiącu. Zamiast wydatków mogą to być zamówienia, abonamenty lub jakakolwiek inna aktywność związana z czasem.

Dane źródłowe

Przykłady zostały przygotowane w MySQL, ale dla innych systemów DB nie powinno być istotnych różnic.

Tabela użytkowników — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabela wydatków — 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

Wybieramy wszystkie obciążenia użytkowników oraz datę rejestracji:

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

Wynik:

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

Budujemy kohorty według miesięcy, w tym celu przekształcamy wszystkie daty na miesiące:

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

Teraz musimy wiedzieć, ile miesięcy użytkownik był aktywny — to różnica między miesiącem obciążenia a miesiącem rejestracji. W MySQL jest funkcja PERIOD_DIFF() — różnica między dwoma miesiącami. Dodajemy PERIOD_DIFF() do zapytania:

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

Liczymy aktywowanych użytkowników w każdym miesiącu — grupujemy zapisy według BillingMonth, RegistrationMonth i 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

Wynik:

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

W styczniu, lutym i marcu pojawił się po jednym nowym użytkowniku — MonthsDiff = 0. Jeden użytkownik stycznia był aktywny także w lutym — RegistrationMonth = 2019-01, BillingMonth = 2019-02, tak samo jeden użytkownik lutego był aktywny w marcu.

Na dużej próbce danych zjawiska są naturalnie lepiej widoczne.

Jak przenieść kohorty do Grafana

Nauczyliśmy się formować kohorty, ale gdy zapisów staje się dużo, analiza ich już nie jest prosta. Można eksportować dane do Excela i utworzyć estetyczne tabele, ale to nie nasza metoda!

Kohorty można przedstawić w postaci interaktywnego wykresu w Grafana.

Dodajemy jeszcze jedno zapytanie, aby przekształcić dane w odpowiedni format dla 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 (
  ## stare zapytanie, zwracające kohorty
  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

I eksportujemy dane do Grafana.

Przykład wykresu z demo:

Dotknij rękami:

Repozytorium GitHub z przykładem — to obraz docker z MySQL i Grafana, który można uruchomić na swoim komputerze. W bazie znajdują się już dane demo za półtora roku, od stycznia 2018 do lipca 2019.

W razie potrzeby można załadować własne dane do tego obrazu.

P.S. Artykuły o analizie kohortowej w SQL:

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

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

Źródło: habr.com