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
- 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 Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket Updates. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Comments > Public comments and Comments > Internal comments, then click Apply.
- In the Columns panel, click Add.
- From the list of attributes, choose Updater > Updater name, then click Apply.
- In the Filters panel, click Add.
- From the list of attributes, choose Updater > Updater role, then click Apply.
- Click the Updater role filter you just added and then, on the Selected tab, tick Admin and Agent.
- When you are finished, click Apply.
- From the Visualization type (
) menu. choose Column.
- In the Chart configuration (
) menu, click Chart
- On the Chart page, enable Stacked and Stacked: Show total values.
- In the Chart configuration menu () click Displayed values.
-
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.
30 Comments
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
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!
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.
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!
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?
Hey Danny,
There are a couple of Explore assignment recipes that may help point you in the right direction:
I hope this helps!
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
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
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.
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
Hello Joachim Jonkers,
We have you covered! Below are the following steps for creating an overview report on ticket updates per agent.
The metric you want to use is Agent Updates
Under Columns apply Update - Year, Update - Month and Filter on the appropriate time range
For Rows use Updater name
For Filter use Updater role -> Filter on Agent and Admin
For Formatting: Chart configuration menu -> Row selector, hit hide row selector, and under Selected position choose Select all
Best regards.
Thanks a lot for the help Devan, this has been a lifesaver. I can make future reports myself based on your help!
Hi there,
My issue starts right from the beginning unfortunately.
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!
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!
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.
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
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.
Hey Marina Storozhenko
You can put up some guardrails via the "Ticket Created - Date" filter. Hope this helps!
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
Hi Sharin Kaur,
In your case I would:
Hope that helps :)
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!
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?
Hey Mark Leci -
This custom standard calculated metric ran within the Ticket Updates dataset should get you pointed in the right direction:
From there you can use the Ticket ID attribute to get the total for each ticket.
Hope This Helps!
Brandon
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
to
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!
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?
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!
Is there a way to filter where comment contains a specific string?
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,
Hello. Was wondering if someone can help me here asap.
I am trying to figure out how many Agent Public Comments & End User Public Comments have been made in a single day. When I run a report, unfortunately, it tallies up ALL of the comments a ticket have rather than on the day its actually been commented. For example, If i had a single ticket that was submitted on 4/6 with 2 end user comments and then we responded to them on 4/7 with 2 public agent comments, then on 4/8 we received a comment from the end user. It should tell me that on 4/8 this agent who worked on the ticket have 0 public comments, but on 4/7 they had 2.
My reports are unfortunately not doing this rather they are giving me the TOTAL
Daniel Chikanov if you create a query using the Ticket Updates dataset (instead of the tickets dataset) you can filter by comments made by day (use attribute update date instead of create or solved date).
Please sign in to leave a comment.