カテゴリ別解決済みトピックとモデレーター応答統計

SaraDev · 2024 年 4 月 3 日午後 10:36

以下のレポートには、Discourse Solved プラグインの有効化が必要です。

以下のデータエクスプローアクエリは、サイト内の各カテゴリのトピックを分析するために設計されており、トピックの解決やモデレーターのトピックへの対応に関する指標に焦点を当てています。これらは指定された日付範囲内で動作し、必要に応じてカテゴリ名でフィルタリングすることも可能です。これらのクエリは、すべてのカテゴリのトピックが解決可能であると仮定しています。

管理者はこれらのレポートを使用して、異なるカテゴリ内でトピックがどの程度効果的に解決されているかを理解し、モデレーターの対応時間を改善すべき領域を特定できます。親カテゴリとサブカテゴリにおけるトピック解決のダイナミクスを理解することで、管理者はコミュニティのエンゲージメントとサポートを向上させるための情報に基づいた意思決定を行うことができます。

両方のクエリは同じパラメータを持ち、非常に類似した結果を返します。

パラメータ

: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	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 -- 'accepted solution' の 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	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 sql-query , solved	4	346	2025 年 9 月 19 日
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting sql-query , solved	0	190	2023 年 4 月 22 日
Topics solved by staff Data & reporting sql-query , solved	0	201	2018 年 2 月 12 日
Average Staff Response Times to Topics Data & reporting sql-query	3	259	2024 年 5 月 3 日
Topics solved by staff (lists topics) Data & reporting sql-query , solved	0	171	2018 年 2 月 12 日

カテゴリ別 解決済みトピックとモデレーター応答統計

カテゴリ別解決済みトピックおよびモデレーター対応統計

サブカテゴリ別解決済みトピックおよびモデレーター対応統計

関連トピック

カテゴリ別解決済みトピックとモデレーター応答統計