Average Staff Response Times to Topics

data & reporting
1

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
1 Like
2

Is there any easy way exclude topics started by staff?

1 Like
3

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:

  1. 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.
  2. 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.
1 Like
4

Thanks! The result is much less restless now.

Damn, everything is easy… when has enough skills.

1 Like