Zendesk Insights captures and enables you to report on pre-built timestamp metrics, such as First Reply Time and First Resolution Time.
To report on other times, you need to create your own custom metric in Insights (see Creating custom metrics in Insights). You can use custom metrics to retrieve the epoch timestamps (in minutes) for specific ticket updates by using the Ticket updated (minutes) fact. You can then create a third custom metric to subtract your most recent occurring event (Event 2) from the older event (Event 1), and receive the duration between your two events.
This recipe will instruct you on how to create these custom metrics. If you want a more thorough guide on creating custom metrics, see Creating custom metrics in Insights and Building custom metrics for the Events model in Insights. For information on creating reports, see Building custom reports in Insights.
This recipe contains the following sections:
- Retrieving timestamps using custom metrics
- Finding the duration between ticket events
- Creating your report
Retrieving timestamps using custom metrics
This recipe will includes two different examples, Tier 2 first reply time and First internal note reply time. You can follow this recipe and substitute the formula for the example's metrics with your own. The formula of your metric will depend on the report you are creating.
Retrieving the first event timestamp (Event 1)
This section will instruct you on how to retrieve the timestamp of the first ticket event for the duration. This section contains example formulas for retrieving the timestamp for when a ticket is sent from Tier 1 to Tier 2 (Tier 2 first reply time) and when a ticket is created (First internal note reply time).
These recipes use the Advanced Metric Editor in Insights. For details on how to work with the metric editor, please refer to this guide: Creating custom metrics in Insights.
The metrics for this recipe are as follows:
- T1 to T2 timestamp
SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Group] Tier 2 AND [Text Field] Previous Value = [Group] Tier 1) BY Ticket Id)
- Ticket created timestamp
SELECT MIN( SELECT( SELECT( SELECT MIN (Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE Text Field = Status AND [Text Field] Previous Value = (empty value)) BY Ticket Id)
Please note, you can't copy and paste in the metric editor. Color-coded items need to be selected from the "Elements" list on the right.
- Blue elements are under Facts
- Purple elements are under Attributes
- Orange elements are under Attribute Values > Attribute. For example, Status is under Attribute Values > Text Field.
- Green elements are under Metrics
Retrieving the second event timestamp (Event 2)
This section will instruct you on how to retrieve the timestamp for the second ticket event. This section will show formulas for retrieving the timestamp for when Tier 2 leaves a public comment (Tier 2 first reply time) and when an agent left a private comment (First internal note reply time).
The formulas for the first event examples in this tip are as follows:
- First public agent comment in T2
SELECT MIN( SELECT ( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true AND Updater Role IN (Agent, Admin) AND Ticket updated (minutes) > (SELECT T1 to T2 Timestamp BY Ticket Id)) BY Ticket Id)
- First private comment
SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = false AND Comment Present = true) BY Ticket Id)
Finding the duration between ticket events
After you have created your custom metrics, you can find the duration between the two metrics. You can also change the aggregation, to alter how your duration is measured. For example, you can use the AVG aggregator to retrieve the average duration.
The formulas for the examples in this tip are as follows:
- T2 first reply time
SELECT AVG((SELECT First public agent comment in T2 BY Ticket Id) - (SELECT T1 to T2 timestamp BY Ticket Id)
- First internal note reply time
SELECT AVG((SELECT First private comment BY Ticket Id) - (SELECT Ticket created timestamp BY Ticket Id))
Creating your report
After you have created your third metric, you can then add it to your report. This section will instruct you on how to add your metrics and create your report. For more information on creating, editing, and customizing reports, see Building custom reports in Insights.
- In the What panel, select your third metric.
- In the How panel, select any attributes you would like to include in your report. For example, you could use the Ticket ID attribute.
- Click Done.
- Click on the Filters button to add any filters to your report. For example, you can add the Numeric range filter, to filter out all tickets that do not have any duration (Filtering your report).
This filter would resemble the image below:
- After you finish customizing your report, click Create at the top left corner.
96 Comments
This is AWESOME! Thanks for documenting!
Would like to use this but I created triggers to auto assign all our tickets to their appropriate Tier groups ( we call them Pods). How would I create a time stamp to get first response agent update/comment when ticket is in open status and assigned?
@Corrin - Most of this can be done with a few modifications to these metrics. For example, if tickets are routed directly to the right group, you could use the “Timestamp - T1 to T2" metric above, but remove the [Text Field] Previous Value part. That should show you the first time a ticket was assigned to a group, regardless of whether it was assigned before that.
As for the first agent response, that gets tricky. Filtering by the status or assignee can be complicated, and it depends heavily on your workflow. Instead, you could modify the “Timestamp - First private comment” metric above to find the first agent comment after a ticket is assigned to the group.
To do this, you can change the Public Comment part to true, or remove it entirely if you want to count any comment. Next, after the comment conditions, you could add "AND Updater Role IN (Agent, Admin)" to limit it to comments made by agents. Finally, after the "BY Ticket Id", you can add "WHERE Ticket updated (minutes) > Timestamp - Assigned to group-name, using the name of your first metric. The final result would look like this:
SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes)
BY Ticket Updates) WHERE Public Comment = true
AND Comment Present = true AND Updater Role IN (Agent, Admin)) BY Ticket Id WHERE Ticket updated (minutes) > Timestamp - Assigned to group-name)
These metrics should allow you to find the amount of time from when a ticket is first sent to a group to the next public comment from an agent. Keep in mind that this is only a starting point. There are edge cases and exceptions that can skew your results. You may need to fine-tune the metrics a bit to better fit your workflow.
@Robbert: Thanks a bunch! This is awesome, just what I've been looking for!
@Amy: Thanks for the variations and comments, also very helpful!
I'm such a fan of this, I have a follow-up question.
What I'm trying to do, is get a number of how many tickets were ever in a given state. The state I'm looking for is based on a custom drop-down field.
For example. Say I have a custom drop-down field that have the following options:
I'm trying to see how many tickets were ever saved set to "Option 3."
I was thinking that I might be able to accomplish this using timestamps. If I could record a timestamp of when the ticket was saved at "Option 3," I should be able to then create a metric of how many tickets have a timestamp. Right?
Well, I'm having trouble. I don't see a way to build a metric that counts the number of tickets that have a timestamp, and not to count the ones that don't.
Am I thinking about this the right way? Is there a better way for me to get this number? THANKS!!!
Hi Sam,
It has been a while since I last messed around with custom metrics, so I could be a bit off, but I think you could create something simpler, like:
SELECT COUNT(Ticket Updates,Ticket Text Field Change)
WHERE [Text Field] New Value = [[The value option of your choice]]
and use that metric as a Numeric filter where:
1 What do you want to filter?
Ticket Id
2 Select metric and range
where your Metric is greater than 0.
You still need to specify the report's WHAT and possibly HOW also.
There is an outstandingly good article on creating custom metrics for the events model here.
Hope that helps!
Yes! I actually worked with Zendesk support and came up with something similar.
Thanks!
This article is great, thank you!
I have everything working perfectly, but now I'm trying to display the average reply time for all tickets per month. I thought this would work:
Average T2 Reply Time Metric:
SELECT AVG (T2 first reply time)
Then I make that metric my WHAT and set Month/Year (Ticket Created) as my how, but the information being return isn't correct.
Can anyone help me figure this out?
Cheers!
Hi Keith,
This is quite strange, based on your metric example the report should display the correct data. In order to investigate this further I'll need specific metric/report examples. I have created a ticket for you, so let's continue communication within the ticket.
I'm trying to calculate response times beyond the first reply time. I thought this set would work:
Timestamp--Ticket Reopened
SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field ChangeWHERE Text Field = Status AND Updater Role = End-user AND [Text Field] New Value = [Status] open AND [Text Field] Previous Value = [Status] new OR [Text Field] Previous Value = [Status] pending) BY Ticket Id)
Timestamp--Ticket Responded To
SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true) BY Ticket Id)
Average Re-reply Time
SELECT AVG(Timestamp--Ticket Responded To - Timestamp--Ticket Reopened)
I'm getting negative numbers in the report, which doesn't seem right -- could it be that I've got the final metric's arithmetic in the wrong order?
Hi Cameron,
Timestamp--Ticket Reopened
If this metric should capture the timestamp of the ticket reopening event, then [Text Field] Previous Value must be [Status] Solved:
Timestamp--Ticket Responded To
I don't think that the metric configuration will help you to track the right ticket event, it just returns timestamp of the first public comment on the ticket. But in fact you'll need to capture first public comment submitted by agent after ticket got reopened. To do so you could set few triggers which will help you to track this event. Here is an example of the set-up:
Average Re-reply Time
This metric is set correctly, so you don't need to modify it.
You're a wizard, Eugene! It's working brilliantly. As long as I remember to set the date after July 13th 2016, everything looks good to go.
Thanks!
Hi!
Do you know any way to catch up business hours inside these metrics?
How should I get the timestamp of the last user (either agent or end-user) update on a ticket? I'm trying to get the full resolution time from the very last update of a person on a ticket instead of when the ticket is in "solved" status.
To Andrei - These recipes will not be able to show results in business hours, unfortunately. GoodData cannot access your schedule, so it has no way to know what your business hours are. Also, these recipes are based on epoch timestamps, which do not show the date or time of day without extra conversion steps.
To Denielle - Looks like you changed the question in your post after I wrote a reply. Whoops!
If you're looking for the timestamp of the last user update on a ticket, you could try something like this:
You will certainly want to test that out in your account with your workflows before using it in a report. However, it looks promising in my own tests.
Happy reporting!
Thanks @Amy! I'll definitely give that a try!
Hi @Amy!
I am trying to get a created_at like timestamp i.e.
However, I can't seem to get it from insights. I tried converting an Epoch timestamp from above in an epoch time converter, and it also did not properly represent the time the ticket was submitted.
For example, 24607424 translates to
GMT: Mon, 12 Oct 1970 19:23:44 GMT
Your time zone: 10/12/1970, 12:23:44 PM GMT-7:00 DST
But when I review the ticket, I get a created at of:
My ultimate goal is to get a created_at timestamp and a assigned_at timestamp, that are formatted in this way:
Any help would be greatly appreciated!
Hi Matthew! The timestamps in Insights are displayed in minutes, while most epoch converters work in seconds. That could cause the discrepancy.
From your example, 24607424 * 60 is 1476445440. When I put that time in an epoch converter, I get this: Fri. 14 Oct 2016 11:44:00 GMT.
There isn't an easy way to convert the epoch timestamp to a standard date/time within Insights. Those timestamps are usually used as intermediate steps in other metrics, not as standalone results.
You may be able to do some creative math within the metric and/or elaborate functions in the formatting (GoodData has documentation here). It's probably more reliable, though, to convert them outside of Insights.
Happy reporting!
I am trying to replicate the report explained in this article above and do not seem to get any data.
I tried opening a ticket but looks like the support team does not help on custom metrics.
Hoping for some from the community on this :). Below are the metrics:
Ticket created timestamp
SELECT MIN(SELECT(SELECT(SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE Text Field = New AND [Text Field] Previous Value = (empty value)) BY Ticket Id)
L2 to L3 timestamp
SELECT MIN(SELECT(SELECT(SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = Kony L3 Product Support AND [Text Field] Previous Value = Kony Product Support) BY Ticket Id)
L3 leaves first public comment
SELECT MIN(SELECT(SELECT(SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true) BY Ticket Id)
Time Taken for first Public reply
SELECT AVG (L3 leaves first public comment - L2 to L3 timestamp)
Appreciate any help
Thank you
Manish Khemka.
@Manish,
Could you post a screenshot of your metrics? The color-coding is helpful to ensure that the metrics are being built in the correct way.
Here you go:
WHAT: Time Taken for first Public reply
How: Ticket Id
I'm trying to create a filter like Hour Ticket Created, but by half hour intervals instead. Is there a way to create a custom metric with this by retrieving the time stamp?
@ Natalie, It is not possible to report on half hour intervals in Insights.
Thanks! this is very usefull!
If i want to see for example all tickets that yesterday changed from Open to other status, how can i define the date in the report?
Thanks!
Hi Samuel! If you just want to count tickets with a certain type of update, you don't need to use Ticket updated (minutes). That fact is useful for durations, but not as much for dates.
In your case, I would recommend something like this:
This should count the number of tickets that had their status changed from Open to something else at least once during the reporting period. You can use Date (Event) to see results for specific days.
This metric is just a basic starting point for demonstration purposes. I can't guarantee it for all use cases, and I highly recommend auditing your results.
I hope this helps! Happy reporting!
Hi All,
I am new to MAQL and need some help please.
I am trying to replicate the above query for Ticket created time stamp but I am getting the below error
Unexpected 's'. Expecting: ELEM_IDENTIFIER, FUNCNAME1, NEXT, OBJECT, STRING, -, RANKFUNCNAME, IF, PREVIOUS, FUNCNAMEN, COUNT, RUNFUNCNAME, REAL, IDENTIFIER, FUNCNAME2, CASE, FUNCNAME12, PERCENTILE, TEXT_ELEM, THIS, NATURAL, (, IFNULL
Regards
Raj
I change the status to New I get the below error
Please help
Regards
Raj
Hi Raj,
Looks like your attribute values are not connected (the orange parts) - they are text only. Also it doesn't appear to be exactly set up like in your first screenshot.
Thanks Jacob :)
Got it!!
I created a test report & the values looks something as below
Could you please help, how do I fix this
Regards
Raj
I don't know exactly what you want to do, have you gone through all the steps in the article above?
I'm guessing you may need to filter out tickets that have no duration. Using a numeric range filter like described at the very end of the article.
Please sign in to leave a comment.