Tip: This recipe enables you to find the duration between two different types of ticket event, like a group change and a comment. For durations within a single ticket field, like how long a ticket was in a certain group before escalating, use this recipe instead: Insights recipe: Reporting on duration of a text field.

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.

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).

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).

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)

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

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:

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.

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:

Option 1

Option 2

Option 3

Option 4

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!!!

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 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:

Create a Custom checkbox field and place it on you ticket form, let's call it "Ticket reopened". Check and uncheck the field box on few tickets, in order to pass this information to GoodData quicker (don't forget data sync occurs once per hour).

Set the first trigger which will mark the ticket as reopened

Create the second trigger which will un-check the box when Agents replies publicly on the ticket, in other words, upon first reply after reopening event.

The last step will be to modify your "Timestamp--Ticket Responded To" metric, you can use following configuration.

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:

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.