Bonjour ! Nous utilisons la version hébergée sur le cloud de Discourse et utilisons notre plateforme depuis environ 1,5 an. Nous aimerions extraire des métriques historiques sur l’adoption et l’utilisation de la plateforme. Nous examinons déjà le rapport « Inscriptions » disponible dans l’onglet Rapports de l’administrateur, mais nous souhaitons également pouvoir afficher le nombre total d’utilisateurs actifs chaque mois. Quelqu’un a-t-il une idée de la façon dont je pourrais faire cela ? Je n’arrive pas à trouver quoi que ce soit d’approprié dans Rapports ou Explorateur de données.
Cette requête d’exploration de données renverra les visites mensuelles uniques des utilisateurs. Est-ce ce que vous voulez ?
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins
WITH not_allowed_groups AS (
SELECT gu.user_id FROM group_users gu
INNER JOIN groups g ON (g.id = gu.group_id)
WHERE g.name IN (:group_name)
)
SELECT
to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
Merci beaucoup, mais pas exactement… Nous aimerions connaître le nombre d’utilisateurs actifs (inscrits et non désactivés ou supprimés) à la fin de chaque mois civil, qu’ils aient ou non visité la plateforme ce mois-là.
Nous examinons le graphique des inscriptions, mais la somme des inscriptions au cours d’un mois donné ne reflète pas notre base d’utilisateurs car chaque mois, nous avons également des désactivations automatiques dues à l’inactivité. Je me demande si la seule façon est d’accéder aux exportations de listes d’utilisateurs rétrospectives pour des dates spécifiques ? Mais comme nous sommes hébergés sur le Cloud, je ne sais pas si nous y avons accès. Devrions-nous faire une demande à Discourse ?
Je suis Discourse. 
Vous pouvez soit demander ici, soit nous envoyer un e-mail directement à team@discourse.org. Quelqu’un ayant de meilleures connaissances en SQL pourra vous aider.
4 « J'aime »
Je ne m’en étais pas rendu compte car je suis nouveau sur ce forum, merci ! Avez-vous par hasard des sauvegardes historiques de sites hébergés sur le cloud que vous pourriez fournir sur demande ?
1 « J'aime »
Nous conservons des sauvegardes à des fins de continuité des activités et elles peuvent être restaurées en cas d’urgence, mais pas vraiment pour d’autres raisons.
1 « J'aime »
Ok, j’ai une autre idée. J’ai demandé à notre assistant IA.
Requête SQL pour les utilisateurs actifs par mois IA
HAWK :
Écris-moi une requête d’explorateur de données qui renvoie une liste de comptes d’utilisateurs qui sont toujours actifs et qui ont été enregistrés au cours d’un mois donné.
DiscourseHelper :
Recherche
Recherche de : Requête d'explorateur de données Discourse pour trouver les utilisateurs actifs enregistrés au cours d'un mois spécifique
Lecture
Pour créer une requête d’explorateur de données qui renvoie une liste de comptes d’utilisateurs qui sont toujours actifs et qui ont été enregistrés au cours d’un mois spécifique, vous pouvez utiliser la requête SQL suivante. Cette requête suppose qu’un utilisateur actif est quelqu’un qui a publié ou répondu dans un délai donné.
--[params]
-- int :month = 5
-- int :year = 2023
SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
AND EXTRACT(MONTH FROM u.created_at) = :month
AND EXTRACT(YEAR FROM u.created_at) = :year
AND u.last_seen_at IS NOT NULL -- En supposant qu'actif signifie qu'ils ont accédé au site récemment
ORDER BY u.created_at
Explication de la requête :
:monthet:yearsont des paramètres que vous pouvez définir pour filtrer le mois et l’année spécifiques.u.active = TRUEgarantit que le compte d’utilisateur est toujours actif.EXTRACT(MONTH FROM u.created_at) = :monthetEXTRACT(YEAR FROM u.created_at) = :yearfiltrent la date d’enregistrement au mois et à l’année spécifiés.u.last_seen_at IS NOT NULLconsidère l’utilisateur comme actif s’il s’est connecté à tout moment.- Le résultat fournit l’ID de l’utilisateur, le nom d’utilisateur, la date d’enregistrement et la date de dernière connexion.
N’hésitez pas à ajuster les paramètres et les champs selon vos besoins. Faites-moi savoir si vous avez besoin d’aide supplémentaire !
N’hésitez pas à lui donner plus de contexte si je manque quelque chose.
3 « J'aime »
Ceci est une excellente explication de requête, merci ! Cependant, je ne suis pas sûr que ce soit ce que nous recherchons malheureusement.
Par exemple, si je définis les paramètres 01 et 2024, je regarde janvier de cette année.
Je comprends que le résultat inclura tous les utilisateurs qui se sont inscrits en janvier et qui sont toujours actifs aujourd’hui. Est-ce correct ?
Mais ce que nous voulons, c’est le nombre de personnes qui ont eu u.active=TRUE chaque mois depuis le lancement (pas maintenant). Ensuite, nous pourrons tracer et montrer comment notre base d’utilisateurs a changé de mois en mois. À la fin de chaque mois, pour nous, ce serait :
Utilisateurs actifs = Valeur du mois précédent + nouvelles inscriptions - nouvelles suppressions - nouvelles désactivations
Je ne vois rien concernant les désactivations ou les dates liées à la variable active dans l’Explorateur de données. Le chemin le plus probable serait alors d’utiliser les journaux d’actions du personnel et d’analyser par date, MAIS je ne pense pas que la fonction d’exportation fonctionne pour nous. Lorsque nous exportons les résultats du journal d’actions du personnel, nous obtenons étrangement une feuille de calcul avec le contenu de différents messages, pas un tableau d’actions du personnel. Si vous pouviez nous aider avec cela, ce serait peut-être une solution alternative ! Êtes-vous au courant que d’autres rencontrent le même problème ?
Une question intéressante.
J’ai d’abord examiné l’exemple ici. Mais cela ignore les utilisateurs supprimés. Vous obtenez uniquement le nombre d’utilisateurs qui étaient enregistrés à ce moment-là et qui le sont toujours, pas ceux qui ont été supprimés entre-temps.
Mon idée était donc de prendre l’ID de l’utilisateur qui s’est enregistré en dernier dans le mois. C’est le nombre maximum d’utilisateurs possibles à ce moment-là. Le nombre d’utilisateurs supprimés peut alors être soustrait de cela. Cependant, les comptes de bots (comme forum-helper) ont un ID négatif, mais sont comptés s’ils sont supprimés. (Mais c’est probablement un écart mineur). Ma requête était :
-- [params]
-- date :start_date
-- date :end_date
WITH month_dates AS (
-- Générer les dates de fin de mois entre la date de début et la date de fin
SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
-- Pour chaque date de fin de mois, trouver l'utilisateur le plus récent créé avant cette date
SELECT md.month_end,
(SELECT id
FROM users u
WHERE u.created_at < md.month_end
ORDER BY u.created_at DESC
LIMIT 1) AS user_max_id
FROM month_dates md
),
cumulative_deletion_count AS (
-- Calculer les suppressions cumulées jusqu'à chaque date de fin de mois
SELECT md.month_end,
(SELECT COUNT(*)
FROM user_histories uh
WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
FROM month_dates md
)
SELECT
md.month_end,
ru.user_max_id,
cdc.deletions_count,
ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end
Mais ce qu’il ne prend pas en compte, ce sont les (dés)activations, qui sont également stockées dans la table user_histories. Mais cela peut vous aider comme point de départ.
5 « J'aime »