Explore recipe: Reporting on the duration of fields

Return to top
Have more questions? Submit a request

25 Comments

  • Jason Brown

    Hello,

    We are currently looking for a way to combine these two metrics, so that we can understand how long a ticket is in the Open status for each ticket group. We have run into an issue in that we can only slide this by updated group, meaning if a ticket is opened in tier 1, and moves to tier 2, then the entire open time is reported for tier 2.

    Is there any way to slice the open time by previous group/current group instead of updated group?

    Thanks,
    Jason

    0
  • Eugene Orman
    Zendesk Product Manager

    @Jason, 

    You can use the Ticket group attribute to return the current ticket group or Update ticket group to return the group of the ticket set at each update. Here is an example:

    0
  • Wesley Bowles

    How would we go about doing this for a custom drop down field?

    For example we have a field called "Custom field" with Value1, Value 2, Value 3, Value 4

    Im trying to figure out the calculated metric for the time spent in each of those values. 

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hell Wesley Bowles,

    This article goes into detail on how to set up your reporting with custom drop fields that I've linked below. If you have any trouble setting this up, please let us know, and we'd be happy to troubleshoot it.

    Reporting with custom fields

    Best regards. 

    0
  • Bryan Telfer

    Is there a way to exclude any time outside of business hours?

    1
  • Brett Bowser
    Zendesk Community Team

    Hey Bryan,

    You should be able to filter your queries by business hours using the following recipe: Explore recipe: Filtering queries by business hours

    I hope this helps!

    0
  • Eugene Orman
    Zendesk Product Manager

    Bryan Telfer, the recipe that Brett provided allows filtering a report based on the ticket creation time or any other time attribute, but it will not help you to exclude the time ticket spent outside of the business hours in the specific field value.

    0
  • Bryan Telfer

    Eugene Orman Is there a formula that could allow me to exclude the time that the ticket spent outside of working hours? Tickets that sit in an open, pending, etc. status over the weekend are greatly offsetting my query.

    0
  • Eugene Orman
    Zendesk Product Manager

    Bryan Telfer Unfortunately, there is no formula that would allow you to create such clever logic. 

    0
  • Miyahara KEIKO(宮原 恵子)

    Is it a correct formula to measure the time between ticket creation & ticket update to a certain value (Yes)?

    I want to make a custom SLA-related metric for each of the restoration steps (finer steps than the available SLA metrics)

    IF ([Changes - Field name]="certain field name in multi-select" )
    AND ([Changes - Previous value] = NULL)
    AND ([Changes - New value] = "Yes")
    THEN VALUE(Field changes time (min))
    ENDIF

    Thanks!

    0
  • Brett Bowser
    Zendesk Community Team

    Hey Miyahara,

    If you're trying to measure the duration between ticket field changes then the above formula should work for you. Are you running into any issues with the metric you provided?

    Let me know!

    0
  • Shelly Fleishaker

    The avg here is is calculated every time the ticket moved to another group. meaning it the avg time per 'visit'

    But if I want to know the avg time that group spent per ticket and not per visit - how do I do that?

    for example - ticket 1234

    group A 12 min

    Group B 15 min

    Group A 10 min

    ticket 1122

    Group A 20 min

    Group B 5 min

    Group A 30 min

    Group B 25 min

    in the above example the average will be:

    Group A: (12+10+20+30)/4 = 18

    Group B: (15 + 25 + 5)/3= 15

    Per ticket Should be:

    Ticket 1234:

    Group A 12+10 = 22

    Group B 15

    ticket 1122:

    Group A: 20 + 30 = 50

    Group B: 25 + 5 = 45

    Avg:

    Group A (22 + 50) /2 = 36

    Group B:  (45 + 15) / 2 = 30

     

    As you can see the avg are different

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hello Shelly Fleishaker,

    At this time, there is no way to accomplish this in Explore natively. I would recommend sharing your use case with product feedback so our developers can consider implementing this change for future updates. 

    Best regards. 

    0
  • Edouard Gonet

    Hello Devan - Community Manager,

    Is there a way for calculating how long tickets sit with a support group in a specific status (open) ?

    Thanks!

     
    0
  • Taylor Bowser
    Zendesk Customer Advocate

    Hi Edouard

    Thanks for reaching out! You can pull this type of report using the Support: Ticket Updates dataset as Eugene mentioned earlier in this thread. I recommend using these metrics and attributes if you'd like to break down this report by individual tickets: 

     

    Metrics: Open Status Time (min/hrs/days) 

    Rows: Ticket ID, Update Ticket Group

    If this isn't quite what you're looking for let us know and we'd be happy to take another look! :) 

    0
  • Amit Aharoni

    Hi Zendesk team.

    When I approach a customer of ours, I set a reminder (trigger) for a few days later in case the customer hasn't responded yet - in this case the ticket changes form Pending to Open.

    It seems that when the remainder scenario takes place, Zendesk attributes the time from Pending to Open to my team - distorting the actual amount of time which the ticket was under my team's care.

    Could you think of a solution to the problem?
    Please take under consideration that we want to see data for all groups who touched the ticket and only for the current one.

    0
  • Taylor Bowser
    Zendesk Customer Advocate

    Hi Amit, 


    Thanks for reaching out! I'd like to get a better understanding of your workflow for this trigger. Is the trigger configured to open the ticket so that the agent sees it has not been responded to? Once this ticket is re-opened,  does the agent solve it out or send another response to the end user? 

    It may be worth checking out this article that walks you through how to set up automated reminders for end users that have not responded to their ticket. This configuration does not move these tickets to open status preventing them from skewing with your data. 

    Hope this helps!

    0
  • Amit Aharoni

    Hi Taylor,

    Thank you very much for your answer.

    I'm aware of the method you've shared in article above and using it for some cases.
    Other cases requires a representative discretion regarding how to move forward with the case in hand - hence the status change to open and the resurfacing of the ticket.
    So I'm afraid that won't solve the issue.

    If we've already touched Zendesk's timing mechanism, is there a way to set up a definite time for action?
    For example, instead of configuring 96 hours, can one set up an event to take place at 2:57 PM 19/10/2020?

    0
  • Taylor Bowser
    Zendesk Customer Advocate

    Hi Amit, 

    Thanks for clarifying! It isn't possible to do this natively within Support but you could utilize third party apps to set reminders for specific times and dates without changing the status of the ticket. Here are a couple apps you may find useful for your workflow: 



    0
  • Eshmeet Singh

    I am trying to create a calculated metrics using "[Changes- field name]", however the field list does not display an option that starts with the key word "[Change". Is there something I am missing here? 

    I even tried copy pasting the formula given in this article, but it is not accepting any field that starts from the key word [Change". 

    Could you please help how can I add this field to the calculated metrics.

    Thanks

    Eshmeet

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Eshmeet Singh, thanks for the question!

    If the field isn't appearing, it's possible you're not using the correct dataset. You must use the Support: Ticket updates dataset to see that metric. I hope this helps!

    0
  • Eshmeet Singh

    Thanks Rob, right I was using incorrect dataset. It fixed my problem!

    Eshmeet

    1
  • Rob Stack
    Zendesk Documentation Team

    Excellent, thanks Eshmeet Singh I'm glad it's working for you now :-) 

    0
  • Eshmeet Singh

    Hello Rob, I tried applying the same metrics to calculate duration between two custom field values. However, it's not throwing any results.

    Below is the calculated metrics I applied:

    • Here "Calendar Status" is the custom field and it has drop down values of "Build Assigned" and "Build Complete".
    • I am attempting to find duration between "Build Assigned" and "Build Complete" status submitted by the agents.
    • Query just returns blank values.

    Metrics used

    IF ([Changes - Field name]="Calendar Status")
    AND ([Changes - Previous value] ="Build Assigned")
    AND ([Changes - New value] = "Build Complete")
    THEN VALUE(Field changes time (days))
    ENDIF

    Is there something I am not doing right in this query? thanks

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Eshmeet Singh, while I'm unable to try this with your particular custom field, your formula looks correct. 

    What I like to do is try breaking down the formula into smaller sections for testing. Here's a suggested order:

    Confirm that the Calendar status field has data and that you can report on it. To do that, you could try adding the Calendar status attribute to the rows column of a query.

    Then, try taking one or both of the AND statements out. Does it still fail?

    If you're still having problems, I'd recommend posting this question over in the Explore Q and A section at https://support.zendesk.com/hc/en-us/community/topics/360001200893 - there's a bunch of query experts over there who I'm sure will help you out. Thanks!

    0

Please sign in to leave a comment.

Powered by Zendesk