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 or Enterprise
- 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.
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - 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.
5 Comments
This is great work! However, is there not a limit on explosions? I want to say I read there is an upper limit of 30, so if I had more than 30 requester orgs, the query would fail. Please correct me if I'm wrong.
Thanks
Hi Samuel,
There isn't a set number of maximum Explosions - it really depends on how many results are included in the date range specified, and how long their attribute names are. You can fit more results if you change the number of columns associated with the Explosions in Chart configuration. From what I can tell from testing, it looks like it can handle approximately 25-35 results before it errors out. If you are requiring a report with more results than Explosions can handle, it is recommended to utilize Rows instead and find a chart style that works for you.
Is there a way to do this but for over 500 organizations? The Explosion is limiting the number of accounts we are able to see. The other option that I think could work is to only see the orgs with days greater than a certain sum. I attempted to do that by limiting the CreatedAt dates of tickets but that does not work because all groups are still included.
Nathan
If you are only interested in how long ago an organisation last submitted a ticket, and can do without the ticket number, you can try:
Metrics
Rows
You can then remove the Top/ Bottom filter.
Hope that helps
Graeme Carmichael Thank you that did the trick!
Please sign in to leave a comment.