Time to first response by group members

data & reporting
1

Time to first group response for topics created within a given time period

Returns the time to first response by a member of a given group to “regular” (not Personal Message) topics created by a user who is not a member of the given group. The query’s :group_name parameter is set to “staff” by default. With that value, it will give the time to first response by staff members. You can change the value of that parameter to get response times for different groups. For example “customer_support”.

Note that dates should technically be supplied in the form yyyy-mm-dd, but the query would also accept dates in the form dd-mm-yyyy.

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    topic_id,
    staff_user_id,
    ROUND(response_time_minutes::numeric, 2) AS response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

Average time to first group response per category:

Uses the same logic as the previous query, but returns the average time to the first response by members of the given group per category for topics created by users who are not members of the given group within the time period set by the :start_date and :end_date parameters. As with the previous query, if the :group_name parameter is left at its default value of “staff”, it will return the average staff first response times for regular topics created by non-staff users.

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    ROUND(AVG (response_time_minutes)::numeric, 2) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id
2

Hi @simon

Thanks for sharing a potential solution to generate reports for time to respond by staff monthly. Is this code still viable or is there an updated version? Also, is there a way to make this report on a weekly basis vs monthly? Thanks so much

1 Like
3

It’s not. Honestly, I’m not sure how it ever worked. I’ll post an updated version soon and ping you to let you know it’s ready.

Edit: @IreneT
Here’s a fixed version of the original query. I’d ignore that query and look at the other queries I’ve posted in this reply instead. Let me know if you have any questions about the queries, or if you run into any issues adding the required parameters to the queries. From testing today, I’m finding that I have to refresh the page after saving a Data Explorer query in order for the parameter input fields to show up below the query. (That may just be a quirk on my local development site.)

-- [params]
-- int :months_ago = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),
staff_responses AS (
SELECT
DISTINCT ON (p.topic_id)
p.topic_id,
p.created_at,
t.category_id,
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time
FROM posts p
JOIN topics t
ON t.id = p.topic_id
AND t.category_id = ANY ('{46,25,43,40,44,35,22,7,20,17,6,12}'::int[])
JOIN users u
ON u.id = p.user_id
WHERE p.post_number > 1
AND u.admin = 't' OR u.moderator = 't'
ORDER BY p.topic_id, p.created_at
),
user_topics AS (
SELECT
t.id
FROM topics t
JOIN users u
ON u.id = t.user_id
WHERE u.admin = 'f' AND u.moderator = 'f'
)

SELECT
sr.category_id,
AVG(sr.response_time) AS "Average First Response Time",
COUNT(1) AS "Topics Responded to"
FROM staff_responses sr
JOIN query_period qp
ON sr.created_at >= qp.period_start
AND sr.created_at <= qp.period_end
JOIN user_topics t
ON t.id = sr.topic_id
GROUP BY sr.category_id

What was changed was:

--DATE_TRUNC('minute', p.created_at - t.created_at) AS response_time
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time

I’d be surprised if I wrote the old query without testing it. In any case, the updated version works as expected now.

From memory, this query was written for a specific site and not intended to be posted on Meta. Here are a couple more useful queries for getting information about staff response times:

Time to first group response for topics created within a given time period

Returns the time to first response by a member of a given group to “regular” (not Personal Message) topics created by a user who is not a member of the given group. The query’s :group_name parameter is set to “staff” by default. With that value, it will give the time to first response by staff members. You can change the value of that parameter to get response times for different groups. For example “customer_support”.

Note that dates should technically be supplied in the form yyyy-mm-dd, but the query would also accept dates in the form dd-mm-yyyy.

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    topic_id,
    staff_user_id,
    response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

Average time to first group response per category:

Uses the same logic as the previous query, but returns the average time to the first response by members of the given group per category for topics created by users who are not members of the given group within the time period set by the :start_date and :end_date parameters. As with the previous query, if the :group_name parameter is left at its default value of “staff”, it will return the average staff first response times for regular topics created by non-staff users.

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    AVG (response_time_minutes) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id

@JammyDodger, maybe replace the query in the OP with the last two queries in this post. Also, probably update the title to something like “Time to first response by group members.”

