Comment créer des cohortes d'utilisateurs sous forme de graphiques dans Grafana [+image Docker avec exemple]

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

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

Certaines personnes se sont inscrites et sont restées inactives pour toujours. D'autres ont oublié leur mot de passe et se sont réinscrites plusieurs fois en six mois. Certains apportent des fonds à la caisse, tandis que d'autres viennent chercher des outils. Et il serait bon d'obtenir un certain profit de chacun.

Sur de tels volumes de données, comme les nôtres, analyser le comportement d'un utilisateur individuel et prendre des micro-décisions est futile. En revanche, détecter des tendances et travailler avec de grands groupes est possible et nécessaire. C'est ce que nous faisons, en fait.

Résumé

1. Qu'est-ce que l'analyse de cohortes et pourquoi est-elle nécessaire.
2. Comment créer des cohortes par mois d'inscription des utilisateurs sur SQL.
3. Comment transférer des cohortes dans Grafana.

Si vous savez déjà ce qu'est l'analyse de cohortes et comment la réaliser sur SQL, passez directement à la dernière section.

1. Qu'est-ce que l'analyse de cohortes et pourquoi est-elle nécessaire

L'analyse de cohortes est une méthode basée sur la comparaison de différents groupes (cohortes) d'utilisateurs. Le plus souvent, nous formons des groupes par semaine ou par mois, selon le moment où l'utilisateur a commencé à utiliser le service. À partir de cela, nous calculons la durée de vie de l'utilisateur, ce qui devient un indicateur sur la base duquel nous pouvons effectuer une analyse assez complexe. Par exemple, comprendre :

• comment le canal d'acquisition influence la durée de vie de l'utilisateur ;
• comment l'utilisation d'une fonctionnalité ou d'un service influence la durée de vie ;
• comment le lancement de la fonctionnalité X a influencé la durée de vie par rapport à l'année précédente.

2. Comment créer des cohortes sur SQL ?

La taille de l'article et le bon sens ne permettent pas de fournir ici nos données réelles — dans un échantillon de test, la statistique couvre un an et demi : 1200 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 explorer tout cela vous-même. Le lien vers GitHub est à la fin de l'article.

Et ici, nous allons montrer la création de cohortes avec un exemple simplifié.

Supposons que nous avons un service. Les utilisateurs s'y inscrivent et dépensent de l'argent pour des services. Avec le temps, les utilisateurs se désabonnent. Nous souhaitons savoir combien de temps les utilisateurs restent et combien d'entre eux se désabonnent après 1 et 2 mois d'utilisation du service.

Pour répondre à ces questions, nous devons construire des cohortes par mois d'inscription. Nous mesurerons l'activité en fonction des dépenses de chaque mois. Au lieu des dépenses, il peut s'agir de commandes, d'abonnements ou de toute autre activité liée au temps.

Données d'origine

Les exemples sont faits en MySQL, mais il ne devrait pas y avoir de différences significatives pour les autres SGBD.

Table des utilisateurs — users :

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Table des dépenses — 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

Nous sélectionnons tous les débits des utilisateurs et leur date d'inscription :

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

Résultat :

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

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

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

Nous devons maintenant savoir combien de mois l'utilisateur a été actif — c'est la différence entre le mois de débit et le mois d'inscription. MySQL dispose de la fonction PERIOD_DIFF() — la différence entre deux mois. Ajoutons PERIOD_DIFF() à la requête :

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

Nous comptons les utilisateurs activés chaque mois — nous groupons les enregistrements par BillingMonth, RegistrationMonth et 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

Résultat :

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

En janvier, février et mars, un nouvel utilisateur est apparu chaque mois — MonthsDiff = 0. Un utilisateur de janvier était actif en février — RegistrationMonth = 2019-01, BillingMonth = 2019-02, tout comme un utilisateur de février était actif en mars.

Avec un grand ensemble de données, les tendances sont bien sûr plus visibles.

Comment transférer les cohortes dans Grafana

Nous avons appris à former des cohortes, mais lorsque le nombre d'enregistrements devient important, leur analyse devient difficile. Les enregistrements peuvent être exportés vers Excel et de beaux tableaux peuvent être générés, mais ce n'est pas notre méthode !

Les cohortes peuvent être affichées sous forme de graphique interactif dans Grafana.

Pour cela, nous ajoutons une autre requête pour convertir les données au format approprié pour 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 (
  ## ancienne requête, retournant les cohortes
  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 nous exportons les données vers Grafana.

Exemple de graphique provenant de une démo:

Toucher de ses propres mains :

Dépôt GitHub avec exemple — c'est une image Docker avec MySQL et Grafana, que vous pouvez exécuter sur votre ordinateur. La base contient déjà des données de démonstration couvrant un an et demi, de janvier 2018 à juillet 2019.

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

P.S. Articles sur l'analyse de cohortes en SQL :

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

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

Source : habr.com