Wie wir die Aufgabe gelöst haben, Kohorten von Benutzern im Promopult-Dienst mithilfe von Grafana zu visualisieren.
Promopult ist ein leistungsstarker Dienst mit einer großen Anzahl von Benutzern. In 10 Betriebsjahren hat die Anzahl der Registrierungen im System eine Million überschritten. Diejenigen, die auf ähnliche Dienste gestoßen sind, wissen, dass diese Gruppe von Benutzern alles andere als homogen ist.
Jemand hat sich registriert und ist für immer „eingeschlafen“. Jemand hat das Passwort vergessen und sich in sechs Monaten noch ein paar Mal registriert. Jemand bringt Geld zur Kasse, und jemand kam, um kostenlose Werkzeuge zu bekommen . Und es wäre schön, von allen etwas Profit zu machen.
Bei so großen Datenfeldern wie unserem macht es keinen Sinn, das Verhalten eines einzelnen Benutzers zu analysieren und Mikroentscheidungen zu treffen. Es ist jedoch möglich und notwendig, Trends zu erkennen und mit großen Gruppen zu arbeiten. Was wir tatsächlich tun.
Zusammenfassung
- Was ist Kohortenanalyse und warum wird sie benötigt?
- So erstellen Sie Kohorten nach Monat der Benutzerregistrierung in SQL.
- So übertragen Sie Kohorten nach Grafana .
Wenn Sie bereits wissen, was Kohortenanalyse ist und wie sie in SQL ausgeführt wird, fahren Sie mit dem letzten Abschnitt fort.
1. Was ist Kohortenanalyse und warum wird sie benötigt?
Die Kohortenanalyse ist eine Methode, die auf einem Vergleich verschiedener Benutzergruppen (Kohorten) basiert. In den meisten Fällen werden unsere Gruppen nach der Woche oder dem Monat gebildet, in der der Benutzer den Dienst verwendet hat. Hier wird die Lebensdauer des Benutzers berechnet. Dies ist ein Indikator, auf dessen Grundlage eine ziemlich komplizierte Analyse durchgeführt werden kann. Zum Beispiel verstehen:
- Wie zieht der Kanal das Leben des Benutzers an?
- wie sich die Nutzung einer Funktion oder eines Dienstes auf ein Leben auswirkt;
- Wie sich die Einführung von Features X auf die Lebensdauer im Vergleich zum Vorjahr ausgewirkt hat.
2. Wie erstelle ich Kohorten in SQL?
Die Größe des Artikels und der gesunde Menschenverstand erlauben es uns nicht, unsere realen Daten hier darzustellen - im Test-Dump sind die Statistiken für eineinhalb Jahre: 1200 Benutzer und 53.000 Transaktionen. Damit Sie mit diesen Daten spielen können, haben wir mit MySQL und Grafana ein Docker-Image erstellt, in dem Sie alles selbst fühlen können. Link zu GitHub am Ende des Artikels.
Und hier zeigen wir die Erstellung von Kohorten anhand eines vereinfachten Beispiels.
Angenommen, wir haben einen Service. Benutzer registrieren sich darin und geben Geld für Dienste aus. Im Laufe der Zeit fallen Benutzer ab. Wir möchten wissen, wie lange Benutzer leben und wie viele von ihnen nach dem 1. und 2. Monat der Nutzung des Dienstes abfallen.
Um diese Fragen zu beantworten, müssen wir Kohorten nach Monat der Registrierung erstellen. Die Aktivität wird an den Ausgaben in jedem Monat gemessen. Anstelle von Kosten können Bestellungen, eine monatliche Gebühr oder andere zeitgebundene Aktivitäten anfallen.
Ausgangsdaten
Die Beispiele stammen aus MySQL, aber für den Rest des DBMS sollten keine signifikanten Unterschiede bestehen.
Benutzertabelle - Benutzer:
Ausgabentabelle - Abrechnung:
Wählen Sie alle Nutzungsgebühren 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:
Wir bauen Kohorten nach Monaten, dafür konvertieren wir alle Daten in Monate:
DATE_FORMAT(Date, '%Y-%m')
Jetzt müssen wir wissen, wie viele Monate der Benutzer aktiv war - dies ist der Unterschied zwischen dem Monat der Belastung und dem Monat der Registrierung. MySQL hat die Funktion PERIOD_DIFF () - die Differenz zwischen zwei Monaten. Fügen Sie der Anforderung PERIOD_DIFF () hinzu:
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
Wir zählen die in jedem Monat aktivierten Benutzer - wir gruppieren Datensätze 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:
Im Januar, Februar und März erschien ein neuer Benutzer - MonthsDiff = 0. Ein Januar-Benutzer war aktiv und im Februar RegistrationMonth = 2019-01, BillingMonth = 2019-02 und ein Februar-Benutzer war im März aktiv.
Bei einem großen Datensatz sind Muster natürlich besser zu sehen.
So übertragen Sie Kohorten nach Grafana
Wir haben gelernt, wie man Kohorten bildet, aber wenn es viele Datensätze gibt, ist es nicht mehr einfach, sie zu analysieren. Datensätze können nach Excel exportiert werden und schöne Tabellen erstellen, aber dies ist nicht unsere Methode!
Kohorten können in Grafana als interaktive Grafiken angezeigt werden .
Fügen Sie dazu 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 (
Und laden Sie die Daten auf Grafana hoch.
Beispieldiagramm aus der Demo :
Berühren Sie:
Das GitHub-Beispiel-Repository ist ein Docker-Image mit MySQL und Grafana, das auf Ihrem Computer ausgeführt werden kann. Die Datenbank enthält bereits anderthalb Jahre lang Demodaten von Januar 2018 bis Juli 2019.
Wenn Sie möchten, können Sie Ihre Daten auf dieses Bild hochladen.
PS Artikel zur Kohortenanalyse in SQL:
https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/
https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/