3 Likes
4

@simon Looking forward to it.

Much appreciated. Thanks

1 Like
5

No, unfortunately this is a known thing. A quick refresh does the trick though. :+1:

4 Likes
6

This is perfect @simon . Super appreciate your help on this. Just a little tweak from our end to have the data we need, but I think we’re good. Thanks so much :heart:

1 Like
7

Hi @simon

We have installed Data Explorer and used the codes you shared. What we specifically want is to have the Time to first response by name of each group member. However, the query is sending response times grouped by the category ID and not the user ID.

image (1)
image (1)1178×1418 133 KB

Would appreciate your help. Thanks

8

Thanks for bringing this up, because there’s an error in the queries that I posted:

EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time_minutes

That line is doing exactly what it says, extracting minutes from the timestamps. That means that if a topic was created at 12:00, then responded to a month later at 12:05, the query was calculating a response time of 5 minutes.

The fix is:

EXTRACT(EPOCH FROM (p.created_at - t.created_at))/ 60 AS response_time_minutes

Dates can be tricky. I can’t edit the OP, so I’ll post the fixes here, then the answer to your question in a separate post.

@JammyDodger, here are new versions of the 2 queries in the OP:

Time to first response per topic for members of a group

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    topic_id,
    staff_user_id,
    ROUND(response_time_minutes::numeric, 2) AS response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

Average time to first group response per category

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at)) / 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    ROUND(AVG (response_time_minutes)::numeric, 2) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id
4 Likes
9

I’m assuming you want the average time to first response per group member, if you just want the time it took group members to respond to individual topics, use the fixed version of the first query in the OP:

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at))/ 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    topic_id,
    staff_user_id,
    ROUND(response_time_minutes::numeric, 2) AS response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

That will tell you the time it took group members to respond to individual topics. The results are organized by category, but the category can be ignored.

I’m not sure how meaningful data for average response times for individual group members will be. I’d be wary of using it for any kind of performance review because it might penalize group members who respond to difficult questions or topics that other group members have ignored. With that in mind, here’s a query that returns the average response times for group members and the number of topics they’ve been the first member of the group to respond to:

-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(EPOCH FROM (p.created_at - t.created_at))/ 60 AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    staff_user_id,
    ROUND(AVG(response_time_minutes)::numeric, 2) AS average_response_time_minutes,
    COUNT(*) AS number_of_topics_responded_to
FROM group_response_times
WHERE row_num = 1
GROUP BY staff_user_id
ORDER BY average_response_time_minutes
2 Likes
10

Thanks @simon - very helpful and fast response as always. Really appreciate it!

Actually, our objective is for our coaches (added in one of the groups) to measure their average response time. This way we have a reference on how well we’re doing when it comes to providing community support in discourse and ensuring satisfied members in the long run.

11

That makes sense. There’s a lot of data that could be interesting or useful if it’s interpreted in the right way. With Data Explorer queries I’m sometimes worried that I could be writing queries that are used to generate performance reports.

I’ve done lots of customer support work. I do think the time to first response is a useful metric, but the quality of the response also needs to be taken into account.

One thing that the queries in this topic aren’t telling you is anything about topics that have not received a response from a group member. The queries are only returning data about topics that have been responded to. I’m not sure how to best go about adding information about topics with no response to the queries.

12

@simon that makes sense.

Thanks for sharing that info. Maybe this metric (time to first response), should always be backed up the other report on “Topics with no response”.

Is it possible that when a value is clicked on this report, it will be a link to show a summary of those “topics that have no response”? That way we can verify if we’re able to answer all topics that need responses within a specified time and that would exclude those topics that really don’t need a response at all.

image
image1141×580 33.8 KB

13

It’s not. The best option for getting links to unanswered topics might be to use a Data Explorer query. There might be an example query for that on Meta, but I’m not finding one when I search for it.

Another option is to install the Unanswered Filter theme component: Unanswered Filter. It adds a dropdown item to the site’s navigation menue that allows you to filter topic lists to only display unanswered topics.

14

Thanks so much @simon . Appreciate as always :heart:

1 Like