Custom metric to show the date a user last updated a ticket


  • Amy Dee
    Comment actions Permalink

    Hi Alex! The report you described would be possible in Insights, but it gets very complicated very quickly. The challenge is in reliably finding the most recent event date for each user, since each user can submit any number of updates on any number of tickets. Dates have unique properties in the Insights model as well, so they can be difficult to use effectively in metrics.

    In your case, I'd recommend a different approach. You could create a metric that counts users who have submitted at least one ticket update in the past 180 days, then filter your report by whether users would be included in that count. It wouldn't show you the most recent update date, but it would help you identify inactive users.

    This approach combines a couple different recipe articles. The user/updater count is based on this recipe, and the numeric range filtering logic comes from this recipe. If you're not familiar with the metric editor, you may want to keep our creating metrics guide handy as well.

    Here is a metric that counts updaters who have not been deleted, who have also submitted at least one ticket update in the past 180 days. If they have not submitted an update in that time, the metric returns a 0:

    • SELECT IFNULL((SELECT COUNT(Updater, Ticket Updates) WHERE Updater Active <> false AND Date (Event) > THIS-180), 0)

    Once you create this metric, you can use it in a numeric range filter on your report:

    • Attribute: Updater 
    • Metric: custom metric above 
    • Range: "is equal to 0" for users who have not submitted a recent update; "is equal to 1" for users who have submitted a recent update

    Please note: custom metrics are not officially supported. The steps here are for demonstration purposes only. I can't guarantee these metrics for all use cases, and I strongly advise you to audit your results.

    I hope this helps! Happy reporting!


Please sign in to leave a comment.

Powered by Zendesk