Résolu : Statistiques des sujets et des réponses des modérateurs par catégorie

Communauté Données et rapports
,
1

:discourse: Les rapports suivants nécessitent que le plugin Discourse Solved soit activé.

Les requêtes Data Explorer suivantes sont conçues pour analyser les sujets au sein de chaque catégorie d’un site, en se concentrant sur les métriques liées à la résolution des sujets et aux réponses des modérateurs. Elles fonctionnent dans une plage de dates spécifiée et filtrent optionnellement par nom de catégorie. Ces requêtes supposent que les sujets de toutes les catégories peuvent être résolus.

Les administrateurs peuvent utiliser ces rapports pour comprendre dans quelle mesure les sujets sont résolus efficacement dans différentes catégories et identifier les domaines où les délais de réponse des modérateurs peuvent être améliorés. En comprenant la dynamique de résolution des sujets dans les catégories parentes et les sous-catégories, les administrateurs peuvent prendre des décisions éclairées pour améliorer l’engagement et le soutien de la communauté.

Les deux requêtes ont les mêmes paramètres et des résultats très similaires.

Paramètres

  • :start_date et :end_date : Définissent la plage de dates pour l’analyse.
  • :category_name : Filtre optionnellement les résultats vers une catégorie spécifique.

Résultats

  • date / date_range : La date spécifique ou le début de la période pour laquelle les données sont rapportées, et spécifie la granularité de la période couverte par chaque ligne de données (mois).
  • parent_category_name / category_name : Le nom de la catégorie ou de la sous-catégorie spécifique à laquelle les données se rapportent.
  • total_number_of_topics : Nombre total de sujets créés dans la catégorie ou la sous-catégorie spécifiée dans la plage de dates.
  • number_of_solved_topics : Nombre total de sujets marqués comme résolus dans la catégorie ou la sous-catégorie spécifiée et dans la plage de dates.
  • avg_time_to_solve_hours : Temps moyen pris pour marquer un sujet comme résolu, mesuré en heures.
  • percent_solved_under_24h : Pourcentage de sujets résolus dans les 24 heures suivant leur publication (exclut les sujets non résolus).
  • avg_solved_topics_moderator_time_to_first_response_hours : Temps moyen qu’il a fallu à un modérateur pour répondre aux sujets qui ont finalement été marqués comme résolus, mesuré en heures.
  • avg_time_to_first_response_hours : Temps moyen pris pour la première réponse publiée dans un sujet, mesuré en heures.
  • percent_all_topics_with_moderator_responses_under_24h : Pourcentage de tous les sujets ayant reçu une réponse d’un modérateur dans les 24 heures suivant leur publication.

Statistiques sur les sujets résolus et les réponses des modérateurs par catégorie

Cette requête regroupe les résultats par catégories principales ( parentes ).

