Comment collecter des cohortes d'utilisateurs sous forme de graphiques dans Grafana [+ image docker avec un exemple]

Comment nous avons résolu la tâche de visualiser des cohortes d'utilisateurs dans le service Promopult en utilisant Grafana.

Promopult est un service puissant avec un grand nombre d'utilisateurs. En 10 ans de fonctionnement, le nombre d'inscriptions dans le système a dépassé le million. Ceux qui ont rencontré des services similaires savent que ce panel d'utilisateurs est loin d'être homogène.

Quelqu'un s'est inscrit et s'est «endormi» pour toujours. Quelqu'un a oublié le mot de passe et s'est enregistré plusieurs fois en six mois. Quelqu'un apporte de l'argent à la caisse et quelqu'un est venu chercher des outils gratuits. Et ce serait bien de tirer profit de tout le monde.

Sur des baies de données aussi volumineuses que la nôtre, cela n'a aucun sens d'analyser le comportement d'un utilisateur individuel et de prendre des micro-décisions. Mais saisir les tendances et travailler avec de grands groupes est possible et nécessaire. Ce que nous faisons, en fait.

Résumé

1. Qu'est-ce qu'une analyse de cohorte et pourquoi est-elle nécessaire?
2. Comment faire des cohortes par mois d'enregistrement d'utilisateur en SQL.
3. Comment transférer des cohortes à Grafana .

Si vous savez déjà ce qu'est l'analyse de cohorte et comment la faire en SQL, passez à la dernière section.

1. Qu'est-ce qu'une analyse de cohorte et pourquoi est-elle nécessaire

L'analyse de cohorte est une méthode basée sur une comparaison de différents groupes (cohortes) d'utilisateurs. Le plus souvent, nos groupes sont constitués par la semaine ou le mois au cours duquel l'utilisateur a commencé à utiliser le service. La durée de vie de l'utilisateur est calculée à partir d'ici, et c'est un indicateur sur la base duquel une analyse assez compliquée peut être effectuée. Par exemple, comprenez:

• Comment le canal attire-t-il sur la vie de l'utilisateur;
• comment l'utilisation d'une fonction ou d'un service affecte une vie;
• comment le lancement des fonctionnalités X a affecté la durée de vie par rapport à l'année dernière.

2. Comment faire des cohortes en SQL?

La taille de l'article et le bon sens ne nous permettent pas de fournir nos vraies données ici - dans le vidage de test, les statistiques pour un an et demi sont: 1 200 utilisateurs et 53 000 transactions. Pour que vous puissiez jouer avec ces données, nous avons préparé une image docker avec MySQL et Grafana, dans laquelle vous pouvez tout ressentir vous-même. Lien vers GitHub à la fin de l'article.

Et nous montrons ici la création de cohortes avec un exemple simplifié.

Supposons que nous ayons un service. Les utilisateurs s'y inscrivent et dépensent de l'argent pour des services. Au fil du temps, les utilisateurs tombent. Nous voulons savoir combien de temps les utilisateurs vivent et combien d'entre eux tombent après le 1er et le 2e mois d'utilisation du service.

Pour répondre à ces questions, nous devons construire des cohortes par mois d'inscription. L'activité sera mesurée par les dépenses de chaque mois. Au lieu des coûts, il peut y avoir des commandes, des frais mensuels ou toute autre activité limitée dans le temps.

Données source

Les exemples sont faits dans MySQL, mais pour le reste du SGBD, il ne devrait pas y avoir de différences significatives.

Tableau des utilisateurs - utilisateurs:

Tableau des dépenses - facturation:

Sélectionnez tous les frais d'utilisation et la date d'enregistrement:

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

Résultat:

Nous construisons des cohortes par mois, pour cela nous convertirons toutes les dates en mois:

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

Maintenant, nous devons savoir combien de mois l'utilisateur a été actif - c'est la différence entre le mois de débit et le mois d'enregistrement. MySQL a la fonction PERIOD_DIFF () - la différence entre deux mois. Ajoutez PERIOD_DIFF () à la demande:

 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 

Nous comptons les utilisateurs activés chaque mois - nous regroupons les enregistrements par BillingMonth, RegistrationMonth et MonthDiff:

 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 

Résultat:

En janvier, février et mars, un nouvel utilisateur est apparu - MonthDiff = 0. Un utilisateur de janvier était actif et en février RegistrationMonth = 2019-01, BillingMonth = 2019-02, et un utilisateur de février était actif en mars.

Sur un grand ensemble de données, les modèles sont naturellement mieux visibles.

Comment transférer des cohortes à Grafana

Nous avons appris à former des cohortes, mais quand il y a beaucoup d'enregistrements, il n'est plus facile de les analyser. Les enregistrements peuvent être exportés vers Excel et créer de magnifiques tableaux, mais ce n'est pas notre méthode!

Les cohortes peuvent être affichées sous forme de graphiques interactifs dans Grafana .

Pour ce faire, ajoutez une autre requête pour convertir les données dans un format adapté à 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 

Et téléchargez les données sur Grafana.

Exemple de graphique de la démo :

Touchez:

Le référentiel d'exemples GitHub est une image de docker avec MySQL et Grafana, qui peut être exécutée sur votre ordinateur. La base de données contient déjà des données de démonstration pour un an et demi, de janvier 2018 à juillet 2019.

Si vous le souhaitez, vous pouvez télécharger vos données sur cette image.

Articles PS sur l'analyse de cohorte en SQL:

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

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