Insights recipe: Reporting on created and solved tickets Follow

Comments

33 comments

  • Avatar
    Guy Dee (Edited )

    @Patrick -

    Hello!

    Yes, you can capture the initial solve instead of the latest solve by building a custom metric. Fortunately, the required change is very simple: You need only to copy the default # Tickets Solved metric and change the SELECT MAX to SELECT MIN. This will cause the metric to return the very first date the ticket was solved, no matter how many times it has been reopened.

    For example, here's the default metric (I've highlighted the "SELECT MAX"): 

    Change this to "SELECT MIN": 

    Save your new metric with a name that makes it clear it's counting the initial resolution, not the latest resolution.

    I hope this helps!

    EDIT: 

    Sorry, I forgot to mention something important!

    If you'd like the metric to return the date of the first solve even if the ticket isn't solved anymore, one more change is needed, as this section of the metric will otherwise limit it to return only tickets that are currently Solved or Closed:

    This statement can be updated to include other statuses you'd like to include, or it can be removed entirely to include all statuses; there are a lot of right ways to handle it depending on your needs!

  • Avatar
    Heather R

    Hi Sunee,

    I know what you mean! Here's my go to for things like what you're looking for: The main View Only Insights reports with all the amazing tags and filters. Here's one that might work for the couple of scenarios you listed. PS. When you hover your mouse over the bars on the day by day, it will display the numbers so you don't have to estimate based on the graph...

  • Avatar
    Graeme Carmichael

    Sunee 

    Sorry to hear that you are struggling with reporting.  It does get easier with practice. 

    Although the Insights provided reports cannot be modified, you can open these reports to see how they are put together. You can also save as to create your own editable report. Changing your copy will not impact the Insights provided version. Often when we want a custom report it can be based on an original Insights report as a starting point. 

  • Avatar
    Patrick Feehan

    When I look at the Tickets Solved Metric I get one number and then a few days later the number has changed slightly, sometimes just by 1 or 2 but sometimes by 10 or 20. I think this is because Tickets that are counted when I first look are reopened and re-solved when I look again, so that ticket is now being counted in a different date. Is this correct?

    If so, is there a way to capture the number of solved tickets on a date that won't change as those cases are reopened? 

    I am trying to count the number of tickets that are solved on a specific date so we can project how many cases we will resolve vs. how many tickets will be created on any day, but it's hard to do it accurately if the tickets solved metric is changing.

  • Avatar
    Brent

    @Simon I've looked into this issue a little bit and I've determined it is an issue specific to your Insight project, so I'm going to make a separate ticket and work with you in there.

  • Avatar
    Shannon Lewis

    thanks Diogo.

  • Avatar
    Jessie - Community Manager

    Hi Boris!

    I'm going to check with some of our Community Moderators to see if they can help you out with this!

  • Avatar
    Ping Lu

    Hi,

    I'm new to Insight and trying to create a report on the number of tickets that are open by month/year, along with # tickets created and solved. The problem is that there might be no event happening on an open ticket throughout a month, thus the How of Date(Event) doesn't work. Any thoughts?

    Thanks

     

     

     

  • Avatar
    Rich

    I have another question now: I'm trying to create a metric that will show % of tickets more than 2 days old.

    This would be very easy with actual SQL but I'm at a complete loss how to build this metric in GoodData. Basically, I don't know how to determine the number of tickets meeting the criteria:

    - Not Closed, Solved, Deleted

    - More than 48 hours since the ticket was created

    Is there any concept of the current time available in GoodData?

    I found this "Fact" Ticket Created at (Minutes) but I'm not really sure what that means. And I would still need to know the current time to do any sort of calculation and logical decision based on the amount of time between created and "now".

    I have a feeling this is not going to be possible to do the way I am trying to do it. Does anybody have any ideas?

  • Avatar
    Sunee Lewitzky

    I might be missing something here, but here are my thoughts on Zendesk Reports so far.

    It's super great that ZD offers so much freedom to customize reports, but the process is super complicated. While they should keep the ability to create robust customizable reports, they should also have an option for people to QUICKLY and EASILY run simple basic reports, such as How many emails did I get in this specific day in May? How many in that specific week? How many emails do we have that are tagged with this specific tag?

    The purpose of my post is in hopes that I am missing something and that someone here can help guide me to easily and more quickly run BASIC reports (similar to what I could do with www.desk.com). Or, if there is no way, someone might pass this along as customer feedback.

  • Avatar
    Jessie - Community Manager

    Hi Lynette! 

    I'm going to check with our Community Moderators to see if they can help with this. Stand by!

  • Avatar
    Rich (Edited )

    I figured it out. Very simple!

    I just needed to wrap the Tickets Created metric in an IFNULL

    SELECT IFNULL(# Tickets Created,0)-IFNULL(# Tickets Solved, 0)
     
     
  • Avatar
    Sunee Lewitzky

    Thanks Heather and Graeme.

     

    What if you use two separate brands? The Overview page doesn't really work if you want to run a report just on a specific bran within your organization. Unless I am missing something?

  • Avatar
    Brian Manning

    @Intis- The '# Solved Tickets' metric described in this article isn't dependent on comments. It will pick up the last event where a ticket was set to solved regardless of the comments on the ticket.

    If you're having trouble with the behavior of your implementation of this metric let me know and I'll spin up a ticket for you.

    Cheers!

  • Avatar
    Rich (Edited )

    I'm trying to show "tickets change" in a report where I have tickets created and tickets solved by Group.

    I created a custom metric: select(# tickets created - # tickets solved)

    And I've added that metric to the below report in the rightmost column: #CREATED, #SOLVED, #CHANGE

    This works fine except for when no tickets were created for a particular group on the date selected.

    I think what happens is the # Created, even though it displays a 0 (zero) is actually NULL. And then NULL - 1 = NULL

    The desired result would be that I'm working with a count of the unique Ticket ID's whereby the Count of Ticket ID's for Tickets Created would be 0. And that way the arithmetic would be possible 0-1=-1.

    The custom metric calculation for Tickets Change seemed kind of workaround-ish to begin with and this problem with the NULL value exposes that. Does anybody have any suggestions or better approaches for what I'm trying to accomplish?

  • Avatar
    Rachael W

    I'm having the same trouble as Caleb, but I don't have the # Tickets Solved metric, only # Solved Tickets, which doesn't yield the results described above and still has Date (Event) greyed out. See attached photos 

  • Avatar
    Lynette

    I'm a bit confused. I'm able to create a report that shows tickets created over the past 12 months, but I'd like the average number of tickets, not all created. Is there a way to do that?

  • Avatar
    Jacob J Christensen

    Ah, OK. Maybe the # Tickets Solved was a custom metric, I seem to recall having to do that one manually. But the original article by Hammad doesn't seem to exits anymore. I don't know what the reason for that might be.

    My # Tickets Solved metric looks like this (take a deep breath):

    SELECT IFNULL(COUNT(Ticket Id, Ticket Text Field Change), 0) WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved AND Ticket Status IN (Solved, Closed) AND Date (Event) = (SELECT MAX(SELECT Date (Ticket Solved) BY Ticket Id) BY Ticket Id)

     

    Metric format: #.##0

    I find the easiest way to create a custom metric is by pasting in the whole text and then select the Attributes (purple) and attribute values (orange) one by one, and save.

    Sorry to Caleb if i missed what this was really about.

  • Avatar
    Ping Lu

    Hi Dennis,

    Thanks a lot for getting back to me. While trying the backlog dataset, we found it's hard to understand the relationship between backlog and other ticket counts like # Tickets Created / Solved / Deleted / Reopened. They don't seem to add up. It seems to add more complicity if we add ticket priority as a filter, as the backlog priority isn't necessarily the ticket priority as the latter is the current value.

    We will ask for more help by opening a ticket. Many thanks.

     

     

     

  • Avatar
    Caleb Strawn

    When I try to create this report, the "Date (Event)" is greyed out. When I force select it, the report won't pull up due to a "nonsensical data" error.  I'd like to build this report for the current week and for the current month but because I can't select any of the "event" based ways in the "how" section of the report builder. Looks like it's effectively forcing me to choose one or the other (ticket creation or ticket solution) to associate a timeframe to.

    Any help here is much appreciated!

  • Avatar
    Rich

    Thanks Graeme, this is very helpful. I'm very much aware that the data feed is not real time. My thinking was that I would be able to rationalize "Ok, so that's the % of tickets older than 48 hours as of 3 hours ago". 

    However, it seems there are other things I hadn't considered. I think your suggestions can help me get close enough to what I was trying for. Basically, average age of open tickets is not a helpful metric when one ticket could be lingering, for very good reason, for months and skew the average. Perhaps I could try something more like median ticket age but I still would prefer something that shows me % of open tickets above a certain age.

    I'm sure there are plenty of other ways to paint the picture I'm trying to paint in my dashboards, I just wish it didn't require so much arcane knowledge and creativity. Thanks for your help, without these forums building these reports for my exec dash would be truly a nightmare..

  • Avatar
    Nicole - Community Manager

    Thanks for sharing your solution, and glad to hear you got it figured out, Rich!

  • Avatar
    Intis Kalnins

    Hi Guys,

    As far as I understand a "solved" ticket is only counted when a comment is present. I'd also like to include tickets solved without comment in the custom metric above and I guess I would just add something like Public Comment present = (true, false), but where would I position that in the full metric?

    Thanks

  • Avatar
    Jacob J Christensen

    Hi Caleb,

    I just tried adding the 2 metrics # Tickets Created and # Tickets Solved and I'm allowed to select from the event attributes.

    Could you try to create the report again and make sure the right metrics are selected? I sometimes have metrics that are similarly named.

  • Avatar
    Caleb Strawn

    Okay, just now getting back to this. I've created a new custom metric for #of solved tickets using the MAQL formula you suggested which DID allow me to select Date (Event) however, that custom metric isn't pulling the numbers I'm looking for. It's actually not returning any numbers at all. I can assure you we solved tickets last month.. :)  Take a look at these screenshots. Maybe you can identify what's wrong with my metric.






  • Avatar
    Simon Peach

    Hello,

    I've been able to build this metric to allow me to build reports covering up to 4 months of data using date (event), I'd like to use this metric to build a 12 month view showing points for each of the 12 months. I can not find a month of year attribute and using the month(event) attribute only returns four months of data.

    Where am I going wrong?

  • Avatar
    Dennis Lynn

    Hey Ping,

    If you are just looking to see the number of tickets that are open as of a specific day, you can take a look at the backlog dataset and either use it in your own report, or use the prebuilt Insights report which should give you what you're looking for. The trick with this dataset is that the backlog only gives you the number of tickets in a given status at the end of a given day, so slicing it by month or something like that won't work either. You can find more information on this dataset here:

    Using the Backlog Dataset in Insights (Professional and Enterprise)

    You can also use the same data set to give you the number of tickets in any other status. Good luck, and happy reporting!!

  • Avatar
    Boris Krutiy

    Hey everyone, 

    I have a question someone here may be able to answer. Trying to create an SLA report that shows

    What=% Achieved

    How=Month/Year (Solved Date), SLA Metric (Requester wait time ONLY)

    Filter=I have a number of filter show what I want and have a valid report. What I need to modify is that I want to only show %Achieved tickets by Month with another filter for tickets that are created >= 01/12/2018. 

    How do I do this? Any help would be appreciated!

     

  • Avatar
    Graeme Carmichael

    Rich

    Creating hourly measures in GoodData is a little dangerous as the feed from Zendesk is not real time. For that kind of monitoring of unsolved tickets, it may be better to use a view. Views are real time and are a good way of managing live tickets.

    In GoodData, the macro THIS points to the current date, but not the current time. So the easy way to monitor unsolved tickets 2 days and older is:

    Just for fun, to attempt an hourly report in GoodData, you can try:

    The base date for THIS and the ticket created at minutes are not the same, so some manipulation is needed. There is also daylight saving to consider too. I would not recommend this route.

  • Avatar
    Shannon Lewis

    I have a question on the solved date.  Lets say a ticket is solved on Feb. 1, 2018.  On Feb. 4, 2018 we update an ticket field and/or add an internal comment and "submit as solved".  When running reports on ticket solved date, will this ticket come up as solved on Feb. 1, 2018 (the first solved date) or will it come up as Feb. 4, 2018 when we did some ticket clean up and resubmitted it as solved.

    thanks

    Shannon

Please sign in to leave a comment.

Powered by Zendesk