Recreating native ticket metrics with Insights event data Follow

Disclaimer: This article is provided for instructional purposes only. Zendesk does not support or guarantee custom reports. Please post any issues you have in the comments section or try searching for a solution online. 

Zendesk Support calculates and captures key metrics on each ticket in the metric_sets object to simplify reporting. On occasion, however, you may need to use Insights to calculate these metrics instead. The recipes in this article recreate native ticket metrics using ticket events in Insights. 

There is one significant exception for all of these metrics:

Insights can’t calculate durations in business hours. GoodData has no access to the Zendesk Support schedule, and there is no way to dynamically filter metric results. Unfortunately, this means Insights will only be able to calculate durations in calendar (non-business) hours.

This tip contains the following sections:

How to read metric recipes

These recipes all use the Advanced Metric Editor in Insights. For a quick introduction, check out GoodData's short tutorial video.

Note: Do not copy and paste in the metric editor. You must select your elements manually from the drop-down list.

The elements are color-coded based on their type. They can be found in the following locations:

  • Blue elements are under Facts
  • Green elements are under Metrics
    • Most of these recipes use metrics that build on each other. Be sure to create everything in order, so intermediate custom metrics appear in this list.
  • Purple elements are under Attributes
  • Orange elements are under Attribute Values
    • Values are listed through their associated attribute.
    • Example: Text Field = Status. To find "Status," click Attribute Values, then select Text Field. Status will be in that list. It will not appear under Attribute Values on its own.

Reporting on tagged tickets

Ticket tags are unique in Insights, and they require special steps to be used in reports. For details, see Reporting on ticket tags. That article includes recipes to include or exclude tickets based on their tags.

# Tickets Solved / Date (Ticket Solved)

This recipe requires a series of metrics that build on each other. You do not need to display the first three metrics in the report. You just need to create them so the fourth metric, # Tickets Solved (Recreation), functions properly. 

The first metrics use epoch timestamps to reliably identify the most recent solve event on any given ticket. The final metric counts tickets based on their final solve event. 

This metric behaves same way as # Tickets Solved. With this metric, Date (Event) is the final solve date on the ticket. It should hold when Event dates are used under How or Filter in a report, or through a Date (Timeline) filter on a dashboard. 

