Gelöste Themen und Statistiken zu Moderationsantworten nach Kategorie

Gemeinschaft Daten & Berichterstattung
,
1

:discourse: Die folgenden Berichte erfordern das Aktivieren des Discourse Solved -Plugins.

Die folgenden Data Explorer-Abfragen sind so konzipiert, dass sie Themen in jeder Kategorie einer Website analysieren und sich auf Metriken im Zusammenhang mit dem Lösen von Themen und Moderatorantworten auf Themen konzentrieren. Sie arbeiten innerhalb eines angegebenen Datumsbereichs und filtern optional nach Kategorie-Namen. Diese Abfragen gehen davon aus, dass Themen in allen Kategorien gelöst werden können.

Administratoren können diese Berichte nutzen, um zu verstehen, wie effektiv Themen in verschiedenen Kategorien gelöst werden, und Bereiche identifizieren, in denen die Antwortzeiten der Moderatoren verbessert werden können. Durch das Verständnis der Dynamik der Themenlösung in übergeordneten Kategorien und Unterkategorien können Administratoren fundierte Entscheidungen treffen, um die Community-Interaktion und -Unterstützung zu verbessern.

Beide Abfragen haben dieselben Parameter und sehr ähnliche Ergebnisse.

Parameter

  • :start_date und :end_date: Definieren den Datumsbereich für die Analyse.
  • :category_name: Filtert die Ergebnisse optional nach einer bestimmten Kategorie.

Ergebnisse

  • date / date_range: Das spezifische Datum oder der Beginn des Zeitraums, für den die Daten berichtet werden, und gibt die Granularität des von jeder Datenzeile abgedeckten Zeitraums an (Monat).
  • parent_category_name / category_name: Der Name der Kategorie oder spezifischen Unterkategorie, auf die sich die Daten beziehen.
  • total_number_of_topics: Gesamtzahl der im angegebenen Zeitraum in der angegebenen Kategorie oder Unterkategorie erstellten Themen.
  • number_of_solved_topics: Gesamtzahl der Themen, die innerhalb des angegebenen Zeitraums und der angegebenen Kategorie oder Unterkategorie als gelöst markiert wurden.
  • avg_time_to_solve_hours: Durchschnittliche Zeit, die benötigt wurde, um ein gelöstes Thema als gelöst zu markieren, gemessen in Stunden.
  • percent_solved_under_24h: Prozentsatz der Themen, die innerhalb von 24 Stunden nach dem Erstellen gelöst wurden (Themen, die nicht gelöst wurden, sind ausgeschlossen).
  • avg_solved_topics_moderator_time_to_first_response_hours: Durchschnittliche Zeit, die ein Moderator benötigte, um auf Themen zu antworten, die schließlich als gelöst markiert wurden, gemessen in Stunden.
  • avg_time_to_first_response_hours: Durchschnittliche Zeit, die für die erste Antwort in einem Thema benötigt wurde, gemessen in Stunden.
  • percent_all_topics_with_moderator_responses_under_24h: Prozentsatz aller Themen, die innerhalb von 24 Stunden nach dem Erstellen eine Moderatorantwort erhalten haben.

Statistiken zu gelösten Themen und Moderatorantworten nach Kategorie

Diese Abfrage gruppiert die Ergebnisse nach Hauptkategorien (übergeordneten Kategorien).

--[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 = dst.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 dst ON dst.topic_id = t.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 -- Beinhaltet Top-Level-Kategorien als ihre eigenen Eltern
    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 -- Beinhaltet Top-Level-Kategorien als ihre eigenen Eltern
    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, -- Verwende die Kategorie selbst, wenn sie keine übergeordnete Kategorie hat
        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

Beispielergebnisse

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 Allgemeine Diskussion 150 100 12.5 75.00 5.00 6.00 80.00

Statistiken zu gelösten Themen und Moderatorantworten nach Unterkategorie

Diese Abfrage gruppiert die Ergebnisse nach Unterkategorien.

--[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 für 'angenommene Lösung'
    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

Beispielergebnisse

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 Allgemeine Diskussion Rückmeldungen 50 30 8.00 80.00 4.00 5.00 85.00
5 „Gefällt mir“
Private topics not showing up in Posts created report