English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore recipe: Displaying tickets answered within different time brackets

Andrew Forbes
  • Edited
Zendesk team member

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

This Explore recipe shows the percentage of tickets being replied to in different time brackets, for example, tickets replied to within one hour. You can then use this to compare different groups and other attributes.

What you'll need

Skill level: Easy

Time Required: 15 minutes

How to create the report in Explore

  1. In Explore, click the query () icon.
  2. In the Queries library, click New query.
  3. On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
  4. In the Metrics panel, click Add.
  5. From the list of metrics, choose Tickets > Tickets, then click Apply.
  6. In the Rows panel, click Add.
  7. From the list of attributes, choose Brackets > First reply time brackets, then click Apply.

  8. In the Filters panel, click Add.
  9. From the list of attributes, choose Time - Ticket created > Ticket created - Year, then click Apply.
  10. In the Filters panel, click the Ticket Created - Year filter you just added.
  11. On the filter's page, click Edit date ranges to restrict the period for which you are seeing results, for example for the year to date - This year.

  12. To show your results as a chart instead of a table, move First reply time brackets to the Columns panel. Then in the visualization menu, choose the chart type you want. A Bar or Column chart looks great with this kind of query.

  13. In the result manipulation menu, go to the Result path calculation options, and choose the Pattern % of total, and the Path On columns. Click Apply.

Now you can configure your chart using the chart configuration menu - for example you can show the result labels using displayed values; change the color; add gridlines; change the names of the axes, and more.

Return to top
Have more questions? Submit a request

