Exigence complexe de rapport d'activité utilisateur

Communauté Données et rapports
1

Je cherche un moyen de faire en sorte que Discourse génère des rapports d’activité sous la forme d’un aperçu de toutes les activités des utilisateurs entre deux dates personnalisées chaque année (la fonctionnalité intégrée actuelle semble ne proposer que des plages prédéfinies, et uniquement par rapport à la date d’aujourd’hui), et d’exporter les résultats dans un fichier compatible Excel (actuellement, cela semble ne être possible que pour les rapports d’activité individuels).

  • Je voudrais également inclure les messages envoyés par e-mail et les réponses par e-mail (sans compter deux fois les mêmes messages lus ou publiés lors de l’utilisation du forum en ligne). Les rapports actuels semblent exclure les messages par e-mail.

  • Je souhaiterais filtrer ces résultats selon un champ de profil utilisateur personnalisé spécifique (un numéro d’adhésion unique).

  • Je voudrais exclure les résultats des utilisateurs dont les numéros d’adhésion ne se situent pas dans une plage numérique désignée.

  • Idéalement, je voudrais également pouvoir générer un score de points d’activité par utilisateur pour la période, pondéré en fonction des messages lus, des messages publiés et des messages ayant reçu des « j’aime ».

  • Les pondérations (multiplicateurs) pour chacune de ces variables d’activité doivent être ajustables et définies par l’administrateur. Les résultats doivent être arrondis à la baisse au multiple de 5 le plus proche et plafonnés à un nombre maximum de points prédéfini par l’administrateur.

  • Ce serait un plus d’avoir une ventilation par catégorie de sujet ou par tag pour chaque utilisateur.

  • Idéalement, ce rapport serait généré automatiquement et envoyé par e-mail à des moments désignés chaque année (ce serait la cerise sur le gâteau).

Quelle est la faisabilité de tout cela ?

Cela nécessiterait-il un nouveau plugin personnalisé, ou est-ce possible via une requête SQL élaborée dans le plugin Data Explorer actuel ?

Ou la démarche la plus judicieuse serait-elle de chercher une option d’exportation « tout » relativement simple, puis de traiter le reste dans Excel ?

2 « J'aime »
2

L’explorateur de données serait probablement la meilleure approche ici. Vous devrez peut-être créer des requêtes différentes pour chaque tâche (par exemple, la ventilation par utilisateur nécessiterait une requête distincte).

La seule chose que l’explorateur de données ne peut pas faire, c’est le point « généré automatiquement et envoyé par e-mail ». Si cela constitue une exigence, vous pourriez éventuellement le réaliser en appelant l’API de l’explorateur de données depuis un autre système.

3 « J'aime »
3

Merci David.
Il semble que je doive me familiariser avec les requêtes SQL.

Une requête peut-elle prendre en entrée les résultats filtrés d’une autre requête ? De plus, les interactions basées sur les listes d’e-mails peuvent-elles être incluses de manière certaine ? Est-ce stocké d’une manière différente ? J’étais inquiet à l’idée qu’elles aient été exclues des rapports d’activité standards pour une raison obscure mais insurmontable.

4

Non, vous devrez copier/coller des sections de la requête.

La table posts dans la base de données inclut un booléen via_email, donc oui, vous pouvez déterminer quels messages ont été créés par courriel :+1:

Cependant, Discourse n’inclut aucun traceur dans les courriels qu’il envoie, il n’existe donc aucun moyen de déterminer si une notification par courriel a été « lue » ou non.

5

La fonctionnalité de rapport d’activité utilisateur intégrée est-elle gérée par des requêtes SQL que je peux copier et modifier à partir de n’importe où, afin d’éviter de passer des semaines à réinventer la roue ?

6

Consultez-vous le rapport « utilisateurs actifs quotidiens » ? Il est généré avec cette logique, qui utilise ActiveRecord (donc pas de SQL brut). Cependant, cette logique peut tout de même constituer un point de départ utile.

7

Je vise un rapport d’activité par utilisateur, au format CSV, couvrant une période donnée. L’objectif final est d’attribuer à chaque utilisateur un score de points d’activité pour l’année (ou toute autre période) basé sur les messages reçus/lus en ligne ou par e-mail, les messages publiés en ligne ou par e-mail, avec une pondération plus élevée pour les messages ayant reçu des « likes ». Le rapport que je souhaiterais utiliser comme base est le premier que l’on voit en cliquant sur Admin/Utilisateurs, car il fait déjà beaucoup de ce que je recherche.

image
image868×321 44.3 KB

8

La logique du répertoire des utilisateurs se trouve ici : discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

9

