This recipe shows you how to create custom reports to analyze your created and solved tickets by date, and by a specific day of the week.
Note: The data in this report includes a small margin of error due to the calculations used in the metric formulas. As an alternative to this report, you can use the Tickets dataset to create two separate reports, one filtered by ticket creation date and the other by ticket resolution date. For details, see this Zendesk comment.
What you'll need
Skill level: Easy
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket event data in Zendesk Support
How to create the report in Explore
You can click the preview below to watch a video of this recipe, or continue to the walkthrough.
- In Explore, click the reports icon () .
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.
- Next, add your metrics, the things you want to measure. In this case, you'll add the number of tickets created and solved. In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets created and Tickets > Tickets solved, then click Apply.
- Next, apply a date range. In the Filters panel, click Add
- From the list of attributes, choose Time - Ticket update > Update - Year, then click Apply.
- In the Filters panel, click Update - Year, and then Edit date ranges. You can choose one of the simple time ranges offered, or click the Advanced tab for more options.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket update > Update - Date, which will show the daily results for the period you chose previously. Click Apply.
- From the Visualizations menu (), choose the Column chart type.
- From the chart configuration menu (), click Chart.
- On the Chart page, check Stacked, and uncheck Aggregated values.
- From the chart configuration menu, click Displayed values.
- On the Displayed values page, click to show the values, inside the columns, and choose the color and size that you want.
Tip: In the chart configuration menu(), you can also change and format many chart features including chart colors, x-axis and y-axis titles and labels, the chart legend, and more. For details, see Customizing reports.
- Click Save to save the report. If you are ready, you can also add the report to a dashboard. Otherwise you can reopen the report from the library later.
Reporting on Tickets Created and Solved by Day of the Week
- Follow steps 1 to 7 above. When you add Update - Year at step 8, click the Advanced tab, and from the dropdown list, select a full number of weeks, such as 12 weeks in the past to 1 week in the past. This will give you an accurate picture of activity by weekday. Click Apply.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket Update section, select Update - Day of week, then click Apply
- In the chart configuration menu, click Displayed values.
- On the Displayed values page, click to show the values, then choose the color and size that you want for those values.
In the chart configuration menu you can also change the colors of the series, edit and format the x-axis and y-axis titles and labels, move the legend, and many other things!
- Click Save to save the report. You can also add the report to a dashboard now if you are ready. Otherwise you can reopen the report in the library later.
This article is really helpful, thank you!
Does anyone know how I can find the following:
This is what I have so far but it's not quite there yet. This shows me how many tickets were created (yesterday) compared to how many were solved yesterday. Right?
So... if I have 100 tickets created yesterday, how many of those 100 tickets were solved yesterday? Let's pretend it's 50; of those 50 tickets (which were created and solved on the same day) how many were one-touch?
Help me Obi Wan... You're my only hope.
I had the exact same scenario and need a few months back. The last bit you're after - the COUNT(One-touch tickets) metric - does not exist in the Ticket Updates dataset.
If you're looking to get all three pieces of data you identified into a single visual, you'll need to use the Support: Tickets dataset. In my example below, I added my three metrics then used filters for Ticket Created + Ticket Updated attributes, both set to 'Yesterday':
The only real downside to this visual is we can only view one day at a time due to how the dataset's date/time filters work, but this is all we need.
Hope this helps!
Does anyone have a way to do similar to what @... did above, but for a week long view instead of just daily. Looking to have tickets created by day of the week and of those tickets, how many were solved that same day - doesn't need to include one-touch tickets
Without that third metric involved, you would want to use the Ticket Updates dataset instead. I added the default system metrics Tickets Created and Tickets Solved, then set the date range. In the example below, I used 'Last Week' as my timeframe, and for flavor also added a Ticket Created - Date filter to remove Saturday/Sunday's values.
Hope this helps!
RJ Hines I might be wrong, but I think your graph above is the same problem I'm running into, which is that instead of 'tickets created or solved on x date' you have 'tickets created on x date that were also solved'. This has been a problem for me because what I want is 'all the tickets that were created or solved on x date', i.e. the delta. If I try to do that using the ticket created date it's wrong as noted, if I use the 'date updated' the graph shows maybe 5x as many tickets for last week as it should. I haven't yet been able to figure this out.
Hi Mark Leci
Did you get your answer?
I am running this query and i get 40k created tickets and 50k solved ones in one day, so I am assuming (maybe wrongly) that the solved ones are not only those created in that day. Am i wrong?
I am actually looking for the data that answer me how many unsolved Tickets i got in a specific day, and these calculations are the ones i need. So if i know how many tickets were created from the beggining until that specific date and how many tickets were solved from the beggining unitl that date i ll get the outstanding number of tickets on that specific date.
So far, i have been unable to get that right number.
Let me know if you got any answer on your question please.
I see what you mean. Basically, this would be caused by the having to choose what time filter to use (either ticket created - date or ticket solved - date). This is resolved by creating two different queries, created tickets uses the ticket created - date time filter, and the solved tickets using the ticket solved - date filter. This is how it is done in the pre-built dashboard for Support. Then it would be a matter of putting both those queries on the same dashboard.
Hope this clarifies it for you! Cheers!
Thanks for the answer Marco.
To be honest i am still not convinced. so i can see the created tickets at any point, but when talking about solved tickets in a past date, this number is not stable as it changes due to reopened tickets right? so if i want to see how many tickets outstanding i had on the 31/12/2021 i am not gonna get the same number I had if i was checking on the 31st dec.
I am only finding numbers for live outstanding but nothing historical. Am I wrong?
I have had trouble with this report in the past. Support has told me to use Update-Month and Update-Year for your date restrictions.
I did data validation and the numbers match if I run the reports date just for Total ticket Assigned (using date assigned) and Ticket Solved (using date solved). I also used in my metrics D_Count instead of Count.
Hope that helps
Thanks bill cicchetti for the time and effort.
Does anyone know if it's possible to pull up a list of the individual tickets that are being counted when you click on the KPI metric?
This query is close to what I need, but I can't figure out how to make it work for my purposes. I have an agent that works outside of our business hours on the weekends (Saturday & Sunday), and I need a query to show the following:
- All tickets created between end of business on Friday, to the end of Sunday.
- What percentage of those tickets they updated to "pending" on Saturday and Sunday.
- What percentage of the total available tickets they solved each day.
Simply calculating how many tickets they solved isn't enough data given what we do, and only counting tickets received on the day that they work also isn't enough because of the surge of tickets we receive on Friday night. I need to determine how many tickets they are neglecting to touch during their two-day shift so as to establish possible knowledge gaps.
You can use the Drill-in function to show the individual tickets. More information can be found in Using drill in to refine your queries.
Hi AMU Cx Lead,
For this one, you can use the standard calculated metric below. In addition, don't forget to adjust the metric filter so that it will only show tickets with the "1" value.
The query below shows how many tickets were set to Pending using the Metric above.
You can use the result metric calculation below.
Don't forget to adjust the display format to "%".
A final reminder: Please take note that we don't support building queries, dashboard and formulas from scratch. The information should give you an overview and serve as a guide on how to create one.
Dane Thank you for the reply, however, when I perform the tasks you outline and add the Ticket Status attribute, it removes "new" tickets for the results?
The original poster here seemed to include an image , which I'm unable to view on the post still...
Having read through the thread, I'm still confused about how I can have the following 2 KPIs (can be simple numbers rather than graphs). It's a simple dashboard and so I'm trying to achieve the following...
1. Tickets created (easy and in place using standard metric)
2. Of those tickets in KPI 1, which are currently SOLVED or CLOSED.
Everything I try seems to give me total crated / solved on any given time period, rather than KPI 2 being a subset of KPI 1.
Hope that's clear and that someone can help :-)
Can you check Explore recipe: Reporting on created and solved tickets if it will work for your use case?
This report isn't great in the real world when its going to include the ticket created metric for every solved ticket during my time range. This results in a massive graph spanning back possibly years depending on when the report time range of the solved ticket was created. :(
I am trying to get the average number of tickets created on the weekend.
When I use the formula you posted in the Comments above,
and I choose (AVG) I get wildy crazy numbers in the tens of thousands.
Any idea how I can get the average tickets created each Friday evening, Saturday and Sunday over the course of the year?
Did you see Dane's note in their comment?
For more information, see Selecting the metric result range
I am trying to get a single report table of a count of tickets solved today and yesterday by assignee. I cant see how to add in the second column (yesterday) to the table. The filter based on year against solved seems to only be able to be used the once
Hi Dave. With your filter for Ticket solved - Year (or Date), why don't you choose Edit date ranges > Advanced and select "from the beginning of Yesterday" to "the end of Today". Then in the Columns, section, add Ticket Solved - Date. With Assignee in the rows and the report set to Table, this sounds like it will give you what you want.
Sorry - not sure what I was thinking there - what about including tickets open for a set of dates (in this case yesterday and today) as another column.
Trying to report on the count of assigned tickets by ticket status for assignees for two selected days and showing the two dates as columns
Also how do you report by date on a status change - I want to show those that were open or solved by assignee for two identified dates say 5/11 and 7/11
Dane I have been using this report for a while now and noticed last week that I am off by a few records on numerous customers. I have tweaked my report and found there have been 72 cases open that have 0 value for created. The problem appears to have started in November and resolved itself in December. Can you please help me understand what happened. I looked at the history on the ticket and they all look fine. Again, they show up fine on regular ticket reports but not the history report
We would need to look into your account to see what's causing "0" to be returned for "Tickets created" metric on those tickets. I will create a ticket for this, please expect a separate email for the ticket that I am going to create. Thank you.
Please sign in to leave a comment.