In this Explore recipe, you'll learn how to create a query that counts the average number of replies and agent makes for each ticket on a daily basis.
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
How to create the report in Explore
To create the query
- In Explore, click the queries (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.
- Now, create a Standard calculated metric to compute the ratio between public agent replies and tickets commented. Click Calculations (
), and then click Standard calculated metric.
-
Copy the formula below and paste it into the metric editor. Add a title for the metric like Average agent replies per ticket per day.
D_COUNT(Agent comments)/D_COUNT(Tickets updated)
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - When you are finished, click Save.
-
In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics, > Average agent replies per ticket per day (the custom metric you just created), then click Apply.
- Ensure the metric aggregator for Average agent replies per ticket per day is set to AVG. For more information, see Choosing metric aggregators.
- In the Rows panel, click Add.
- From the list of attributes, choose Updater > Updater name, then click Apply.
- In the same attributes list choose Time - Ticket update > Update - Date, then click Apply.
- In the Filters panel, click Add.
- From the list of attributes, choose Ticket update > Comment type, then click Apply.
- In the Filters panel, click the Comment type filter you just added and include only Public comments.
- In the Filters panel, click Add.
- From the list of attributes, choose Updater > Updater role, then click Apply.
- In the Filters panel, click the Updater role filter you just added and include only the Admin and Agent roles.
The query is complete. See the screenshot below for an example of how it will look.
7 Comments
Hello,
Is their a way to view average ticket replies per month. Not per agent or anything, the point is just to get an idea how how many replies on avg does each ticket take to solve per month.
Lg
Chris
Hello Christopher,
In the Updates dataset, you can see, eg Agent Replies, and you can use the KPI viz to see the AVG over the period easily looked at (date range filter).
Best regards.
Hi
What is the best way to pull the avg number ( multi-touch) of contacts to ticket resolution?
Basically, I am looking to see how many back and forth replies an agent made before closing tickets in a period of time. e.g an agent takes avg 3 replies before closing a ticket.
Mukesh Kumar I created a new metric to do this, D_COUNT(Agent comments)/COUNT(Tickets solved)
Hello,
Interesting recipe.
Is there a way to measure, by month, the average number of public comment updates made by agents? i.e. my aim is to make sure that every tickets receive at least one public comment from an agent, and visualise this by month.
Hello Jais,
While you can do a count of comments made by each role, you can't switch the aggregator to average as Explore currently stands. I would recommend sharing your use case on our product feedback forums so our developers can consider it for a future update.
Best regards.
Hi Devan - Community Manager
Thanks for your answer. What about this formula:
where "Agent public comment" is worth:
Please sign in to leave a comment.