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
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Brackets > First reply time brackets, then click Apply.
- In the Filters panel, click Add.
- From the list of attributes, choose Time - Ticket created > Ticket created - Year, then click Apply.
- In the Filters panel, click the Ticket Created - Year filter you just added.
- 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.
- 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.
- 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.
23 Comments
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:
And here's my query:
and the error message indicates that line 1 is not correct.
Hello, Mengxin,
It looks like we have an open ticket with you regarding that. I will follow up there. Thank you!
Hello Ricardo,
I dont see any open tickets, could you confirm?
I have just sent you an email from that ticket. Hope it helps.
Is it possible to create a version of this that is within business hours?
Thanks!
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!
Hello,
How can you stack the result on top of each other and list them per day for the last 8 days?
For example:
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!
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)?
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.
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!
Hey Sophie,
You'll want to create a standard calculated metric similar to what I provided below:
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!
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
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?
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.
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.
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...
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!
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!
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.
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:
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!
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.
Fantastic thank you
Please sign in to leave a comment.