카테고리별 해결된 토픽 및 관리자 응답 통계

SaraDev · 4월 3, 2024, 10:36오후

다음 보고서들은 Discourse Solved 플러그인이 활성화되어 있어야 합니다.

다음 Data Explorer 쿼리는 사이트 내 각 범주(topic)에 있는 주제(topics)를 분석하도록 설계되었으며, 주제 해결 및 주제에 대한 모더레이터 응답과 관련된 지표를 중심으로 합니다. 이러한 쿼리는 지정된 날짜 범위 내에서 작동하며, 선택적으로 범주 이름으로 필터링할 수 있습니다. 이 쿼리는 모든 범주 내의 주제가 해결될 수 있다고 가정합니다.

관리자는 이러한 보고서를 사용하여 다양한 범주 내에서 주제가 얼마나 효과적으로 해결되고 있는지 이해하고, 모더레이터 응답 시간을 개선할 수 있는 영역을 식별할 수 있습니다. 상위 범주와 하위 범주 내의 주제 해결 동향을 이해함으로써, 관리자는 커뮤니티 참여도와 지원을 개선하기 위한 정보에 기반한 결정을 내릴 수 있습니다.

두 쿼리 모두 동일한 매개변수를 가지며, 매우 유사한 결과를 제공합니다.

매개변수

: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 = 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 -- Includes top-level categories as their own 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 -- Includes top-level categories as their own 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, -- Use the category itself if it has no 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

예시 결과

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	General Discussion	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 for 'accepted solution'
    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	General Discussion	Feedback	50	30	8.00	80.00	4.00	5.00	85.00

글		댓글	조회수
Solved and Unsolved Topic Stats with Date and Tag Parameters Data & reporting solved , sql-query	4	432	9월 19, 2025
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting solved , sql-query	0	223	4월 22, 2023
Topics solved by staff Data & reporting solved , sql-query	0	219	2월 12, 2018
Average Staff Response Times to Topics Data & reporting sql-query	3	278	5월 3, 2024
Topics solved by staff (lists topics) Data & reporting solved , sql-query	0	195	2월 12, 2018

카테고리별 해결된 토픽 및 관리자 응답 통계

범주별 해결된 주제 및 모더레이터 응답 통계

하위 범주별 해결된 주제 및 모더레이터 응답 통계

Related topics