Insights recipe: Work time between first reopen and final solve using Time Tracking

Insights has now been removed for most customers. If you used Insights before, you'll now need to move to Explore, the latest generation of reporting for MorgWard products.

There are a number of reasons why a ticket might be reopened by an end-user. They could be looking for clarification on an agent's response or have other unrelated questions. There could also be a number of reasons why it would be important to report on the amount of time your agents spend on these tickets after they've been reopened.

You can achieve these reports using the Time Tracking app and custom metrics. The app's ticket attributes only record the times of the most recent updates or the total time spent on the ticket, which would include the time before the ticket was initially solved.

Custom metrics enable you to record the timestamp of the first transition from solved to open, the time stamp of the final transition from solved to closed, and add the time spent on individual updates by agents between these two events. You will need to use the Events data set in these metrics.

For more information on creating custom metrics see, How to create a custom metric (GoodData).

Skill Level: Advanced

Time to Complete: 30 mins

Ingredient List

• 3 custom metrics
• Time tracking app
• 1 bar chart

Instructions

The Time Tracking app must be installed. Instructions on how to install the app can be found at Time Tracking integration 

Create a new Insights report

1. Click the Reporting icon () in the sidebar, then click the Insights tab.

2. Click the GoodData link in the top right corner of the panel.

3. In GoodData, in the top menu, click Reports>Create Report.

Metric 1: First Reopen

 Create a custom metric to record the ticket reopen event. 

1. In the What panel, select Add New Metric.
2. Click on the Advanced Metric Editor.
3. Name your metric 'First Reopen'.
4. Create your metric. The metric is defined as: 
   • SELECT MIN (SELECT (SELECT (SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Status] open AND [Text Field] Previous Value = [Status] solved) BY Ticket Id)
   • 'Ticket updated (minutes)' can be found in the Facts folder. 
   • 'Ticket Updates', 'Ticket Text Field Change', '[Text Field] New Value', '[Text Field] Previous Value', and 'Ticket Id' can be found in the Attributes folder. 
   • '[Status] solved' and '[Status] open' can be found in the Attribute values folder. 
5. Click Add to add your metric. 

 

Metric 2: Final Solve

Create a custom metric to record the final solve event. 

1. Enter the Advanced Metric Editor.
2. Name your metric 'Final Solve'. 
3. Create your metric. The metric is defined as: 
   • SELECT MAX (SELECT (SELECT (SELECT Ticket updated (minutes) BY Ticket updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Status] solved AND [Text Field] Previous Value in ([Status open , [Status hold, [Status] pending)) BY Ticket Id)
   • '[Status] hold' and '[Status] pending' can be found in the Attribute Values folder.
4. Click Add to add your metric. 

 

Metric 3: Agent Work After Reopen

Create a custom metric to sum the agent work time between the two events. 

1. Enter the Advanced Metric Editor.
2. Name your metric 'Agent work after reopen'.
3. Create your metric. The metric can be defined as:
   • SELECT SUM ([Numeric Field] New value/60) WHERE Numeric Field =Time spent last update (sec) AND Ticket updated (minutes) BETWEEN First Reopen AND Final Solve. 
   • '[Numeric Field] New Value' can be found in the Facts folder. 
   • 'Numeric Field' can be found in the Attributes folder.
   • 'Time spent last update (sec)' can be found in the Attribute Values folder. 
   • 'First Reopen' and 'Final Solve' can be found in the Metrics folder.
4. Click Add to add your metric. 

 

Complete report

1. Select the How panel to add report attributes.
2. Select 'Ticket Id' to view which ticket IDs have work time after a reopen. 
3. Click Done to complete your report.

 

Customize the report

1. Add a Numeric Range Filter. Filter by 'Ticket Id' where the 'Agent Work After Reopen' custom metric is greater than 0. This filter prevents the metric from returning a 0 for any tickets that weren't reopened after being solved.
2. Click Apply. 

   

3. Select the bar chart icon at the top of the report.
4. Your finished report should look like the one below.