ProHoster > Log > administrasjon > Hvordan samle brukerkohorter som grafer i Grafana [+ docker-bilde med eksempel]

Hvordan samle brukerkohorter som grafer i Grafana [+ docker-bilde med eksempel]

Hvordan samle brukerkohorter som grafer i Grafana [+ docker-bilde med eksempel]

Hvordan vi løste problemet med å visualisere kohorter av brukere i Promopult-tjenesten ved hjelp av Grafana.

reklamere - en kraftig tjeneste med et stort antall brukere. I løpet av de 10 driftsårene har antallet registreringer i systemet passert én million. De som har støtt på lignende tjenester vet at dette utvalget av brukere er langt fra homogent.

Noen meldte seg på og "sov" for alltid. Noen har glemt passordet sitt og registrerte seg et par ganger til i løpet av seks måneder. Noen tar med penger til kassen, og noen kom for å få gratis instrumenter. Og det ville vært fint å få litt overskudd fra alle.

På så store datasett som vårt er det meningsløst å analysere oppførselen til en individuell bruker og ta mikrobeslutninger. Men å fange opp trender og jobbe med store grupper er mulig og nødvendig. Det er akkurat det vi gjør.

sammendrag

  1. Hva er kohortanalyse og hvorfor er det nødvendig?
  2. Hvordan lage kohorter etter måned med brukerregistrering i SQL.
  3. Hvordan overføre årskull til grafana.

Hvis du allerede vet hva kohortanalyse er og hvordan du gjør det i SQL, hopper du rett til den siste delen.

1. Hva er kohortanalyse og hvorfor er det nødvendig?

Kohortanalyse er en metode basert på å sammenligne ulike grupper (kohorter) av brukere. Oftest er gruppene våre dannet av uken eller måneden der brukeren begynte å bruke tjenesten. Herfra beregnes brukerens levetid, og dette er en indikator som en ganske kompleks analyse kan utføres på grunnlag av. Forstå for eksempel:

  • hvordan påvirker anskaffelseskanalen brukerens levetid;
  • hvordan bruken av funksjoner eller tjenester påvirker levetiden;
  • Hvordan lanseringen av funksjon X påvirket levetiden sammenlignet med i fjor.

2. Hvordan lage kohorter i SQL?

Størrelsen på artikkelen og sunn fornuft tillater oss ikke å presentere våre virkelige data her – i testdumpen, statistikk for halvannet år: 1200 53 brukere og 000 XNUMX transaksjoner. For at du skal kunne leke med disse dataene, har vi utarbeidet et docker-bilde med MySQL og Grafana der du kan oppleve alt selv. Link til GitHub på slutten av artikkelen.

Og her vil vi vise opprettelsen av kohorter ved å bruke et forenklet eksempel.

La oss anta at vi har en tjeneste. Brukere registrerer seg der og bruker penger på tjenester. Over tid faller brukerne fra. Vi ønsker å finne ut hvor lenge brukere lever, og hvor mange av dem som faller fra etter 1. og 2. måned med bruk av tjenesten.

For å svare på disse spørsmålene må vi konstruere kohorter basert på registreringsmåneden. Vi vil måle aktivitet etter utgifter i hver måned. I stedet for utgifter kan det være bestillinger, abonnementsavgifter eller annen tidsbasert aktivitet.

Innledende data

Eksemplene er laget i MySQL, men for andre DBMS skal det ikke være noen vesentlige forskjeller.

Brukertabell - brukere:

bruker-ID
Registreringsdato

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kostnadstabell - fakturering:

bruker-ID
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

Velg alle brukeravskrivninger og registreringsdato:

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

Resultat:

bruker-ID
Dato
Registreringsdato

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 etter måned; for å gjøre dette konverterer vi alle datoer til måneder:

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

Nå må vi vite hvor mange måneder brukeren var aktiv - dette er forskjellen mellom debet- og registreringsmåneden. MySQL har en funksjon PERIOD_DIFF() - forskjellen mellom to måneder. Legg til PERIOD_DIFF() i forespørselen:

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

bruker-ID
Faktureringsmåned
Registreringsdato
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 teller brukerne som er aktivert i hver måned – vi grupperer postene etter 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:

Antall brukere
Faktureringsmåned
Registreringsmåned
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 mars dukket det opp én ny bruker hver – MonthsDiff = 0. Én januarbruker var aktiv i februar – RegistrationMonth = 2019-01, BillingMonth = 2019-02, og én februarbruker var aktiv i mars.

Naturligvis er mønstre bedre synlige i et stort datasett.

Hvordan overføre kohorter til Grafana

Vi har lært å danne kohorter, men når det er mange poster er det ikke lenger lett å analysere dem. Poster kan eksporteres til Excel og formes til vakre tabeller, men dette er ikke vår metode!

Kohorter kan vises som en interaktiv graf i grafana.

For å gjøre dette legger vi til en annen spørring for å konvertere dataene til et format som passer 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 (
  ## старый запрос, возвращающий когорты
  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

Og last opp dataene til Grafana.

Eksempelgraf fra demo:

Hvordan samle brukerkohorter som grafer i Grafana [+ docker-bilde med eksempel]

Berør med hendene:

GitHub-depot med eksempel er et docker-bilde med MySQL og Grafana som du kan kjøre på datamaskinen. Databasen inneholder allerede demodata for et og et halvt år, fra januar 2018 til juli 2019.

Hvis du ønsker det, kan du laste inn dataene dine i dette bildet.

PS Artikler om kohortanalyse i SQL:

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

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

Kilde: www.habr.com

Legg til en kommentar