Explore's built-in metrics can display the amount of time it takes from tickets being created to their first public agent comment. This is known as first reply time. However, sometimes you might want to return the actual date and time of the first reply. While this isn't included as a metric or attribute in Explore, you can use this recipe to create a standard calculated attribute that returns this information.
What you'll need
Skill level: Average
Time Required: 5 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Building your calculated attribute
- 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.
- In the calculations menu (
), click Standard calculated attribute. - On the Standard calculated attribute page, give your attribute a name like Ticket first reply - Date.
-
paste the following formula:
IF (VALUE(First reply time (min))!=NULL)
THEN DATE_FORMAT(DATE_FROM_TIMESTAMP(DATE_TO_TIMESTAMP([Ticket created - Timestamp])+VALUE(First reply time (min))*60),"YYYY-MM-dd")
ELSE "-"
ENDIFTip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - When you are finished, click Save.
You can now use this calculated attribute in your own reports. For example:
16 Comments
How would you write this if you wanted to group your First reply time by the week of the year. I want to see the reply time for the tickets updated each week so if a ticket gets opened on a Friday night. We won't respond to it until Monday which is the following week. Otherwise, when I report the week's response times it will change later on that day and it will make my reporting look in accurate.
Hello @...,
You can do this by changing the last part of the formula, which formats the date. For example, if you want this formula to show the year and week number, you can change the formula to this:
Here is a link to an article that describes the values you can use in the DATE_FORMAT() function:
Hello,
I want actual first reply datetime, so create below formula based on article
https://support.zendesk.com/hc/en-us/articles/360039944933-Explore-recipe-Creating-a-ticket-first-reply-date-and-time-attribute
"IF (VALUE(First reply time (min))!=NULL AND VALUE(First reply time (min))>0)
THEN DATE_FORMAT(DATE_FROM_TIMESTAMP(NUMBER(DATE_TO_TIMESTAMP([Ticket created - Timestamp]))+VALUE(First reply time (min))*60),"YYYY-MM-dd HH:mm:ss")
ELSE "-"
ENDIF"
However, it is not working and giving blank values, please help....
BR,
Vinod
@...
For the timestamp you can use this formula:
However, the date format "MI" syntax is not fully supported. It might be updated in future which will affect your calculation.
Thanks Eugene for your reply.
However, this "MI" addition is not working here.
Not sure, if this is the correct way to get the actual datetime of first reply.
@Vidon, This formula works on my test account. To investigate this issue further will be good to have a look at your query. I have created a ticket for you with our Support team.
can you provide the solutions to all past customers here instead of saying "we're going to submit a ticket and someone will get back to you"?
Hello @...,
When we pull issues brought up in the community to a private ticket, this is to work with that user to resolve their problem. At times, this requires going over personal information and data, which can be part of the solutions, and create resolutions unique to their account. When we have troubleshooting steps that can be applied as a general fix, we will often publish them in the help center. However, if you are facing an issue similar to another user's post, we'd be happy to assist if you could share the details of this problem.
Best regards.
Hi there,
Don't know if anyone has brought this up before, how do we filter this attribute like all the other time attributes? (eg. This year, Last month, etc.)
I've check the box "sort like time attribute" when creating it, apparently it doesn't work.
Thanks!
Hi Mengxin,
Unfortunately, it is not possible to add a date range as a filter for custom attributes. For more information, please see Can I add a custom date range in a custom date field attributes?
However, as a workaround, you can create custom attributes that will instead show the Year/Month by modifying the formula in this article. The simplest way is, instead of displaying the result in "YYYY-MM-dd" format, you can remove the month and date to only get the year. See below:
You can name this "Ticket first reply - Year" and use it as a filter instead. You can also do the same for Month by leaving only the "MM" in the result.
Regards,
Giuseppe
Hello, this custom field started to fail in my ZD instance. I reached support via chat and they said is a problem with the amount of data I was trying to get. However the query I was using used to work fine on 05/31/2021 and now this field is not showing recent data even if I cap the search to one week in the past.
Thanks in advance.
Hey, Andres!
Thanks for creating this feedback post. I’ve checked on this on your account and confirmed that data may not load as instantly as possible due to the number of data being pulled up, as advised by my colleague you got in touch with via Live Chat. But I see the attribute is already working as expected. Should you have other concerns, please let us know!
Cheers!
@...
Please can you share the answers to @...'s and @...'s issues?
Thanks.
Hello @...,
Those issues were unable to be resolved in the tickets created on their behalf, but I'd be happy to try and help find a solution for your issue if you could share some details on what is currently impacting you right now.
Best regards.
Hello ZenDesk support,
I have followed the steps which are mentioned in this article but the querie I created vs the default FRT dashboard view is not matching.
This querie won't exclude the tickets created by the employees.
Example: If I create a ticket with in ZenDesk on 10th Aug 2021 and reply to it the next day, this querie is calculating the FRT as 24 hrs. Tickets like this should be excluded.
How do I go about excluding such tickets created by the employees/agents?
Awaiting your response!!
Hey @...! You can use the Submitter role attribute to filter your query to only tickets submitted by end-users.
Please sign in to leave a comment.