Explore recipe: Due date performance report

In this Explore recipe, you'll learn how to create a query that helps report on your agents' adherence to due dates on average at a group level. This recipe is the Explore equivalent of the Insights recipe Due date performance report. 

What you'll need

Skill level: Moderate

Time Required: 20 minutes

• Zendesk Explore Professional
• Editor or Admin permissions (see Adding users 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. Now, select a dataset containing the due date data you'll need to build the query.
   
   Select Zendesk: Tickets. Query Builder opens.
   
4. Now, create a Standard calculated metric to show the number of days before or after a due date that a ticket was solved. Click Calculations (), and then Standard calculated metric.
   
   
5. Copy the metric below and paste it into the metric editor. Add a title for the metric like Due Date Delivery. It will look like the screenshot below:

   DATE_DIFF([Ticket solved - Timestamp],[Ticket due - Timestamp],"nb_of_days")

   
6. In the Metrics panel, click Add.
7. From the list of metrics, expand Calculated metrics, click the custom metric you created, Due Date Delivery, then click Apply.
8. Ensure the metric aggregator for Due Date Delivery is set to AVG. For more information, see Choosing metric aggregators.
9. In the Rows panel, click Add.
10. From the list of attributes, expand Ticket, click Ticket group, then click Apply.
11.  Click Chart configuration (), then Display format. Change AVG(Due Date Delivery) to Advanced. Then copy the formatting below and replace the text in the Display format editor.

12. IF (AVG(Due Date Delivery) < 0) THEN
    
    {
    
     "backgroundColor": "",
    
     "precision": 0,
    
     "scale": 1,
    
     "prefix": "",
    
     "decimalSeparator": ".",
    
     "italic": FALSE,
    
     "bold": FALSE,
    
     "suffix": "",
    
     "fontColor": "#228B22",
    
     "thousandsSeparator": " "
    
     }
    
    ELIF (AVG(Due Date Delivery) > 1) THEN
    
     {
    
     "fontColor": "#ff0000"
    
     }
    
    ELSE
    
     {
    
     "fontColor": "#000000"
    
     }
    
    ENDIF

    

This will change the color of the values based on whether your agents are solving a ticket ahead of the due date, or exceeding the due date.

The query is complete! See the screenshot below as an example.