Below are the four metrics listed in the order you will need to create them: 

  • Timestamp: Ticket Solve Event (MAX):
    • SELECT MAX(SELECT(SELECT MAX(Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved) BY Ticket Id WHERE Ticket Status IN (Solved, Closed

  • Timestamp: Final Ticket Solve:
    • SELECT Timestamp: Ticket Solve Event (MAX) BY Ticket Id ALL OTHER WITHOUT PF

  • Final Solve Timestamp Comparison:
    • SELECT Timestamp: Final Ticket Solve - Timestamp: Ticket Solve Event (MAX)

  • # Tickets Solved (Recreation):
    • SELECT COUNT(Ticket Id, Ticket Text Field Change) WHERE (SELECT Final Solve Timestamp Comparison BY Ticket Id)=0

 

First reply time in minutes

This is a complex recipe that requires several custom metrics and a three-part numeric range filter. The full recipe is already published in its own article, First reply time by event. The recipe converts results to hours and aggregates as an average.

First resolution time in minutes

This recipe requires a series of metrics that build on each other. You do not need to display the first three metrics in the report. You just need to create them so the fourth metric, First resolution time in minutes (Mdn), functions properly.

This recipe starts by finding the timestamp of the very first solve event on a ticket, using the same logic as the # Tickets Solved (Recreation) metric above. The key differences are that it uses MIN instead of MAX and allows tickets to be in an unsolved status.  

The fourth metric takes the first solve timestamp and subtracts the Ticket created at (minutes) fact (epoch timestamp of ticket creation), then restricts the metric to the event of the first solve. The difference is the first resolution time based on the date of the first solve. 

The fourth metric is aggregated here as a median to match our default metrics.

Below are the four metrics listed in the order you will need to create them: 

  • Timestamp: Ticket Solve Event (MIN)
    • SELECT MIN(SELECT(SELECT MIN(Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved) BY Ticket Id WHERE Ticket Status <> Deleted

  • Timestamp: First Ticket Solve
    • SELECT Timestamp: Ticket Solve Event (MIN) BY Ticket Id ALL OTHER WITHOUT PF

  • First Solve Timestamp Comparison
    • SELECT Timestamp: First Ticket Solve - Timestamp: Ticket Solve Event (MIN) 

  • First resolution time in minutes (Mdn)
    • SELECT MEDIAN(SELECT Timestamp: First Ticket Solve - Ticket created at (minutes) BY Ticket Id WHERE (SELECT First Solve Timestamp Comparison BY Ticket Id)=0)

Full resolution time in minutes

This recipe requires a series of metrics that build on each other. You do not need to display the first three metrics in the report. You just need to create them so the fourth metric, Full resolution time in minutes (Mdn), functions properly.

This recipe uses the same starting metrics as # Tickets Solved (Recreation) to find the final solve event on a ticket. You only need to build those starting metrics once.

The fourth metric takes the final solve timestamp and subtracts the Ticket created at (minutes) fact (epoch timestamp of ticket creation), then restricts the metric to the event of the final solve. The difference is the full resolution time based on the date of the final solve. 

The fourth metric is aggregated here as a median to match default metrics. 

Below are the four metrics listed in the order you will need to create them:

  • Timestamp: Ticket Solve Event (MAX)
    • SELECT MAX(SELECT(SELECT MAX(Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved) BY Ticket Id WHERE Ticket Status IN (Solved, Closed)

  • Timestamp: Final Ticket Solve
    • SELECT Timestamp: Ticket Solve Event (MAX) BY Ticket Id ALL OTHER WITHOUT PF

  • Final Solve Timestamp Comparison
    • SELECT Timestamp: Final Ticket Solve - Timestamp: Ticket Solve Event (MAX)

  • Full resolution time in minutes (Mdn)
    • SELECT MEDIAN(SELECT Timestamp: Final Ticket Solve - Ticket created at (minutes) BY Ticket Id WHERE (SELECT Final Solve Timestamp Comparison BY Ticket Id)=0) 

Agent wait time in minutes (Mdn)

Agent wait time in minutes is defined as the total time spent in Pending status and aggregated here as a median.

This might differ slightly from the default Agent wait time in minutes fact, due to rounding differences on ticket update timestamps.

If the value is null at ticket solve, not before, the default metric records zero. This recipe includes an extra step to mimic that behavior. If that isn’t critical, then the first metric can stand on its own.

  • Agent wait time in minutes (Mdn)
    • SELECT MEDIAN(SELECT SUM(SELECT SUM([Text Field] Duration in minutes) WHERE Text Field = Status AND [Text Field] Previous Value [Status] pending) BY Ticket Id WHERE Ticket Status <> Deleted)

  • Agent wait time in minutes (Recreated)
    • SELECT CASE WHEN Ticket Status IN (Solved, Closed) THEN (SELECT IFNULL (Agent wait time in minutes (Mdn), 0)) ELSE Agent wait time in minutes (Mdn) END 

Requester wait time in minutes (Mdn)

Requester wait time in minutes is defined as the combined total time spent in New, Open, and On-hold status and aggregated here as a median.

Please note, this may differ slightly from the default Requester wait time in minutes fact, due to rounding differences on ticket update timestamps.

If the value is null at ticket solve, not before, the default metric records zero. This recipe includes an extra step to mimic that behavior. If that isn’t critical, then the first metric can stand on its own.

  • Requester wait time in minutes (Mdn)
    • SELECT MEDIAN(SELECT SUM(SELECT SUM([Text Field] Duration in minutes) WHERE Text Field = Status AND [Text Field] Previous Value IN ([Status] new, [Status] open, [Status] hold) BY Ticket Id WHERE Ticket Status <> Deleted)

  • Requester wait time in minutes (Recreated)
    • SELECT CASE WHEN Ticket Status IN (Solved, Closed) THEN (SELECT IFNULL (Requester wait time in minutes (Mdn), 0)) ELSE Requester wait time in minutes (Mdn) END 

On hold time in minutes (Mdn)

On hold time in minutes is defined as the total time spent in On-hold status and aggregated here as a median.

Please note, this might differ slightly from the default On hold time in minutes fact, due to rounding differences on ticket update timestamps.

  • On hold time in minutes (Mdn)
    • SELECT INFULL((SELECT MEDIAN(SELECT SUM(SELECT SUM([Text Field] Duration in minutes) WHERE Text Field = Status AND [Text Field] Previous Value [Status] hold) BY Ticket Id WHERE Ticket Status <> Deleted)),0) 

Replies

The # Replies metric is defined as public comments made by an agent after ticket creation. 

  • # Replies (Recreation)
    • SELECT IFNULL((SELECT # Public Comments WHERE Updater Role IN (Agent, Admin) AND Ticket Status <> Deleted),0)

Please note, this metric will count all public agent comments without exception. The default metric only counts public agent comments after ticket creation. This will lead to a discrepancy of 1 on tickets created by agents. The agent's first comment will be included by this metric, but it will not be included in the default Replies fact.

If excluding the first comment on agent-created tickets is critical, then the following metrics can be used to filter the results. They use the # Replies (Recreation) metric above. The intermediate metrics do not need to be displayed in the report.

Below are the three additional metrics listed in the order you will need to create them:

  • Ticket Creation Event
    • SELECT COUNT(Ticket Id, Ticket Text Field Change) WHERE Text Field = Status AND [Text Field] Previous Value = (empty value) AND Ticket Status <> Deleted

  • Agent Comment at Creation
    • SELECT COUNT(Ticket Id, Ticket Updates) WHERE Public Comment true AND Updater Role IN (Agent, Admin) AND (SELECT Ticket Creation Event BY Ticket Updates)=1

  • # Replies (Filtered)
    • SELECT IFNULL((SELECT CASE WHEN (SELECT Agent Comment at Creation BY Ticket Id)=1 THEN # Replies (Recreation)-1 ELSE # Replies (Recreation) END), 0)

These metrics find tickets where an agent posted a public comment at ticket creation. The third metric subtracts 1 from the replies metric for those tickets. This is more accurate than the basic metric above, but it might still have discrepancies on shared tickets that allow public comments.

Reopens

Reopens is defined as the number of times a ticket changes from a solved status to an unsolved status.

Please note, this metric will include results when a status is changed from solved to unsolved and when a business rule changes the status back to solved within the same update. The default metric will not count these changes. 

  • # Reopens (Recreation)
    • SELECT IFNULL((SELECT # Ticket Updates WHERE [Text Field] Previous Value [Status] solved AND [Text Field] New Value IN ([Status] hold, [Status] new, [Status] open, [Status] pending) AND Ticket Status <> Deleted),0) 

Assignee Stations

Assignee stations is defined as the number of agent assignment changes throughout the ticket, including initial assignment.

Please note, this metric will include results when an agent changes the assignee and when a business rule changes the assignee back to the starting value within the same update. The default metric will not count these changes. 

  • # Assignee Stations (Recreation)
    • SELECT IFNULL((SELECT # Ticket Updates WHERE Text Field Assignee AND [Text Field] New Value <> (empty value) AND Ticket Status <> Deleted),0)

Group Stations

Group stations is defined as the number of group assignment changes throughout the ticket, including initial assignment.

Please note this metric will include results when an agent changes the the group and when a business rule changes the group back to the starting value within the same update. The default metric will not include these results. 

  • # Group Stations (Recreation)
    • SELECT IFNULL((SELECT # Ticket Updates WHERE Text Field Group AND Ticket Status <> Deleted),0)

 

Have more questions? Submit a request

Please sign in to leave a comment.

Powered by Zendesk