Help Scout

Sample Queries

1. How to calculate the average resolution time for your conversations

   WITH customer_messages AS (
       SELECT
           conversation_id,
           created_at AS customer_message_at
       FROM helpscout.conversation_thread_history
       WHERE type = "customer"
   ),
   user_messages AS (
       SELECT
           conversation_id,
           created_at AS user_message_at
       FROM helpscout.conversation_thread_history
       WHERE type = "message" AND state = "published"
   ),
   last_customer_messages AS (
       SELECT
           customer_messages.conversation_id,
           MAX(customer_message_at) AS last_customer_message_that_received_a_response_at
       FROM customer_messages
       JOIN user_messages ON user_messages.conversation_id = customer_messages.conversation_id
           AND user_message_at > customer_message_at
   ),
   resolutions AS (
       SELECT
           user_messages.conversation_id,
           MIN(user_message_at) AS resolved_at
       FROM last_customer_messages
       JOIN user_messages ON user_messages.conversation_id = last_customer_messages.conversation_id
           AND user_message_at > last_customer_message_that_received_a_response_at
       GROUP BY 1
   ),
   conversation_resolution_times AS (
     SELECT
       conversation_id,
       conversation.created_at,
       resolved_at,
       TIMESTAMP_DIFF(resolved_at, conversation.created_at, SECOND) AS resolution_time
     FROM helpscout.conversation_history
     JOIN resolutions ON resolutions.conversation_id = conversation.id
     WHERE conversation.closed_at IS NOT NULL
   )
   SELECT
     TIMESTAMP_TRUNC(resolved_at, MONTH) AS resolution_month,
     AVG(resolution_time) / 3600 AS avg_resolution_time_in_hours
   FROM conversation_resolution_times
   GROUP BY 1
   ORDER BY 1 DESC;