Wie man Nutzerkohorten in Form von Grafiken in Grafana erstellt [+Docker-Image mit Beispiel]

Wie wir das Problem der visuellen Darstellung von Nutzerkohorten im Dienst Promopult mit Hilfe von Grafana gelöst haben.

Promopult — ein leistungsstarker Service mit einer großen Anzahl von Nutzern. In 10 Jahren sind die Registrierungen im System auf über eine Million gestiegen. Diejenigen, die mit ähnlichen Services in Berührung kamen, wissen, dass diese Nutzerbasis alles andere als homogen ist.

Einige haben sich registriert und sind für immer „eingeschlafen“. Manche haben ihr Passwort vergessen und sich in einem halben Jahr noch ein paar Mal registriert. Einige bringen Geld in die Kasse, während andere nach kostenlosen Werkzeugensuchen. Gut wäre es, von jedem einen gewissen Profit zu erzielen.

Bei so großen Datenmengen wie unseren ist es sinnlos, das Verhalten eines einzelnen Nutzers zu analysieren und Mikroentscheidungen zu treffen. Stattdessen ist es wichtig, Trends zu erkennen und mit großen Gruppen zu arbeiten — genau das tun wir.

Zusammenfassung

1. Was ist eine Kohortenanalyse und warum ist sie wichtig?
2. Wie man Kohorten nach Monat der Nutzerregistrierung in SQL erstellt.
3. Wie man Kohorten in Grafana.

Wenn Sie bereits wissen, was eine Kohortenanalyse ist und wie man sie in SQL umsetzt, gehen Sie direkt zum letzten Abschnitt.

1. Was ist eine Kohortenanalyse und warum ist sie wichtig?

Kohortenanalyse ist eine Methode, die auf dem Vergleich verschiedener Gruppen (Kohorten) von Nutzern basiert. In der Regel werden diese Gruppen nach der Woche oder dem Monat gebildet, in dem der Nutzer den Service begonnen hat. Daraus ergibt sich die Lebensdauer des Nutzers, die eine Kennzahl ist, auf deren Grundlage man relativ komplexe Analysen durchführen kann. Beispielsweise kann man verstehen:

• wie der Akquisekanal die Lebensdauer des Nutzers beeinflusst;
• wie die Nutzung einer bestimmten Funktion oder Dienstleistung die Lebensdauer beeinflusst;
• wie die Einführung der Funktion X die Lebensdauer im Vergleich zum Vorjahr beeinflusst hat.

2. Wie erstellt man Kohorten in SQL?

Der Umfang des Artikels und der gesunde Menschenverstand lassen es nicht zu, hier unsere echten Daten zu präsentieren – im Testdump sind die Statistiken über anderthalb Jahre: 1200 Nutzer 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 alles selbst ausprobieren können. Der Link zu GitHub befindet sich am Ende des Artikels.

Hier zeigen wir die Erstellung von Kohorten anhand eines vereinfachten Beispiels.

Angenommen, wir haben einen Service. Darin registrieren sich Benutzer und geben Geld für Dienstleistungen aus. Mit der Zeit fallen Benutzer ab. Wir möchten wissen, wie lange die Benutzer aktiv bleiben und wie viele nach dem ersten und zweiten Monat der Nutzung des Services abspringen.

Um diese Fragen zu beantworten, müssen wir Kohorten nach dem Registrierungsmonat erstellen. Die Aktivität messen wir an den Ausgaben in jedem Monat. Statt Ausgaben könnten auch Bestellungen, Abonnements oder jede andere zeitgebundene Aktivität betrachtet werden.

Rohdaten

Die Beispiele sind in MySQL erstellt, aber es sollten keine wesentlichen Unterschiede zu anderen DBMS bestehen.

Benutzertabelle — 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
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

Wir wählen alle Abbuchungen der Benutzer 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

Das 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

Die Kohorten erstellen wir nach Monaten, dazu wandeln wir alle Daten in Monate um:

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

Jetzt müssen wir wissen, wie viele Monate der Benutzer aktiv war – das ist die Differenz zwischen dem Abbuchungsmonat und dem Registrierungsmonat. In MySQL gibt es die Funktion PERIOD_DIFF() – die Differenz zwischen zwei Monaten. Wir fügen PERIOD_DIFF() zur Abfrage hinzu:

WÄHLEN
    b.userId,
    DATE_FORMAT(b.Date, '%Y-%m') AS Abrechnungsmonat,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS Registrierungsmonat,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonateDiff
VON billing AS b LINKS JOIN users AS u ON b.userId = u.userId

userId
Abrechnungsmonat
RegistrationDate
MonateDiff

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 Benutzer in jedem Monat — wir gruppieren die Datensätze nach Abrechnungsmonat, Registrierungsmonat und MonateDiff:

WÄHLEN
    COUNT(DISTINCT(b.userId)) AS BenutzerAnzahl,
    DATE_FORMAT(b.Date, '%Y-%m') AS Abrechnungsmonat,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS Registrierungsmonat,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonateDiff
VON billing AS b LINKS JOIN users AS u ON b.userId = u.userId
GRUPPIEREN NACH Abrechnungsmonat, Registrierungsmonat, MonateDiff

Das Ergebnis:

BenutzerAnzahl
Abrechnungsmonat
Registrierungsmonat
MonateDiff

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 Benutzer — MonateDiff = 0. Ein Benutzer aus Januar war auch im Februar aktiv — Registrierungsmonat = 2019-01, Abrechnungsmonat = 2019-02, und ebenso war ein Benutzer aus Februar im März aktiv.

In großen Datenmengen sind Muster natürlich besser sichtbar.

Wie man Kohorten in Grafana überträgt

Wir haben gelernt, Kohorten zu bilden, aber wenn die Datensätze zahlreich werden, ist ihre Analyse nicht mehr einfach. Man kann die Datensätze nach Excel exportieren und schöne Tabellen erstellen, aber das ist nicht unsere Methode!

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

Fügen wir einen weiteren Abfrage hinzu, um die Daten in ein für Grafana geeignetes Format zu bringen:

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 exportieren die Daten nach Grafana.

Beispielgrafik aus Demo:

Anfassen:

GitHub-Repository mit dem Beispiel — ist ein Docker-Image mit MySQL und Grafana, das Sie auf Ihrem Computer ausführen können. In der Datenbank sind bereits Demodaten für anderthalb Jahre, von Januar 2018 bis Juli 2019, enthalten.

Falls gewünscht, können Sie Ihre eigenen Daten in dieses Image hochladen.

P.S. Artikel über Kohortenanalyse 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