Explore recipe: Reporting on custom ticket statuses

What's my plan?

Suite

Professional, Enterprise, or Enterprise Plus

Support with

Explore Professional or Enterprise

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.

Note: Data is available only from January 18, 2023 onward.

To create the report

1. In Explore, click the reports icon ().
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
4. In the Metrics panel, click Add.
5. From the list of metrics, expand the Tickets folder and select Tickets.
6. In the Columns panel, click Add and select Ticket custom status category and Ticket custom status name (in that order).
7. Click the Visualization type () menu and select Column.
8. Click the Chart configuration () menu, select X-axis, and select the Multi-level category labels checkbox.
9. Click the Chart configuration () menu again, select Drill in, and select the Enable drill in checkbox.
10. 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.
11. (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.

Note: Data is available only from October 19, 2023 onward.

To create the report

1. In Explore, click the reports icon ().
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates History, then click Start report. The report builder opens.
4. In the Metrics panel, click Add.
5. From the list of metrics, expand the Duration - Field changes (min) folder and select Field changes time (min).
6. In the Filters panel, click Add, expand the Ticket field changes folder, and select Changes - Field name.
7. Click the filter you just added and select the custom_status_id value.
8. 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.

Note: Data is available only from October 19, 2023 onward.

To create the report

1. In Explore, click the reports icon ().
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates History, then click Start report. The report builder opens.
4. Click the Calculations menu () and select Standard calculated metric.
5. In the Name field, give your metric a descriptive name, such as Time spent in escalated status.
6. 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

7. In the Metrics panel, click Add and select the metric you created above.
8. 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.

Note: Data is available only from October 19, 2023 onward.

To create the report

1. In Explore, click the reports icon ().
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates History, then click Start report. The report builder opens.
4. Click the Calculations menu () and select Standard calculated metric.
5. In the Name field, give your metric a descriptive name, such as Last wait time in status (days).
6. 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 - 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")
   ENDIF

   For example:

   IF ([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

7. Filter the report by the Ticket custom status value that corresponds to the custom field in the formula.
8. (Optional) In the Columns panel, add the Ticket group or Ticket ID attribute to focus the results of this report.