Hoe gebruikersgroepen als grafieken in Grafana te verzamelen [+docker-image met voorbeeld]

Hoe we het probleem van gebruikers cohortvisualisatie in de Promopult service hebben opgelost met behulp van Grafana.

Promopult — een krachtige service met een groot aantal gebruikers. In 10 jaar is het aantal registraties in het systeem de miljoen passeert. Degenen die ooit met dergelijke diensten te maken hebben gehad, weten dat deze gebruikersgroep verre van homogeen is.

Sommigen hebben zich geregistreerd en zijn voor altijd 'in de slaapmodus' gebleven. Anderen zijn hun wachtwoord vergeten en hebben zich in het afgelopen halfjaar nog een paar keer geregistreerd. Sommigen brengen geld in de kas, terwijl anderen zijn gekomen voor gratis tools.En het zou goed zijn om van iedereen enige winst te halen.

Bij zulke grote datamassas zoals die van ons is het zinloos om het gedrag van een enkele gebruiker te analyseren en micro-beslissingen te nemen. Het is echter wel mogelijk en noodzakelijk om trends te vangen en met grote groepen te werken. Dat doen we eigenlijk.

Korte samenvatting

1. Wat is cohortanalyse en waarom is het nodig.
2. Hoe je cohorts kunt maken op basis van de registratiedatum van gebruikers in SQL.
3. Hoe je cohorts kunt overzetten naar Grafana.

Als je al weet wat cohortanalyse is en hoe je het in SQL moet doen, ga dan meteen naar de laatste sectie.

1. Wat is cohortanalyse en waarom is het nodig

Cohortanalyse is een methode die is gebaseerd op de vergelijking van verschillende groepen (cohorten) gebruikers. Vaak worden deze groepen gevormd op basis van de week of maand waarin een gebruiker de service begon te gebruiken. Van daaruit wordt de levensduur van de gebruiker berekend, en dat is al een indicator waarop vrij complexe analyses kunnen worden uitgevoerd. Bijvoorbeeld begrijpen:

• hoe het aanwerkanal de levensduur van de gebruiker beïnvloedt;
• hoe het gebruik van een bepaalde functie of dienst de levensduur beïnvloedt;
• hoe de lancering van functie X de levensduur heeft beïnvloed in vergelijking met het vorige jaar.

2. Hoe maak je cohorts in SQL?

De omvang van het artikel en gezond verstand staan niet toe om hier onze echte gegevens te presenteren — de testdump bevat statistieken van anderhalf jaar: 1200 gebruikers en 53.000 transacties. Om je de mogelijkheid te geven met deze gegevens te spelen, hebben we een docker-image met MySQL en Grafana voorbereid, waar je zelf alles kunt verkennen. De link naar GitHub is aan het einde van het artikel.

Hier laten we de creatie van cohorts zien aan de hand van een vereenvoudigd voorbeeld.

Stel je voor dat we een service hebben. Gebruikers registreren zich en besteden geld aan diensten. Na verloop van tijd haken gebruikers af. We willen weten hoe lang gebruikers blijven en hoeveel van hen afhaakt na de 1e en 2e maand gebruik van de service.

Om deze vragen te beantwoorden, moeten we cohorts opbouwen op basis van de registratiemaand. We zullen de activiteit meten aan de hand van uitgaven in elke maand. In plaats van uitgaven kunnen het ook bestellingen, lidmaatschapskosten of andere tijdgebonden activiteiten zijn.

Brongegevens

De voorbeelden zijn gemaakt in MySQL, maar voor andere DBMS zouden er geen wezenlijke verschillen moeten zijn.

Tabel gebruikers — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabel uitgaven — 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

We selecteren alle uitgaven van gebruikers en de registratie datum:

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

Resultaat:

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

We bouwen cohorts op basis van maanden, hiervoor transformeren we alle data naar maanden:

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

Nu moeten we weten hoeveel maanden de gebruiker actief was — dit is het verschil tussen de maand van uitgave en de maand van registratie. In MySQL is er een functie PERIOD_DIFF() — het verschil tussen twee maanden. We voegen PERIOD_DIFF() toe aan de query:

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

We tellen het aantal geactiveerde gebruikers per maand — we groeperen records op BillingMonth, RegistrationMonth en 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

Resultaat:

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

In januari, februari en maart was er één nieuwe gebruiker per maand — MonthsDiff = 0. Een gebruiker uit januari was ook actief in februari — RegistrationMonth = 2019-01, BillingMonth = 2019-02, en ook de gebruiker uit februari was actief in maart.

Bij een grote dataset zijn patronen natuurlijk beter zichtbaar.

Hoe cohorts over te dragen naar Grafana

We hebben geleerd hoe we cohorts kunnen opbouwen, maar als er veel records zijn, is het moeilijk om ze te analyseren. Records kunnen in Excel worden geëxporteerd en mooie tabellen gemaakt, maar dat is niet onze methode!

Cohorts kunnen worden weergegeven in de vorm van een interactieve grafiek in Grafana.

Daarvoor voegen we nog een aanvraag toe om de gegevens om te zetten naar een formaat dat geschikt is voor 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 (
  ## oude aanvraag die cohorten teruggeeft
  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

En we exporteren de gegevens naar Grafana.

Voorbeeld van een grafiek uit demo:

Met de handen aanraaken:

GitHub-repository met voorbeeld — dit is een docker-image met MySQL en Grafana, dat je op je computer kunt draaien. De database bevat al demo-gegevens voor anderhalf jaar, van januari 2018 tot juli 2019.

Als je wilt, kun je je eigen gegevens in dit image uploaden.

P.S. Artikelen over cohortanalyse in SQL:

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

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

Bron: habr.com