Ceci est une version SQL d’un rapport d’analyse de cohorte pour l’activité des utilisateurs, à utiliser dans le plugin Data Explorer.
Ce rapport nécessite que le plugin Discourse Solved soit activé.
Ce rapport nécessite que le plugin Ce rapport d’analyse de cohorte fournit des informations sur l’engagement des utilisateurs en suivant l’activité des utilisateurs qui se sont inscrits depuis une date de début spécifiée. Il se concentre spécifiquement sur les utilisateurs qui atteignent ou dépassent certains seuils pour les publications créées et les solutions fournies chaque mois après leur inscription.
Ce rapport est précieux pour les administrateurs cherchant à comprendre l’efficacité de leur communauté à retenir et à engager les nouveaux utilisateurs au fil du temps, en particulier en ce qui concerne la publication et la fourniture de solutions aux sujets. Ce rapport peut également être utile pour évaluer la santé de la communauté et identifier l’efficacité des stratégies de croissance de la communauté.
Utilisateurs actifs par mois après inscription avec paramètres Min Posts + Solutions
--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_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
),
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
),
solutions_counts AS (
SELECT
p.user_id,
COUNT(p.user_id) as solutions_count,
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 discourse_solved_solved_topics dsst
INNER JOIN posts p ON p.id = dsst.answer_post_id
JOIN topics t ON t.id = p.topic_id
JOIN users u ON p.user_id = u.id
WHERE p.created_at >= u.created_at
GROUP BY months_after_registration, cohort, p.user_id
HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
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
),
active_users AS (
SELECT
sc.cohort,
sc.months_after_registration,
COUNT(DISTINCT ac.user_id) AS active_users
FROM solutions_counts sc
FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
AND sc.months_after_registration = ac.months_after_registration
AND sc.cohort = ac.cohort
GROUP BY sc.cohort, sc.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
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 "Joined In", -- Include the year in the Joined In column
users_signed_up AS "Users Signed Up",
MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort
Explication de la requête SQL
Paramètres
Ce rapport utilise trois paramètres :
start_date: La date à partir de laquelle commencer le suivi des nouvelles inscriptions d’utilisateurs.min_posts_per_month: Le nombre minimum de publications qu’un utilisateur doit effectuer par mois pour être considéré comme actif.min_solutions_per_month: Le nombre minimum de solutions (réponses acceptées) qu’un utilisateur doit fournir par mois pour être considéré comme actif.
Les utilisateurs doivent satisfaire aux exigences min_posts_per_month et min_solutions_per_month pour être considérés comme actifs pour le mois.
CTEs
La requête SQL fournie utilise plusieurs expressions de table communes (CTE) pour décomposer le processus de calcul des utilisateurs actifs par mois après l’inscription, en fonction des critères minimaux de publications et de solutions. Voici une explication de chaque CTE :
user_cohorts
Cette CTE identifie les cohortes d’utilisateurs en fonction de leur mois d’inscription. Pour chaque utilisateur, elle calcule le mois de son inscription (cohort) et compte le nombre total d’utilisateurs qui se sont inscrits le même mois. Cela permet de comprendre la taille initiale de chaque cohorte.
posts_activity
Cette CTE suit l’activité des utilisateurs en termes de publications effectuées après leur inscription. Pour chaque publication, elle calcule le nombre de mois écoulés depuis la date d’inscription de l’utilisateur (months_after_registration) et regroupe ces informations par cohorte d’inscription de l’utilisateur. Ceci est utilisé pour suivre l’activité des utilisateurs en termes de publication de contenu au fil du temps.
solutions_counts
Cette CTE se concentre sur le comptage des solutions (réponses acceptées) fournies par les utilisateurs. Elle filtre les publications marquées comme solutions et les compte pour chaque utilisateur, en s’assurant que seules les publications effectuées après l’inscription de l’utilisateur sont prises en compte. Elle calcule également le nombre de mois écoulés depuis l’inscription de l’utilisateur pour chaque solution. Les utilisateurs ne sont inclus dans ce décompte que s’ils atteignent ou dépassent le nombre minimum spécifié de solutions par mois.
activity_counts
Cette CTE agrège le nombre de publications effectuées par chaque utilisateur par mois après l’inscription. Elle regroupe les utilisateurs par leur cohorte d’inscription et le nombre de mois depuis l’inscription, puis compte le nombre de publications effectuées. Seuls les utilisateurs qui atteignent ou dépassent le nombre minimum spécifié de publications par mois sont inclus dans ce décompte.
active_users
Cette CTE combine les données de solutions_counts et activity_counts pour identifier les utilisateurs actifs, c’est-à-dire ceux qui répondent aux critères de publication et de solution. Elle compte les utilisateurs distincts qui sont actifs en fonction des critères définis pour les publications et les solutions, regroupés par leur cohorte d’inscription et le nombre de mois depuis l’inscription.
cohorts_series
Cette CTE génère une série de nombres de 0 à 11, représentant le nombre de mois après l’inscription. Ceci est utilisé pour s’assurer que le rapport final inclut des données pour chaque mois jusqu’à 12 mois, même s’il n’y a pas d’utilisateurs actifs certains mois.
cohorts
Cette CTE agrège les données de user_cohorts pour obtenir le nombre total d’utilisateurs inscrits dans chaque cohorte. Elle garantit que le rapport final inclut le nombre total d’utilisateurs inscrits pour chaque cohorte.
cross_join
Cette CTE effectue une jointure croisée entre la CTE cohorts et la CTE cohorts_series. Cela garantit que chaque cohorte est représentée pour chaque mois après l’inscription, facilitant le calcul des utilisateurs actifs pour chaque mois dans l’étape finale.
final_counts
Cette CTE combine toutes les CTE précédentes pour calculer les décomptes finaux d’utilisateurs actifs pour chaque mois après l’inscription, pour chaque cohorte. Elle utilise une jointure gauche pour faire correspondre les utilisateurs actifs de la CTE active_users avec les cohortes et les mois générés dans la CTE cross_join. Elle garantit que chaque paire cohorte-mois a un décompte d’utilisateurs actifs, avec une valeur par défaut de 0 s’il n’y a pas d’utilisateurs actifs pour cette paire.
SELECT final
La SELECT finale de la requête regroupe les utilisateurs en cohortes en fonction de leur mois et année d’inscription, puis calcule le nombre d’utilisateurs actifs pour chaque mois jusqu’à un an après l’inscription. Ceci est fait grâce à une combinaison de transformations et d’agrégations conditionnelles, qui transforment les données dans un format où chaque ligne correspond à une cohorte, et chaque colonne représente le nombre d’utilisateurs actifs pour chaque mois après l’inscription, de “Mois 1” à “Mois 12”.
Résultats
Le rapport génère un tableau avec les colonnes suivantes :
- Joined In : Le mois et l’année de la cohorte (quand les utilisateurs se sont inscrits).
- Users Signed Up : Le nombre total d’utilisateurs qui se sont inscrits dans cette cohorte.
- Month 1 à Month 12 : Le nombre d’utilisateurs actifs pour chaque mois après l’inscription, jusqu’à 12 mois.
Exemple de résultats
| Joined In | Users Signed Up | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jan 2023 | 50 | 40 | 8 | 4 | 3 | 3 | 3 | 4 | 3 | 2 | 1 | 1 | 4 |
| Feb 2023 | 63 | 40 | 7 | 5 | 3 | 2 | 2 | 7 | 2 | 2 | 2 | 1 | 1 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
Les résultats complets du rapport fourniront une année de données après la start_date.