In this recipe, you’ll learn how to report on custom ticket statuses. Custom ticket status attributes are available in the following Explore datasets:
- Support: Tickets
- Support: Updates History
- Support: SLAs
- Support: Group SLAs
- Chat: Messaging tickets
- Chat: Engagement
- Talk: Calls
This article contains the following topics:
- Calculating the number of tickets per custom ticket status and category
- Calculating how long a ticket spent in a custom ticket status per update
- Calculating how long a ticket spent in a custom ticket status during its lifecycle
- Calculating how long a ticket has spent in its current custom status
What you'll need
Skill level: Intermediate
Time required: 15 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving users access to Explore)
- Custom ticket statuses (See Activating custom ticket statuses)
Calculating the number of tickets per custom ticket status and category
This report shows you the volume of tickets in each custom ticket status. It also shows which category each status belongs to.
The instructions below use the Support: Tickets dataset, but you can also build this report in the Chat: Messaging tickets, Chat: Engagements, or Talk: Calls dataset if you want a different channel-specific view.
To create the report
- In Explore, click the reports icon (
).
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, expand the Tickets folder and select Tickets.
- In the Columns panel, click Add and select Ticket custom status category and Ticket custom status name (in that order).
- Click the Visualization type (
) menu and select Column.
- Click the Chart configuration (
) menu, select X-axis, and select the Multi-level category labels checkbox.
- Click the Chart configuration (
) menu again, select Drill in, and select the Enable drill in checkbox.
- In the Select attributes field, select Ticket ID. This lets report viewers drill into the report results with ticket ID-level data. See Using drill in to refine your reports.
- (Optional) Add additional attributes or time filters to focus the results of this report.
Calculating how long a ticket spent in a custom ticket status per update
This report shows you how long a ticket spent in a custom ticket status per update.
To create the report
- 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.
- In the Metrics panel, click Add.
- From the list of metrics, expand the Duration - Field changes (min) folder and select Field changes time (min).
- In the Filters panel, click Add, expand the Ticket field changes folder, and select Changes - Field name.
- Click the filter you just added and select the custom_status_id value.
- In the Rows panel, click Add and select the following in attributes in order:
- Update ticket ID
- Update ID
- Changes - Previous value
- Changes - New value
As with other change-based attributes, custom ticket statuses (the values in the Changes - Previous value and Changes - New value fields in your report) are represented as system IDs. To determine which custom ticket status corresponds with each ID, go to the following URL, where <your_subdomain> is your Zendesk subdomain, and <custom_ticket_status_id> is a custom ticket status ID from your report.
<your_subdomain>.zendesk.com/admin/objects-rules/tickets/ticket_statuses/edit/<custom_ticket_status_id>
Calculating how long a ticket spent in a custom ticket status during its lifecycle
If you already have the ID for a custom ticket status of interest (see above), you can create a standard calculated metric to obtain the time a ticket spent in that custom ticket status overall.
To create the report
- 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.
- Click the Calculations menu (
) and select Standard calculated metric.
- In the Name field, give your metric a descriptive name, such as Time spent in escalated status.
- In the Formula field, copy and paste the following formula. Replace <custom status ID number> with the ID of the custom ticket status you noted previously.
IF ([Changes - Field name]="custom_status_id") AND ([Changes - Previous value]="<custom status ID number>") THEN VALUE(Field changes time (min))/60 ENDIF
For example:IF ([Changes - Field name]="custom_status_id") AND ([Changes - Previous value]="19313738408081") THEN VALUE(Field changes time (min)) ENDIF
- In the Metrics panel, click Add and select the metric you created above.
- In the Rows panel, click Add and select Ticket ID.
Calculating how long a ticket has spent in its current custom status
This report shows you how long tickets are currently spending in a specific custom status. You’ll create a standard calculated metric that returns the duration of time a ticket was set to a specific status last time until now.
To create the report
- 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.
- Click the Calculations menu (
) and select Standard calculated metric.
- In the Name field, give your metric a descriptive name, such as Last wait time in status (days).
- In the Formula field, copy and paste the following formula. Replace <custom status ID number> with the ID of the custom ticket status you noted previously.
IF ([Changes - Field name]="custom_status_id"
For example:
AND [Changes - New value]="<custom status ID number>"
AND DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name], [Changes - New value])=[Update - Timestamp])
THEN DATE_DIFF(NOW(), [Update - Timestamp], "nb_of_days")
ENDIFIF ([Changes - Field name]="custom_status_id"
AND [Changes - New value]="4962645400102"
AND DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name], [Changes - New value])=[Update - Timestamp])
THEN DATE_DIFF(NOW(), [Update - Timestamp], "nb_of_days")
ENDIF - Filter the report by the Ticket custom status value that corresponds to the custom field in the formula.
- (Optional) In the Columns panel, add the Ticket group or Ticket ID attribute to focus the results of this report.
7 comments
Almog Zamir
How does it work for calculations within business hours?
0
Dainne Kiara Lucena-Laxamana
Hi Almog Zamir
You can filter the report by following this guide here: Explore recipe: Filtering reports by business hours
0
Almog Zamir
Hey Dainne Kiara Lucena-Laxamana
We set multiple Schedules, which means we have several business hours (several time zones) based on the Customer's region. How does this recipe support our need to calculate metrics within different business hours (regions' business hours)?
0
Tony
The suggested recipes will help you in filtering by business hours. If you have different schedules, the dataset used doesn't look like has any reference to schedules. Being within business hours is like a bool: the ticket is o is not within business hours. I leave you the dataset here for further reference:
https://support.zendesk.com/hc/en-us/articles/4408827693594-Metrics-and-attributes-for-Zendesk-Support
In that case you will need to autonomously build a custom process where maybe you can use tags based on the schedule applied or something similar that can help you to manage your business in the intended way you are planning to manage.
Best,
0
Almog Zamir
Hey Tony, thank you for your reply!
On Explore, there are already at least two metrics that have been calculated within business hours (based on Schedules):
1) "First reply time—Business hours (mins or hrs)"
2)"Full resolution time—Business hours (min or hrs)"
Therefore, the mechanism already exists in ZD's backend.
Is there an option for you to provide the logic/mechanism of those metrics calculations so we could use it for every two timestamp fields, which would give us the flexibility to use it for any use case we wish?
2
Sameer Naringrekar
How do I display average time spent in custom status in a bar chart?
2
Hannah Lucid
I would like to mimic Almog Zamir's statement. We need the ability to report on Time Spent in Statuses, both system and custom, in Business Hours. Is there anything on the roadmap for this?
1