Insights recipe: First reply time by event (version 2)

Zendesk is removing Insights reporting on February 5th, 2021. If you are using Insights, you’ll need to move to Explore, the latest generation of reporting for Zendesk products, before that time.

In Zendesk, the default First Reply Time metric measures the time between ticket creation and the first public comment reply by an agent. It is calculated by Zendesk, then stored as a ticket property.

There are a couple benefits to the default metric:

• First reply time available in calendar or business hours
• No extra steps in Insights

There is a downside, though. As it is now, the first reply time is just a number. It does not include information about the first reply as a ticket event. The differences between the default and Event-based metrics are relevant for some specific use cases, like the following:

If there is a sharp spike in your first reply Overview graph, it is probably due to few outliers.

• The default metric requires time-consuming guesswork to narrow down to the most likely tickets.
• The Event-based metric will list all tickets that received their first reply on the day of the spike, so you can review the highest results.

If your tickets are frequently reassigned between teams, you may want to give credit to the agent who made the first reply, not the current assignee.

• This is not possible with the default metric.
• The Event-based metric can report on first reply times by the agents who submit them.

To create a report showing the first reply time based on the ticket event, you need to recreate the first reply from scratch using a pair of complex custom metrics. If you don’t need this type of report, I highly recommend using the prebuilt first reply time metrics instead. That will be much easier.

Skill level: Expert
Time required: 1 hour
Ingredient list: 2 custom metrics

Step 1: First reply timestamp

This metric finds the timestamp of the first public agent comment on a ticket after the ticket is created. It works by using two Insights facts: Ticket updated (minutes) and Ticket created at (minutes).

These facts return event timestamps in epoch minutes. Epoch minutes are the number of minutes since a standardized starting point (January 1, 1970). Epoch timestamps don’t mean much on their own, but they are great for finding durations. 

Note: These metrics use the Advanced Metric Editor. For more information, check out our guide on creating custom metrics in Insights.

Here is the metric:

• Metric: Timestamp: First agent reply
• SELECT MIN(
  SELECT MIN(Ticket updated (minutes)) BY Ticket Updates
  WHERE Public Comment = true
  AND Updater Role IN (Agent, Admin)
  AND Ticket updated (minutes) > (SELECT MIN(Ticket created at (minutes)) BY Ticket Id))
  BY Ticket Id ALL OTHER WITHOUT PF

Please note: you can’t just copy and paste in the metric editor. The color-coded items need to be selected from the Elements list on the right: 

• Ticket updated (minutes) and Ticket created at (minutes) are both under Facts
• Ticket Updates, Updater Role, Public Comment, and Ticket Id are all under Attributes
• Agent and Admin are under Attribute Values > Updater Role
• true is under Attribute Values  > Public Comment

This metric works by finding the lowest timestamp for an update with a public comment by and agent or administrator, which occurred after the ticket creation timestamp for the ticket.

The “SELECT MIN( [...] ) BY Ticket Id ALL OTHER WITHOUT PF” around the metric means it will always find the earliest timestamp for the ticket as a whole, regardless of how the report is sliced or filtered. “ALL OTHER” means it ignores all attributes under HOW (except Ticket Id), and “WITHOUT PF” means it ignores all filters (“PF” means “Parent Filter”).

Step 2: First reply duration

This metric finds the amount of time between the event of the first public comment by an agent and ticket creation. It does this by subtracting the creation timestamp from the update timestamp. The two timestamps are on the same scale, so the difference between them is the first reply time. 

Here is the metric:

• Metric: First reply time by event (hrs) [Avg]
• SELECT AVG((
  SELECT MIN(Ticket updated (minutes)) BY Ticket Updates
  WHERE Updater Role IN (Agent, Admin)
  AND Public Comment = true
  AND Ticket updated (minutes) = (SELECT Timestamp: First agent reply BY Ticket Id))
  -
  (SELECT MIN(Ticket created at (minutes)) BY Ticket Id))
  /60

Like before, you need to select color-coded items from the Elements list: 

• Ticket updated (minutes) and Ticket created at (minutes) are both under Facts
• Ticket Updates, Updater Role, Public Comment, and Ticket Id are all under Attributes
• Agent and Admin are both under Attribute Values > Updater Role
• true is under Attribute Values > Public Comment
• Timestamp: First agent reply is under Metrics (after you create it in step 1)

This metric works by finding the timestamp for an update with a public comment by an agent, where the timestamp matches the earliest agent reply timestamp on the ticket. That timestamp comparison keeps the metric locked to the first agent reply on the ticket while still allowing you to report on individual updates.

After the metric finds that first agent reply timestamp, it subtracts the creation timestamp to find the first reply time.

The “SELECT AVG( … ) /60” around the metric means it takes the average across tickets and converts minutes into hours. You can use a MEDIAN instead if you’d prefer. You can also exclude the “/60” part to leave the results in minutes.

Note: This will return results in calendar hours. There is no way to convert this type of metric to business hours through Insights.

Step 3: Construct the report

You do not need to display the Timestamp: First agent reply metric in the final report. It just needs to exist for the first reply time metric to work correctly.

This means you can use the First reply time by event (hrs) [Avg] metric on its own, similar to the way you use the default first reply time metrics. It doesn’t require any extra filters or special steps.

Here’s a simple report to get started. This report recreates the first reply graph on the native Overview dashboard: 

• WHAT: First reply time by event (hrs) [Avg]
• HOW: Date (Event)
• FILTER: List of Values: Date (Event) is the last 30 days

This report should show you the average first reply time for each day, based on when the reply was actually submitted. The results should be very close to the Overview graph:

(Overview above; Insights below)

The Overview is not customizable, so you can’t see anything beyond the graph. Insights allows you to drill into the results. You can find which tickets received their first replies each day, how long those reply times were, and who submitted them. This makes it much easier to investigate a first reply time spike.

Caveats

Any report can be skewed by odd edge cases. These are the four situations most likely to affect this report:

Deleted Tickets

This recipe includes deleted tickets. That makes it a closer match to the native Overview (which also includes deleted tickets), but it may not be as useful for other reports.

If you want to exclude deleted tickets, you can add a “List of Values” filter for the ticket status to your report. You can also add a filter the first reply metric, just after WHERE:  

• […] BY Ticket Updates WHERE Ticket Status <> Deleted AND Public Comment [...]

Two (or more) agents post replies in the same minute

These metrics are based on whole minutes, so they can’t pick out which agent was first. In an event like this, all agents will get equal credit for the first reply.

The first reply occurs in the same minute that the ticket is created

The metric described here is based on whole minutes, and it is looking for the first comment after ticket creation. It will not count any reply made in the first minute.

You can capture those replies by changing the first metric to “greater than or equal to” (>=)

• Ticket updated (minutes) >= (SELECT MIN (Ticket created at (minutes)) BY Ticket Id)….

This will include replies submitted in the same minute as ticket creation. However, it will always return a 0-minute reply time for agent-created tickets.

An agent is demoted to an end-user

These metrics use the Updater Role attribute, which looks at a user’s current role. If an agent is later demoted to an end-user, all their comments, including comments on past tickets, will no longer count for this metric. That might lead to unusually high numbers on the former agent’s tickets, since the metric will count the next agent comment instead.