Insights tag reporting: Reporting on tickets with one or more tags (Professional and Enterprise) Follow

professional enterprise plans

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 IdTicketTagId) WHERE Ticket Status <> Deleted AND Ticket Tag Deleted Flag <> true

The # Tickets metric counts tickets with their tag data and excludes deleted tickets and deleted tags. If you use # Tickets in your report, tags should work like any other attribute.

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, but most other metrics do not connect. Rather than customizing all your metrics, you can build tag filters using the # Tickets metric, even if you don't need to use that metric in your final report. 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 

The examples below will report on full resolution time for VIP tickets that have not merged. These are tickets that have a "vip" tag, but do not have the "closed_by_merge" tag. You can substitute in your own tags, as long as you follow the same basic formula.
 
Note: These metrics use the Advanced Metric Editor. For more information, check out GoodData's video on How to create a custom metric.

To create metrics for tags:

  1. Enter in the following formula: 
    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = your_tag),0)
  2. 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:

  1. Select the Filter panel. 
  2. Select Numeric Range Filter.
  3. Under Select Attribute, select Ticket Id.
  4. Under Select Metric, select one of the custom metrics you created above. 
  5. For the Range, set the drop-down to "is equal to."
  6. 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. 

 

 

Have more questions? Submit a request

Comments

  • 0

    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.

  • 0

    Hi Zach,

    The numeric range filter is basically forcing the metric to run for each Ticket Id and then seeing if that ticket matches the conditions (counting "1" ticket if it has that tag). To get this working within a metric we need to set up this as a filter clause using MAQL:

    SELECT # Replies [Avg] WHERE (SELECT (SELECT # Tickets WHERE Ticket Tag = billing) BY Ticket Id) = 1

    Replace "billing" with whatever tag you want to report on. I believe this should work but let me know if you run into any snags.

  • 0

    Hi There,

    How would I create a metric that only returns tickets that have two specific tags? I too am trying to avoid using filters for this.

    Edited by Sean Cuevas
  • 0

    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.

  • 0

    Perfect. Thank you Joseph.

  • 0

    This is great! 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!

    Edited by Martin Siwy
  • 1

    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

  • 2

    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! 

     

  • 0

    I am trying to create a metric to report on # of tickets with a specific tag.

    I am going to Manager > add metric > custom metric

    I am typing in SELECT IFNULL((SELECT ... and then going to the right hand side and selecting Metrics > # tickets, but I am getting the following error message:

    Unexpected end of string. Expecting one of: ELEM_IDENTIFIER, FUNCNAME1, NEXT, OBJECT, STRING, -, RANKFUNCNAME, IF, PREVIOUS, FUNCNAMEN, COUNT, RUNFUNCNAME, NUMBER, IDENTIFIER, FUNCNAME2, CASE, FUNCNAME12, PERCENTILE, TEXT_ELEM, THIS, (
     
    Any input?
  • 0

    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!

  • 0

    @Amy - that worked! Thanks so much!

  • 0

    I'm trying the same thing Sean mentioned earlier, creating a metric for tickets with two specific tags. I used the steps Joseph lists and I'm returning no results. Has anyone verified how to do this?

  • 0

    Hi Travis! Those steps should work. We've also updated this article since those comments were posted. There are screenshots above showing very similar metric recipes.

    There are a lot of moving parts in a recipe like this. The results also depend on your account's data. I'm going to start up a ticket for you, so we can take a closer look at your report so far.

    Watch for my email, and happy reporting!

  • 0

    Hey guys,

    I wondered whether you could help with something I'm really struggling with or even if you can tell me whether it's possible using Insights at all (we're on the Professional plan).

    I would like to be able to report on FRT (business hours) by tag.

    Some of our incoming tickets are auto-tagged with 'subscribed' upon entering Zendesk - ideally we'd like to see that these tickets are being answered more quickly than tickets without a tag (so the FRT would be lower).

    Logically I would have thought you'd be able to select the pre-made FRT (business hours) metric in the WHAT, select the time scale in the HOW and then FILTER by tag. However, this doesn't seem to work like that. I couldn't see to create what I'm after using the metrics already set up, so I built out some custom metrics.

    The standard metric (First Reply Time (hrs) [Mdn]) that is already built into Insights has this SQL:

    SELECT MEDIAN(First reply time in minutes/60) WHERE Ticket Status <> Deleted

    I tried to create custom FRT metrics for tickets where the tag was 'subscribed' and for tickets where the tag was not 'subscribed' i.e. no tag. As below:

    SELECT MEDIAN(First reply time in minutes within business hours/60)WHERE Ticket Tag = subscribed

    AND

    SELECT MEDIAN(First reply time in minutes within business hours/60) WHERE Ticket Tag <> subscribed

    I then set up a report with:

    WHAT - the two custom metrics above

    HOW - Date (Ticket Created)

    FILTER - Date (Ticket Created) is the last 11 days

    I did a controlled day where I responded to the tickets with the 'subscribed' tag in under an hour and responded to the tickets without a tag in over 2 hours.

    The results however were identical for each metric and seemed to draw on the FRT metric without the 'subscribed' tag. You can see this on the date of the 3.3.2017.

    Does anyone have any suggestions as to how I can get around this issue. It's creating a fairly large blockage in our work. If it's not possible at all in Insights, even that would be great to know so we can start exploring other methods and tools.

    Best

    Sophie

  • 0

    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!

  • 0

    • SELECT COUNT(Ticket IdTicketTagId) WHERE Ticket Status <> Deleted AND Ticket Tag Deleted Flag <> true

    I use the fiter "ticket tag" and It did not work

  • 0

    Hi Linda! Tags are not like other attributes. For other attributes, tickets can only have one value at a time. It's easy to see whether a ticket is in, say, Pending status -- the ticket only has one status, and it either is or isn't "Pending."

    Tags are different. Tickets can have any number of tags at the same time. That makes things quite a bit more complicated. A ticket may have one of the tags from your filter, but not another. There are lot of ways to handle those situations, and Insights doesn't know which one you need.

    Because of all this, ticket tags do not consistently work in a dashboard filter like the one in your screenshot. Every single report would need to have metrics that accurately connect to tag data, and even then, there would be a lot of exceptions.

    We do not recommend putting a tag filter on a dashboard. If you want to use tags, you must use custom tag filtering metrics within your reports. Please follow the recipe above to filter your reports.

    I hope this helps! Happy reporting!

Please sign in to leave a comment.

Powered by Zendesk