إحصائيات المواضيع المحلولة وردود المراقبين حسب الفئة

[SaraDev (Sara Devlaeminck)]

تتطلب التقارير التالية تمكين إضافة 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 = 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