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.
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 query icon (
) .
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query 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 queries.
- Click Save to save the query. If you are ready, you can also add the query to a dashboard. Otherwise you can reopen the query 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 query. You can also add the query to a dashboard now if you are ready. Otherwise you can reopen the query in the library later.
63 Comments
Hmmm when I expand the Tickets choice list under Metrics I don't see a Tickets Created option
Hello Bill,
Thank you for reaching out to us. Based on your screenshot it looks like you've selected the wrong dataset. You'd want to select the dataset called "Support: Ticket Events" from the looks of your screenshot it appears you've selected the "Support: Tickets, Users, Organizations" dataset instead.
Because we are tracking ticket events we'd want to use that dataset. Event metrics are not available in the other datasets. I hope that helps!
Thanks Stephen!
The amount of tickets solved on a day differs from the information I gather out of the dataset "Support: Tickets, Users, Organizations". In this last dataset the solved tickets are calculated based on "date tickets solved" whereas in this example it is calculated based on "update date". Any idea what is creating the difference in these values?
Hi Ann,
Thanks for reaching out! I'm going to send you a ticket so I can follow up and get some more specifics on your reports. Stay tuned for that update.
Hello,
Whenever we use "Ticket created by Month" and date range from Aug 2018 until Today we get January and February 2019 starting on the X axis and following the 2018 months. Is there any way to rearrange them, eg. put Ian/Feb 2019 at the end of interval ?
Thanks,
Traian
Hi Traian,
Have you tried adding Ticket-Created Year for the x-axis and placing that before the Ticket- Created Month in the list of columns?
When I flip those back and forth with these two the placement changes the ordering of the months on the axis
That works ! Thanks a lot Bill !
Is there any way to report on solved tickets only? On the dashboard, the pop up message states solved and closed tickets.
Thanks
Hey Sarah!
Solved tickets transition to closed tickets via automation 4 days after they have been set to solved (this is a default automation so this statement only applies if you haven't adjusted this automation in your Zendesk instance). This is why the dashboard shows both solved and closed tickets in that data point.
Can you explain your use case a bit more and what you're ultimately trying to see? With more information I may be able to recommend a workaround. :)
Thanks!
Hi Delores - thank you for coming back to me.
I wanted to know if a ticket will appear in the query/ dashboard if set to solved and then show again in four days when changed to closed?
This would mean that the reporting is not accurate for tickets we are actually solving if they appear twice.
Thanks
Sarah
Hey Sarah!
I wanted to know if a ticket will appear in the query/ dashboard if set to solved and then show again in four days when changed to closed?
The ticket will only be counted once when using the default standard calculated metric for Solved tickets.
I am assuming that this includes tickets that have been 'closed by merge'. Has there been a fix for excluding 'closed_by_merge' tickets?
Ian,
I've simply been adding a Filter for [Ticket Tags], then excluding 'closed_by_merge'.
Ian Morgan This can be accomplished using the new tag reporting functionality that we've just released an early access preview for. See https://support.zendesk.com/hc/en-us/articles/360042887973
Thank you for your responses.
RJ Hines Unfortunately that method was not working for me.
Rob Stack That is great news Rob. Thank you for the update. I will have a look at this.
Just to follow up. I have tried out the 'new tag reporting functionality' as suggested by Rob Stack and it works as I hoped it would. Many thanks.
Glad to hear it Ian! Thanks for taking the time to share this with everyone :)
I'm having trouble understanding why there's a difference when you use COUNT or DCOUNT with the "Tickets Created" metric in Explore.
e.g. my numbers for Q4 2019 (same filters on both):
DCOUNT: 11 322
COUNT: 24 284
It appears some tickets are being counted as "Created" several times. Why would this be the case - is it re-opened tickets?
The number I get from GoodData for "Tickets Created" is almost the same as DCOUNT "Tickets Created" in Explore (there's a difference of about 300).
Thanks!
Hello,
Is it possible to filter dates using the Time filter widget? I'm not hable to find out the right setting to do that.
Thank you for helping on that.
Filippo
Hi,
Is it possible to express Solved/Created as a ratio to be able to track relative performance?
Yes, you can do this by adding a Result metric calculation to this query (info here: https://support.zendesk.com/hc/en-us/articles/360022184374)
You'll want this Result metric calculation to calculate COUNT(Tickets created)/COUNT(Tickets solved)
I'm pretty lost on this one. It appears that the Data Set names changed since this recipe was written. I'm working in "Support:Tickets".
There is no "Time - ..." metric choices. I tried using "COUNT (Tickets created - Last week)" and "Count (Solved tickets)" but the results are definitely just not accurate.
I don't see anywhere in the comments or the edited article what metrics or attributes to use based on the new data sets. I would think that getting "Tickets Created vs. Tickets Resolved" for any given time period would be easier to get. Right now I run two separate queries and report on each but now I'm even questioning the validity of THAT data.
Help...
Hi Jay McCormack this recipe did indeed point to the wrong dataset to use. I've just made a bunch of corrections and additions throughout this recipe and hopefully it works better for you now. Sorry for any inconvenience.
Hi,
I am trying to view how many tickets are getting into a specific View per day. I have made a report with the following structure:
Dataset: Support: Tickets [default]
(COUNT)End-user submitted tickets
Ticket updated - Date
Ticket group - (I have chosen 1 of our views "Tier 1 Support")
Now I get a report which shows some data but it appears incorrect. I have Decomposed 1 day which seems to have only gotten 40 cases and checked the Ticked ID of some of the cases and it appears that it filters all cases Updated on this particular day. We have a rule to CLOSE tickets after 30 days so basically it is showing cases that entered the queue 30+ days ago and not what actually entered our queue on that particular day.
Can you please let me know how it is possible to filter the report to show only the following: Tickets Submitted by end users on a specific day. Not solved or updated cases but the first date that they entered our Zendesk?
Thank you!
Hello Viktor,
I appreciate the screenshot with your post. You seem to be using the Ticket last updated date, which gives the time of the most recent update to the ticket. What you will want to use is Update Date, which would resolve your issue.
Hi there!
How do you show cumulative outstanding unsolved tickets as a time series?
E.g. on Day 1 20 tickets are created and 10 tickets are solved, on Day 2 40 tickets are created and 20 are solved and on Day 3 80 tickets are created and 40 are solved.
Instead of a time series graph showing unsolved tickets as 10 on Day 1, 20 on Day 2 and 40 on Day 3, I would like to have a time series graph which shows the cumulative outstanding amount of unsolved tickets: 10 on Day 1, 30 on Day 2 and 70 on Day 3.
Thanks
Hi Nakul, you might be able to do this using running totals. For help, see https://support.zendesk.com/hc/en-us/articles/360022366793
Brilliant, thanks Rob - that's helped enormously!
Hi Nakul Patel
could you share how you did this please? I'm trying to create something similar but still cant get my head around it.
Thanks,
Lisa
Please sign in to leave a comment.