Rapport d'analyse de cohorte - Activité mensuelle des utilisateurs

[SaraDev (Sara Devlaeminck)]

Ceci est une version SQL d’un rapport d’analyse de cohorte pour l’activité des utilisateurs, à utiliser dans le plugin Data Explorer.

Le rapport d’analyse de cohorte est conçu pour fournir aux administrateurs des informations sur l’engagement des utilisateurs au fil du temps. En analysant l’activité des utilisateurs regroupés par leur mois d’inscription (cohortes), ce rapport suit le nombre d’utilisateurs actifs chaque mois après l’inscription qui répondent à des critères d’activité de publication minimum.

Ce rapport peut être une ressource précieuse pour comprendre la rétention des utilisateurs, les tendances d’engagement, évaluer la santé de la communauté et identifier l’efficacité des stratégies de croissance de la communauté.

Rapport d’analyse de cohorte - Utilisateurs actifs par mois

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1

WITH user_cohorts AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS cohort,
        COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
    FROM users
    WHERE created_at >= :start_date -- Utiliser le paramètre start_date pour filtrer les utilisateurs
),
posts_activity AS (
    SELECT
        p.user_id,
        EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
        DATE_TRUNC('month', u.created_at) AS cohort
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
),
activity_counts AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(user_id) AS posts_count,
        user_id
    FROM posts_activity
    GROUP BY cohort, months_after_registration, user_id
    HAVING COUNT(user_id) >= :min_posts_per_month -- Filtrer les utilisateurs par le nombre minimum de publications par mois
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, months_after_registration
),
cohorts_series AS (
    SELECT generate_series AS months_after_registration
    FROM generate_series(0, 11)
),
cohorts AS (
    SELECT
        cohort,
        MAX(users_signed_up) AS users_signed_up -- Agréger pour obtenir le nombre total d'utilisateurs inscrits pour chaque cohorte
    FROM user_cohorts
    GROUP BY cohort
),
cross_join AS (
    SELECT
        c.cohort,
        c.users_signed_up,
        cs.months_after_registration
    FROM cohorts c
    CROSS JOIN cohorts_series cs
),
final_counts AS (
    SELECT
        cj.cohort,
        cj.users_signed_up,
        cj.months_after_registration,
        COALESCE(au.active_users, 0) AS active_users
    FROM cross_join cj
    LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
    TO_CHAR(cohort, 'Mon YYYY') AS "Inscrit en", -- Inclure l'année dans la colonne "Inscrit en"
    users_signed_up AS "Utilisateurs inscrits",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Mois 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Mois 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Mois 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Mois 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Mois 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Mois 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Mois 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Mois 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Mois 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Mois 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Mois 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Mois 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

Explication de la requête SQL

Le rapport fonctionne en segmentant les utilisateurs en cohortes en fonction du mois où ils ont rejoint. Il suit ensuite ces cohortes pour voir combien d’utilisateurs restent actifs les mois suivants, sur la base d’un nombre minimum défini de publications par mois.

Paramètres

Ce rapport comporte deux paramètres :

• start_date : La date de début à partir de laquelle les utilisateurs sont pris en compte pour l’analyse de cohorte. Les utilisateurs qui ont rejoint après cette date sont inclus dans le rapport.
• min_posts_per_month : Le nombre minimum de publications qu’un utilisateur doit effectuer par mois pour être considéré comme actif pour ce mois.

CTEs

Le rapport d’analyse de cohorte utilise plusieurs expressions de table communes (CTE) pour organiser et traiter les données pour l’analyse. Chaque CTE sert un objectif spécifique dans la requête globale, en s’appuyant sur les précédentes pour produire finalement le rapport final. Voici une répartition du fonctionnement de chaque CTE :

1. user_cohorts

Cette CTE identifie les cohortes en fonction du mois où les utilisateurs ont rejoint. Pour chaque utilisateur, elle calcule la cohorte à laquelle il appartient en tronquant son horodatage created_at au mois près. Elle compte également le nombre d’utilisateurs qui se sont inscrits dans chaque cohorte.

• Opérations clés :
  • DATE_TRUNC('month', created_at) AS cohort : Tronque l’horodatage created_at à la granularité du mois, regroupant efficacement les utilisateurs par leur mois d’inscription.
  • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) : Compte le nombre d’utilisateurs dans chaque cohorte.

2. posts_activity

Cette CTE suit l’activité de publication des utilisateurs par rapport à leur date d’inscription. Elle joint les tables posts et users pour associer chaque publication à l’utilisateur qui l’a faite et calcule le nombre de mois écoulés depuis l’inscription de l’utilisateur au moment de chaque publication.

