Time to first response by group members

[simon]

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

[IreneT (Irene Tolentino)]

您好 @simon

感谢您分享了一个关于按月生成员工响应时间报告的潜在解决方案。这段代码是否仍然可用，或者有更新的版本吗？另外，是否可以按周而不是按月生成此报告？非常感谢！

[simon]

@IreneT

Is this code still viable or is there an updated version?

它不再可用了。老实说，我不知道它以前是怎么起作用的。我很快会发布一个更新版本，并通知你它已准备就绪。

编辑：@IreneT
这是原始查询的修复版本。我建议忽略那个查询，而是查看我在这个回复中发布的其他查询。如果你对这些查询有任何疑问，或者在向查询添加必需的参数时遇到任何问题，请告诉我。根据今天的测试，我发现保存数据探索器查询后必须刷新页面，才能在查询下方显示参数输入字段。（这可能只是我本地开发站点的一个怪癖。）

-- [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

更改之处在于：

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

我很惊讶我会写出旧的查询而不进行测试。无论如何，更新后的版本现在可以按预期工作了。

据我回忆，这个查询是为特定站点编写的，并不打算发布在 Meta 上。以下是另外两个用于获取有关员工响应时间信息的有用查询：

特定时间段内创建主题的首次组响应时间

返回指定组成员对非该组成员用户创建的“常规”（非个人消息）主题的首次响应时间。查询的 :group_name 参数默认为“staff”。使用此值，它将给出员工的首次响应时间。你可以更改此参数的值以获取不同组的响应时间。例如“customer_support”。

请注意，日期在技术上应以 yyyy-mm-dd 的形式提供，但查询也接受 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

每个类别的平均首次组响应时间：

使用与上一个查询相同的逻辑，但返回指定组成员对非该组成员用户在 :start_date 和 :end_date 参数设定的时间段内创建的主题的首次响应的_平均_时间。与上一个查询一样，如果 :group_name 参数保留其默认值“staff”，它将返回非员工用户创建的常规主题的平均员工首次响应时间。

-- [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，也许可以用这个帖子中最后两个查询替换 OP 中的查询。另外，也许可以把标题更新为类似“组成员的首次响应时间”的内容。

[IreneT (Irene Tolentino)]

@simon 期待中。

非常感谢。谢谢

[JammyDodger]

@simon

我发现我必须在保存 Data Explorer 查询后刷新页面，参数输入字段才能显示在查询下方。（这可能只是我本地开发站点的一个怪癖。）

不，这不幸的是一个已知问题。不过，快速刷新一下就能解决问题。

[IreneT (Irene Tolentino)]

这太完美了@simon。非常感谢您在这方面的帮助。我们这边只需要做一点小调整就能获得所需的数据，但我认为我们已经准备好了。非常感谢

[IreneT (Irene Tolentino)]

您好 @simon

我们已经安装了数据探索器并使用了您分享的代码。我们特别想要的是每个小组成员的首次响应时间。但是，查询返回的是按类别 ID 分组的响应时间，而不是按用户 ID 分组的。

如果您能提供帮助，我们将不胜感激。谢谢。

[simon]

@IreneT

我们特别想知道每个小组成员首次响应的时间。

感谢您提出这个问题，因为我发布的查询中存在一个错误：

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

那一行正如其名，提取了时间戳中的分钟数。这意味着，如果一个主题在 12:00 创建，然后在 12:05（一个月后）得到回复，查询将计算出 5 分钟的响应时间。

修复方法是：

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

日期可能会很棘手。我无法编辑原始帖子，因此我将在其中发布修复程序，然后在单独的帖子中回答您的问题。

@JammyDodger，以下是原始帖子中两个查询的新版本：

小组成员的每个主题首次响应时间

-- [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

每个类别的平均首次小组响应时间

-- [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

[simon]

@IreneT

我们特别希望按每个小组成员的姓名获得首次响应时间。

我假设您想要每个小组成员的平均首次响应时间。如果您只想知道小组成员响应单个主题所需的时间，请使用 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

这将告诉您小组成员响应单个主题所需的时间。结果按类别组织，但类别可以忽略。

我不确定个人小组成员的平均响应时间数据有多大意义。我将谨慎使用它进行任何绩效审查，因为它可能会惩罚那些响应困难问题或被其他小组成员忽略的主题的小组成员。考虑到这一点，这里有一个查询，它返回小组成员的平均响应时间和他们作为小组成员首次响应的主题数量：

-- [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

[IreneT (Irene Tolentino)]

感谢 @simon - 一如既往的非常有帮助和快速的响应。非常感谢！

实际上，我们的目标是让我们的教练（已添加到其中一个组中）来衡量他们的平均响应时间。这样我们就有了一个参考，了解我们在提供社区支持和确保长期会员满意度方面的表现如何。

[simon]

@IreneT

我们的目标是让我们的教练（已添加到其中一个组中）衡量他们的平均响应时间。这样，我们就有了一个参考，了解我们在提供 Discourse 社区支持以及确保长期会员满意度方面做得有多好。

这说得通。如果以正确的方式解读，有很多数据可能很有趣或很有用。使用数据探索器查询时，我有时担心我可能会编写用于生成绩效报告的查询。

我做过很多客户支持工作。我确实认为首次响应时间是一个有用的指标，但响应的质量也需要考虑在内。

本主题中的查询没有告诉您的是关于 未 收到组成员响应的主题的任何信息。查询仅返回有关已响应的主题的数据。我不确定如何最好地将有关无响应主题的信息添加到查询中。

[IreneT (Irene Tolentino)]

@simon 这说得通。

感谢分享这些信息。也许这个指标（首次响应时间）应该始终与关于“无响应主题”的其他报告一起备份。

当此报告中的某个值被点击时，是否可以将其链接到一个显示“无响应主题”摘要的页面？这样我们就可以验证我们是否能够在指定时间内回答所有需要响应的主题，并且可以排除那些实际上不需要响应的主题。

[simon]

@IreneT

当报告中的某个值被点击时，它是否可以成为一个链接，显示“没有回复的主题”的摘要？

不可以。获取未回复主题链接的最佳选择可能是使用数据探索器查询。元（Meta）上可能有一个示例查询，但我搜索时找不到。

另一个选择是安装“未答题筛选器”主题组件：https://meta.discourse.org/t/unanswered-filter/126257。它会在站点的导航菜单中添加一个下拉项，允许您筛选主题列表，仅显示未答题。

[IreneT (Irene Tolentino)]

非常感谢您，@simon。一如既往地感激