Reporting on tags is tricky in Insights. Tags do not function like other ticket properties, so it takes special steps to use them in reports. This tip will provide an overview of tag reporting in Insights and how to filter reports by tags. If you are looking for a more basic recipe for reporting on tags, see Reporting on ticket tags in our Support tech notes.
This tip contains the following sections:
About tag reporting
Tags are in a unique, many-to-many relationship with tickets. One tag can be on multiple tickets, and one ticket can contain multiple tags simultaneously. This is different from all other ticket properties. For example, a ticket can only be in one group at a time, but it could have 200 tags.
To manage that many-to-many relationship, tags are treated differently when they are imported into Insights. Instead of simply listing the tags associated with a ticket, a unique ID is created for each ticket/tag combination. This tag data is stored in its own dataset.
This means ticket/tag data is completely separate from other ticket properties, even though they are all associated with ticket ID.
For tag data to function in a report, the metric must have a connection to the special tags dataset. The default # Tickets metric below already does this:
- SELECT COUNT(Ticket Id, TicketTagId) WHERE Ticket Status <> Deleted AND Ticket Tag Deleted Flag <> true
For contrast, look at the default Full Resolution Time (min) [Mdn] metric below:
- SELECT MEDIAN(Full resolution time in minutes) WHERE Ticket Status <> Deleted
This metric has no connection to ticket tag data. If you use this default metric in your report, it will ignore any filters based on ticket tags.
If you want to report on tags consistently, you'll need to take a few extra steps (see Filtering reports by ticket tags).
Using custom fields as an alternative to tag reporting
Before jumping in, ask yourself if there is an easier method to get the same result. For some tags and workflows, it might be more effective to use a custom field rather than a tag.
For example, if you receive tickets from region 1, region 2, or region 3. One way to track this is to automatically add "region1," "region2," and "region3" tags when a ticket is created. That works well in Zendesk business rules, but it makes reporting more difficult. Since a ticket can only come from one region, you can create a custom drop-down field for it. You can then report on fields rather than tags.
Reports using custom fields are easier to filter. You can just use the field attribute, instead of going through all the required steps to report on ticket tags.
For more information see, Reporting on custom fields in Insights.
Filtering reports by ticket tags
As described before, tag data won't function in a report unless the metric connects to the tags dataset. The default # Tickets metric does this for tag inclusion, but to exclude tags from your report you will need to follow the steps outlined in this section. This section will instruct you on how to build these filters using a custom metric and a numeric range filter. For information on user and organization tags, see Reporting on user and organization tags in our support tech notes.
Step 1: Create metrics for each tag
To create metrics for tags:
- Enter in the following formula:
- SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = your_tag),0)
- Substitute your_tag with your desired tag.
This metric does two things. The inner part of the metric uses the default # Tickets metric to count all tickets with a certain tag. The outer part of the metric ("IFNULL ... 0") means that tickets without the tag are counted as 0 instead of null.
Please note, you can't just copy and paste in the metric editor. You need to select the items below from the Elements drop-down list:
- # Tickets is under Metrics
- Ticket Tag is under Attributes>Ticket Tags
- your_tag is under Attribute Values>Ticket Tags>Ticket Tag
If you want these custom metrics to be available for any report, check the Add to Global Metrics box.
Below are the custom metrics for VIP tickets that have not merged:
Step 2: Create numeric range filters
After you create your metrics, you can use them to filter your report. If you do not want to use numeric range filters to filter your report see, Adding tag filters within a custom metric.
To filter your report with custom metrics:
- Select the Filter panel.
- Select Numeric Range Filter.
- Under Select Attribute, select Ticket Id.
- Under Select Metric, select one of the custom metrics you created above.
- For the Range, set the drop-down to "is equal to."
- Enter a value of 1 or 0.
- If the metric is equal to 1, the report will only include tickets that have the tag.
- If the metric is equal to 0, the report will only include tickets that do not have the tag.
For this example report, tickets should have the "vip" tag, so the numeric range filter for the "vip" metric is set to equal 1.
For this example, tickets should not have the "closed_by_merge" tag, so the numeric range filter for the "closed_by_merge" metric is set to equal 0.
Step 3: Build your report as normal
After you add your filters, you can remove the custom metrics from your report. As long as the report is looking at ticket data, the filters should keep working.
This example is reporting on the full resolution time, using the default Full Resolution Time (min) [Mdn] metric under What.
The image below demonstrates a full resolution time without any filters:
With the filters in place, only the VIP tickets that have not merged will be included in the report. The image below demonstrates the change in results:
Adding tag filters within a custom metric
These numeric range filters will apply to the whole report, but that is not always ideal. For example, you might want to compare the VIP resolution time above to the overall resolution time. With report-level filters, you can't see both numbers in the same report at the same time.
In cases like this, you would need to build a new metric that includes the tag filter(s). This will vary a bit, depending on the metric. However, the tag filtering portion should look like this:
- SELECT metric WHERE (SELECT # Tickets with your_tag BY Ticket Id)=1
This piece does the same thing as the numeric range filter, but it works within a larger metric. That way, you can filter each metric individually.
In the example below, the "vip" and "closed_by_merge" tag filters have been combined with the Full Resolution Time metric:
Since the tag filters are built into the metric, the numeric range filters can be removed from the Filter panel of the report. This enables you to compare metrics with different filters.
For example, the image below shows the VIP resolution time and overall resolution time in the same chart with no filters:
When it comes to reporting, ticket tags are certainly complex. They have their own spot in the data model with complicated architecture to maintain their unique relationships.
Fortunately, the hard work is already done. After you create these metrics, you can apply them to any ticket-based report.
89 Comments
I can't really find an easy solution for my question.
I want to create a metric that checks for tickets that have these two tags: tag1 and tag2.
So i created "SELECT # Tickets WHERE Ticket Tag = tag 1 AND Ticket Tag = tag2" but this doesn't work.
What am i doing wrong?
Hi Cristian,
you have to write different select for each tag.
Hi Mindaugas,
You mean within 1 metric different selects?
Yes, you can do in one metric, or create in 3 separate metrics, important that for one tag one SELECT.
SELECT IFNULL((Select # Tickets Where Ticket Tag = {{TAG1}} ), 0)
SELECT IFNULL((Select # Tickets Where Ticket Tag = {{TAG2}} ), 0)
after when (first=1) and (second=1) = 1 else 0
Hi,
Thanks.
But i don't really get it to work :(
I have the following:
SELECT IFNULL((SELECT # Tickets where Ticket Tag = TAG1),0)
SELECT IFNULL((SELECT # Tickets where Ticket Tag = TAG2),0)
after when (first=1) and (second=1) = 1 else 0
But i get this error when i want to add it:
"Unexpected 'SELECT'. Expecting: ;"
The last part wasn't for copy/paste. In one metric it should look like this:
SELECT # Tickets WHERE
(SELECT IFNULL((SELECT # Tickets where Ticket Tag = {tag1}),0))=1
AND
(SELECT IFNULL((SELECT # Tickets where Ticket Tag = {tag2}),0))=1
Hi everybody,
Is there any possibility to create a report based on Ticket Tag where to count all the Tickets that have a certain word included?
I tried the following and it brings dates but not the needed ones.
Basically i need a dashboard in Zendesk that will show a count on subcategories. I didn't find a way using the categories label and that is why i am trying to use the Ticket Tags with all the correspondents that they have in subcategories.
Is it possible to obtain somehow a subcategory report?
Thanks a lot!
Violeta
Hi Violeta! It is possible to report on ticket tags based on text strings. You're on the right track, but you're missing a couple key details.
First, you can't just use SELECT COUNT (Ticket Id) for this type of metric. Tag data is stored in its own dataset, and the metric must define the connection that dataset. This relationship is already defined in the default # Tickets metric.
The article above covers the relationships between the datasets and how to use # Tickets in a filtering metric. We also have a recipe that covers reporting on tag labels here: Insights recipe: Reporting on multi-select fields using tags. (If you're not using a multi-select field, you can skip to the tag label metric part of the recipe.)
If you're using a dropdown field with nested values, you can use text strings with the field value instead of the tags. That would give you more flexibility in a larger report. We have details for that approach here: Insights recipe: Reporting on nested drop-down fields.
I hope this helps! Happy reporting!
Thank you very much for your fast response Amy.
Regarding the nested fields, i already create a report with metrics for each one of the categories and yes it is useful but it only tells me: How many Subcategories are included in each Category (please see the pic below).
The view that i need now is regarding the number of tickets where it was used a subcategory that contains the word "Refund" without taking into account the Category. I might need to mention that i have the same subcategories in 15 categories and i want to count all that subcategories :).
Thanks,
Violeta
Hi again,
It's fine i created the report on subcategories as i need with this one:
for each one of my subcategories.
Thank you very much. I am so happy :D
Have a nice day!
Violeta
Hi Violeta,
Should be something like that:
SELECT IFNULL((Select # Tickets Where Ticket Tag [Ticket Tag] ILIKE "%refund%" ), 0)
Yes Mindaugas that is what i used (i corrected the message from above). Thanks :)
Hello everyone,
It's actually a little off from the topic.
Is there a way i could get the total time the agents spent on the ticket/s? and also get the total time the agents worked throughout their shift in zendesk?
Hi everyone,
I'm trying to look up the count of tickets/chats that have two (in this case) specific tags.
Reading the comments made me try:
But I keep getting the error:
I actually get that error no matter which metric I try.
Karsten
I know this is not very intuitive, but you cannot just paste the code for the custom metric into the editor. Instead, you must select each item from the element window on the right hand side.
You can see an example here.
Hi, I'm curious if the comment made here by Martin was still relevant:
"Can you track the time in (days or hours) from when the specific tag was added and report it? If not, what would be a work around? I am trying to create a report to see how long a ticket "sits" in a specific stage. Thanks!"
My support team will work as Tier 1 & Tier 2. Tier 3 will be one of three different organizations within my company. I want to report how long a ticket "sits" at any given state. (i.e. time spent with engineering to close)
Instead of reporting on tags (we currently report off jira_escalated when tickets are escalated via the JIRA integration), we should create custom fields? And then track the time between events?
Hi Lucas! Tags are not connected to ticket events, so there's no way to see when a tag was added or removed. You can only report on whether tags are currently present or not.
In an escalation workflow, like Tier 1 to Tier 2 to Tier 3, I recommend using ticket groups. Groups give you more routing options within the Zendesk Support interface (including sorting your agents and controlling permissions), and they allow you to report on how and when the group changed.
However you approach your workflow, though, you'll need to capture it in a synced ticket field if timing is important. Since you're working with tags, the smoothest transition would be with a checkbox or dropdown field.
Once you've moved to a ticket field, you have a couple options for finding durations:
I hope this helps! Happy reporting!
Thanks Amy!
Some of these "Tiers" aren't in Zendesk (Engineering, Sales teams, etc.). We'l be using T1 & T2 as routers to specific areas, so I think I'll incorporate a drop-down in this case.
So, let's say I want to measure length of time from a Tier change (drop-down selection) to close, would that be two events recipe?
Hi Lucas,
It's complicated but you can count also the tags. You need to find a Timestamp MIN update where ticket has that TAG, after just count difference between two timestamps. Of course it's easier to count by adding ticket fields values, we updating some of ticket fields in background without adding them into ticket form.
Hi Lucas! For the use case you describe, that would use the duration between two events recipe.
You'd look for the first time the dropdown changed to (or from) a certain value, then compare it with the solve timestamp. Fortunately, there's a default fact for the solve - Ticket solved at (minutes) - which should make that part of the metric easier. (Defining a solve by event is surprisingly tricky.)
Keep in mind, if you want to see durations within your "Tier" dropdown field, use the "duration of a text field" recipe. It's much easier overall, and it's more dynamic to field changes. You only need to use the two timestamps version when you compare the field with the status (or some other change).
I hope this helps! Happy reporting!
Hi everyone.
Is there a way to get a list of tickets by using an events-based metric but then encapsulate it in another metric so we can use "ticket tag" in the "How" tab?
For instance, I have this metric that basically gives me all closed tickets with certain specific characteristics (it's not the best example as I know I could select the same tickets using a metric that doesn't use events but please forget about that for now, it's for other reasons):
This metric allows me to have a list of ticket IDs and use it in a table report to list them all or to show the total count. However, what I need is to take those tickets and produce a report that shows me how many of them have specific tags. Much like this:
So what I tried (doesn't work - report is not computable or similar...) is to have a metric that encapsulates the initial one like this:
My idea was to use "# Tickets" (that allows "Ticket Tag" to be used in the "How" tab) but using the initial events-based metric as a filter so that only the ticket list I'm interested in would be shown in the report.
But I can't get it to work.
Is there something I'm doing wrong? A better approach? Or is it just impossible?
Thank you.
Hi Carlos! You're on the right track here. The conflict comes from that "BY Ticket Id" element - it pulls the metric back out of the ticket tag dataset. You need to get everything to TicketTagId to slice by Ticket Tag under HOW.
Try making this adjustment:
In my tests, this allowed me to add Ticket Tag under HOW and see how many tickets had each tag.
This approach works because the Tickets dataset (BY Ticket Id) is directly connected to the tags dataset (BY TicketTagId), so you can shift any ticket metric up to the tags dataset.
You may need one extra piece, though. As it is, this report would find tickets that are counted in your "BASE LOGIC" metric, then show you ALL tags from those tickets. It wouldn't be limited to the tags you have in the #Tickets - SCOPE (SF) filter.
If you want to limit the report to just those tags, you need to add them to this metric as well:
I hope this helps! Happy reporting!
Hi @Amy,
Is there any way to report on ticket updates with one or more tags? Looks like Ticket Tag attribute should lock the data on Ticket level without breaking down further to Update level.
Hi Andrei! Tags are indeed locked to the ticket as a whole; they are not connected to ticket events. There is no way to aggregate tag data by ticket update or report on when a tag was added or removed. You can only report on whether a tag was ever present on a ticket, and whether that tag is still present now.
If you need to report on changes within individual updates, you'll need to use ticket fields instead. As long as it isn't a multi-line text field or a multi-select field (which don't sync), you can report in individual changes using the events model.
I hope this helps! Happy reporting!
Hi Amy,
yes, this answers my question completely! Thank you.
Hi Amy and Everyone,
For my Team, I have now a process where they have to add 2 tags to a ticket when a customer contacts us for an article damaged:
I am trying to create a table with 2 entries which shows the numbers of tickets tagged with every possible combinations of both tag values (#occurences of article 1 with damage 1, #occurences of article 1 with damage 2, etc.)
This is ideally how it could look like:
I have read through the questions asked before and I could not quite figure the way to do it. The only way I can think of would be to create a metric for every possible combination (Article + Damage type) but that would take a huge time.
What would be the best way to get to the result? Thank you so much.
Sébastien
Hi.
I have a simple filter to get all the tickets that have the "reqtype_task_outbound" tag. Should be pretty straightforward (or so I thought):
SELECT IFNULL( (SELECT # Tickets WHERE Ticket Tag = reqtype_task_outbound),0)
I named this metric "filter 3".
And then I use this "filter 3" metric on a report:
So, basically what I expected was to get all the tickets that have that tag. However, although it does return a lot of them that do have the tag, I detected at least 20 that have the tag and don't show up in the results.
Am I doing something wrong?
Thank you.
Hi Amy,
I'm trying to the # solved tickets with a specific tag but the numbers don't really make sense comparing them with all tickets solved. Is there anything I'm doing incorrectly?
Thanks,
Hi Andrea! The issue in your screenshot is the # Tickets Solved metric. That metric finds tickets based on the solve event, and it's designed to work in an event-based report. It isn't connected to ticket tags, so it won't work in this construction.
For your use case, you'll need to stick with # Tickets and filter for solved/closed status in the report, or you'll need to use the # Solved Tickets metric in your filter. The # Solved Tickets metric finds tickets based on their current status, so it does play nice with tags.
We have more information in this article on the difference between # Solved Tickets and # Tickets Solved.
I hope this helps! Happy reporting!
Please sign in to leave a comment.