• Opérations clés :
  • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) : Calcule le nombre de mois écoulés depuis l’inscription de l’utilisateur pour chaque publication.
  • DATE_TRUNC('month', u.created_at) AS cohort : Identifie la cohorte de l’utilisateur en fonction de son mois d’inscription.

3. activity_counts

Cette CTE agrège l’activité de publication de posts_activity pour compter le nombre de publications effectuées par chaque utilisateur dans chaque mois après l’inscription. Elle filtre ces comptes pour inclure uniquement les utilisateurs qui répondent à l’activité de publication minimale spécifiée par le paramètre min_posts_per_month.

• Opérations clés :
  • GROUP BY cohort, months_after_registration, user_id : Regroupe les données par cohorte, mois après l’inscription et identifiant de l’utilisateur pour préparer le comptage des publications.
  • HAVING COUNT(user_id) >= :min_posts_per_month : Filtre les données groupées pour inclure uniquement les utilisateurs qui ont effectué au moins le nombre minimum de publications dans un mois.

4. active_users

Cette CTE agrège davantage les données de activity_counts pour compter le nombre d’utilisateurs actifs distincts dans chaque cohorte pour chaque mois après l’inscription.

• Opérations clés :
  • COUNT(DISTINCT user_id) AS active_users : Compte le nombre d’utilisateurs actifs uniques dans chaque cohorte pour chaque mois après l’inscription.

5. cohorts_series

Cette CTE génère une série d’entiers de 0 à 11, représentant les mois après l’inscription. Cette série est utilisée pour garantir que le rapport final inclut tous les mois jusqu’à 12 pour chaque cohorte, même s’il n’y a pas de données d’activité pour certains mois.

• Opérations clés :
  • generate_series(0, 11) : Génère une série d’entiers de 0 à 11.

6. cohorts

Cette CTE agrège les données de user_cohorts pour obtenir le nombre total d’utilisateurs inscrits pour chaque cohorte.

• Opérations clés :
  • MAX(users_signed_up) AS users_signed_up : Agrège le nombre total d’utilisateurs inscrits pour chaque cohorte.

7. cross_join

Cette CTE effectue une jointure croisée entre cohorts et cohorts_series pour créer une grille de toutes les combinaisons possibles de cohortes et de mois après l’inscription. Cela garantit que le rapport final inclut des lignes pour chaque mois pour chaque cohorte, facilitant le calcul des utilisateurs actifs par mois.

8. final_counts

Cette CTE combine les données de cross_join et active_users pour calculer le nombre final d’utilisateurs actifs pour chaque cohorte pour chaque mois après l’inscription. Elle utilise une jointure gauche pour s’assurer que toutes les combinaisons de cohortes et de mois sont incluses, même s’il n’y a pas d’utilisateurs actifs pour certaines.

• Opérations clés :
  • COALESCE(au.active_users, 0) AS active_users : Garantit que le rapport affiche 0 utilisateur actif pour les combinaisons sans aucune activité, au lieu de les laisser vides.

La SELECT finale en dehors des CTEs formate et présente ensuite ces données, montrant le nombre d’utilisateurs inscrits et le nombre d’utilisateurs actifs pour chaque mois après l’inscription pour chaque cohorte.

Résultats

Le rapport génère un tableau avec les colonnes suivantes :

• Inscrit en : Le mois et l’année de création de la cohorte, indiquant quand ces utilisateurs se sont inscrits.
• Utilisateurs inscrits : Le nombre total d’utilisateurs qui se sont inscrits dans cette cohorte.
• Mois 1 à Mois 12 : Chacune de ces colonnes représente le nombre d’utilisateurs actifs pour la cohorte dans chaque mois suivant l’inscription, jusqu’à 12 mois. Un utilisateur actif est défini comme une personne qui a effectué au moins le nombre minimum de publications spécifié par le paramètre min_posts_per_month.

Exemple de résultats

Inscrit en	Utilisateurs inscrits	Mois 1	Mois 2	Mois 3	Mois 4	Mois 5	Mois 6	Mois 7	Mois 8	Mois 9	Mois 10	Mois 11	Mois 12
Jan 2023	120	40	8	4	3	3	3	4	3	2	1	1	4
Fév 2023	119	40	7	5	3	2	2	7	2	2	2	1	1
…	…	…	…	…	…	…	…	…	…	…	…	…	…

Les résultats complets du rapport afficheront une année de données après la start_date.