Hmm, cela ne semble pas être une tâche triviale à reproduire en SQL (du moins pas pour moi), car je devrai apprendre suffisamment de SQL pour le réaliser à partir de zéro en même temps, s’il n’y a rien de suffisamment similaire que je puisse copier directement. La dernière fois que j’ai fait de la programmation remonte à plusieurs années, au lycée, quand le BASIC n’était pas encore aussi embarrassant à avouer.

1 « J'aime »
10

Oui, je pense que cela nécessitera une connaissance raisonnable du SQL pour être mis en œuvre. Si vous avez un budget pour ce travail, vous pourriez trouver quelqu’un pour vous aider dans le Marketplace.

11

Merci bien ! Je n’ai pas de budget (le forum génère très peu de revenus en dons, à peine suffisants pour couvrir les frais d’hébergement de base), mais il semble que je doive m’engager dans cette voie de toute façon.

1 « J'aime »
12

@Paul_King

Cette requête pourrait aider.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

13

Un grand merci ! J’ai essayé, mais je rencontre une erreur de syntaxe.

PG::SyntaxError: ERROR: erreur de syntaxe près de “WITH”
LIGNE 13 : WITH date_range AS (

(Apologies, j’ai envoyé un MP via ce fil avant de réaliser que vous et l’auteur original n’étiez qu’une seule et même personne !)

1 « J'aime »
14

Est-ce que cela vous dérange si je vous demande quelles valeurs vous utilisez pour les variables ?

Je viens d’extraire la requête et de l’exécuter sur un site de test avec les valeurs suivantes :

  • start_date : 2021-07-01
  • end_date : 2021-07-30
  • coverage : all
15

Bonjour, j’en ai essayé plusieurs, mais tous m’ont donné ce résultat – par exemple

image
image1428×491 21.7 KB

16

Désolé, je ne parviens pas à reproduire l’erreur.

Pourriez-vous coller la requête ici telle que vous l’avez ?

17 
SELECT 1-- couverture : 'semaine', 'tout' ou 'date'
-- [paramètres]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- texte :couverture = semaine

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "semaine"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.semaine, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.semaine, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS réponses, count(t.id) AS sujets, COALESCE(sum(p.like_count),0) AS likes_reçus
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.semaine
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_lus, COALESCE(sum(time_read),0) AS temps_de_lecture, COUNT(uv.*) AS visites
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.semaine
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :couverture::text = 'semaine' THEN ps.semaine::text
WHEN :couverture::text = 'tout' THEN '-1'
ELSE ps.date::text
END  AS période, sum(ps.réponses) AS réponses, sum(ps.sujets) AS sujets, sum(ps.likes_reçus) AS likes_reçus, sum(lg.likes) AS likes_donnés, COALESCE(sum(posts_read),0) AS posts_lus, COALESCE(sum(time_read),0) AS temps_de_lecture, SUM(visites) AS visites
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :couverture::text = 'semaine' THEN ps.semaine::text
WHEN :couverture::text = 'tout' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :couverture::text = 'semaine' THEN ps.semaine::text
WHEN :couverture::text = 'tout' THEN '-1'
ELSE ps.date::text
END
1 « J'aime »
18

Ah, je vois.

Le SELECT 1 au début ne fait pas partie de la requête et est à l’origine de votre problème. C’est un espace réservé qui apparaît lorsque vous créez une nouvelle requête dans l’Explorateur de données. Supprimez-le et tout devrait fonctionner.

-- coverage: 'week', 'all', ou 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
19

Merci @Grayden_Shand

L’erreur a disparu.

Si je peux encore te solliciter un peu, les totaux générés par cette requête incluent-ils les publications par e-mail et les réponses par e-mail pour les utilisateurs en mode liste d’e-mails ? Si non, comment puis-je les inclure ?

Comment puis-je également inclure la valeur d’un champ personnalisé du profil utilisateur à côté du nom de l’utilisateur ?

As-tu des conseils à offrir pour identifier le nom du champ concerné et mettre cela en œuvre ?

20

Oui, cela devrait être le cas. Comme David l’a mentionné, la table posts contient un champ booléen via_email. La requête actuelle ignore ce champ et compte toutes les publications, qu’elles aient été envoyées par e-mail ou non.

Il existe une table nommée user_custom_fields. Vous devrez effectuer une jointure sur cette table pour inclure un champ personnalisé spécifique.

Je le ferais probablement dans la sous-requête post_summary.

Par exemple :

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == \"VOTRE_NOM_DE_CHAMP_PERSONNALISÉ\") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

J’ai ajouté une colonne à la clause SELECT et ajouté une nouvelle clause JOIN pour la table user_custom_fields.

Notez que vous devrez remplacer \"VOTRE_NOM_DE_CHAMP_PERSONNALISÉ\" et LABEL_FOR_CUSTOM_FIELD.

Vous devrez ensuite également mettre à jour les colonnes que vous sélectionnez dans la requête finale.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

Voilà probablement comment je procéderais.

Bonne chance !