Building custom metrics for the Events model in Insights (Professional and Enterprise)

Have more questions? Submit a request

74 Comments

  • Christa Heinrich
    Comment actions Permalink

    Trying to create a report for the last 30 days showing the number of solved tickets per timeframe. (ie 10 tickets solved within 1 hour, 3 tickets solved within 2 hours, etc).

    Can anyone help??

    0
  • Jacob J Christensen
    Comment actions Permalink

    Hi Christa,

    Insights has some pre-built metrics for three such time frames:

    • Fast resolution (solve took less than 2 hours)
    • Normal resolution (solve took 2-8 hours)
    • Slow resolution (solved took longer than 8 hours)

    You could either use those or create duplicates of them and modify them for your preferred time frames.

     Hope that helps you out, let me know if you need more details to move forward.

    0
  • Christa Heinrich
    Comment actions Permalink

    Thanks Jacob! That's really helpful!

    How do I get the actual number of tickets to show next to each section like you have listed?

    Also how can I modify this to get my preferred time frame?

    0
  • Montoya, Maita L.
    Comment actions Permalink

    Hello!! First time to create a new metric. :) I was told by zendesk support to replicate this metric for a report I'm building. 

    However, this is what I came up with because there is no 'ticket text field change" and "status". Just "Records of Ticket Text Field Change" and "Ticket Status". 

     

    So I continued with that metric. I was also told to input the following in the HOW Tab and Filters 

    The report is supposed to look like this:

    However,when I followed everything (except for the custom metric), no data will show. It says no data  match the filtering criteria. I think it might be the custom metric because that's the only one that's not exactly the same as what I was told to do. Am I missing something?

    I appreciate the help! 

    0
  • Jacob J Christensen
    Comment actions Permalink

    @Christa

    Great! You can select Data labels from the Show configuration menu (as shown below).

     

    @Montoya-Maita-L

    It looks like you are very close, seems you have selected Ticket status from the Attributes folder, it should be found in the Attribute values folder instead - the text should be orange.

    See if that does the trick.

    0
  • Jacob J Christensen
    Comment actions Permalink

    @Christa, missed the part about modifying the time frame.

    There is a great guide for creating custom metrics here, but the metrics you need shouldn't be too difficult to create, so here's a quick guide.

    1. Select one of the metrics we have been discussing, eg. Normal Resolution and click the View details button.


    2. Click the Duplicate button (so we don't overwrite the existing metric but create a new from it instead) and give the new metric an appropriate name, maybe also modify the description to fit.

      Now we have a copy of the original metric, click the Edit button to make changes to it.

    3. Let's say you would like to have a time frame of 10-12 hours as an example, simply change the number values and save. 



      That's one new metric, you can create as many as you like. I recommend you use the Fast- and Slow Resolution time metrics to model the upper and lower end of your time frames. Hope that makes sense.
    0
  • Nicole - Community Manager
    Comment actions Permalink

    Hey @Maita - 

    Hopefully one of our awesome community members or moderators will have an answer for you soon! IN the meantime,I encourage you to head over to the Welcome Thread in The Lounge to introduce yourself.

    Welcome! We look forward to seeing you around the Community. Happy Zendesking!

    0
  • Jason Benedict
    Comment actions Permalink

    I am trying to complete the above Example #1; Number of times a ticket gets reopened.  However, I cannot for the life of me find the Status attribute...only Ticket Status.

    Has this field been updated?  I can't get this to work.

    0
  • Jacob J Christensen
    Comment actions Permalink

    Hi Jason,

    I think I see what you're struggling with and it has tripped me up a number of times too.

    To get to the Status attribute value in the Text Field = Status part of the metric statement, you first go to the Attribute Values folder and select Text Field:

    From here you should be able to find the Status value:

    Hope that helps you out!

     

    1
  • Jacky Feng
    Comment actions Permalink

    How can I use Prev/New value twice in the same statement? E.g.

    Prev value (status) = open

    AND

    New value (status) = on hold

    AND

    Prev value (group = L1)

    AND

    New value (group = L2)

    Not a real example but hope it gets the point across. Is the above possible?

    0
  • Amy Dee
    Comment actions Permalink

    Hi Jacky! You can only capture one text field change at a time in a single metric, since each field change has its own ID. However, you can nest a filtering metric within a larger one. This works because one update may include any number of individual field changes, and you can work with updates and field changes separately.

    In this case, you would start by finding one of the field changes in its own metric:

    • SELECT # Ticket Updates WHERE [Text Field] Previous Value[Status] open AND [Text Field] New Value[Status] hold 

    Once you have that, you can create a second metric to find the other field change. When you do, you can add a numeric filter with the first metric:

    • SELECT # Ticket Updates WHERE [Text Field] Previous Value = [Group] L1 AND [Text Field] New Value = [Group] L2 AND (SELECT Status change metric BY Ticket Updates) >=1

    (The # Ticket Updates at the start is under Metrics, while the Ticket Updates at the end is under Attributes.)

    This metric counts ticket updates with a text field change from Group L1 to Group L2, where the update as a whole also includes a status change from open to on-hold. The result should be updates with both types of field change.

    Please note - these examples are provided for demonstration purposes only. I can't guarantee them for all use cases, and I strongly recommend that you audit your results.

    I hope this helps! Happy reporting!

    0
  • Jacky Feng
    Comment actions Permalink

    Thank you so much! I was trying to figure out how to filter a metric by a metric for ages but couldn't.

    Perhaps a good opportunity for a new help centre article? Thanks!! :-) 

     

    Follow up question I have is: how do I express that the group has not changed in a query? I want to see ticket updates where there has been an update, but the group has not changed.

    My initial thought was to use WHERE Ticket Group (historic) = L1 AND Text Field = Group AND [Text Field] New Value = <empty>.

    Reason for not using Ticket Group = L1 is because if it gets escalated in the future then this event would no longer count towards the report. 

    0
  • Mindaugas Verkys
    Comment actions Permalink

    Hi Jacky,

    So what are you looking to do is to report on how many Updates has been made on the ticket while it was in one group? 

    0
  • Amy Dee
    Comment actions Permalink

    Hi Jacky! If you have Text FieldGroup AND [Text Field] New Value(empty value) in your metric, it will find updates where the group is changed to empty. That isn't quite what you need.

    It's difficult to filter for cases where something didn't happen, but you could make it work with another numeric range filter. You'd start with a simple metric that just looks for group changes:

    • SELECT # Ticket Updates WHERE Text FieldGroup 

    Then, you'd use IFNULL logic when you nest that into a larger metric:

    • SELECT # Ticket Updates WHERE Ticket Group (Historic)L1 AND [Text Field] Previous/New Value[Separate Field] Other Values AND (SELECT IFNULL((SELECT Group metric above BY Ticket Updates),0)) =0

    This finds ticket updates with some other text field change, where the ticket was in the "L1" group at the start of the update, and the update as a whole is not counted in the group change metric. 

    For more information, you can check out GoodData's documentation on IFNULL Best Practices. It's not directly related to this use case, but we also have similar numeric range filtering in our recipe for Reporting on ticket tags.

    As before, this metric is just to give you a starting point and some ideas. I can't guarantee this for all use cases, and I strongly recommend that you test and audit the results. There are a lot of moving parts here, so you'll want to make sure they fit together as you intend.

    I hope this helps! Happy reporting!

    0
  • Punit Patpatia
    Comment actions Permalink

    Thank you for this wonderful post!

    I tried a lot to create, but unsuccessful in creating a report on all the category (text field) change events.

    For example, I have a ticket (ID 178868); wherein the user (agent) changed the category three times until the ticket changed the status from 'open' to 'closed'.

    Hence, I am looking to create a report wherein, I can list these category changes in a table. Just like the one below.

    I created two metrics:

    Count Cat Status: To count the total number of changes in the text field 'Category' wherein the Ticket Status is 'Closed'

    SELECT COUNT(Ticket Updates, Ticket Text Field Change) WHERE Text Field = Category AND Ticket Status <> Deleted AND Ticket Status = Closed

    Date Created on or after 01 Jan 2019: To report only the ticket created on or after January 1, 2019

    SELECT # Tickets WHERE Date (Ticket Created) >= 01/01/2019
     
    In the report,
    under WHAT I used Count Cat Status and # Ticket Events
    under HOW I used Category, Date (Event), Ticket Id, Ticket Updates, and Updater
    under FILTER I used Ticket Id where Date Created on or after 01 Jan 2019 is greater than 0
     
    Although, the report I am receiving is showing the date event, but not the different categories used in the ticket (please see below). It is only showing one category (i.e., the last category updated).
     
     
    I am not sure, what wrong I am doing here? Looking forward to your help and great support.
    0
  • Graeme Carmichael
    Comment actions Permalink

    Punit

    The 'Category' field included under your HOW section shows the current value of the field. That is why it shows the same value against each date.

    To show the new value set on each date:

    HOW

    • [Text Field] New Value

    To reduce your output to your original table:

    ...you can also add:

    FILTER

    • Text Field> Is > Category

    You can then drop your custom metric 'Count Cat Status'.

    2
  • Punit Patpatia
    Comment actions Permalink

    Thank you so much Graeme! It worked like a charm.

    You are a rockstar. My million thanks to you.

    4
  • Peter Kania
    Comment actions Permalink

    Hi Guys, after some guidance. I must be doing something wrong and am just missing it.....

    I am trying to report on tickets that have changed severity, (which is a custom field but shows in the ticket event history when it is changed etc) and found this article which pointed me in what I hoped was the right direction. So I took the example that was used to show tickets being re-opened etc and tried to create a metric for my severity report. So what I ended up doing was this....

    No matter what I do it says it has an unexpected ']' and is expecting a ')'.

    I re-did the example provided in the article, namely:

    SELECT COUNT(Ticket UpdatesTicket Text Field Change) WHERE Text Field = Status AND [Text Field] New Value IN ([Status] open[Status] pending[Status] hold) AND [Text Field] Previous Value = [Status] solved AND Ticket Status <> Deleted

    and that too complains about the ']'.

    I edited all the way back to the first value after the IN statement and it looks like it is having an issue with the first attribute listed having the [ ] around it after the IN, however that is what the example shows and I'm assuming this works for others, so I must be doing something wrong.......

    Any help or guidance gratefully received.

    0
  • Punit Patpatia
    Comment actions Permalink

    Hi Peter,

    It seems like you are using the attributes instead of attribute values. Please refer to the below elements.

    Ticket Updates >> Attributes
    Ticket Text Field Change >> Attributes
    Text Field >> Attributes
    Severity >> Attribute values of Text Field
    [Text Field] New Value >> Attributes
    [Severity] 2-Urgent, [Severity] 3-Important, [Severity] 4-Moderate, [Severity] 5-Minor >> Attribute values of [Text Field] New Value
    [Text Field] Previous Value >> Attributes
    [Severity] 1-Critical >> Attribute Values of [Text Field] Previous Value
    Ticket Status >> Attributes
    Deleted >> Attribute Values of Ticket Status

    Also, I can see that [Severity] 4-Moderate starts with a Curly Brackets, like {Severity] 4-Moderate. I am not sure, if it is pasted from somewhere.

    Please select the right elements from the list (as mentioned above) and see if it works.

    1
  • Peter Kania
    Comment actions Permalink

    Hi Punit

    Many thanks for your input. You were correct re the typo on the Moderate Severity bracket, that indeed was showing as a curly bracket.

    So I think I understand what you were saying re the attribute vs the attribute values, that makes sense.

    However when I add the attribute value for severity it brings up the list of values and I have to select one or all of them, in my case 1 through 5 from Critical to Minor.

    I cannot add just the one value in orange like you have in your example.

    And then again when I select the values for the text field New Value and Previous Value etc it adds the values as 1-Critical, 2-Urgent, etc, etc, but they do not have the Severity part first in the square brackets, [Severity] 1-Critical, etc.

    So have I somehow got the add of the attribute value incorrect?

    So for example if I add the attribute values for Severity my Metric looks like this:

    0
  • Punit Patpatia
    Comment actions Permalink

    Hi Peter,

    What is the name of the custom field, that contains the drop-down values, i.e., Critical, Urgent, Moderate, Minor?

    For example, in the syntax shared by me (above) the name of the field was 'Priority', just like the screenshot below.

    So, there must be a custom field, with the name 'Severity'.

    If you have a field with the name 'Severity' you must see it as 'Severity' in the attibute values of the attribute 'Text Field'.

    Trust, this helps!

    1
  • Peter Kania
    Comment actions Permalink

    Many thanks Punit,

    All sorted and working now. I was confusing the attribute values of the actual attribute I had called 'Severity' with the attribute values for the [Text Field] New Value etc, which has the severity values included.Got my head around it now. Thanks for your assistance, very much appreciated.

    2
  • Punit Patpatia
    Comment actions Permalink

    1
  • Andrei Kamarouski
    Comment actions Permalink

    Hi,
    Is there any way to get replies data in the report based on metrics with Text Field changes and attributes? I have a report with Priority (Historic) attribute and a custom metric for counting customers' comments (using Text Field changes attribute), but can not build a proper metric for counting ALL agents' replies (because not all events with replies have text field change there). I know the comments are not connected with any field changes but maybe there is some workaround?

    0
  • Punit Patpatia
    Comment actions Permalink

    Hi Andrei,

    I don't think you would need a custom metric if it is just about showing the number of public comments by agents on the tickets. You may try exploring this.

    WHAT:

    # Ticket Events

    HOW:

    Public Comment and apply the filter Public Comment is True

    [Text Field] New Value

    FILTER:

    [Text Field] New Value is [Assignee], [Assignee],...

     

    You will get the following report.

    Please let me know if this is not the one you were looking for. So I may dig more to find a solution for you.

    0
  • Andrei Kamarouski
    Comment actions Permalink

    Hi Punit, 
    Thank you, but I know how to build this type of report. I have a bit more complex case where historic attribute is involved. 

    0
  • Brett - Community Manager
    Comment actions Permalink

    Hi Andrei,

    While I'm rather limited on what I can assist with when it comes to custom metrics, it may be helpful for other users to jump in if we had a screenshot of the custom metric you've set up. Any additional information you can provide may beneficial :)

    Thanks!

    0
  • Jacky Feng
    Comment actions Permalink

    Hi Andrei, try creating a new # Ticket Updates metric.

    Instead of:

    SELECT COUNT(Ticket Updates, Ticket Text Field Change)

    Make the new one as:

    SELECT COUNT(Ticket Updates)

    I believe this should work for your purposes.

    I'm not sure if there are any other implications to duplicating the metric with this slight edit other than the fact that it will begin to include Ticket Updates even if there were no Text Field changes. If anyone knows more about this I would be interested to know as well.

    0
  • Brett - Community Manager
    Comment actions Permalink

    Thanks for sharing Jacky :)

    0
  • Andrei Kamarouski
    Comment actions Permalink

    Hi guys, 
    Thank you but removing Text Field Change attribute causes an error. This is the challenge here. I will try submitting a ticket on this. 

    0

Please sign in to leave a comment.

Powered by Zendesk