23 Comments

  • Mengxin

    Hi there,

    I'm trying to create the same bracket for calls and there's an error message on my query, whereas i've basically copy pasted "first reply time brackets". 

    Here's the first reply time brackets query:

    IF (VALUE(First reply time (min)) <= 60) 
    THEN "0-1 hrs" 
    ELIF (VALUE(First reply time (min))>60 AND VALUE(First reply time (min)) <= 60*8) 
    THEN "1-8 hrs" 
    ELIF (VALUE(First reply time (min))>60*8 AND VALUE(First reply time (min)) <= 60*24) 
    THEN "8-24 hrs" 
    ELIF (VALUE(First reply time (min)) > 60*24) 
    THEN ">24 hrs" 
    ELSE "No Replies" 
    ENDIF

    And here's my query: 

    and the error message indicates that line 1 is not correct. 

    IF (VALUE(Call wait time (sec)) <= 10)
    THEN "0-10 sec" 
    ELIF (VALUE(Call wait time (sec)) > 10 AND (VALUE(Call wait time (sec)) <= 30)
    THEN "10-30 sec"
    ELIF (VALUE(Call wait time (sec)) > 30 AND (VALUE(Call wait time (sec)) <= 60)
    THEN "30-60 sec"
    ELIF (VALUE(Call wait time (sec)) > 60
    THEN ">1 min"
    ELSE "Missed" 
    ENDIF
    0
  • Ricardo
    Zendesk Community Team

    Hello, Mengxin,

    It looks like we have an open ticket with you regarding that. I will follow up there. Thank you!

    0
  • Mengxin

    Hello Ricardo,

     

    I dont see any open tickets, could you confirm? 

    0
  • Ricardo
    Zendesk Community Team

    I have just sent you an email from that ticket. Hope it helps.

    0
  • Katie Saddlemire

    Is it possible to create a version of this that is within business hours? 

     

    Thanks! 

    1
  • Joe Beaudoin
    Zendesk Customer Advocate

    Hi Katie,

    Thanks for your question!

    Yes, it's possible. The recipe calls for the Tickets metric and the First Reply Time Brackets attribute, but you will need to leverage the First Reply Time Business Hours (min) metric in a custom calculated attribute to get what you're asking for.

    Take a look at the following screenshot:

    Notice how the First Reply Time Brackets attribute has a syntax that references VALUE(first reply time (min)) -- you will need to build your calculated attribute in a similar fashion but reference VALUE(first reply time business hours (min)) instead. Any adjustments to the bracket ranges would be up to your discretion based on how you want the data to display.

    I hope that helps!

    0
  • Christopher C.

    Hello,

    How can you stack the result on top of each other and list them per day for the last 8 days?

    For example:

    0
  • Brett Bowser
    Zendesk Community Team

    Hey Christopher,

    We have some documentation available that goes over setting up stacked bar charts here: Using stacked charts to display results

    You can then add your date filter for the last 8 days.

    Hope this helps!

    0
  • Sophie Harris

    Hi, would it be possible to create something like this, but instead showing ticket created within different time brackets? (E.g. % tickets created between 7am and 12pm, then between 12pm and 5pm etc)? 

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hello Sophie Harris,

    So you should be able to replicate this report with tickets created by utilizing the following metric. I've also included an article that explains how to build a report similar to what you've described if you need some further reference. Let us know, though, if you run into any hiccups along the way.

    COUNT(Tickets Solved)

    Explore recipe: Reporting on created and solved tickets

    Best regards. 

     

    0
  • Sophie Harris

    Hi Devan - Community Manager, 

    Thanks for the reply! I'm a bit confused, because won't COUNT(Tickets Solved) show me a metric based on the tickets solved? Whereas I'm trying to find what % of tickets are created in various time brackets (e.g. what % of our tickets came in before 9am last week). Let me know if I'm missing something here though!

    I'm also unsure how to create the time brackets - I can see in the query above that there are some pre-made "first reply time" brackets that you can just select, but I don't know have any brackets for when tickets were created (e.g. between 12am and 7am, between 7am and 12pm etc.). Is there a query that can be shared on how to build that? Thanks, and sorry for all the questions!

    0
  • Brett Bowser
    Zendesk Community Team

    Hey Sophie,

    You'll want to create a standard calculated metric similar to what I provided below:

    IF (IN([Ticket created - Hour],ARRAY("7", "8","9","10","11", "12"))) THEN "7am-1pm"
    ELIF (IN([Ticket created - Hour], ARRAY("13","14","15","16","17"))) THEN "1pm-6pm"
    ELIF (IN([Ticket created - Hour], ARRAY("18","19","20","21","22","23","24"))) THEN "6pm-1am"
    ELIF (IN([Ticket created - Hour], ARRAY("1","2","3","4","5","6"))) THEN "1am-7am"
    ENDIF

    You can then customize the times you'd like reflect in your report. After that's completed you should be able to add COUNT(Tickets) to the metrics and this custom attribute to slice it.

    Hope this points you in the right direction!

    0
  • fellownarrator

    I am trying to create a report that brackets tickets by # in Pending, New or Open status by 0-3 tickets, 3-6 tickets and 6 or more tickets.  What is the right formula?  Here is what I have so far, this keeps just giving me the count for >6 tickets:

     

    IF (([Ticket status]="New" OR [Ticket status]="Pending" OR [Ticket status]="Open") AND VALUE(Tickets)<4)
    THEN "0-3 tickets"
    ELIF (([Ticket status]="New" OR [Ticket status]="Pending" OR [Ticket status]="Open") AND (VALUE(Tickets)>=4 AND VALUE(Tickets)<=6))
    THEN "4-6 tickets"
    ELIF (([Ticket status]="New" OR [Ticket status]="Pending" OR [Ticket status]="Open") AND VALUE(Tickets)>6)
    THEN ">6 tickets"
    ELSE "Invalid"
    ENDIF

    0
  • Chris Stock
    Community Moderator

    Hi fellownarrator I'm struggling to envisage what your report will look like. Number of tickets will be your metric, but what will your attribute(s) be?

    0
  • Farhan Virji

    I'm using the Pre-Built Support dashboard, there is a chart that shows the number of tickets as a percentage that is broken down by First Reply Time bracket, which shows the total of tickets with no reply.  However, I want to dig into the details to understand what's going on.

    I cloned the pre-built dashboard so that I could enable Drillthrough and Decompose, but those options are not working on this chart.

    Does this chart type not allow Drill through?  I'd like to see the tickets that make up each bracket.

     

    0
  • Melissa Norton

    Hey Farhan Virji,

    I've noticed when you clone the Support dashboard, it will allow you to add drillthrough on its queries, but it doesn't show any data. I've instead recreated the query from scratch and that action allows drillthrough. Not sure why, but that's what I've found.

     

     

    0
  • Farhan Virji

    Hey Melissa Norton,

    Thanks for the tip! This is going to be a pain to have to recreate all queries.  I hope Zendesk is fixing this problem.

     I recreated the query in question and I now get the option for Decompose and Focus.  The Drillthrough option doesn't appear. 

    I've noticed on some charts, Drillthrough doesn't appear even though the configuration of that chart has Allow drillthrough selected.

     

     

    Am I missing a step?

    I'm shocked that it's this difficult to get details of a chart to see the underlying tickets...

    0
  • Brett Bowser
    Zendesk Community Team

    Hey Farhan,

    You'll want to take a look at our Using Drillthrough article as provides more information on what's expected with the Drillthrough feature.

    One important note is that Drillthrough can only display attributes or metrics that are part of the already queried data tables.

    I hope this points you in the right direction!

    0
  • Sarah

    Hello!

    Is it possible to have in the same tab the value in absolute and the values in % of the total (so 2 columns for each range)?

     

    Thank you!

    0
  • Taylor Bowser
    Zendesk Customer Advocate

    Hi Sarah, 

    Absolutely! You can duplicate the metric and add both metrics to your query. Then you can go to chart configuration>>display format and change the dropdown of one of these metrics to show as a percentage. 

    0
  • Sarah

    Hello,

    Thanks for your answer but its not what I want to do, I dont want to just change the format, but put the values in % of the total.

    For instance: 

    • 1st column: 5 - 5 - 5
    • 2nd column: 33% - 33% - 33%

    Today Im duplicating the query with the 1st column, and on the second query I go to Result manipulation > Result path calculation > Pattern = % of total

    I would like to have the 2 columns in the same query.

    Thanks a lot!

     

    0
  • Taylor Bowser
    Zendesk Customer Advocate

    Hi Sarah, 

    My apologies! You could still apply the same logic here as I mentioned in my previous response. You would want to duplicate your first metric and then add both to the query.

    Lastly, you'll want to go to result manipulation>>result path calculation and change the dropdown to Pattern =  % of total for the second metric. 

    1
  • Sarah

    Fantastic thank you

    0

Please sign in to leave a comment.

Related articles

Powered by Zendesk