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.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional
- Editor or Admin permissions (see Adding users 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. Query Builder opens.
- Now, create a Standard calculated metric to show the time between first assignment to first resolution. Click Calculations (), and then Standard calculated metric.
- Copy the metric below and paste it into the metric editor. Add a title 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))
- In the Metrics panel, click Add.
- From the list of metrics, expand Calculated metrics, click the custom metric you created, First assignment to first resolution time, then click Apply.
- Ensure the metric aggregator for First assignment to first resolution time is set to AVG. For more information, 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.
- Click Chart configuration (), then Display format.
- On the Display format page, change AVG(First assignment to first resolution time) to Custom. Then make the changes below:
- Decimal place: 1
- Suffix: days
You might also want to add a date filter to the report by using the information in Editing dates and date ranges.
Then your query is complete! See the screenshot below as an example.