English (US)
  1. Zendesk help
  2. Support
  3. Using Support
  4. Best practices and recipes

Insights recipe: Reporting on created and solved tickets

Hammad Naseem
  • Edited

professional enterprise plans

This recipe will instruct you on how to create custom reports to analyze your created and solved tickets by date and by a specific day of the week. For more information on Insights reporting, see Insights resources. 

Ingredients List

  • 1 pre-built metric
  • 2 custom reports

Number of Tickets Created and Solved by Date

The first report will measure the number of tickets created and solved by date. 

To create your report

  1. In the Zendesk Support agent interface, click the Reporting icon. 
  2. Select the Insights tab. 
  3. Click the GoodData link in the upper-right corner. 
  4. Select Report in the toolbar. 
  5. Click Create Report. 
  6. In the What panel, select # Tickets Created and # Tickets Solved.

  7. In the How panel, select Date (Event). 

  8. Click Done. 

You can apply a filter to limit your results to a specific date range. This report will limit the date range to the last thirty days. 

To apply a date range filter

  1. Click the Filter button. 
  2. Select the Select from a list of values filter. 
  3. For the attribute to filter, select Date (Event). 
  4. Select either a floating date range or specific dates. 

  5. Click Apply.

 The final report should resemble the image below. 

Number of Tickets Created and Solved by Day of Week

The second report will measure the number of tickets created and solved by the day of week, drilled down to hour. 

To create the second report

  1. In Reports, click Create Report. 
  2. In the What panel, select # Tickets Created and # Tickets Solved. 
  3. Click the # Tickets Solved metric. 
  4. In the Detail panel, click Add Drill Step underneath Drill In Settings. 
  5. Select Hour of Event from the drop-down list. 
  6. In the How panel, select Day of Week (Mon-Sun)(Event)

     

  7. Click Done.

The final report should resemble the image below.

If you wanted to drill down to see the number of solved tickets on Thursday by Hour of Event, simply click on the green bar for Thursday.

Here's an example of using drill in.

Return to top
Have more questions? Submit a request

40 Comments

  • 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?

    0
  • 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.

    0
  • 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

    0
  • 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!

    0
  • 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

     

     

     

    0
  • Dennis Lynn
    Zendesk Customer Advocate

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

    0
  • 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.

     

     

     

    0
  • 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!

    0
  • Jacob J Christensen - Cloudhuset A/S
    Community Moderator

    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.

    0
  • 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 

    0
  • Jacob J Christensen - Cloudhuset A/S
    Community Moderator

    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.

    0
  • 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.






    0
  • Jacob J Christensen - Cloudhuset A/S
    Community Moderator

    Hi Caleb,

    It looks like we don't have the same attribute values, I'm guessing this could be why.

    Notice (above) than my attribute values for status are [status] Solved, where yours are merely Solved.

    To find that I search for the New Value attribute in the Attribute value folder, click the right one and again search Status. Now I can select the [status] solved attribute value and the others as well.

    Hope that makes sense, and solves your issue.

    0
  • 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

    0
  • Diogo Maciel

    Hi Shannon!

     

    The date in the report should still be the date it was solved. You can use the second date if you  choose "Date Ticket Updated" as an attribute instead of "Date Ticket Solved"

    0
  • Shannon Lewis

    thanks Diogo.

    0
  • 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!

     

    0
  • Jessie Schutz
    Zendesk team member

    Hi Boris!

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

    0
  • 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.

    1
  • 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?

    0
  • Jessie Schutz
    Zendesk team member

    Hi Lynette! 

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

    0
  • 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!

    1
  • 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.

    0
  • Heather Rommel
    Community Moderator

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

    1
  • Graeme Carmichael
    Community Moderator

    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. 

    1
  • 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?

    0
  • Heather Rommel
    Community Moderator

    Brands are a drop down filter available on the same screen from my screenshot above. Its's to the right of "Customer"
    Simply select the desired Brand and presto! It will report only tickets for that Brand.

    As Graeme stated, if you wanr to customize reports you don't have to start from scratch. Yoi can clone existing reports and create your own.

    0
  • Rich Lorenzo
    • 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?

    0
  • Rich Lorenzo
    • 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)
     
    https://help.gooddata.com/display/doc/IFNULL
     
    0
  • Nicole S. - Community Manager
    Zendesk Community Team

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

    0

Please sign in to leave a comment.

Related articles

Powered by Zendesk