ProHoster > Blog > administrasie > Hoe om gebruikerskohorte as grafieke in Grafana te versamel [+ docker-beeld met voorbeeld]

Hoe om gebruikerskohorte as grafieke in Grafana te versamel [+ docker-beeld met voorbeeld]

Hoe om gebruikerskohorte as grafieke in Grafana te versamel [+ docker-beeld met voorbeeld]

Hoe ons die probleem opgelos het om groepe gebruikers in die Promopult-diens te visualiseer deur Grafana te gebruik.

bevorder - 'n kragtige diens met 'n groot aantal gebruikers. Oor die 10 jaar van werking het die aantal registrasies in die stelsel een miljoen oorskry. Diegene wat soortgelyke dienste teëgekom het, weet dat hierdie verskeidenheid gebruikers ver van homogeen is.

Iemand het ingeskryf en vir altyd “aan die slaap geraak”. Iemand het hul wagwoord vergeet en nog 'n paar keer in die loop van ses maande geregistreer. Iemand bring geld na die kasregister, en iemand het gekom vir freebies instrumente. En dit sal lekker wees om 'n bietjie wins van almal te kry.

Op sulke groot datastelle soos ons s'n is dit sinloos om die gedrag van 'n individuele gebruiker te ontleed en mikrobesluite te neem. Maar om tendense te vang en met groot groepe te werk is moontlik en nodig. Dit is presies wat ons doen.

opsomming

  1. Wat is kohortanalise en hoekom is dit nodig?
  2. Hoe om kohorte te maak volgens maand van gebruikerregistrasie in SQL.
  3. Hoe om kohorte oor te dra na grafana.

As jy reeds weet wat kohortanalise is en hoe om dit in SQL te doen, gaan reguit na die laaste afdeling.

1. Wat is kohortanalise en hoekom is dit nodig?

Kohortanalise is 'n metode wat gebaseer is op die vergelyking van verskillende groepe (kohorte) gebruikers. Dikwels word ons groepe gevorm deur die week of maand waarin die gebruiker die diens begin gebruik het. Van hier af word die gebruiker se leeftyd bereken, en dit is 'n aanwyser op grond waarvan 'n taamlik komplekse analise uitgevoer kan word. Verstaan ​​byvoorbeeld:

  • hoe beïnvloed die verkrygingskanaal die gebruiker se leeftyd;
  • hoe die gebruik van enige kenmerk of diens leeftyd beïnvloed;
  • Hoe die bekendstelling van kenmerk X die leeftyd beïnvloed het in vergelyking met verlede jaar.

2. Hoe om kohorte in SQL te maak?

Die grootte van die artikel en gesonde verstand laat ons nie toe om ons werklike data hier aan te bied nie - in die toetshoop, statistieke vir 'n jaar en 'n half: 1200 53 gebruikers en 000 XNUMX transaksies. Sodat jy met hierdie data kan speel, het ons 'n docker-beeld met MySQL en Grafana voorberei waarin jy dit alles self kan ervaar. Skakel na GitHub aan die einde van die artikel.

En hier sal ons die skepping van kohorte wys deur 'n vereenvoudigde voorbeeld te gebruik.

Kom ons neem aan ons het 'n diens. Gebruikers registreer daar en bestee geld aan dienste. Met verloop van tyd val gebruikers uit. Ons wil uitvind hoe lank gebruikers leef, en hoeveel van hulle val na die 1ste en 2de maand van die gebruik van die diens.

Om hierdie vrae te beantwoord, moet ons kohorte saamstel op grond van die maand van registrasie. Ons sal aktiwiteit volgens uitgawes in elke maand meet. In plaas van uitgawes kan daar bestellings, intekengelde of enige ander tydgebaseerde aktiwiteit wees.

Aanvanklike gegewens

Die voorbeelde is in MySQL gemaak, maar vir ander DBBS'e behoort daar geen beduidende verskille te wees nie.

Gebruikerstabel - gebruikers:

gebruiker-ID
Registrasiedatum

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kostetabel - fakturering:

gebruiker-ID
datum
Som

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

Kies alle gebruikerafskrywings en registrasiedatum:

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

Gevolg:

gebruiker-ID
datum
Registrasiedatum

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

Ons bou kohorte per maand; om dit te doen, skakel ons alle datums om in maande:

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

Nou moet ons weet hoeveel maande die gebruiker aktief was - dit is die verskil tussen die maand van debiet en die maand van registrasie. MySQL het 'n funksie PERIOD_DIFF() - die verskil tussen twee maande. Voeg PERIOD_DIFF() by die versoek:

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

gebruiker-ID
Rekeningmaand
Registrasiedatum
MaandeVerskil

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

Ons tel die gebruikers wat in elke maand geaktiveer is - ons groepeer die rekords volgens 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

Gevolg:

Gebruikerstelling
Rekeningmaand
Registrasie Maand
MaandeVerskil

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 Januarie, Februarie en Maart het een nuwe gebruiker elk verskyn - MonthsDiff = 0. Een Januarie-gebruiker was aktief in Februarie - RegistrationMonth = 2019-01, BillingMonth = 2019-02, en een Februarie-gebruiker was aktief in Maart.

Natuurlik is patrone beter sigbaar in 'n groot datastel.

Hoe om kohorte na Grafana oor te dra

Ons het geleer hoe om kohorte te vorm, maar wanneer daar baie rekords is, is dit nie meer maklik om dit te ontleed nie. Rekords kan na Excel uitgevoer word en in pragtige tabelle gevorm word, maar dit is nie ons metode nie!

Kohorte kan as 'n interaktiewe grafiek in getoon word grafana.

Om dit te doen, voeg ons nog 'n navraag by om die data te omskep in 'n formaat wat geskik is vir 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

En laai die data op na Grafana.

Voorbeeld grafiek van demo:

Hoe om gebruikerskohorte as grafieke in Grafana te versamel [+ docker-beeld met voorbeeld]

Raak met jou hande:

GitHub-bewaarplek met voorbeeld — это docker-образ с MySQL и Grafana, который можно запустить на своем компьютере. В базе уже есть демо-данные за полтора года, с января 2018 по июль 2019 года.

As jy wil, kan jy jou data in hierdie prent laai.

NS Artikels oor kohortanalise in SQL:

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

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

Bron: will.com

Voeg 'n opmerking