This Data Explorer report is designed to analyze the average response times of staff members (admins and moderators) to topics created within a specified date range and category. This report can help with understanding the efficiency and responsiveness of staff interactions on a Discourse site, which can be crucial for community management. -- [params] -- date :start_date = 2024-01-01 -- date :end_date = 2024-01-30 -- int_list :categories = 0 -- boolean :include_subcategories = false WITH filtered_topics AS ( SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at FROM topics t WHERE ( ':categories' = '0' OR t.category_id IN ( SELECT id FROM categories WHERE id IN(:categories) OR (:include_subcategories AND parent_category_id IN(:categories)) ) ) AND t.created_at >= :start_date AND t.created_at < :end_date AND t.archetype = 'regular' AND t.deleted_at IS NULL ), staff_replies AS ( SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply FROM posts p JOIN users u ON p.user_id = u.id WHERE (u.admin = true OR u.moderator = true) AND p.deleted_at IS NULL AND p.post_type = 1 GROUP BY p.topic_id ), response_times AS ( SELECT ft.topic_id, ft.created_at AS topic_created_at, sr.first_staff_reply, ft.category_id as category_id, EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours FROM filtered_topics ft LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id ) SELECT topic_id, category_id, topic_created_at, response_time_hours as staff_response_time_hours FROM response_times ORDER BY topic_created_at ASC SQL Query Explanation The report uses several Common Table Expressions (CTEs) to break down the query: filtered_topics: This CTE filters topics based on the input parameters such as date range, category, and whether to include subcategories. It ensures that only non-deleted, regular archetype topics are considered. staff_replies: This CTE identifies the first reply made by a staff member (either admin or moderator) to the topics identified in the filtered_topics CTE. It filters out deleted posts and considers only the main posts (post_type = 1). response_times: This CTE calculates the response time by finding the difference between the topic creation time and the first staff reply time. The result is converted from seconds to hours. The final SELECT statement from the response_times CTE fetches the topic ID, category ID, topic creation date, and the calculated staff response time in hours, ordering the results by the topic creation date. Parameters start_date (date): The beginning date of the period for which to analyze topic creation. end_date (date): The end date of the period for which to analyze topic creation. categories (int_list): A list of category IDs to filter the topics. If set to 0, all categories are included. include_subcategories (boolean): A flag to determine whether to include subcategories of the specified categories in the analysis. Results topic_id: The unique identifier of the topic. category_id: The category to which the topic belongs. topic_created_at: The date when the topic was created. staff_response_time_hours: The time taken by staff to respond to the topic in hours. A NULL value indicates that there is no staff response to the topic. Example Results topic_id category_id topic_created_at staff_response_time_hours 101 5 2024-01-02 1.5 102 5 2024-01-02 3.2 103 12 2024-01-03 NULL 104 12 2024-01-04 0.75

Is there any easy way exclude topics started by staff?

Yes, you could modify the report as follows to exclude topics created by staff users. -- [params] -- date :start_date = 2024-01-01 -- date :end_date = 2024-01-30 -- int_list :categories = 0 -- boolean :include_subcategories = false WITH staff_users AS ( SELECT id FROM users WHERE admin = true OR moderator = true ), filtered_topics AS ( SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at FROM topics t LEFT JOIN staff_users su ON t.user_id = su.id WHERE ( ':categories' = '0' OR t.category_id IN ( SELECT id FROM categories WHERE id IN(:categories) OR (:include_subcategories AND parent_category_id IN(:categories)) ) ) AND t.created_at >= :start_date AND t.created_at < :end_date AND t.archetype = 'regular' AND t.deleted_at IS NULL AND su.id IS NULL -- Exclude topics created by staff ), staff_replies AS ( SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply FROM posts p JOIN staff_users su ON p.user_id = su.id WHERE p.deleted_at IS NULL AND p.post_type = 1 GROUP BY p.topic_id ), response_times AS ( SELECT ft.topic_id, ft.created_at AS topic_created_at, sr.first_staff_reply, ft.category_id as category_id, EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours FROM filtered_topics ft LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id ) SELECT topic_id, category_id, topic_created_at, response_time_hours as staff_response_time FROM response_times ORDER BY topic_created_at ASC Modifications Explained: staff_users CTE: A new CTE is added to identify users who are either admins or moderators. This helps in filtering out topics created by staff in the subsequent steps. filtered_topics CTE: This CTE now includes a LEFT JOIN with the staff_users CTE to exclude topics where the user_id matches any staff member’s ID. This is done by checking su.id IS NULL, which ensures that the topic was not created by a staff member.

Average Staff Response Times to Topics

Community Building Data & reporting

Jagster (Jakke Flemming) May 3, 2024, 5:37am 4

Thanks! The result is much less restless now.

Damn, everything is easy.. when has enough skills.

Topic		Replies	Views
Dashboard Report - Time to First Response Data & reporting sql-query , dashboard-reports , dashboard-sql	0	500	December 14, 2023
Solved Topic and Moderator Response Stats by Category Data & reporting solved , sql-query	0	453	April 3, 2024
Time to first response by group members Data & reporting sql-query	13	1281	October 12, 2023
Dashboard Report - Topics with No Response Data & reporting sql-query , dashboard-reports , dashboard-sql	8	631	September 5, 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	965	October 30, 2024

Average Staff Response Times to Topics

Related topics