English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore recipe: Average replies per ticket for each agent per day

Jessica Blake
  • Edited
Zendesk team member

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

In this Explore recipe, you'll learn how to create a query that counts the average number of replies an agent makes for each ticket on a daily basis. 

What you'll need

Skill level: Moderate

Time Required: 20 minutes

How to create the report in Explore

    To create the query

    1. In Explore, click the queries () icon.
    2. In the Queries library, click New query.
    3. On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.
    4. 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.
    5. 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.
    6. When you are finished, click Save.
    7. In the Metrics panel, click Add.
    8. From the list of metrics, choose Calculated metrics, > Average agent replies per ticket per day (the custom metric you just created), then click Apply.
    9. Ensure the metric aggregator for Average agent replies per ticket per day is set to AVG. For more information, see Choosing metric aggregators.
    10. In the Rows panel, click Add.
    11. From the list of attributes, choose Updater > Updater name, then click Apply.
    12. In the same attributes list choose Time - Ticket update > Update - Date, then click Apply.
    13. In the Filters panel, click Add.
    14. From the list of attributes, choose Ticket update > Comment type, then click Apply.
    15. In the Filters panel, click the Comment type filter you just added and include only Public comments.
    16. In the Filters panel, click Add.
    17. From the list of attributes, choose Updater > Updater role, then click Apply.
    18. 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. 

      

    Return to top
    Have more questions? Submit a request

    8 Comments

    • Christopher C.

      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

      0
    • Devan - Community Manager
      Zendesk Community Team

      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.

      0
    • Mukesh Kumar
      • Edited

      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. 

      0
    • Mark Leci

      Mukesh Kumar I created a new metric to do this, D_COUNT(Agent comments)/COUNT(Tickets solved)

      1
    • Jaïs Pingouroux

      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.

      0
    • Devan - Community Manager
      Zendesk Community Team

      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.

      0
    • Jaïs Pingouroux
      • Edited

      Hi Devan - Community Manager

      Thanks for your answer. What about this formula:

      D_COUNT(Agent public comment)/DATE_DIFF([Ticket solved - Timestamp],[Ticket assigned - Timestamp],"nb_of_days")

      where "Agent public comment" is worth:

      IF ([Comment present] = TRUE AND [Comment public] = TRUE AND [Updater role] != "End-user") THEN [Update ID] ENDIF
      0
    • Rob Stack
      Zendesk Documentation Team

      Hi Jaïs Pingouroux, here's an idea to try. Use the standard calculated metric from the recipe above. In the columns panel, Add the update month attribute.
      Then, add two filters. The first, Comment type should include only Public comments.

      The second, Updater role should exclude End-user. The query would look something like this:

      If that isn't what you needed, please ask this question over at the product feedback forums to see if it's a feature that could be added. Thanks!

      0

    Please sign in to leave a comment.

    Related articles

    Powered by Zendesk