English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore recipe: Finding the agent who made the first public reply

Nhia Lor
  • Edited
Zendesk Customer Care

What's my plan?

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

In this Explore recipe, you'll learn how to use the Updates history dataset to find the first agent who performed a public comment on a ticket.

What you'll need 

Skill level: Average

Time required: 15 minutes

Creating the report

This report returns the agent who performed the first public reply for each ticket in your instance.

To create the report
  1. In Explore, click the reports () icon.
  2. In the Reports library, click New report.
  3. On the Select a dataset page, click Support > Support - Updates history, then click Start report.
  4. In the Calculations menu (), click Standard calculated attribute.
  5. On the Standard calculated attribute page, give your attribute a name like "First public reply agent".
  6. In the Formula field, enter or paste the following formula: 
    IF ([Comment present]=TRUE
    AND [Comment public]=TRUE
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])=[Update - Timestamp])
    THEN [Updater name] ENDIF
  7. When you are finished, click Save.
  8. In the Metrics panel, click Add.
  9. From the list of metrics, choose Tickets > Tickets updated w/public comment, then click Apply.
  10. In the Rows panel, click Add.
  11. From the list of attributes, choose Tickets > Ticket ID and Calculated attributes > First public reply agent, then click Apply.
  12. In the Filters panel, add the Updater role attribute and configure this to show Agents and Admins.
  13. In the Filters panel, add the First public reply agent attribute and configure this to exclude NULL.
  14. The report will match the following:
    mceclip0.png

Optionally, you can add a date filter such as Ticket created - Date to narrow the date range being reported.

Return to top

7 Comments

  • Nhia Lor
    Zendesk Customer Care

    Hey Patrick,

    If you're finding that the custom attribute "First Response Agent" is taking too long to load then its potentially due to the increased amount of real attribute or NULL values which can sometimes result in that particular error.

    To get around this I can also recommend creating a custom metric that implicitly excludes the NULL values then applying a metric filter.  In the case of this recipe as we're using the default metric "Tickets updated w/public comment" the custom metric would be something like this:

    IF ([Comment present] = TRUE AND [Comment public] = TRUE AND [First public reply agent]!=NULL)
    THEN [Update ticket ID] 
    ENDIF

    And then you would just need to apply the query metric filter under Result manipulation > Metric filter.  And ensure it's set to at least "1".

    Alternatively you could always specify and hardcode your agents names into the custom metric itself as well so hopefully these suggestions help to overcome the difficulty you've encountered there!

    0
  • Tony
    Hi Judy,
    We suggest that you reach out to our Customer Support, so we can have a closer look at the query that you are working on.
     
     I see you created a ticket with us already. We will get back to you as soon as possible,
     
    Thanks!
    0
  • Kitty Lee

    Hi,

    I followed your instruction but received the following error - 

    "The set of calculations isn’t valid. The calculated attribute "firstpublicreplyagent-a35b7392a0" references "Comment public" which is as an unknown attribute. Adjust the calculation and try again. (Error code: 11c3fd53)"

    What can I do to fix it?

    0
  • Judy Correia

    My report seems to include the originating ticket comment (when ticket was created by a customer) in this report. I'd expect to see only agent first replies. why would the originating comment be included? Should the report not show first agent replies after the ticket was originally logged?

    0
  • Patrick Lieu

    This is great Nhia!

    I'm just having issues with the below step:

    13. In the Filters panel, add the First public reply agent attribute and configure this to exclude NULL.

    I'm running into the below error:

    Any idea on how to fix this or how to edit the query in such a way that it doesn't pull up every single updater's name in our database?

    0
  • Cheeny Aban
    Zendesk Customer Care

    Hi Kitty,

    We suggest that you reach out to our Customer Support so we can have a closer look at the Query that you are working on

     

    0
  • Chris Gould

    We just used DATE_FIRST_FIX function to find the time the first call was made against a ticket ID, given a ticket can have multiple calls, DATE_FIRST_FIX was ideal.

    We were able to nest this into a DATE_DIFF to find the length in time between the ticket creation and the first call. You can then aggregate any way you like as there is only one value provided per ticket ID.

    DATE_DIFF(DATE_FIRST_FIX([Call - Timestamp], [Ticket ID]), [Ticket created - Timestamp], “nb_of_minutes”)
     

     

    0

Please sign in to leave a comment.

Powered by Zendesk