Time to first response by group members

simon · 12 فبراير 2018، 1:56م

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 · 27 سبتمبر 2023، 6:13م

مرحباً @simon

شكراً لمشاركتك حلاً محتملاً لإنشاء تقارير حول وقت الاستجابة من قبل الموظفين شهرياً. هل هذا الكود لا يزال صالحاً أم أن هناك إصداراً محدثاً؟ وهل هناك طريقة لإنشاء هذا التقرير أسبوعياً بدلاً من شهري؟ شكراً جزيلاً.

simon · 27 سبتمبر 2023، 6:50م

إنه ليس كذلك. بصراحة، لست متأكدًا كيف نجح الأمر في أي وقت مضى. سأنشر إصدارًا محدثًا قريبًا وسأقوم بتنبيهك لإعلامك بأنه جاهز.

تعديل: @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، ربما استبدل الاستعلام في المنشور الأصلي بالاستعلامين الأخيرين في هذا المنشور. أيضًا، ربما قم بتحديث العنوان إلى شيء مثل “الوقت المستغرق لأول استجابة من قبل أعضاء المجموعة”.

IreneT · 27 سبتمبر 2023، 8:00م

@simon نتطلع إلى ذلك.
أقدر ذلك كثيرا. شكرا

JammyDodger · 27 سبتمبر 2023، 9:58م

لا، للأسف هذا أمر معروف. التحديث السريع يحل المشكلة.

IreneT · 29 سبتمبر 2023، 1:18م

هذا مثالي يا @simon . أقدر بشدة مساعدتك في هذا. مجرد تعديل بسيط من جانبنا للحصول على البيانات التي نحتاجها، ولكن أعتقد أننا بخير. شكراً جزيلاً لك

IreneT · 11 أكتوبر 2023، 4:10م

مرحباً @simon

لقد قمنا بتثبيت مستكشف البيانات واستخدمنا الرموز التي شاركتها. ما نريده تحديدًا هو الحصول على الوقت المستغرق للاستجابة الأولى حسب اسم كل عضو في المجموعة. ومع ذلك، فإن الاستعلام يرسل أوقات الاستجابة مجمعة حسب معرف الفئة وليس معرف المستخدم.

سنكون ممتنين لمساعدتك. شكراً

simon · 11 أكتوبر 2023، 7:21م

شكرًا على طرح هذا الموضوع، لأن هناك خطأ في الاستعلامات التي نشرتها:

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 · 11 أكتوبر 2023، 7:37م

أفترض أنك تريد متوسط الوقت المستغرق للاستجابة الأولى لكل عضو في المجموعة. إذا كنت تريد فقط الوقت الذي استغرقه أعضاء المجموعة للرد على مواضيع فردية، فاستخدم النسخة الثابتة من الاستعلام الأول في 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 · 11 أكتوبر 2023، 9:19م

شكراً @simon - استجابة مفيدة وسريعة كالعادة. أقدر ذلك حقًا!

في الواقع، هدفنا هو أن يقيس مدربونا (المضافون في إحدى المجموعات) متوسط وقت استجابتهم. بهذه الطريقة يكون لدينا مرجع حول مدى جودة أدائنا فيما يتعلق بتقديم الدعم المجتمعي في الخطاب وضمان رضا الأعضاء على المدى الطويل.

simon · 11 أكتوبر 2023، 9:37م

هذا منطقي. هناك الكثير من البيانات التي يمكن أن تكون مثيرة للاهتمام أو مفيدة إذا تم تفسيرها بالطريقة الصحيحة. مع استعلامات مستكشف البيانات، أقلق أحيانًا من أنني قد أكتب استعلامات تُستخدم لإنشاء تقارير الأداء.

لقد قمت بالكثير من أعمال دعم العملاء. أعتقد أن وقت الاستجابة الأول هو مقياس مفيد، ولكن يجب أيضًا أخذ جودة الاستجابة في الاعتبار.

أحد الأشياء التي لا تخبرك بها الاستعلامات في هذا الموضوع هو أي شيء عن الموضوعات التي لم تتلق استجابة من عضو في المجموعة. تُرجع الاستعلامات فقط بيانات حول الموضوعات التي تم الرد عليها. لست متأكدًا من أفضل طريقة لإضافة معلومات حول الموضوعات التي لم يتم الرد عليها إلى الاستعلامات.

IreneT · 11 أكتوبر 2023، 9:49م

@simon هذا منطقي.

شكرا لمشاركة هذه المعلومات. ربما يجب أن تدعم هذه المقياس (الوقت حتى الاستجابة الأولى) دائمًا بالتقرير الآخر حول “المواضيع التي لم يتم الرد عليها”.

هل من الممكن أنه عند النقر على قيمة في هذا التقرير، ستكون رابطًا لعرض ملخص لتلك “المواضيع التي لم يتم الرد عليها”؟ بهذه الطريقة يمكننا التحقق مما إذا كنا قادرين على الإجابة على جميع المواضيع التي تحتاج إلى ردود في غضون فترة زمنية محددة، وهذا من شأنه استبعاد تلك المواضيع التي لا تحتاج حقًا إلى رد على الإطلاق.

simon · 11 أكتوبر 2023، 10:11م

ليس كذلك. أفضل خيار للحصول على روابط للمواضيع التي لم يتم الرد عليها قد يكون استخدام استعلام مستكشف البيانات. قد يكون هناك استعلام مثال لذلك في ميتا، ولكني لا أجده عند البحث عنه.

خيار آخر هو تثبيت مكون سمة “عامل تصفية غير مجاب عليه”: Unanswered Filter. يضيف عنصر قائمة منسدلة إلى قائمة تنقل الموقع يسمح لك بتصفية قوائم المواضيع لعرض المواضيع التي لم يتم الرد عليها فقط.

IreneT · 12 أكتوبر 2023، 4:28م

شكرا جزيلا لك يا سيمون. أقدر ذلك دائما

الموضوع		الردود	مرات العرض
Dashboard Report - Time to First Response Data & reporting dashboard-reports , sql-query , dashboard-sql	0	411	14 ديسمبر 2023
Time to first response for a support group Data & reporting sql-query	0	154	12 أبريل 2019
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	22	5486	12 نوفمبر 2021
Average Staff Response Times to Topics Data & reporting sql-query	3	231	3 مايو 2024
Solved Topic and Moderator Response Stats by Category Data & reporting solved , sql-query	0	293	3 أبريل 2024