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

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. 

 

 

Have more questions? Submit a request

Comments

  • 0

    I cannot find the "Time Spent Last update (sec)" anywhere for the "Agent Work After Reopen" metric. We have had the Time Tracking app installed for some time now... can you help?

  • 0

    I have since located the above.. but now that I have built all 3 metrics, my report is showing blank.

  • 0

    Hi Abbey! I see you submitted a ticket about this request - I'll follow up with you there!

  • 0

    I'm also seeing "No data match the filtering criteria".  I noticed that my MAQL queries are slightly different for "First Reopen" and "Final Resolve".  When selecting attribute values for ticket status it writes the query as "open" and not "[Status] open"

    Here is my query for First Reopen as an example:


    SELECT MIN (SELECT (SELECT (SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = Open AND [Text Field] Previous Value = Solved) BY Ticket Id)

     

    I also noticed in the recipe that Time Spent Last update (sec) is listed as a metric, however I noticed that Time Spent Last update (sec) is listed as a Fact under my current configuration--would this also impact reporting?

  • 0

    Hi Natalie,

    The reason you're seeing no data is that the attribute values need to be selected from within the [Text Field] Previous/New Value attribute in the Attribute Values folder (ex. Attribute Values > [Text Field] New Value > [Status] solved). It sounds like you've found these values under the Ticket Status attribute.

    Regarding the Time Spent Last Update (sec) element listed in the screenshot-- this is also an attribute value, but for the Numeric Field attribute. You'll find this element in a similar way-- Attribute Values > Numeric Field > Time Spent Last Update (sec).

    I hope this helps! Happy reporting!

Please sign in to leave a comment.

Powered by Zendesk