Reporting on ticket tags

Have more questions? Submit a request

79 Comments

  • Antonio King
    Comment actions Permalink

    Tried to build out a metric around tags as follows and got this error. For Zendesk being built on being beautifully simple, reporting on tags is a nightmarishly difficult.

     

    2
  • Amy Dee
    Comment actions Permalink

    Hi Jamie! At first glance, I see one big issue here.

    You shouldn't leave the numeric range filter undefined. Instead, make sure you specify Ticket Id as the attribute. That way, Insights knows how to apply the filter to the rest of the report. In this case, it would only allow tickets (by their ID) that return a 0 in the tag metric.

    If you're still seeing odd behavior, even with # Tickets in the tag metric and Ticket Id in the report filter, then I recommend submitting a ticket to our support team. We may need to take a closer look.

    I hope this helps! Happy reporting!

    2
  • Thomas
    Comment actions Permalink

    What if I want a metric to report on when two or more tags are present on a single ticket? For example, I want to report on the number of tickets that deal with Appointment and Questions as well as Appointment and Bugs? I tried this, but did not get the results I was hoping for:

    SELECT IFNULL ((SELECT # Tickets WHERE Ticket Tag [Ticket Tag] = questions) AND (SELECT # Tickets WHERE Ticket Tag [Ticket Tag] = appointments),0)

    I also tried SELECT IFNULL ((SELECT # Tickets WHERE Ticket Tag [Ticket Tag] = questions AND appointments),0)

    I'm stumped and frustrated!

     

    1
  • Amy Dee
    Comment actions Permalink

    Hi Jamie! I see the issue here. The # Tickets Created metric is looking for ticket creation events. It is not connected to tag data, so it will not work in this metric.

    You need to use the # Tickets metric for the tag filter. It is the only default metric that is correctly connected to tags.

    Fortunately, you do not need to display the tag filtering metric in your report. It just needs to exist for a report filter. (The layout is in the "Numeric Range Filter" section of the recipe above.) Once you have the report filter in place, it should work with any ticket-based metric.

    The rest of your recipe looks fine. Just use the updated tag metric under FILTER and your usual # Tickets Created metric under WHAT, and you should get the results you need.

    I hope this helps! Happy reporting!

    1
  • Amy Dee
    Comment actions Permalink

    Hi Nick! At first glance, the biggest difference between your custom metrics and the default # Tickets metric is the Ticket Tag Deleted Flag condition. The default metric excludes deleted tags, while your custom versions do not. If your workflow involves tags being added and later removed, it could lead to your metrics skewing high.

    Try replacing the SELECT COUNT(Ticket Id, TicketTagId) WHERE Ticket Status <> Deleted portion with just SELECT # Tickets WHERE Date (Ticket Created)... That should limit your report to tickets and tags that have not been deleted.

    I hope this helps! Happy reporting!

    1
  • Amy Dee
    Comment actions Permalink

    Hi Tom! It looks like there are a couple typos in the first nested metric: (SELETmy_tag_metric) by Ticket ID). You're missing a C in "SELECT," and there shouldn't be a parenthesis after "my_tag_metric." It should look like this: (SELECT my_tag_metric BY Ticket Id).

    You're also using the # Tickets Created metric here. That metric is based on ticket updates, and it does not connect to tag data. It's designed to let you report on ticket creations and solves in the same chart.

    If you're not combining different metrics like that, I recommend using # Tickets instead. That does connect to tag data, so it should be more consistent with your filters. You can still see when tickets were created by using Date (Ticket Created).

     

    What you're doing here should work. You can list each tag separately in its own metric. This is helpful if you only have a few tags, and you need to report on them individually as well.

    If you have a lot of tags that you want to exclude, there is another option. You could combine them in the filtering metric like this:

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag IN (tag1tag2tag3tag4)),0)

    If this metric counts a ticket, it means the ticket has at least one of the listed tags. If this metric does not count a ticket, it means the ticket doesn't have any of the tags. That means you could filter for this metric to equal 0, and you'd exclude all those tags at the same time.

    I hope this helps! Happy reporting!

    1
  • Jesse Dacumos
    Comment actions Permalink

    What would be the best way to work this into a CSAT report?

    I created the # of tickets by tag metric, but i can't seem to filter by it on a CSAT % report. 

    1
  • Nick
    Comment actions Permalink

    Cheers, Amy! That did the trick. Thanks so much for the help!

    1
  • Amy Dee
    Comment actions Permalink

    Hi Emma! This is whichever metric you want to see. For example, if you want to look at full resolution times for tickets that do not have a closed_by_merge tag, the metric would look like this:

    SELECT Full Resolution Time (hrs) [Mdn] WHERE (SELECT # Tickets with closed_by_merge tag BY Ticket Id) =0

    (You need to create the # Tickets with closed_by_merge tag separately.)

    This just means that you can nest a tag filtering metric in a larger metric. You can use that to compare tickets with and without a certain tag in the same chart.

    I hope this helps! Happy reporting!

    0
  • Mikael Hellström
    Comment actions Permalink

    Hello!

    I really like the guide and has just begun unraveling the more hidden potentials of Zendesk, and ofc I have encountered a little snag that I am wondering about.

    I have created a custom metric that contains a couple of custom metrics, the function is to filter out all tickets that contains a couple of custom tags.

    So, the "main metric" that holds the other ones works well, it contains everything I need. This little "snag" that I encountered is that I can´t render a report that sorts tickets on "ticket tag" instead of "ticket group" as an example (which works but doesn´t give me what I want).
    I might add that all the custom metrics are build on the "# Tickets" metrics, so this should work.

    As an example of what I want to do is shown below. The problem seems to be that I can´t render the report with "ticket tag" as a "How".

    "What"
    - satisfaction score

    "How"
    - ticket tag

    "Filters":
    - ticket tag where id is <the custom metric> is equal to 1
    - ticket tag where id is closed_by_merge is equal to 0

    So the question is, how do I solve this puzzle? The only solution that I have right now is to make a dashboard with one report per metric, which will take a boatload of time since I estimate to end up on 200 tags.

    Thanks
    Mikael

    0
  • Jesper
    Comment actions Permalink

    Hi Amy!

    I am trying to nest the tag filter in a custom metric as described in the article because I want several different metrics in the same report. However I'm always running in to the "Report not computable due to improper metric definition" error. What's causing it is that I want to group the result by Updater or Ticket Assignee (Historic) in the How-section.

    It's kind of weird, because if I choose WHAT: # Tickets solved, HOW: Updater, FILTER:Ticket Id where MyTagFilterMetric is equal to 1, it works fine.

    But as soon as I create the metric "SELECT # Tickets Solved WHERE (SELECT MyTagFilterMetric BY Ticket Id)=1" and group by Updater, I get the error message.

    I guess these setups aren't equivalent in every way, but since the connections between the datasets seems to exist, I think it should somehow be possible to create th report with the tag filter nested as well. I noticed the caveat about the template varying a bit depending on the metric, but I've tried all kinds of different variations now and can't get it to work so I'm hoping for some guidance on this!

    Thanks in advance,

    Jesper

    0
  • Amy Dee
    Comment actions Permalink

    Hi Jamie! That's awesome! I'm glad it's working for you.

    There is one odd behavior with nested metrics that may have had an impact here. When you nest a metric in a larger metric, Insights stores the full query of the nested metric at that time. It does not keep a live link to the nested metric.

    This is great for cases where you delete a metric without realizing it's in use elsewhere. Insights can keep using the original query in any larger metrics without any problem, even if the metric itself is gone.

    This is awkward for cases where you make changes to component metrics. Insights will still have the outdated version stored until you remove and re-add it in the larger metric (and save the change). That may have been what happened here, if you had an older version of the tag metric in your larger headline metric.

    Happy reporting!

    0
  • Linda-SI
    Comment actions Permalink

    SELECT metric WHERE(SELECT #Ticket with your_tag BY Ticket Id)= 1 

    what is  "#Ticket with your_tag"?

    where can I get it ?

    0
  • Amy Dee
    Comment actions Permalink

    Hi Thomas! There are a couple picky issues here, but you're on the right track.

    First, it looks like you used the Ticket Tag [Ticket Tag] Attribute Label, instead of the Ticket Tag Attribute. That will cause syntax issues. Make sure you're using the Attribute in this recipe.

    Second, a single metric is good for OR logic, or to exclude a large group of tags at once. It's not ideal for using AND logic to find multiple tags on the same ticket.

    If you're interested in AND logic, I recommend making separate filtering metrics for each tag:

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tagappointments),0)
    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = questions),0)

    You can put both of them in numeric ranges on the same report. That would find tickets that would be counted in both metrics, meaning the tickets have both tags. You can also combine them within one larger metric:

    • SELECT IFNULL((SELECT # Tickets WHERE (SELECT Tickets with appointments tag BY Ticket Id)=1 AND (SELECT Tickets with questions tag BY Ticket Id)=1),0)

    This copies the logic of multiple report filters: it only shows tickets that would be counted in both filtering metrics.

    It may be possible to combine both tag filters into one metric without going through these setup stages, but it would be exceptionally complex at best. The relationship between ticket and tag data is a bit rigid. It's easier to work with small building blocks than to build everything at once.

    I hope this helps! Happy reporting! 

    0
  • Carla
    Comment actions Permalink

    Hi Amy,

     

    I'm getting a similar error code to Michael's. Can you please take a look?

    0
  • Anoop Mehta
    Comment actions Permalink

    Thanks Amy - that helped a bunch!

    0
  • Andy L
    Comment actions Permalink

    @William Macken

    Thank you for your input, it does make sense and i was able to fix the ones related to global metric. The syncing once per day is not as convenient and i didn't expect that to be an Enterprise feature but it is what it is, I will just live with it. :) Cheers.

    0
  • John Ferreira
    Comment actions Permalink

    Hello,

     

    I am getting an error on my syntax but I can not find the problem.

     

    Can you help me?

     

     

    Thank you,

     

    John

    0
  • Saz Ehibor-Smith
    Comment actions Permalink

    Hi Amy,

    Thank you in advance. 

    I am having difficulty following the 2nd stage of this. I think you started to reference it in this comment but I am not following the #Ticket with your_tag portion of this.

    I have created the initial metric to isolate the tag but no idea where I find the #Ticket with your_tag. I can find the metric created. Just not with the #Ticket.

    Saz

    0
  • Saz Ehibor-Smith
    Comment actions Permalink

    Thanks Amy

    0
  • Michael Thingmand
    Comment actions Permalink

    When I try the example and has selected my tag via Attribute ValuesAttribute Values (it's orange) I still gets this error:

    SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = kundeservice_efterlysning),0)
     
    Unexpected end of string. Expecting one of: ELEM_IDENTIFIER, FUNCNAME1, NEXT, OBJECT, STRING, -, ZERO, RANKFUNCNAME, IF, PREVIOUS, FUNCNAMEN, COUNT, RUNFUNCNAME, REAL, IDENTIFIER, FUNCNAME2, CASE, PERCENTILE, FUNCNAME12, TEXT_ELEM, THIS, NATURAL, REPORT, (, IFNULL
    0
  • Amy Dee
    Comment actions Permalink

    Hi Chris! It looks like you ultimately want to see how many tickets are solved with one of those macro tags, compared to the total number of tickets solved. Is that correct?

    If so, you'll need to start by building a tag-filtering metric, then nesting it in your report metrics. A Ticket Tag IS filter on the report is inconsistent at best, especially when you mix and match metrics like this. (# Tickets Solved does not connect to tags, so it will break your filtering.) It's better to be explicit within the metrics.

    Start with a metric matching the recipe above:

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag IN (macro_tag1macro_tag2, [etc])),0)

    If all your macro tags start or end with the same string (like "macro"), you can use the text string in your metric instead of selecting each individual value. This recipe uses the same technique: Reporting on multi-select fields with tags.

    From there, you can create comparison metrics like this - 

    • SELECT( (SELECT # Solved Tickets WHERE (SELECT macro filter metric BY Ticket ID) =1) / # Solved Tickets )

    This should show you solved tickets with at least one of the macro tags divided by all solved tickets. Since the numerator and denominator use the same base metric, it should give you more consistent results in the report.

    Please note - these examples are provided for demonstration purposes only, to give you a starting point. I can't guarantee them for all use cases, and I highly recommend that you audit your results. 

    I hope this helps! Happy reporting!

    0
  • Amy Dee
    Comment actions Permalink

    Hi Anoop! Insights is not a real-time platform. There will be a delay between an event on a ticket and that event syncing to Insights.

    For Professional accounts, Insights syncs once per day. For Enterprise syncs, Insights syncs hourly (based on the time of the last sync, so they're not perfectly even). If you recently added tags to your tickets, give the data a chance to sync before you check the metrics.

    I hope this helps! Happy reporting!

    0
  • Emma MacDonald
    Comment actions Permalink

    Hi Amy Dee,

    Yes! I was able to get #2 to work for me. I tried to submit a ticket, but the person who replied gave a different answer that wasn't helpful.

    Is there a way to create a similar alternative metric for # Public Comments? It makes sense the way you did it for Satisfaction Score, but I can't figure it out for other metrics that aren't a %. I need to be able to report on # Public Comments for tickets with certain tags as well. 

    0
  • Christopher C.
    Comment actions Permalink

    Hello,

    I am trying to build a report to show our agents' macro usage per week.

    WHAT

    # Tickets

    # Tickets Solved

    # Custom Metric  (# Tickets / # Tickets Solved) to get percentage of ticket solved vs ticket solved with macro

    HOW

    # Ticket Assignee

    FILTER

    Ticket Tag is... (All Ticket tags associated with a macro) over 50 tags

    Week (Mon-Sun) (Ticket Solved) is Last Week

     

    But I still feel like the outcome is not correct. Any ideas how I can improve my report?

     

    Kind Regards,

    Chris

     

    0
  • Jamie McLaren
    Comment actions Permalink

    Hi Amy,

    Thanks very much for your response, that worked perfectly for the example report that I shared, it removed the 2 tickets that I know for a fact were "closed by merge".

    Unfortunately, I haven't been able to replicate this across my other reports, I've compared them side-by-side and can't see what fundamental difference there might be that makes them work differently.

    A further example of a report (line chart) that I'm struggling with is as follows:

    WHAT: #Tickets Created

    HOW: Week (Mon-Sun)/Year (Ticket Created)

    FILTER: Week (Mon-Sun)/Year (Ticket Created) is from 6 weeks ago to 1 week ago AND <attribute> where Tag Filter "closed_by_merge" is equal to 0 (this my tag filter, which now uses # Tickets rather than # Tickets Created)

    When I save this out I see only "No data match the filtering criteria" again, but it's almost exactly the same as the report discussed yesterday that I was able to successfully amend.  The only differences between the two are:

    1. Chart type (line graph vs. donut chart)
    2. The working chart has 2 attributes defined, the broken one only 1 of these

    Any idea where I'm going wrong?

    0
  • Amy Dee
    Comment actions Permalink

    Hi John! It looks like you typed in the name of your tag. You need to select it from the elements list on the right. It will be under Attribute ValuesTicket Tag. When you add it as an element, it will appear orange in the metric editor.

    I hope this helps! Happy reporting!

    0
  • Tom Ronen
    Comment actions Permalink

    Thanks a lot for this Amy.

    I think I will use this one:

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag IN (tag1tag2tag3tag4)),0)

    I need a report of tickets created (monthly breakdown) that ignores a bunch of tags.

    I hope this will do the trick.

    Thanks again,

    Tom

     

    0
  • Antonio King
    Comment actions Permalink

    Hmm, is it possible to set up a ticket to discuss this further? I think I have an understanding of what you're referencing, but I'm not sure I follow the "how-to" portion since this is unfamiliar territory. 

    0
  • Anastazie Sedlakova
    Comment actions Permalink

    Hello,

    want to create report on tag usage, but it doesn't seem to filter. For different tags it shows exactly the same numbers when I use filtering with custom metric (Ticket Id where macro_test1 is equal to 1).  If I use custom metric (Ticket Id where macro_test1 is equal to 0) and where I get following error: No data match the filtering criteria.

    Can you please help me to figure out where is the problem?

    Anastazie

     

    Details:

    What: # Tickets Solved

    How: Date (Ticket Solved)

    Filter: 1) Ticket Id where macro_test1 is equal to 1

    2) Date (Ticket Solved) is the last 32 days

    macro test metric contains following MAQL query:

    SELECT IFNULL((SELECT # Tickets Solved WHERE Ticket Tag = automat), 0)

     

    0

Please sign in to leave a comment.

Powered by Zendesk