ProHoster > Blog > Administration > Wie man Benutzerkohorten in Grafana in Form von Diagrammen zusammenstellt [+Docker-Image mit Beispiel]

Wie man Benutzerkohorten in Grafana in Form von Diagrammen zusammenstellt [+Docker-Image mit Beispiel]

Wie man Benutzerkohorten in Grafana in Form von Diagrammen zusammenstellt [+Docker-Image mit Beispiel]

Wie wir das Problem der Visualisierung von Benutzerkohorten im Dienst Promopult mit Hilfe von Grafana gelöst haben.

Promopult — ein leistungsstarker Dienst mit einer großen Anzahl von Benutzern. Nach 10 Jahren Betrieb hat die Anzahl der Registrierungen im System die Millionengrenze ĂŒberschritten. Diejenigen, die mit Ă€hnlichen Diensten vertraut sind, wissen, dass diese große Nutzerbasis keineswegs homogen ist.

Einige haben sich registriert und "schliefen" fĂŒr immer ein. Einige haben ihr Passwort vergessen und sich in einem halben Jahr noch ein paar Mal registriert. Einige bringen Geld in die Kasse, wĂ€hrend andere nur nach kostenlosen Werkzeugen. Und es wĂ€re schön, von jedem einen gewissen Gewinn zu erzielen.

Bei so großen Datenmengen wie unseren macht es keinen Sinn, das Verhalten einzelner Nutzer zu analysieren und Mikroentscheidungen zu treffen. Aber Trends zu erkennen und mit großen Gruppen zu arbeiten - das kann und muss man tun. Genau das tun wir.

Kurze Zusammenfassung

  1. Was ist Kohortenanalyse und wozu dient sie?
  2. Wie man Kohorten nach dem Registrierungsmonat der Nutzer in SQL erstellt.
  3. Wie man Kohorten nach Grafana.

Wenn Sie bereits wissen, was Kohortenanalyse ist und wie man sie in SQL durchfĂŒhrt, springen Sie sofort zum letzten Abschnitt.

1. Was ist Kohortenanalyse und wozu dient sie?

Kohortenanalyse ist eine Methode, die auf dem Vergleich verschiedener Gruppen (Kohorten) von Nutzern basiert. In der Regel bilden wir Gruppen nach der Woche oder dem Monat, in dem der Nutzer den Dienst angefangen hat zu nutzen. Daraus wird die Lebensdauer des Nutzers berechnet, und das ist bereits ein Indikator, auf dessen Grundlage man ziemlich komplexe Analysen durchfĂŒhren kann. Zum Beispiel zu verstehen:

  • wie der Akquise-Kanal die Lebensdauer des Nutzers beeinflusst;
  • wie die Nutzung einer bestimmten Funktion oder Dienstleistung die Lebensdauer beeinflusst;
  • wie die EinfĂŒhrung des Features X die Lebensdauer im Vergleich zum Vorjahr beeinflusst hat.

2. Wie erstellt man Kohorten in SQL?

Die GrĂ¶ĂŸe des Artikels und der gesunde Menschenverstand erlauben es nicht, hier unsere echten Daten anzugeben – im Test-Dump sind die Statistiken fĂŒr anderthalb Jahre: 1200 Benutzer und 53.000 Transaktionen. Damit Sie mit diesen Daten experimentieren können, haben wir ein Docker-Image mit MySQL und Grafana vorbereitet, in dem Sie all dies selbst ausprobieren können. Der Link zu GitHub am Ende des Artikels.

Hier zeigen wir die Erstellung von Kohorten an einem vereinfachten Beispiel.

Angenommen, wir haben einen Dienst. Nutzer registrieren sich darin und geben Geld fĂŒr Dienstleistungen aus. Im Laufe der Zeit verlassen die Nutzer den Dienst. Wir möchten herausfinden, wie lange die Nutzer bleiben und wie viele von ihnen nach dem ersten und zweiten Monat der Nutzung abwandern.

Um diese Fragen zu beantworten, mĂŒssen wir Kohorten nach dem Registrierungsmonat erstellen. Die AktivitĂ€t messen wir anhand der Ausgaben in jedem Monat. Anstelle von Ausgaben können auch Bestellungen, Abonnements oder jede andere AktivitĂ€t, die zeitlich gebunden ist, verwendet werden.

Stammdaten

Die Beispiele sind in MySQL erstellt, aber fĂŒr andere DBMS sollte es keine wesentlichen Unterschiede geben.

Tabelle der Nutzer – users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabelle der Ausgaben – billing:

userId
Date
Summe

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

Wir wÀhlen alle Abhebungen der Nutzer und das Registrierungsdatum aus:

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

Ergebnis:

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

Wir erstellen die Kohorten nach Monaten, dafĂŒr wandeln wir alle Daten in Monate um:

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

Jetzt mĂŒssen wir wissen, wie viele Monate der Nutzer aktiv war – das ist die Differenz zwischen dem Monat der Abhebung und dem Monat der Registrierung. In MySQL gibt es die Funktion PERIOD_DIFF() – die Differenz zwischen zwei Monaten. Wir fĂŒgen PERIOD_DIFF() in die Abfrage ein:

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

Wir zĂ€hlen die aktivierten Nutzer in jedem Monat – Gruppierung der EintrĂ€ge nach BillingMonth, RegistrationMonth und 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

Ergebnis:

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

Im Januar, Februar und MĂ€rz gab es jeweils einen neuen Nutzer – MonthsDiff = 0. Ein Nutzer aus Januar war auch im Februar aktiv – RegistrationMonth = 2019-01, BillingMonth = 2019-02, ebenso war ein Nutzer aus Februar im MĂ€rz aktiv.

Bei großen Datenmengen sind Muster natĂŒrlich besser erkennbar.

Wie man Kohorten in Grafana ĂŒbertrĂ€gt

Wir haben gelernt, Kohorten zu bilden, aber wenn die Anzahl der EintrĂ€ge groß wird, wird die Analyse bereits schwer. Die EintrĂ€ge können in Excel exportiert und schöne Tabellen erstellt werden, aber das ist nicht unser Ansatz!

Kohorten können in Form eines interaktiven Diagramms in Grafana.

DafĂŒr fĂŒgen wir eine weitere Abfrage hinzu, um die Daten in ein fĂŒr Grafana geeignetes Format zu konvertieren:

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## alte Abfrage, die Kohorten zurĂŒckgibt
  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

Und wir exportieren die Daten nach Grafana.

Beispielgrafik aus Demo:

Wie man Benutzerkohorten in Grafana in Form von Diagrammen zusammenstellt [+Docker-Image mit Beispiel]

Zum Anfassen:

GitHub-Repository mit Beispiel — das ist ein Docker-Image mit MySQL und Grafana, das auf Ihrem Computer ausgefĂŒhrt werden kann. In der Datenbank sind bereits Demo-Daten fĂŒr anderthalb Jahre vorhanden, von Januar 2018 bis Juli 2019.

Wenn gewĂŒnscht, können Sie Ihre eigenen Daten in dieses Image hochladen.

P.S. Artikel ĂŒber kohortenbasierte Analysen in SQL:

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

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

Quelle: habr.com

60GB SSD 8Gb DDR4