このデータエクスプローラーレポートは、指定された期間内のサイトにおける解決済みおよび未解決のトピックに関する包括的な分析を提供し、オプションで特定のタグでフィルタリングできます。
このレポートには、Discourse Solved プラグインが有効になっている必要があります。
このレポートには、このレポートは、コミュニティの応答性を理解し、ユーザーサポートとエンゲージメントの改善点を見つけたい管理者やモデレーターにとって特に役立ちます。
日付とタグのパラメータを持つ解決済みおよび未解決のトピック統計
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names, -- Aggregate tags for each topic
c.name AS category_name
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name
),
solved_topics AS (
SELECT
vt.id,
dsst.created_at
FROM discourse_solved_solved_topics dsst
INNER JOIN valid_topics vt ON vt.id = dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
vt.total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
SQLクエリの説明
レポートは、共通テーブル式（CTE）を使用してデータを効率的に整理および処理する複雑なSQLクエリを通じて生成されます。クエリは次のように構成されています。
- valid_topics: このCTEは、指定された日付範囲とアーキタイプ（「regular」）でトピックをフィルタリングし、削除されたトピックを除外します。また、後でタグ名でフィルタリングできるように、各トピックに関連付けられたタグを集約します。
- solved_topics: 解決済みとしてマークされたトピックを識別します。
- last_reply: 最大の投稿ID（最新の投稿を示す）で削除されていない、投稿タイプ1（通常の投稿を示す）の投稿を見つけることにより、各トピックでの最後の返信を行ったユーザーを決定します。
- first_reply: last_replyと同様ですが、元の投稿の後にトピックに最初に返信したユーザーを特定します。
メインクエリは、これらのCTEを組み合わせて、各トピックの詳細なレポートをコンパイルします。これには、解決済みか未解決か、タグ名、カテゴリ名、トピックとユーザーID、メールアドレス、ビュー数、返信数、最初の返信と解決のタイミングが含まれます。
パラメータ
- start_date: レポートを生成する日付範囲の開始日。
- end_date: レポートを生成する日付範囲の終了日。
- tag_name: トピックをフィルタリングする特定のタグ。任意のタグを持つトピックを含めるには、「all」を使用します。
結果
レポートは、指定されたパラメータ内の各トピックについて、次の情報を提供します。
- status: トピックが解決済みか、未解決のままであるかを示します。
- tag_names: トピックに関連付けられたタグを表示します。
- category_name: トピックに関連付けられたカテゴリを表示します。
- topic_id: トピックの一意の識別子。
- topic_user_id: トピックを作成したユーザーのID。
- user_email: トピック作成者のメールアドレス。
- title: トピックのタイトル。
- views: トピックが表示された回数。
- last_reply_user_id: トピックに最後の返信を行ったユーザーのID。
- last_reply_user_email: 最後の返信を行ったユーザーのメールアドレス。
- topic_create: トピックが作成された日付。
- first_reply_create: トピックへの最初の返信の日付。
- solution_create: トピックの解決策がマークされた日付（該当する場合）。
- time_first_reply(days/hours): 最初の返信を受け取るまでにかかった時間（日数と時間）。
- time_solution(days/hours): トピックが解決されるまでにかかった時間（日数と時間）。
- created_at: トピックの作成日。
- number_of_replies: トピックへの合計返信数。
- total_days_without_solution: トピックが解決策なしでアクティブであった合計日数。
結果例
| status | tag_names | category_name | topic_id | topic_user_id | user_email | title | views | last_reply_user_id | last_reply_user_email | topic_create | first_reply_create | solution_create | time_first_reply(days) | time_first_reply(hours) | time_solution(days) | time_solution(hours) | created_at | number_of_replies | total_days_without_solution |
|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|—|
| solved | support, password | category1 | 101 | 1 | user1@example.com | How to reset my password? | 150 | 3 | user3@example.com | 2022-01-05 | 2022-01-06 | 2022-01-07 | 1 | 24 | 2 | 48 | 2022-01-05 | 5 | 2 |
| unsolved | support, account | category2 | 102 | 2 | user2@example.com | Issue with account activation | 75 | 4 | user4@example.com | 2022-02-10 | 2022-02-12 | | 2 | 48 | 0 | 0 | 2022-02-10 | 3 | 412 |
| solved | support | category3 | 103 | 5 | user5@example.com | Can’t upload profile picture | 200 | 6 | user6@example.com | 2022-03-15 | 2022-03-16 | 2022-03-18 | 1 | 24 | 3 | 72 | 2022-03-15 | 8 | 3 |
| unsolved | NULL | category4 | 104 | 7 | user7@example.com | Error when posting | 50 | 8 | user8@example.com | 2022-04-20 | | | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
