Help Scoutlink
Sample Querieslink
-
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;