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.
- Access to Insights (Professional and Enterprise)
- At least one ticket and one user that have not been deleted
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:
- # Users by tickets (the custom metric above)
- # Tickets (optional)
- Month/Year (Ticket Created)
- 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.
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