Hvordan man samler brugergrupper i form af grafer i Grafana [+docker-billede med eksempel]

Hvordan vi løste opgaven med visualisering af brugerkohoer i Promopult-tjenesten ved hjælp af Grafana.

Promopult er en kraftfuld tjeneste med et stort antal brugere. I løbet af 10 års drift har antallet af registreringer i systemet overskredet en million. De, der har haft erfaring med lignende tjenester, ved, at denne store brugergruppe langt fra er homogen.

Nogle har registreret sig og "sover" for evigt. Nogle har glemt deres adgangskode og har registreret sig et par gange i løbet af et halve år. Nogle bringer penge ind i kassen, mens andre er kommet for de gratis værktøjer. Og det ville være godt at få en eller anden profit fra hver enkelt.

Ved så store datamængder som vores er det meningsløst at analysere adfærden hos den enkelte bruger og træffe mikrobeslutninger. Men at fange trends og arbejde med store grupper – det kan og skal man. Det er præcis, hvad vi gør.

Kort resumé

1. Hvad er kohorteanalyse, og hvorfor er det nødvendigt.
2. Hvordan man laver kohorter efter registreringsmåned for brugere i SQL.
3. Hvordan man overfører kohorter til Grafana.

Hvis du allerede ved, hvad kohorteanalyse er, og hvordan man laver det i SQL, kan du gå direkte til det sidste afsnit.

1. Hvad er kohorteanalyse, og hvorfor er det nødvendigt

Kohorteanalyse er en metode, der er baseret på at sammenligne forskellige grupper (kohorter) af brugere. Ofte bliver vores grupper dannet efter den uge eller måned, hvor brugeren begyndte at bruge tjenesten. Herfra beregnes brugerens livstid, hvilket er en indikator, som man kan lave ret kompleks analyse baseret på. For eksempel at forstå:

• hvordan kanalen for tiltrækning påvirker brugerens livstid;
• hvordan brugen af en bestemt funktion eller service påvirker livstiden;
• hvordan lanceringen af funktion X påvirkede livstiden sammenlignet med sidste år.

2. Hvordan man laver kohorter i SQL?

Artiklens størrelse og sund fornuft tillader ikke at inkludere vores reelle data her – i testdumpen er statistikken for halvandet år: 1200 brugere og 53.000 transaktioner. For at du kan lege med disse data, har vi forberedt et docker-billede med MySQL og Grafana, hvor du kan udforske det hele selv. Link til GitHub i slutningen af artiklen.

Her vil vi vise oprettelse af kohorter med et forenklet eksempel.

Lad os antage, at vi har en service. Brugerne registrerer sig og bruger penge på tjenester. Over tid falder brugerne fra. Vi ønsker at finde ud af, hvor længe brugerne er aktive, og hvor mange der falder fra efter 1. og 2. måned af brugen af servicen.

For at besvare disse spørgsmål skal vi opbygge kohorter baseret på registreringsmåneden. Aktiviteten måles ved udgifterne hver måned. I stedet for udgifter kan det være ordrer, abonnementer eller enhver anden aktivitet, der er tidsbundet.

Kildedata

Eksemplerne er lavet i MySQL, men for andre databaser bør der ikke være væsentlige forskelle.

Brugertabellen - users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Udgiftstabellen - billing:

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

Vi vælger alle brugerudgifter og registreringsdatoen:

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

Resultat:

userId
Dato
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 opbygger kohorter månedligt, derfor konverterer vi alle datoer til måneder:

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

Nu skal vi vide, hvor mange måneder brugeren har været aktiv - dette er forskellen mellem måneden for betaling og registreringsmåneden. MySQL har funktionen PERIOD_DIFF() - forskellen mellem to måneder. Vi tilføjer PERIOD_DIFF() til spørgsålet:

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

Vi tæller aktive brugere for hver måned - vi grupperer posterne efter BillingMonth, RegistrationMonth og 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

Resultat:

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 januar, februar og marts kom der én ny bruger i hver måned - MonthsDiff = 0. En bruger fra januar var aktiv også i februar - RegistrationMonth = 2019-01, BillingMonth = 2019-02, ligesom en bruger fra februar var aktiv i marts.

På store datamængder er mønstrene naturligvis lettere synlige.

Hvordan man overfører kohorter til Grafana

Vi har lært at danne kohorter, men når antallet af poster bliver stort, bliver det svært at analysere dem. Poster kan eksporteres til Excel og skabe flotte tabeller, men det er ikke vores metode!

Kohorter kan præsenteres som et interaktivt diagram i Grafana.

For this, we add another query to convert the data into a format suitable for 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 (
  ## old query returning cohorts
  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

And we export the data to Grafana.

Example chart from demo:

Feel it in your hands:

GitHub repository with the example — this is a docker image with MySQL and Grafana that you can run on your computer. The database already has demo data for a year and a half, from January 2018 to July 2019.

If desired, you can upload your data to this image.

P.S. Articles on cohort analysis in SQL:

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

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

Kilde: habr.com