Ce rapport Data Explorer fournit une analyse complète des sujets résolus et non résolus sur un site, dans une plage de dates spécifiée, et éventuellement filtrés par une balise spécifique.
Ce rapport nécessite que le plugin Discourse Solved soit activé.
Ce rapport nécessite que le plugin Ce rapport est particulièrement utile pour les administrateurs et les modérateurs qui cherchent à comprendre la réactivité de la communauté et à identifier les domaines à améliorer dans le support et l’engagement des utilisateurs.
Statistiques des sujets résolus et non résolus avec paramètres de date et de balise
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names, -- Agréger les balises pour chaque sujet
c.name AS category_name
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name
),
solved_topics AS (
SELECT
vt.id,
dsst.created_at
FROM discourse_solved_solved_topics dsst
INNER JOIN valid_topics vt ON vt.id = dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
vt.total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
Explication de la requête SQL
La génération du rapport passe par une requête SQL complexe qui utilise des expressions de table communes (CTE) pour organiser et traiter les données efficacement. La requête est structurée comme suit :
- valid_topics : cette CTE filtre les sujets par la plage de dates spécifiée et l’archétype (‘regular’), en excluant les sujets supprimés. Elle agrège également les balises associées à chaque sujet pour un filtrage ultérieur par nom de balise si spécifié.
- solved_topics : identifie les sujets qui ont été marqués comme résolus.
- last_reply : détermine l’utilisateur qui a effectué la dernière réponse sur chaque sujet en trouvant l’ID de publication maximum (indiquant la publication la plus récente) qui n’est pas supprimée et qui est de type de publication 1 (indiquant une publication régulière).
- first_reply : similaire à last_reply, mais identifie le premier utilisateur à répondre au sujet après la publication originale.
La requête principale combine ensuite ces CTE pour compiler un rapport détaillé sur chaque sujet, y compris s’il est résolu ou non, les noms des balises, le nom de la catégorie, les ID des sujets et des utilisateurs, les e-mails, les vues, le nombre de réponses et les délais pour la première réponse et la solution.
Paramètres
- start_date : le début de la plage de dates pour laquelle générer le rapport.
- end_date : la fin de la plage de dates pour laquelle générer le rapport.
- tag_name : la balise spécifique pour filtrer les sujets. Utilisez ‘all’ pour inclure les sujets avec n’importe quelle balise.
Résultats
Le rapport fournit les informations suivantes pour chaque sujet dans les paramètres spécifiés :
- status : indique si le sujet a été résolu ou reste non résolu.
- tag_names : affiche les balises associées au sujet.
- category_name : affiche la catégorie associée au sujet.
- topic_id : l’identifiant unique du sujet.
- topic_user_id : l’ID de l’utilisateur qui a créé le sujet.
- user_email : l’adresse e-mail du créateur du sujet.
- title : le titre du sujet.
- views : le nombre de vues que le sujet a reçues.
- last_reply_user_id : l’ID de l’utilisateur qui a effectué la dernière réponse sur le sujet.
- last_reply_user_email : l’adresse e-mail de l’utilisateur qui a effectué la dernière réponse.
- topic_create : la date de création du sujet.
- first_reply_create : la date de la première réponse au sujet.
- solution_create : la date à laquelle une solution a été marquée pour le sujet (le cas échéant).
- time_first_reply(days/hours) : le temps nécessaire pour recevoir la première réponse, en jours et en heures.
- time_solution(days/hours) : le temps nécessaire pour résoudre le sujet, en jours et en heures.
- created_at : la date de création du sujet.
- number_of_replies : le nombre total de réponses au sujet.
- total_days_without_solution : le nombre total de jours pendant lesquels le sujet a été actif sans solution.
Exemple de résultats
| status | tag_names | category_name | topic_id | topic_user_id | user_email | title | views | last_reply_user_id | last_reply_user_email | topic_create | first_reply_create | solution_create | time_first_reply(days) | time_first_reply(hours) | time_solution(days) | time_solution(hours) | created_at | number_of_replies | total_days_without_solution |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| solved | support, password | category1 | 101 | 1 | user1@example.com | How to reset my password? | 150 | 3 | user3@example.com | 2022-01-05 | 2022-01-06 | 2022-01-07 | 1 | 24 | 2 | 48 | 2022-01-05 | 5 | 2 |
| unsolved | support, account | category2 | 102 | 2 | user2@example.com | Issue with account activation | 75 | 4 | user4@example.com | 2022-02-10 | 2022-02-12 | 2 | 48 | 0 | 0 | 2022-02-10 | 3 | 412 | |
| solved | support | category3 | 103 | 5 | user5@example.com | Can’t upload profile picture | 200 | 6 | user6@example.com | 2022-03-15 | 2022-03-16 | 2022-03-18 | 1 | 24 | 3 | 72 | 2022-03-15 | 8 | 3 |
| unsolved | NULL | category4 | 104 | 7 | user7@example.com | Error when posting | 50 | 8 | user8@example.com | 2022-04-20 | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
