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

Have more questions? Submit a request

87 Comments

  • Amy Dee
    Comment actions Permalink

    Hi Fanny! It looks like you have an extra parenthesis. There are three open parentheses after SELECT IFNULL (((, but only two closed ones at the end of the metric. Remove one of the parentheses at the start, and you should be good.

    I hope this helps! Happy reporting!

    0
  • Mindaugas Verkys
    Comment actions Permalink

    @Fanny, when you open you have to close the query. 

    0
  • Bora
    Comment actions Permalink

    Hi there! - I've followed the instructions above, and added the "*1 trick" to the end of of the query so I can report on the # of Tickets that don't include the tags (as I saw recommended earlier in this thread). With or without the *1 I'm encountering the same error message: 


    My question is, what am I doing wrong here? And is this the best way to report on # Tickets that don't include certain tags? If not, what do you suggest?

    Thanks so much for your help!

     

    0
  • Amy Dee
    Comment actions Permalink

    Hi Bora! Since you have a list of tags, you need to use IN instead of =. That's why the metric editor is throwing an error; it only expects one value after the = sign, so it gets stuck on that first comma.

    Try this format instead. (Watch those parentheses!)

    • SELECT INFULL((SELECT # Tickets WHERE Ticket Tag IN (tag1tag2tag3tag4, ... tagX)), 0)

    This metric will count a ticket if it has at least one of the listed tags. If a ticket does not have any of the listed tags, the metric will return a 0. It's great for excluding a large set of tags all at once.

    I hope this helps! Happy reporting!

    0
  • Bora
    Comment actions Permalink

    Hi Amy, 

    Thanks for the response, but I want to count all tickets that DONT have any of the tags listed. 

    I tried doing :

    • SELECT INFULL((SELECT # Tickets WHERE Ticket Tag NOT IN (tag1tag2tag3tag4, ... tagX)), 0)

    But apparently it's not that simple. What would the correct query be?

    0
  • Amy Dee
    Comment actions Permalink

    Hi Bora! Tags are unusual in the data model. Any number of tags can be on a ticket at the same time, and there's a placeholder value for when there aren't any tags on a ticket yet. This means that almost every ticket will have at least one record for Ticket Tag that is not the one you're looking for. You can't just say "Ticket Tag is not" in a filter - it will count almost everything.

    Instead, you need to create a metric that says Ticket Tag IN (tag1tag2, ...). This means the metric will count a ticket if it DOES have any one of the tags in the list. If a ticket does not have any of the tags, the "IFNULL" part means the metric will return a 0.

    Next, you need to use that metric to build a numeric range filter. The filter can be applied to the report under the FILTER section, or it can be built into a larger metric. Either way, you can use the filter to include or exclude tickets based on the tags.

    If you set the filter equal to 1, then the report will only include tickets that have at least one of the tags. If you set the filter equal to 0, then the report will only include tickets that don't have any of the tags. 

    There are detailed examples and screenshots in the "Step 2: Create numeric range filters" section above. 

    I hope this helps! Happy reporting!

    0
  • Nimrod Barnea
    Comment actions Permalink

    Hi! I'm trying to a list of tags sorted by first resolve time and final resolve time but can't seem to get it to work. How would you recommend doing that? 

    0
  • Jessie Schutz
    Comment actions Permalink

    Hey Nimrod! I'll see if one of our Community Moderators can help with this. Stand by!

    0
  • Alex Aguilar
    Comment actions Permalink

    Is there a way to see when the tag was added to the ticket?

    0
  • Nimrod Barnea
    Comment actions Permalink

    Hi Jessie! Any update on my request?

    0
  • Amy Dee
    Comment actions Permalink

    @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!

    0
  • Nimrod Barnea
    Comment actions Permalink

    @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!

    0
  • Amy Dee
    Comment actions Permalink

    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!

    1
  • Eduardo Franklin Soares Junior
    Comment actions Permalink

    Hello, Amy!

    Firstly, thank you for replying all of the users having issues with this carefully put walkthrough. I know it is not easy to adapt to every company's reality remotely.


    Second, English is not my first language so sorry if I cannot make myself undestandable.


    Now, with that out of the way, lets talk some Reporting. Here in my company, we have the agents tag the #Reopened Ticket with 3 tags:

    thankyou_reopening - Self Explanatory
    valid_reopening -
    The solution was not effective
    invalid_reopening -
    The customer reopened the ticket without a valid reason, could be asking for other ticket's priorization, etc.

    Today, to cross those informations with the agents name, I export [#Ticket, #Assignee] from tickets reopened and then [#Ticket, #Ticket tag] where the tickets contain the tags above. Is it possible to get that data in a single insight? I stumbled into this article while searching for that, tried to follow your step-by-step but I cannot get it to work. All I want is [#Ticket, #Assignee(History),#Ticket Tag (above tags)] so I can provide my Team Leaders that information to help them develop the agents.

    I'm couting on you darling!!

    0
  • Larry Click
    Comment actions Permalink

    How would I do a report for the top 10 tags over a timeframe?

    0
  • Shannon Ramsay
    Comment actions Permalink

    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

    0
  • Alex Aguilar
    Comment actions Permalink

    @ 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.

    1
  • Jessie Schutz
    Comment actions Permalink

    Hey Eduardo!

    Can you clarify what you mean by "a single insight"? Do you mean a single report, or a single metric?

    0
  • Jessie Schutz
    Comment actions Permalink

    Hi Larry! 

    Let me check with some of the Insights experts amongst our Community Moderators to see if someone can help you with this. Stand by!

    0
  • Eduardo Franklin Soares Junior
    Comment actions Permalink

    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!

    1
  • Nicole - Community Manager
    Comment actions Permalink

    Glad to hear you were able to get what you needed, Eduardo! Thanks for sharing your answer. 

    0
  • Kaitlyn Young
    Comment actions Permalink

    Hi all,

     

    Are you able to filter out merged tickets from SLA reporting?  I tried the above custom metric, but it doesn't seem to work in this report.  I did try with '# Tickets,' but that was no computable.

    0
  • Amy Dee
    Comment actions Permalink

    Hi Kaitlyn! This type of reporting gets tricky. Tag data and SLA data are both connected to ticket data, but they are all stored in separate datasets. The default # Tickets metric connects tickets to tag data, while the default # SLA tickets metric connects tickets with SLA data.

    Displaying all three types of data in the same table would take some elaborate custom metrics. Fortunately, you don't need to do that here.

    If you're just trying to exclude tickets with the closed_by_merge plan from your report, you can use a numeric range filter:

    1. Create the tag filtering metric using the # Tickets metric. Make sure you create it as a global metric.
    2. Remove the tag filtering metric from WHAT. This was the problem before; you can't display it alongside an SLA metric without a lot of extra steps.
    3. Go to FILTER and add a Numeric Range Filter: Ticket Id where Tickets with closed_by_merge tag is equal to 0.

    This means the report as a whole will use the tag metric to filter for tickets without the merge tag. The results are based on the ticket ID. The SLA metrics are all connected to Ticket Id as well, so they should respect the filter.

    I hope this helps! Happy reporting!

    0
  • Elene PECK
    Comment actions Permalink

    Hi,

    For ticket tag, understand we can count the tickets containing the tag using below:

    • SELECT INFULL((SELECT # Tickets WHERE Ticket Tag IN (tag1tag2tag3tag4, ... tagX)), 0)

    I would like to display tag1, tag2, etc in a field in report. Example:

    Ticket id    Ticket Tag      Value

    123            tag1               10

    134            tag2               15

    Is this possible and how can it be done?

    Thanks!

    0
  • Amy Dee
    Comment actions Permalink

    Hi Elene! You can display tags in a report, as long as you use a metric with # Tickets in it.

    For example, if you put your tag filtering metric under WHAT and Ticket Tag under HOW, you'll see how many tickets have each tag from your list. 

    I'm not sure where the "Value" column in your example is supposed to come from, though. If you have Ticket Id and Ticket Tag, the value will always be 1 or 0 -- either the ticket has that tag or it doesn't. You'd probably see something like this:

    Ticket Id     Ticket Tag     Value 
    123              tag1               1
                        tag3               1
    134              tag2               1
    145              tag3               1

    I hope this helps! Happy reporting!

    0
  • Stefan Mooha
    Comment actions Permalink

    Hey guys!

    I am trying to setup a custom metric to track NEW, OPEN & PENDING tickets with a particular tag.

    I can track all current tickets that ever had the tag, but i just want currently actionable tickets :) This is as close as i can get, before it stops working :/ Any ideas?

    In the above image, it works very accurately using OPEN, but NEW gives no results. Even though I can see around 40 tickets that should appear, with creations dates that should be appearing.

    Any ideas what could be going wrong (and how to change it so that I can search for all three status types in one metric ^^)

    Any help is much appreciated guys, thanks in advance! :D

    Kind regards,

    Stef

    0
  • Melanie Leu
    Comment actions Permalink

    Hi Stef,

     

    For including more statuses you can try to use ...Ticket Status IN(New, Open, Pending)...

    How do your tickets get the tag "at_basic" while they are in 'New' status? That might have an influence on why the metric works or doesn't work. Is it really a 'Ticket Tag' or does it come from a Form or Custom Metric...?

    I hope this helps a bit!

    Best wishes,

    Melanie

    0
  • Stefan Mooha
    Comment actions Permalink

    Heya Melanie! Thanks for getting back to me :)

    The tag is added manually from a ticket field, after ticket creation. 

    Using a custom tracker on Zendesk I can see all tickets with that tag, and there are tons of NEW tickets sitting there, yet the Metric found nothing :/ But was very accurate for OPEN! 

    Finally, when i used your update, im getting another error message: 


    I feel as though I have made a very rookie mistake here :P Any ideas? 

    Much appreciated! Thanks again! :)

    0
  • Ryan Mitchell
    Comment actions Permalink

    Hey All,

    I am trying to create a metric that will show the number of tickets that have one of or a subset of possible tags. I thought I followed the example fairly well, including changing the = to IN but I am consistently getting an error. Both my formula and a capture of the error are below. 

    0
  • Amy Dee
    Comment actions Permalink

    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!

    0

Please sign in to leave a comment.

Powered by Zendesk