In this Explore recipe, you'll learn how to create a query to report on the average time between a ticket first being assigned to its first resolution time. Keep in mind this query is based on the time between ticket updates, not agent time spent working on each ticket update. If you require that information, check out our article Time Tracking app: metrics you need to be measuring.
For this reason, you'll also see any tickets that your agents assigned to themselves and solved in the same action showing as 0.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Zendesk Explore, click the query (
) icon.
- In the Queries library, click New query.
-
On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- Now, create a standard calculated metric to show the time between first assignment to first resolution. Click Calculations (
), then click Standard calculated metric.
- On the Standard calculated metric page, enter or paste the formula below into the Formula field. Add a name for the metric like First assignment to first resolution time. It will look like the screenshot below:
VALUE(First resolution time (days))-VALUE(First assignment time (days))
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > First assignment to first resolution time (the metric you just created), then click Apply.
- Ensure the metric aggregator for First assignment to first resolution time is set to AVG (see Choosing metric aggregators).
- In the Rows panel, click Add.
- From the list of attributes, choose Assignee > Assignee name, then click Apply.
- Click the Assignee name attribute you just added and exclude NULL from the results.
- From the Chart configuration (
) menu, click Display format.
-
On the Display format page, change AVG(First assignment to first resolution time) to Custom. Then make the following changes:
- Decimal place: 1
- Suffix: days
You could also add a date filter to the report by using the information in Editing dates and date ranges.
The query is now complete. See the screenshot below as an example.
4 Comments
Is there a calculated metric or formula to get this result in business days vs calendar days? I have tried the following:
VALUE(First resolution time - Business hours (min))/1440
But am not sure if that will net the result for Business Days?
Hi Jason, First resolution time - Business hours (hrs) looks like this:
VALUE(First resolution time - Business hours (min))/60
so if you wanted by day, then yes that calculation should work. You can view the formula for any metric that has the eye icon listed with it. Just click on the eye and it will open the formula for that metric.
Assignment time doesn't have a Business Hours option however, so if you try combining the two in this formula I don't think you would get accurate results.
Hope that helps!
That makes sense indeed, we are using created time vs assignment time, so I think we will be solid there.
I appreciate the info on the formula and quick response Chris Bulin!
You're welcome!
Please sign in to leave a comment.