This Explore recipe shows you how to create a report showing users who have not opened any tickets. For more help with reporting on users and organizations, see Reporting on user and organization data.
What you'll need
Skill level: Easy
Time Required: 5 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket, user, and organization data in Zendesk Support
How to create the report in Explore
- In Explore, click the reports (
) icon. - In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
- Next, add your metrics, the things you want to measure; in this case, the number of tickets created. In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets, then click Apply. Explore displays the total number of tickets in your account.
- Next, you'll create a standard calculated metric for users. Although Explore already includes this metric you need to modify it to ensure that its calculation doesn't interfere with the ticket metric you just added by enabling the "compute separately" function. In the calculations menu (
), under Metrics, click Standard calculated metric.
-
In the Standard calculated metric panel, give your new metric a name like "Users (computed separately)" and then paste or type the following formula:
IF ([Requester status]="Active")
THEN [Requester ID]
ENDIF - Ensure that Compute separately is enabled. For more information about this option, see Creating standard calculated metrics and attributes.
- When you are finished, click Save.
- In the Metrics panel of the report builder, click Add.
- From the list of metrics, choose Calculated metrics > Users (computed separately), then click Apply.
- Click the metric you just added and ensure that the aggregator for it is COUNT. Explore displays a table showing all tickets and users in your account.
- In the Rows panel, click Add.
- From the list of attributes, choose Requester/user > Requester name, then click Apply. Explore displays a table showing all users in your account and the number of tickets for each. You can sort the table by clicking the Tickets column heading to easily see users with no tickets.
Tip: You might see some deleted users in your table (with a value of 0 in the Users column). You can remove these from the report by configuring a metric filter on the Users (computed separately) metric that removes zero values.
10 Comments
Rob Stack, im trying to show users from my organization but i want the list to show all users (even without submited tickets) but also exclude any suspended users.
You just have to follow the steps above and it should also show you the users without any tickets. As it turns out, Explore does not return a zero value so you'll need to look for the blank rows in the ticket column.
Doesn't this presume that the organization the customer is from has opened at least 1 ticket ? What if I want to list a report that shows me any organizations that have never logged a ticket?
An end user can belong to multiple organizations, so it's possible for agents to move tickets between user orgs. Agents can also update the ticket requesters, and consequently change the associated ticket organization, when needed; and, agents/admins may also create an organization without users or tickets linked to it. So, it's definitely possible to have an organization with zero tickets listed under it.
If you need to see which organizations currently have no tickets associated to them, then you can follow similar steps outlined above, but instead of using requester status/id, you can reference the Requester organization attributes.
You can then slice this metric and the native Tickets metric by Requester organization name, and then sort or filter the report to see those with null values under
How do I show only one user per organization, the one with the largest number of tickets?
Hi Justin Near
This should be possible using the Top/bottom attribute calculation. You will need to create a top/bottom attribute calculation and add it to your query to show only the top 1 results. You can check out this article here for more details. Here's a screenshot of my test account which you can use as reference. Feel free to customize it as you see fit.
Hope this helps!
Dainne Lucena - that just gives me a single top ticket holder from the whole report, and what I'm wanting is the 1 top ticket holder per organization (so a report would show more than one, but one per org).
Hi Justin,
If you want to see the top ticket holder per organization, you need to change the Strategy in the Top/bottom result manipulation from Auto (depends on chart) to Per block on rows or Per block on columns (depending on where you add your attributes in the report).
Here's what my test report looks like before adding a top/bottom filter:
After applying a top/bottom filter to the test report with the following configuration:
My test report now looks like this:
Hope this helps! Stay safe!
Hi Elaine - I had already tried that and didn't have any success. There are still multiple names/organization. Is it because there are filters?
I am created a ticket on your behalf, so we can check your report and investigate this further. You will receive an email shortly, we can continue the support from there.
Best,
Please sign in to leave a comment.