Overview
This recipe will show you how to find the number of unique users who submitted a ticket in any given month. It doesn't matter if a user submits multiple tickets; they will only be counted once.
Conditions
- Access to Insights (Professional and Enterprise)
- At least one ticket and one user that have not been deleted
Procedure
Step 1: Create metric
Open the Metric Editor and create this metric:
- SELECT COUNT(User, Ticket Id) WHERE User Active = true AND Ticket Status <> Deleted
Please note: You can't simply copy and paste in the Metric Editor. You must select the color-coded items from the Elements list on the right:
- User, Ticket Id, User Active, and Ticket Status are all under Attributes
- true is under Attribute Values > User Active
- Deleted is under Attribute Values > Ticket Status
This metric counts unique Users in the Tickets data-set. This version of the metric filters out deleted users and deleted tickets. If you want to include deleted users, you can remove the User Active portion.
Step 2: Build report
Once you have the metric, you can build a report like this:
WHAT
- # Users by tickets (the custom metric above)
- # Tickets (optional)
HOW
- Month/Year (Ticket Created)
FILTER
- List of Values - Month/Year (Ticket Created) for the months you want to see
Here is what the recipe looks like in a small test account:
In this example, you can see that 13 users created tickets in November. Between them, those 13 users created 25 total tickets.
If one of those 13 users from November created another ticket in December, they would be counted on the December line as well.
Other uses
This metric allows you to report users based on their ticket activity. That could help you find active or inactive users.
For example, if you want to see how many users have created a ticket in the past 30 days, you could create a metric like this:
- SELECT # Users by tickets WHERE Date (Ticket Created) > THIS-30
When you look at ticket data, the User is the requester. You can make similar metrics for other user attributes, if you want to count something else. (You can read more about user attributes in this article.)
For example, if you want to see which assignees have solved a ticket in the past 30 days, you could create a metric like this:
- SELECT COUNT(Ticket Assignee, Ticket Id) WHERE Date (Ticket Solved) > THIS-30 AND Ticket Status <> Deleted
12 Comments
Hello!
I want to exclude tickets that have a specific tag from this metric.
I tried to add "AND Ticket_tag<>"tag" " but no success can you help?
Tom
Amy gives instructions here on tag reporting, including details on how to exclude tickets with a specific tag. Hope that helps.
Thanks Graeme,
I have tried using the technics in this article, however, it didn't work.
To my knowledge the metric above counts "Users" while #tags are on a ticket level. This creates a conflict that doesn't allow me to decrease the amount of tickets tagged with the amount of users that submitted a ticket.
I believe if I create a metric that counts the amount of users that submitted a ticket with a specific tag I can get it to work but I don't know how to build such metric. Any ideas?
SELECT COUNT(User,Ticket ID) WHERE Ticket Tag <> "NPS" didn't work for me.
Tom
Rather than modify the user count metric to look at tags, it is better to create a new, separate metric to flag the tagged tickets. Include that metric in your report filter. This is the method that Amy uses and I would expect that to work here.
Thanks for jumping in, Graeme.
Tom, I see that this is one of your first posts - welcome to the Zendesk Community! I encourage you to head over to the welcome thread in The Lounge to introduce yourself. Happy Zendesking!
Thanks Graeme, it worked.
Thanks for a fast and effective response.
I'm trying to count the number of active agents in a time period.
Trying: SELECT COUNT(User) WHERE User Active=true AND User Role=Agent
but not working. Tried a number of varieties including filtering role at the report level and in the metric definition.
How can I do this? Time period will be defined on the report using month/year or similar metrics.
Hi Brendan!
Let me check with some Insights experts to see what we can figure out for you here. Stand by!
Brendan
Try this metric:
This uses the Ticket Updater rather than user.
To see this by date, use 'Month/Year (Event)' as the HOW part of the report.
Hey Brendan,
Just jumping in to verify, Graeme is spot on! His metric will show you how many active agents you've had within a given time period.
If you want a list of those users you can then just add Updater under How or add a drill-in step to that custom metric for Updater.
Thanks for providing that Graeme! :)
Hi! I was able to get this to work monthly, but is there something about the custom metric that causes it to not work for weekly?
When I tried to change it to weekly, the # Users by Tickets was the same, but # tickets changed.
Hey Emma,
Thanks for reaching out to us! I see you already have a ticket open on this with topic with Jessica so i'll let her help troubleshoot your specific case.
But as a general rule, you should just be able to break this down by week. Jessica should be able to look at your report to figure out why its not working as expected.
Please sign in to leave a comment.