Generating Support Metrics (with Assign and Solved plugins)


(Daquantics) #1

We doubled the size of our support team in May, and added Assign and Solved to our community at the same time. We support a large user base exclusively through our community site, and everything has been working really well.

The one thing I would really like are some metrics about throughput, in particular:

  • How many threads has each staff member of our support team been assigned over the past X days?
  • How many of each staff member’s assigned threads have been marked as solved over the past X days?

It would be interesting to know some other metrics too, like who is doing the assigning, but the two above are the important ones for me trying to track performance.

I’m guessing some of this may be possible with the data explorer plugin, but I wanted to see if anyone has tried this before and has anything to share, or if anyone has another idea.

Thanks everyone, especially the Discourse team for a great product!


(Jeff Atwood) #2

Sure, @Simon_Cossar can probably assist with this excellent request.


(Simon Cossar) #6

Here is a Data Explorer query that returns three columns: the Discourse staff member, the number of topics they have been assigned, and the number of assigned topics they have solved. The query requires start_date and end_date parameters. The date parameters should be in the form ‘year-month-day’, for example 2018-09-17.

--[params]
-- date :start_date
-- date :end_date

WITH assigned AS (
SELECT
user_id,
target_topic_id
FROM user_actions
WHERE action_type = 16
AND created_at::date BETWEEN :start_date AND :end_date
),
solved AS (
SELECT
ua.user_id,
ua.target_topic_id
FROM user_actions ua
JOIN assigned a
ON a.target_topic_id = ua.target_topic_id
AND ua.user_id = a.user_id
WHERE action_type = 15
)

SELECT
a.user_id,
COUNT(a.user_id) AS topics_assigned,
COUNT(s.user_id) AS topics_solved
FROM assigned a
LEFT JOIN solved s
ON s.target_topic_id = a.target_topic_id
GROUP BY a.user_id
ORDER BY topics_assigned DESC

(Daquantics) #7

This is working great, thanks very much @Simon_Cossar and @codinghorror