--[params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01
-- null string :category_name

WITH solved_topics AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        DATE_TRUNC('month', t.created_at) AS month,
        MIN(p.created_at) FILTER (WHERE p.id = dsta.answer_post_id) AS solution_posted_at,
        MIN(p.created_at) FILTER (WHERE p.user_id = u.id AND u.moderator AND p.post_number > 1) AS first_moderator_response
    FROM topics t
    JOIN discourse_solved_solved_topics dsst ON dsst.topic_id = t.id
    JOIN discourse_solved_topic_answers dsta ON dsta.solved_topic_id = dsst.id
    JOIN posts p ON t.id = p.topic_id
    JOIN users u ON p.user_id = u.id
    WHERE t.archetype = 'regular'
    GROUP BY t.category_id, t.id, month
),
moderator_responses AS (
    SELECT
        topic_id,
        MIN(created_at) AS first_response
    FROM posts
    WHERE user_id IN (SELECT id FROM users WHERE moderator)
    AND post_number > 1
    GROUP BY topic_id
),
moderator_response_stats AS (
    SELECT
        pc.id AS parent_category_id,
        DATE_TRUNC('month', t.created_at) AS month,
        COUNT(*) FILTER (WHERE EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600 < 24) AS topics_with_first_response_under_24h,
        COUNT(*) AS total_topics
    FROM topics t
    JOIN categories c ON t.category_id = c.id
    LEFT JOIN categories pc ON c.parent_category_id = pc.id OR c.id = pc.id -- Inclut les catégories de premier niveau comme leurs propres parents
    LEFT JOIN moderator_responses mr ON t.id = mr.topic_id
    WHERE t.archetype = 'regular'
    AND t.created_at >= :start_date
    AND t.created_at <= :end_date
    GROUP BY pc.id, month
),
total_topics_per_category AS (
    SELECT
        pc.id AS parent_category_id,
        DATE_TRUNC('month', t.created_at) AS month,
        COUNT(*) AS total_number_of_topics
    FROM topics t
    JOIN categories c ON t.category_id = c.id
    LEFT JOIN categories pc ON c.parent_category_id = pc.id OR c.id = pc.id -- Inclut les catégories de premier niveau comme leurs propres parents
    WHERE t.archetype = 'regular'
    AND t.created_at >= :start_date
    AND t.created_at <= :end_date
    GROUP BY pc.id, month
),
category_hierarchy AS (
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        COALESCE(pc.id, c.id) AS parent_category_id, -- Utilise la catégorie elle-même si elle n'a pas de parent
        COALESCE(pc.name, c.name) AS parent_category_name
    FROM categories c
    LEFT JOIN categories pc ON c.parent_category_id = pc.id
),
solved_stats AS (
    SELECT
        st.month,
        ch.parent_category_id,
        COUNT(*) AS number_of_solved_topics,
        AVG(EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600) AS avg_time_to_solve_hours,
        AVG(CASE WHEN EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600 < 24 THEN 1 ELSE 0 END) AS percent_solved_under_24h,
        AVG(EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600) AS avg_time_to_first_response_hours,
        AVG(EXTRACT(EPOCH FROM (st.first_moderator_response - t.created_at))/3600) AS solved_topics_avg_time_to_first_response_hours,
        COALESCE(mrs.topics_with_first_response_under_24h::FLOAT / NULLIF(mrs.total_topics, 0), 0) AS percent_moderator_responses_under_24h
    FROM solved_topics st
    JOIN topics t ON st.topic_id = t.id
    JOIN category_hierarchy ch ON t.category_id = ch.category_id
    LEFT JOIN moderator_responses mr ON st.topic_id = mr.topic_id
    LEFT JOIN moderator_response_stats mrs ON ch.parent_category_id = mrs.parent_category_id AND DATE_TRUNC('month', t.created_at) = mrs.month
    WHERE t.created_at >= :start_date
    AND t.created_at <= :end_date
    GROUP BY st.month, ch.parent_category_id, mrs.topics_with_first_response_under_24h, mrs.total_topics
)
SELECT
    st.month::date AS date,
    'month' AS date_range,
    ch.parent_category_name,
    ttpc.total_number_of_topics,
    st.number_of_solved_topics,
    ROUND(st.avg_time_to_solve_hours::numeric,2) AS avg_time_to_solve_hours,
    ROUND((st.percent_solved_under_24h * 100)::numeric, 2) AS percent_solved_under_24h,
    ROUND(st.solved_topics_avg_time_to_first_response_hours::numeric,2) AS avg_solved_topics_moderator_time_to_first_response_hours,
    ROUND(st.avg_time_to_first_response_hours::numeric,2) AS avg_time_to_first_response_hours,
    ROUND((st.percent_moderator_responses_under_24h * 100)::numeric, 2) AS percent_all_topics_with_moderator_responses_under_24h
FROM solved_stats st
JOIN category_hierarchy ch ON st.parent_category_id = ch.parent_category_id
LEFT JOIN total_topics_per_category ttpc ON st.parent_category_id = ttpc.parent_category_id AND st.month = ttpc.month
WHERE (:category_name IS NULL OR ch.parent_category_name = :category_name)
GROUP BY date,st.month,ch.parent_category_name,ttpc.total_number_of_topics,st.number_of_solved_topics,st.avg_time_to_solve_hours,st.percent_solved_under_24h,st.solved_topics_avg_time_to_first_response_hours,st.avg_time_to_first_response_hours,st.percent_moderator_responses_under_24h
ORDER BY st.month, ch.parent_category_name

Résultats d’exemple

date date_range parent_category_name total_number_of_topics number_of_solved_topics avg_time_to_solve_hours percent_solved_under_24h avg_solved_topics_moderator_time_to_first_response_hours avg_time_to_first_response_hours percent_all_topics_with_moderator_responses_under_24h
2023-01-01 month Discussion générale 150 100 12.5 75.00 5.00 6.00 80.00

Statistiques sur les sujets résolus et les réponses des modérateurs par sous-catégorie

Cette requête regroupe les résultats par sous-catégories.

