🥇Hur man samlar användarkohorter i form av diagram i Grafana [+docker-bild med exemplet]

Hur vi löste visualiseringen av användarkohorter i Promopult-tjänsten med hjälp av Grafana.

Promopult – en kraftfull tjänst med ett stort antal användare. Under 10 års tid har antalet registreringar i systemet överstigit en miljon. De som har stött på liknande tjänster vet att denna stora användarbas är långt ifrån homogen.

Vissa registrerade sig och "somnade" för alltid. Andra glömde sitt lösenord och registrerade sig några gånger under ett halvår. En del bidrar med pengar, medan andra kommer för gratistjänster. verktyg. Och det vore bra att få någon form av vinst från var och en.

På så stora datamängder som vi har blir det meningslöst att analysera beteendet hos enskilda användare och fatta mikrobeslut. Men att fånga trender och arbeta med stora grupper – det kan och bör vi göra. Vilket är precis vad vi gör.

Sammanfattning

Vad är kohortanalys och varför behövs den?
Hur man skapar kohorter baserat på registreringsmånaden av användare i SQL.
Hur man för över kohorter till Grafana.

Om du redan vet vad kohortanalys är och hur man gör det i SQL, kan du gå direkt till den sista sektionen.

1. Vad är kohortanalys och varför behövs den?

Kohortanalys är en metod baserad på att jämföra olika grupper (kohorter) av användare. Oftast bildas våra grupper utifrån den vecka eller månad då användaren började använda tjänsten. Därifrån beräknas användarens livslängd, vilket är en indikator som man kan utföra ganska komplex analys baserat på. Till exempel förstå:

hur kanalen för användarförvärv påverkar användarens livslängd;
hur användningen av en viss funktion eller tjänst påverkar livslängden;
hur lanseringen av funktion X påverkade livslängden jämfört med förra året.

2. Hur gör man kohorter i SQL?

Artikeln och sunt förnuft tillåter oss inte att presentera våra verkliga data här – i testdumpen finns statistik för ett och ett halvt år: 1200 användare och 53 000 transaktioner. För att du ska kunna experimentera med dessa data har vi förberett en Docker-image med MySQL och Grafana där du själv kan utforska allt detta. Länken till GitHub finns i slutet av artikeln.

Här kommer vi att visa hur man skapar kohorter med ett förenklat exempel.

Anta att vi har en tjänst. Användare registrerar sig och spenderar pengar på tjänster. Med tiden slutar användare att använda tjänsten. Vi vill ta reda på hur länge användare stannar och hur många som slutar efter den första och andra månaden av användning.

För att svara på dessa frågor behöver vi bygga kohorter baserat på registreringsmånad. Aktiviteten mäts genom utgifter varje månad. Istället för utgifter kan det vara beställningar, abonnemang eller någon annan aktivitet kopplad till tid.

Ursprungliga data

Exemplen är gjorda i MySQL, men det borde inte finnas några betydande skillnader för andra databashanteringssystem.

Användartabellen — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Utgiftstabellen — billing:

userId
Datum
Summa

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

Vi väljer alla uttag av användare och registreringsdatum:

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

Resultatet:

userId
Datum
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

Vi bygger kohorter per månad, och för detta omvandlar vi alla datum till månader:

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

Nu behöver vi veta hur många månader användaren har varit aktiv — detta är skillnaden mellan månaden för uttaget och månaden för registreringen. MySQL har funktionen PERIOD_DIFF() — skillnaden mellan två månader. Vi lägger till PERIOD_DIFF() i frågan:

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

Vi räknar aktiverade användare varje månad — grupperar poster efter BillingMonth, RegistrationMonth och 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

Resultatet:

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

I januari, februari och mars fick vi en ny användare vardera — MonthsDiff = 0. En användare från januari var aktiv även i februari — RegistrationMonth = 2019-01, BillingMonth = 2019-02, likaså var en användare från februari aktiv i mars.

Mönster är naturligtvis lättare att se på en stor datamängd.

Hur man överför kohorter till Grafana

Vi har lärt oss att forma kohorter, men när posterna blir många är det redan svårt att analysera dem. Poster kan exporteras till Excel och skapa vackra tabeller, men det är inte vår metod!

Kohorter kan visas i form av en interaktiv graf i Grafana.

För detta lägger vi till ytterligare en fråga för att konvertera data till ett format som passar Grafana:

VÄLJ
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FRÅN (
  ## gammal fråga som returnerar kohorter
  VÄLJ 
    COUNT(DISTINCT(b.userId)) AS Användare, 
    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
  FRÅN billing AS b VÄNSTER JOIN users AS u PÅ b.userId = u.userId
  VAR
    u.RegistrationDate MELLAN '2018-01-01' OCH CURRENT_DATE
  GRUPPERA EFTER 
    BillingMonth, RegistrationMonth, MonthsDiff 
) AS s
GRUPPERA EFTER 
  time_sec, metric

Och vi exporterar data till Grafana.

Exempel på diagram från demoversioner:

Att känna på det:

GitHub-repository med exemplet — är en docker-bild med MySQL och Grafana som kan köras på din dator. Databasen har redan demodata från ett och ett halvt år, från januari 2018 till juli 2019.

Om så önskas kan du ladda upp dina egna data i denna bild.

P.S. Artiklar om kohortanalys i SQL:

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

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

Källa: habr.com