In this Explore recipe, you'll learn how to use the ticket updates dataset to find the first agent a ticket was assigned to. You'll also create a filter that can be used to exclude results where the first assignee is the same as the current assignee.
What you'll need
Skill level: Intermediate
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
This query reports the first assignee for each ticket in your instance.
To create the query
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates [default], then click New query.
- In the Calculations menu (
), click Standard calculated attribute.
- On the Standard calculated attribute page, give your attribute a name like First assignee name.
- In the Formula field, enter or paste the following formula:
IF ([Changes - Field name]="assignee_id" AND [Changes - Previous value]=NULL AND [Changes - New value]!="0" AND [Changes - New value]!=NULL) THEN [Update ticket assignee] ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - From the Computed from dropdown list, choose Ticket ID.
- When you are finished, click Save.
- Repeat steps 4 to 8 to create a second calculated attribute named Current assignee filter and the following formula:
IF [Update ticket assignee] = [Assignee name] THEN "true" ELSE "false" ENDIF
- Now, you'll construct the query. In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets updated, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes > First assignee name, then click Apply.
- In the Filters panel, click Add.
- From the list of attributes, choose Calculated attributes > Current assignee filter, then click Apply.
- Click the Current assignee filter you just added. On the Exclude tab, exclude one of the following values:
- True: If you want to exclude tickets where the current ticket assignee is the same as the first tickets assignee.
- False: If you want to exclude tickets where the current ticket assignee is NOT the same as the first tickets assignee.
Explore displays the finished table showing agents who were both the first and the current assignee for each ticket and the number of tickets for which that was the case. If you want to see the individual ticket IDs for these, add Tickets > Ticket ID to the Rows panel.
Tip: The ticket updates data set contains a large amount of information related to your tickets. If your query is taking too long to process, consider adding another filter to narrow the results. For example, you could add the Ticket created - Date filter to narrow the date range being reported.
4 Comments
Step 11: the option Tickets updated does not appear in my version of ADD Metrics
I may have misunderstood something.
Please assist if possible.
with kind regards
Chris
Hi Stellenbosch Admin thanks for the question.
I just took a look and confirmed that the metric is correct. Make sure you are using the Support: Ticket updates dataset (see Step 3).
After you click Add in the Metrics panel, expand Tickets, then you'll see Tickets updated.
I hope this helps and thanks!
Hi team,
Is it possible to find all assignees for a ticket?
Thanks,
Ben
Hey Ben,
You should be able to report on this using the Ticket Updates dataset along with the Update Ticket Assignee attribute. This recipe may help: Explore recipe: Total number of assignments
Please sign in to leave a comment.