Explore recipe: Total number of assignments

In this Explore recipe, you'll learn how to create a query that reports the number of times your agents were assigned to a ticket, even if they are no longer the assignee on the ticket.

Note: If a user is assigned to a ticket more than once, it will count the assignment twice (for example if a previously assigned ticket is assigned again to the same agent).

What you'll need

Skill level: Moderate

Time Required: 25 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving agents access to Explore)
• Ticket data in Zendesk Support

Creating the query

 To create the query

1. In Zendesk Explore, click the query () icon.
2. In the Queries library, click New query.
3. On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.        
   
4. Now you'll create standard calculated metrics to calculate initial assignments and reassignments. Click Calculations (), and then Standard calculated metric.
5. On the Standard calculated metric page, enter a name for your metric, in this case New ticket assigns.
6. In the Formula field, enter or paste the New ticket assigns formula:
   

   IF ([Changes - Field name] = "assignee_id"
    AND [Changes - Previous value]=NULL
    AND [Changes - New value]!="0")
   THEN [Update ID]
   ENDIF

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
7. Enable Compute separately.
8. When you are finished, click Save.
9. Repeat steps 4 to 8 to create a second calculated metric named Assignee reassignments (custom). Use the following formula for this metric.
   

   IF ([Changes - Field name] = "assignee_id"
   AND [Changes - Previous value]!=NULL
   AND [Changes - New value]!="0")
   THEN [Update ID]
   ENDIF

   Your metrics will look like the following screenshots:
   
   
   
10. In the Metrics panel, click Add.
11. From the list of metrics, choose Calculated metrics > New ticket assigns and Calculated metrics > Assignee reassignments (custom), then click Apply.
12. Ensure the metric aggregators for your custom metrics are set to COUNT. For more information, see Choosing metric aggregators.
13. In the Rows panel, click Add.
14. From the list of attributes, choose Ticket field update values > Update ticket assignee, then click Apply.
15. To get the sum of the two metrics, click Result manipulation (), then click Result metric calculation.
16. On the Result metric calculation page, click Add a new metric.
17. Enter the name of your metric, in this case Total assignments.
18. Enter or paste the formula below. You must select your metrics and functions from the drop-down next to Insert.
    

    COUNT(New ticket assigns)+COUNT(Assignee reassignments (custom))

    
19. Click + Add to add the calculation.

Your query is complete! See the screenshot below for an example.