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

Explore recipe: Agent interactions on tickets

Jessica Blake
  • Edited
Zendesk team member

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

In this recipe, you'll learn how to create a query to show the number of interactions your agents are having on tickets, both with end-users and with each other. The query counts the number of private and public comments made by agents. 

What you'll need

Skill level: Easy

Time Required: 15 minutes

Creating the query

    To create the query

    1. In Explore, click the query () icon.
    2. In the Queries library, click New query.
    3. On the Choose a dataset page, click Support > Ticket updates > Support: Ticket Updates. Query builder opens.
    4. In the Metrics panel, click Add.
    5. From the list of metrics, choose Comments > Public comments and Comments > Internal comments, then click Apply.
    6. In the Columns panel, click Add.
    7. From the list of attributes, choose Updater > Updater name, then click Apply.
    8. In the Filters panel, click Add. 
    9. From the list of attributes, choose Updater > Updater role, then click Apply.
    10. Click the Updater role filter you just added and then, on the Selected tab, tick Admin and Agent.
    11. When you are finished, click Apply.
    12. From the Visualization type ( ) menu. choose Column. 
    13. In the Chart configuration () menu, click Chart
    14. On the Chart page, enable Stacked and Stacked: Show total values.
    15. In the Chart configuration menu () click Displayed values.
    16. On the Displayed values page, set Show value to Show, and set Position to Inside. 

    The query is complete. See the screenshot below for an example.

    Next steps

    You might want to edit the colors of the columns so that the displayed values are easier to read. To change them, click Chart configuration (), then Colors. 

    For more information about customizing the appearance of the query, check out Customizing queries.

    Return to top
    Have more questions? Submit a request

    28 Comments

    • Thomas D'Hoe
      Community Moderator

      Hi, 

      Thanks for this one.

      How can I filter this report so that I only see the updates from a specific agent group?

      When I filter on Agent Group I still see agent names that not belong to that specific agent group.

       

      Thanks for the help.

       

      Thomas

      0
    • Andy Killingsworth

      Hey, Thomas. 

      Thanks for posting this issue, I'd like to help! To start, I'm not seeing your Ticket Group filter in that screenshot. If that filter was active, I'd expect to see it next to the Updater role filter, above the query display. 

      I tested this kind of filter and was able to make it work within my test account. 

      Here's my copy of this query pre-filter:

      And here it is with the filter applied:

      This filtering also works with the Update ticket group, if you want to filter by the group to which the ticket was assigned at the time of an update.

      Please make sure your filter is appropriately added and let us know if the issue persists!

      0
    • Mistyamber Reynolds

      I only want to see ONE agent's comments on tickets by ticket ID.  Whenever I attempt to get this granular, it gives me the TOTAL comments (including the comments by other agents on the same ticket).  I really need to see the single agent's comments.  Is this possible?   Filtering by Updater name and Ticket ID does not help.

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Mistyamber,

      I'm going to bring this into a ticket so our Customer Advocacy team can take a look at this report you've set up.

      If you're filtering by updater and ticket ID I would expect only comments by that particular updater to show up.

      You'll receive an email shortly stating your ticket has been created.

      Cheers!

      0
    • Danny K

      I'm looking for a way to track which agents change the group assignment of a ticket. 

      In some cases, an agent in group A may assign an unassigned ticket to group B. In others, an agent in group A assigned to a ticket may assign that ticket to group B.

      Is there a way to track ticket group reassignment by agents?

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Danny,

      There are a couple of Explore assignment recipes that may help point you in the right direction:

      I hope this helps!

      0
    • Karolina

      Hey 

      How can I modify this so it gives me the number of comments from a specific time frame e.g. last 24 hours or last 7 days? 

      Thanks 

       

       

       

      0
    • Karolina

      Referring to the above question - I have now amended the data range. 

       

      If I set this as yesterday, and save it, will the dashboard visual always show me the day before? 

       

      Sorry if it seems obvious. Very new to Zendesk

      0
    • Devan - Community Manager
      Zendesk Community Team

      Hello Karolina,

      Welcome to the Zendesk Community forum! No apologies needed because we are glad you are here and love questions from the Community! To answer your question, 100% yes, if you set this range to yesterday, the dashboard visual will always show you the day before. Let us know if there is anything else we can help with and have a wonderful day!

      Best regards. 


      0
    • Joachim Jonkers

      Hi,

      Is there a way to have an overview of the amount of ticket updates per agent (in a stacked column) per month (over the last year for example)? I do not seem to succeed in adding a time range on the x-axis.

      Best,

      JJ

      0
    • Devan - Community Manager
      Zendesk Community Team

      Hello Joachim Jonkers,

      We have you covered! Below are the following steps for creating an overview report on ticket updates per agent. 

      1. The metric you want to use is Agent Updates

      2. Under Columns apply Update - Year, Update - Month and Filter on the appropriate time range

      3. For Rows use Updater name

      4. For Filter use Updater role -> Filter on Agent and Admin

      5. For Formatting: Chart configuration menu -> Row selector, hit hide row selector, and under Selected position choose Select all

      Best regards. 

      1
    • Joachim Jonkers
      • Edited

      Thanks a lot for the help Devan, this has been a lifesaver. I can make future reports myself based on your help!

      0
    • Sharin Kaur

      Hi there,

      My issue starts right from the beginning unfortunately.

      1. In the Metrics panel, click Add.
      2. From the list of metrics, choose Comments > Public comments and Comments > Internal comments, then click Apply.

      I don't have "Comments" in the list of Metrics (screenshot below) and of course I'm trying desperately to get an understanding of how many public and internal comments are happening per agent within a timebox. Thank you so much for your help! 

      0
    • Melissa Norton
      • Edited

      Hi Sharin Kaur,

      It looks like you are using the incorrect dataset. Instead of "Support: Tickets," you will want to use "Support: Ticket Updates."

      Hope that helps!

       

      0
    • Marina Storozhenko

      Hi,

      I’m having a problem creating a daily leaderboard for public comments (not solved tickets) made by each agent in my organization. In the rows, I want to see agent names and in the columns, I want to see days of the week for the period of last week or ideally even two weeks. How do I do that? This is as far as I could go but it's in different format and it doesn't even show the dates. Please help.

      0
    • Brandon Tidd
      Community Moderator

      Hi Marina Storozhenko -

      It looks like you might be confusion the row attribute Ticket updated - Day of week with Ticket updated - Date.

      The latter will give you specific dates, whereas your current selection will show you the sum of all of the days in your filtered date range.  Hope this helps!

      Brandon

      1
    • Marina Storozhenko
      • Edited

      Hi Brandon Tidd, thank you. But it lists all the dates and as my data populates with every day, this query will become bigger and bigger barely fitting into my dashboard. How do I filter results within a certain time frame?

      Also, the date sequence isn't showing correctly on this report.

      Please help. Thanks.

      0
    • Brandon Tidd
      Community Moderator

      Hey Marina Storozhenko

      You can put up some guardrails via the "Ticket Created - Date" filter.  Hope this helps!

      0
    • Sharin Kaur

      Hi Melissa,

      Thank you for your response. I now have the correct metrics, however, how do I see not the total aggregate ticket updates but actually see this per agent per hour? ie. I would like to see a "daily view" of when ticket updates occur, whether it's internal or public and then understand the total as well.. Thanks!

      Sharin

      0
    • Melissa Norton

      Hi Sharin Kaur,

      In your case I would:

      • Measure the metric "Agent updates" 
      • Add attributes "Update - Hour" and "Assignee Name" (you can play with it in rows or columns)
      • Add a time filter for whatever time frame you are looking for (e.g. this week, yesterday, today). I used "Update - Date" as my time filter. You can make your time filter simple or advanced for a more custom time frame.

      Hope that helps :)

       

      0
    • Rob Stack
      Zendesk Documentation Team

      Melissa Norton

      Hi Melissa, thanks for posting this (and all your other great tips!). I thought this might be a great candidate to post in our tips and tricks section if you have time (and we have swag!).

      To find out more, see https://support.zendesk.com/hc/en-us/articles/360051150514

      Once again, thanks for all the help you give to the Zendesk Community!

      1
    • Mark Leci

      I'm looking for a way to find tickets without a public comment in x days, but I'm not seeing a way to do this. Posting here to share my findings so far and also to see if anyone has solved this!

       

      I've tried the following so far

      update date - this doesn't work even with the updater role filtered to admin/agent because any change made by any user (including an automation I believe) counts as an update. So a ticket may have been updated 5 times, but not had a public comment

      Comment public - this doesn't seem to be linked to the update itself, so if I filter on update date and comment public, I'll get only tickets that have at least one public comment, rather than tickets where the update was a public comment

      I also tried creating a custom attribute that uses the update date if the comment is public, this also doesn't work for the same reason I think

       

      What I'm leaning towards right now is creating a custom date field called 'last public comment date' or something, and update that via a trigger when a public comment is added. I could then filter out all tickets where this date is either not set or is older than 30 days. Does anyone have a better approach than this? 

       

      0
    • Brandon Tidd
      Community Moderator

      Hey Mark Leci -

      This custom standard calculated metric ran within the Ticket Updates dataset should get you pointed in the right direction:

      IF ([Ticket status - Unsorted] != "Solved" AND [Ticket status - Unsorted] != "Closed" AND [Updater role] = "End-user") THEN
      DATE_DIFF(NOW(), [Ticket updated - Timestamp], "nb_of_days")
      ENDIF

      From there you can use the Ticket ID attribute to get the total for each ticket.

      Hope This Helps!

      Brandon

      0
    • Mark Leci

      Hi Brandon,

       

      Thank you for the suggestion - unfortunately, this doesn't seem to work. I tried the above formula, when I use this as a metric and use MAX as the aggregator, I get a result of 1 day for a ticket that hasn't had a public comment in about 6 months (which I will have to follow up on!). I tried changing the

      [Updater role] = "End-user")

      to 

      [Updater role] != "End-user")

      but this didn't fix anything. What I'm seeing either way is that the number of days is the number of days since the ticket was last updated in any way, whether it was a field change, private comment etc.

      I believe what this formula is actually doing is finding the days since a ticket was last updated if the ticket is not solved/closed and it has an update from someone who is an End User. To make this work I think we would somehow need to specify that it was the most recent comment...

      Happily though, it does seem like the trigger I created works as expected. In case this is helpful for anyone else, I created a date field for 'last public comment', then set up a trigger as below. It will take some time for the data to become meaningful but I think it will work! 

       

       

       

      0
    • Yuliia Yalovenko
      • Edited

      Hey,

      I am looking for a way to build a report on tickets, which had only 1 interaction with the customer (1 public comment) and the rest of the cases (>1 public interaction). Is there a way to do it in explore?

      0
    • Brett Bowser
      Zendesk Community Team

      Hey Yuliia,

      We actually have a recipe for One Touch Tickets that should get you the results you're looking for.

      I hope this helps!

      0
    • Jeff Callahan

      Is there a way to filter where comment contains a specific string?

      1
    • Taylor Bowser
      Zendesk Customer Advocate

      Hi Jeff, 

      Unfortunately, the contents of ticket comments is not pulled into Explore so you would not be able to filter by a specific string.

      Warm regards, 

      -1

    Please sign in to leave a comment.

    Related articles

    Powered by Zendesk