Решенные темы и статистика ответов модераторов по категориям

Создание сообщества Данные и отчетность
,
1

:discourse: Следующие отчёты требуют включения плагина Discourse Solved.

Представленные ниже запросы Data Explorer предназначены для анализа тем в каждой категории сайта, с акцентом на метрики, связанные с решением тем и ответами модераторов. Они работают в заданном диапазоне дат и при необходимости фильтруются по названию категории. Эти запросы предполагают, что темы во всех категориях могут быть отмечены как решённые.

Администраторы могут использовать эти отчёты для оценки эффективности решения тем в различных категориях и выявления областей, где можно улучшить время реакции модераторов. Понимая динамику разрешения тем в родительских и дочерних категориях, администраторы могут принимать обоснованные решения для повышения вовлечённости сообщества и качества поддержки.

Оба запроса имеют одинаковые параметры и очень схожие результаты.

Параметры

  • :start_date и :end_date: Определяют диапазон дат для анализа.
  • :category_name: При необходимости фильтрует результаты по конкретной категории.

Результаты

  • 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: Процент тем, решённых в течение 24 часов после публикации (исключая темы, которые ещё не решены).
  • avg_solved_topics_moderator_time_to_first_response_hours: Среднее время до первого ответа модератора на темы, которые в итоге были отмечены как решённые, в часах.
  • avg_time_to_first_response_hours: Среднее время до появления первого ответа в теме, в часах.
  • percent_all_topics_with_moderator_responses_under_24h: Процент всех тем, получивших ответ модератора в течение 24 часов после публикации.

Статистика решённых тем и ответов модераторов по категориям

Этот запрос группирует результаты по основным (родительским) категориям.

--[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 -- Включает категории верхнего уровня как их собственных родителей
    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 -- Включает категории верхнего уровня как их собственных родителей
    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, -- Использует саму категорию, если у неё нет родителя
        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

Пример результатов

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 Общие обсуждения 150 100 12.5 75.00 5.00 6.00 80.00

Статистика решённых тем и ответов модераторов по подкатегориям

Этот запрос группирует результаты по подкатегориям.

--[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 для 'принятого решения'
    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

Пример результатов

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 Общие обсуждения Обратная связь 50 30 8.00 80.00 4.00 5.00 85.00
5 лайков
Private topics not showing up in Posts created report