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.
101 Comments
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
Hey everyone!
I just wanted to hop in here and let you know that we've revamped this article to include additional information and clarify some things. Be sure to give it a look!
Hi Nimrod! This type of reporting would likely require custom metrics and careful report construction. There may not be an easy answer here, since tags and resolution times aren't directly connected.
I'm going to move this conversation to a ticket. That will allow us to look at specific examples in your account and hopefully find an approach that fits your workflow.
Watch for my email, and happy reporting!
Hi Martin -
The "Events Dataset" is what allows you to report on when specific changes happened to a ticket, historically (as opposed to simply reporting on a ticket's present day state). Unfortunately, the Ticket Tags dataset is not connected to the Events dataset and therefore you cannot report on when a tag was added to or removed from a ticket.
The way to report on duration is to report on when a Ticket Field (numeric or text) changes - this could be anything from Assignee, Group, or Status to any of your Custom Fields. Here are 2 great resources for learning more on that:
Building Custom Metrics for the Events Model
Duration between two events in minutes
I hope this helps! If you have questions, just drop us a line at support@zendesk.com
Hi Jessie.
I was able to get a report that had the information I needed.
Basically, i wanted the #Ticket, #Reopens, TAG_1, TAG_2, TAG_3. IF the ticket has the tag, it is 1, if not, 0.
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!
@ Shannon
There's two ways to access your metric.
Via Manage tab
1. Select the "Manage" tab in insights (top navigation bar).
2. Select "Metrics" under the "Data" section on the left-hand side.
3. Locate the metric you created.
4. Select your metric to edit/move to another folder.
When creating a report:
1. Select the "What"
2. Find metric you created.
3. Select metric.
4. In the detail window, select "View Detail". This will bring you directly to the metric to edit, etc.
Hope that helps.
Hi Sophie! Ticket tags are not like other attributes. You can have any number of tags on a ticket at the same time, and you may need to report on any one of them. To make that possible, tag data is stored in a separate dataset.
This means you need to take special steps to use tags in a report. You need to use a metric that connects to the tag dataset, and you need to construct filters that work with that metric. The First reply time metrics do not have any connection to tag data on their own, so they will ignore all tag filters.
The article above goes into the theory behind tag reporting, and it shows some examples of metrics that do and do not connect to tag data. It also includes a recipe section describing how to build tag filtering metrics.
Go through the recipe section once again, and try to build the metrics from the example report. That should give you a better idea of how the pieces fit together, which should help you fine tune the results for your use case.
I hope this helps! Happy reporting!
@Alex - Unfortunately, no. Tag data is not connected to event data. You can only see whether a tag is currently present on a ticket or not. If the timing is important, I recommend using a tag-based ticket field instead. That way, you can report on the field events.
@Nimrod - How are you linking tags with resolution times? The resolution time is associated with the ticket, not any given tag.
For example, imagine Ticket 1 has tags A, B, and C, and Ticket 2 has tags C and D. Ticket 1 was resolved in 60 minutes, and Ticket 2 was resolved in 120 minutes.
How would tags A, B, C, and D look in your list? How would you sort them? Would each tag just use the same resolution time as its ticket? If so, how would you handle tag C, since it was on both tickets?
It should be possible to make a list of tags with some metric value next to them, and then to sort by that metric value. You just need to be very clear about how those pieces should be connected.
I hope this helps! Happy reporting!
Hi Amy,
Thank you so much for following up on my comment! I have a few questions since not the best in writing such queries.
You mentioned that I would have to list out each tag for the custom metric. Would that then mean the query should look something like this? Tag_d being the undesired tag.
SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = tag_a), 0) AND ((SELECT # Tickets WHERE Ticket Tag = tag_b),0) AND ((SELECT # Tickets WHERE Ticket Tag = tag_c),0) AND ((SELECT # Tickets WHERE Ticket Tag = tag_d),1)
Also, the numeric range would be ...
Ticket ID where Custom Metric Name is less than 1
Is there a way to see when the tag was added to the ticket?
Glad to hear you were able to get what you needed, Eduardo! Thanks for sharing your answer.
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 Justin! It looks like you clicked "Add" on the lower right. That actually tries to add the metric to your report. It's the last button you should click, once everything else is complete.
To add # Tickets (or any other element) to your report, you can double-click it in the list or click "Add Selected" on the top right. That should put the color-coded element in your metric and allow you to continue.
Happy Reporting!
To Stefan - Sorry about the long response time! The metric is missing a parenthesis between the ticket status list and the ",0". The parentheses there now are for the status list. You need another to contain the metric for the ifnull part.
To Ryan - The formatting looks good so far. There's just a typo at the start of the metric. It's supposed to be "IFNULL," but you have "INFULL." That's they it's getting stuck at an "unexpected I" - it doesn't recognize that word.
I hope this helps! Happy reporting!
@Fanny, when you open you have to close the query.
I use the fiter "ticket tag" and It did not work
Hey everyone,
Hopefully this is an easy question that I'm just overlooking the answer for. After creating a custom metric, is there any way to go back in and edit them? Such as renaming, change the folder they're stored in, etc? Even if its just a delete and remake, how to do that would be good too.
Thank you
I haven't read through this entire thread as it is very complicated and not clear that I can accomplish what I want. Over time, I have tagged tickets with the product name, section of the product where the problem exists, OS that the product is running on, type of problem, etc.
What I would like to do is to generate periodic reports that show, similar to ticket total graphs over the last 30 to 90 days,
"How many new tickets came in per day that were tagged "Windows" "installer" ?"
"How many new tickets came in per day that were tagged "Portal" "connections" ?"
etc. etc.
Not only "how many" but "what are they?" including the list of tags so I could see what other tags I had added and refine the results.
That's really my main focus at this point because I am the only support person actively engaging with end users, we don't currently use any kind of reporting that shows how effective the agents are or how long they take to resolve issues etc. etc.
I need to provide feedback to the engineering team that "the last release you put out caused an uptick in support because you broke something", etc.
Is this type of reporting possible?
Even if I get a table and have to export it to a spreadsheet to make a graph, it is OK.
Thanks.
Hi Amy,
Thanks for the response, but I want to count all tickets that DONT have any of the tags listed.
I tried doing :
But apparently it's not that simple. What would the correct query be?
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 Sean,
If you nest metrics you can make separate calculations on the same ticket, allowing you to check for combinations of tags:
SELECT # Tickets WHERE (SELECT # Tickets BY Ticket Id WHERE Ticket Tag = article_viewer) = 1 AND (SELECT # Tickets BY Ticket Id WHERE Ticket Tag = houston) = 1
I haven't had the opportunity to QA this extensively but from my preliminary testing it appears to work.
@Amy - Basically I'm trying to find out what issues take more time to resolve.
In your example, I would tags A and B have one data point and resolution time of 60 min. Tag C has two data points and average resolution time of 90 min.
Avg resolution time per issue (or tag) is the metric I'm searching for.
Thanks!
@Amy - that worked! Thanks so much!
Hi Jill! Hi Justin!
The default satisfaction metrics do not connect to ticket tags, so they will ignore Ticket Tag. Satisfaction data is pretty far from ticket tag data in the data model, so it would be very difficult to link them directly within one metric.
Instead, you'll need to create a tag filtering metric based on the # Tickets metric. # Tickets is connected to tag data, so it does respect tag filters.
Once you have a tag filtering metric, you can use that whole metric to filter your satisfaction report. As long as everything is based on ticket data, it should work.
There are examples and specific steps in the recipe section above.
I hope this helps! Happy reporting!
Is it possible to apply this filtration at the level of a custom metric, rather than applying the filter to the whole report?
I have several tags that I would like to compare on a single report, rather than creating a separate report for each tag. Essentially, I'm trying to find a way to slice # Replies [Avg] by a select number of tags.
Hi Mindaugas,
You mean within 1 metric different selects?
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!
How do we get access to this page as Professional users?
Hi Amy!
Thank you again for the insights here! I tried as suggested and it worked! Thank you so much!
Taline
Please sign in to leave a comment.