--[params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01
-- null string :category_name

WITH solved_topics AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        DATE_TRUNC('month', t.created_at) AS month,
        MIN(p.created_at) FILTER (WHERE p.id = ua.target_post_id) AS solution_posted_at,
        MIN(p.created_at) FILTER (WHERE p.user_id = u.id AND u.moderator AND p.post_number > 1) AS first_moderator_response
    FROM topics t
    JOIN posts p ON t.id = p.topic_id
    JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.target_post_id = p.id
    JOIN users u ON p.user_id = u.id
    WHERE t.archetype = 'regular'
    AND ua.action_type = 15 -- action_type pour « solution acceptée »
    GROUP BY t.category_id, t.id, month
),
moderator_responses AS (
    SELECT
        topic_id,
        MIN(created_at) AS first_response
    FROM posts
    WHERE user_id IN (SELECT id FROM users WHERE moderator)
    AND post_number > 1
    GROUP BY topic_id
),
moderator_response_stats AS (
    SELECT
        t.category_id,
        DATE_TRUNC('month', t.created_at) AS month,
        COUNT(*) FILTER (WHERE EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600 < 24) AS topics_with_first_response_under_24h,
        COUNT(*) AS total_topics
    FROM topics t
    LEFT JOIN moderator_responses mr ON t.id = mr.topic_id
    WHERE t.archetype = 'regular'
    AND t.created_at >= :start_date
    AND t.created_at <= :end_date
    GROUP BY t.category_id, month
),
total_topics_per_category AS (
    SELECT
        category_id,
        DATE_TRUNC('month', created_at) AS month,
        COUNT(*) AS total_number_of_topics
    FROM topics
    WHERE archetype = 'regular'
    AND created_at >= :start_date
    AND created_at <= :end_date
    GROUP BY category_id, month
),
solved_stats AS (
    SELECT
        st.month,
        st.category_id,
        COUNT(*) AS number_of_solved_topics,
        AVG(EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600) AS avg_time_to_solve_hours,
        AVG(CASE WHEN EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600 < 24 THEN 1 ELSE 0 END) AS percent_solved_under_24h,
        AVG(EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600) AS avg_time_to_first_response_hours,
        AVG(EXTRACT(EPOCH FROM (st.first_moderator_response - t.created_at))/3600) AS solved_topics_avg_time_to_first_response_hours,
        COALESCE(mrs.topics_with_first_response_under_24h::FLOAT / NULLIF(mrs.total_topics, 0), 0) AS percent_moderator_responses_under_24h
    FROM solved_topics st
    JOIN topics t ON st.topic_id = t.id
    LEFT JOIN moderator_responses mr ON st.topic_id = mr.topic_id
    LEFT JOIN moderator_response_stats mrs ON t.category_id = mrs.category_id AND DATE_TRUNC('month', t.created_at) = mrs.month
    WHERE t.created_at >= :start_date
    AND t.created_at <= :end_date
    GROUP BY st.month, st.category_id, mrs.topics_with_first_response_under_24h, mrs.total_topics
),
category_hierarchy AS (
    SELECT
        c.id AS category_id,
        c.name AS category_name,
        COALESCE(pc.name, c.name) AS parent_category_name
    FROM categories c
    LEFT JOIN categories pc ON c.parent_category_id = pc.id
)
SELECT
    st.month::date AS date,
    'month' AS date_range,
    ch.parent_category_name,
    ch.category_name,
    ttpc.total_number_of_topics,
    st.number_of_solved_topics,
    ROUND(st.avg_time_to_solve_hours::numeric,2) AS avg_time_to_solve_hours,
    ROUND((st.percent_solved_under_24h * 100)::numeric, 2) AS percent_solved_under_24h,
    ROUND(st.solved_topics_avg_time_to_first_response_hours::numeric,2) AS avg_solved_topics_moderator_time_to_first_response_hours,
    ROUND(st.avg_time_to_first_response_hours::numeric,2) AS avg_all_topics_moderator_time_to_first_response_hours,
    ROUND((st.percent_moderator_responses_under_24h * 100)::numeric, 2) AS percent_all_topics_with_moderator_responses_under_24h
FROM solved_stats st
JOIN category_hierarchy ch ON st.category_id = ch.category_id
LEFT JOIN total_topics_per_category ttpc ON st.category_id = ttpc.category_id AND st.month = ttpc.month
WHERE (:category_name IS NULL OR ch.parent_category_name = :category_name OR ch.category_name = :category_name)
ORDER BY st.month, ch.parent_category_name, ch.category_name

Résultats d’exemple

date date_range parent_category_name category_name total_number_of_topics number_of_solved_topics avg_time_to_solve_hours percent_solved_under_24h avg_solved_topics_moderator_time_to_first_response_hours avg_all_topics_moderator_time_to_first_response_hours percent_all_topics_with_moderator_responses_under_24h
2023-01-01 month Discussion générale Retour d’information 50 30 8.00 80.00 4.00 5.00 85.00
5 « J'aime »
Private topics not showing up in Posts created report