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.
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.
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.
19 Comments
Hi,
I need to generate a weekly report for the first reply times of each agent.
I've generated a report for each ticket with their first reply times. But I want to sort them from longer to the shorter, and put the agent names right next to each of them.
How can I do that, please?
Hi Selçuk! If you want to see which agent made the first reply, use the Updater attribute. This recipe finds the event of the first reply, so the Updater is the person who submitted it. We have more information about the various user attributes here: Which user attribute should I use in my report?
As for displaying results from highest to lowest, you can do that in the report. Hover the cursor over the metric name. That should make a pair of arrows appear. Those arrows sort the column in ascending or descending order:
I hope this helps! Happy reporting!
This is great. Thanks for sharing. Getting closer to finding a real FRT. In another article, I found a suggestion that I should add an "agent_created" tag to tickets created by an agent and then exclude them from the FRT calculation. How do I modify the recipe(s) to not count FRT for tickets that DO NOT contain the "agent_created" tag? I am happy to entertain another solution if possible too that doesn't involve tags. Our agents often create a lot of tickets which keep the FRT timer ticking so I don't want to count those.
Hi Felipe! Tags are great on the workflow side, but they get tricky on the reporting side. It's especially tricky in cases like this, since tags are not connected to ticket events.
Tags and events are both connected to tickets, though, so you should still be able to add a tag filter. It will just take a few extra steps. We have details in this recipe: Reporting on ticket tags.
Basically, you'll need to make a tag-filtering metric, then use a numeric range filter on the report as a whole. That way, the report will only run the first reply metrics on tickets that have (or don't have) your chosen tag.
As long as you keep the tag elements in a dedicated report filter and your event metrics in the report itself, they should work alongside each other.
I hope this helps! Happy reporting!
Hey @Amy Dee. Is there anyway to tweak this to show the time between Ticket creation and Last Public Comment?
Hi Lisbeth! That should be possible by switching MIN to MAX throughout the metrics. That would find the highest timestamp of a public agent comment, then subtract the ticket creation timestamp. The result should be the duration between ticket creation and the most recent reply.
Side note - for the SELECT MIN(Ticket created at (minutes)) elements, it doesn't really matter whether you use MIN or MAX. There is only one creation timestamp per ticket, so it should return the same number either way.
I hope this helps! Happy reporting!
Hey @Amy Dee
I don't think it worked as expected:
My metrics are:
Last reply time by event (hrs) [Avg]
Timestamp: Last agent reply
I am getting the same results as First Time reply.
Hi Lisbeth! There are a couple issues here.
First, in your "Timestamp: Last agent reply" metric, you replaced the Ticket created at (minutes) fact with Ticket updated (minutes). The end of that metric should be ...Ticket updated (minutes) > (SELECT MAX(Ticket created at (minutes)) BY Ticket Id)... That piece means it only looks for updates that happened after ticket creation, so the metric doesn't record a 0 for agent-created tickets.
Second, your "Last reply time by event (hrs) [Avg]" metric still has the Timestamp: First agent reply metric nested in it. That means it's still looking for an update that matches the first reply timestamp. You need to put the creation timestamp back in the "Last agent reply" metric, then replace the nested metric here.
Once you update the timestamps, you should get the duration from creation to the last agent reply instead of creation to the first agent reply.
I hope this helps! Happy reporting!
Thanks Amy Dee! It worked. I looked through it like 10 times but I missed those little details :(
You are a genius. Thanks for putting this together.
Hello Amy,
I am unable to find 'agent' or 'admin' under any of the elements. I can do a general search and find nothing, I can drill down to where it says above it should be listed. And even in a manual search I cannot find just 'agent' or 'admin'.
I see now that I only have 'Attributes' if I click on Attribute Values or Attribute Labels, within the element window it just says 'Attribute'.
Is there a different service/license level that unlocks the other two elements?
Hey Jim,
I'm going to generate a ticket on your behalf so our Advocacy team can dig into this further. You'll receive a follow-up email shortly stating your ticket has been created so feel free to reply back to that email if you have any additional information to provide.
Thanks!
Thanks Brett. I have the email and ticket number.
Is there a version of this for explore?
Thanks!
Hi, Katie. I believe this Explore article could be what you're looking for:
Please let me know if you have any further questions.
Thanks!
Thanks Carlos. That's close, but I wanted to do it based on the date the first reply occurred instead of the date the ticket was created.
Hello,
Do you know how to adjust these queries or how to write queries to instead show the DATE of the first reply time? not the minutes or hours.
Thank you,
Amanda
Hi,
Is there a way to change the number to HH:MM?
For example 16.31 = 16:18
Thanks in advance
Tom
Hi Tom! Yes, you can change the display formatting to show HH:MM. To start, you'll need to remove the /60 from the end of the metric. That way, the results will be in minutes.
From there, you can use arithmetic format blocks to change the display. These should be entered in the metric format box:
Here are a couple options that you can copy and paste:
The first simply displays hours and minutes, while the second will also display days if the number gets that high. For an idea of what this looks like in a chart, here are three example tickets displayed in minutes, HH:MM, and DD HH:MM:
GoodData has more tips and tricks for the metric format box in their article on Formatting Numbers in Reports.
I hope this helps! Happy reporting!
Hey Amy,
I am trying to create a "Last reply time by event (hrs) [Avg]" in Explore. My current recipe in Insights is:
Please sign in to leave a comment.