In this Explore recipe, you'll learn how to find only tickets that weren't closed by merge.
What you'll need
Skill level: Intermediate
Time Required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
How to create the custom metric in Explore
All merged tickets have the tag closed_by_merge. You can use that information to exclude tickets that were closed by merge by creating a standard calculated metric in your query. Use the following steps to create this report in Explore.
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Calculations panel of query builder, select Standard calculated metric.
- Enter a name for your custom metric, for example, "Exclude closed by merge".
- Add the formula below and select Save after.
IF (NOT INCLUDES_ANY([Ticket tags], "closed_by_merge")) THEN [Ticket ID] ENDIF - In the Metrics panel, click Add. In Calculated metrics, select the metric you created.
- In the Metrics panel, click the added metric and change it to D_COUNT.
For more recipes on reporting with tags see this article: Reporting with tags.
10 Comments
I'm trying to look exclude merged tickets from my dashboard, but I don't want to create a custom metric - since I want to remove merged tickets from all of the metrics / charts on a particular tab. However, I also want to see the metrics with merged tickets included. I added Ticket Tags as a filter on the Dashboard Tab, yet when I exclude any tag, the data is not correct - it shows more tickets than with any filter applied.
Is this a known bug or am I don't something wrong?
I am also interested in doing the above, or does it make sense to add as a filter on a given report? For example, I want to exclude tickets that were closed via merge from my heatmap report.
Farhan Virji That won't work. The way the metric works is like this
1. Loop thru all tickets
2. Only return the Ticket ID if this ticket's tags do no contain closed_by_merge
3. A list of ticket IDs with all tags that are not closed_by_merge will remain
4. The D_COUNT makes sure that only Unique Ticket IDs are counted in the metric.
The exclusion of a tag on your report therefore doesn't work, as step 4 is not taking in to account the unique ticket IDs. The solution (today) is to create the report with the separate tabs.
Keela Ross You need to make sure metric in your report includes the formula from above.
Thank you Kay Heunen! I'll definitely review and edit where needed.
Wouldn't adding a filter to exclude the Tag 'closed_by_merge' be as accurate in getting this job done?
Hi Ahmad,
Unfortunately, you can only exclude tickets closed by merge by following the recipe above.
Thanks all. So what do we do with the metric when it's completed? Do we have to add it to all the queries that have been created?
In our case we want to use the awesome default dashboards and queries but all those reports should exclude tickets with the closed_by_merge tag. So in this scenario do we:
Then all queries should show the correct 'filtered' data?
Seems like a lot of work. Surely filters, like a dashboard filter, should be able to do this? Otherwise why would I ever use a filter and, filtering results becomes a huge undertaking each time. Just can't wrap my head around filtering...and don't get me started on Drill-in :P
Thanks
Chad
I also created this custom metric in my reports but how to I utilize this so that it automatically removes these tickets from my retention rate for instance? I don't want the closed by merged tickets to be taken into consideration in retention since a dropped chat for instance will continue their retention when the customer returns to the chat.
Hi Chad,
You can create a custom attribute as described here: Using INCLUDE in a standard calculated attribute. You formula should look something like this:
You should then be able to add this custom attribute as a data filter in your dashboard, and filter all query widgets to exclude merged tickets.
Hi Jamie,
When you use the custom metric described in this guide in your query, then the results displayed in your report will already have the merged tickets excluded. If you have multiple metrics in your report, then it may be better to use a custom attribute filter, similar to the sample above.
Please sign in to leave a comment.