In this Explore recipe, you'll learn how to create a query that shows you when an organization last submitted a ticket.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Zendesk Explore, click the query () icon.
- In the Queries Library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Tickets, then click New query. Query builder opens.
- Now, you'll create a Standard calculated metric to show the number of days since a ticket was created and today's date. Click Calculations (), then click Standard calculated metric.
- On the Standard calculated metric page, enter a name for the metric like Number of days between today and date ticket created.
- In the Formula field, enter or paste the following formula:
DATE_DIFF(TODAY(),[Ticket created - Timestamp],"nb_of_days")You'll end up with a page that looks similar to the following.
- When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Number of days between today and date ticket created, the custom metric you just created, then click Apply.
- Ensure the metric aggregator for Number of days between today and date ticket created is set to SUM. For more information, see Choosing metric aggregators.
- In the Rows panel, click Add.
- From the list of attributes, expand Ticket, click Ticket ID, then click Apply.
- In the Explosions panel, click Add.
- From the list of attributes, choose Requester organization > Requester organization name, then click Apply.
- (Optional) Click the Requester organization name attribute and exclude NULL values. This will exclude results where the requester does not belong to an organization.
- Click Result manipulation () then Top/bottom. Configure the following options:
- Bottom: 1 result
- Metric: SUM(Number of days between today and date ticket created)
- Strategy: On rows
Then the query is complete. See the screenshot below as an example.