In Zendesk Support, you can create custom drop-down lists that contain multiple levels of organization. Using Explore, you can analyse the data returned by each level of your custom drop-down list.
In this Explore recipe, you'll create a drop-down list with three levels and then create some Explore queries that analyze results from it.
This recipe contains the following topics:
What you'll need
Skill level: Advanced
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
- Some experience creating queries with Explore
- At least one nested custom drop-down list. See Organizing drop-down list options for help.
Creating the custom drop-down list
In this example, you'll create a simple nested drop-down list for a camera shop. From the Cameras list, you can select one of three cameras, Camera 1, Camera 2, and Camera 3 and select to Rent or Buy the selected camera.
To create the drop-down list
- In Zendesk Support, click (
).
- From the Admin menu, click Manage > Ticket Fields.
- On the Ticket Fields page, click Add field.
- On the Select field type page, choose Drop-down.
- Under Title shown to agents, enter a name for the field like Select your camera.
- Under Field values, enter the following values, one line for each field:
Cameras::Camera 1::Buy Camera 1 Cameras::Camera 1::Rent Camera 1 Cameras::Camera 2::Buy Camera 2 Cameras::Camera 2::Rent Camera 2 Cameras::Camera 3::Buy Camera 3 Cameras::Camera 3::Rent Camera 3
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - Once you are finished, click Save. You'll end up with something that looks like the following screenshot:
Now, the ticket field you just created will appear on your new and existing tickets. Make sure to select one of the values from this field on a few of your tickets. After Explore next updates it's data (within one hour), the data will be available in Explore and you can proceed to the next section.
Creating the initial query
In this procedure, you'll create the basic query for your custom field which reports the number of tickets that have each selected combination from your drop-down list. You'll then refine this in later procedures.
To create the initial query
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a Dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets, then click Apply.
- In the Rows panel, add your custom field, in this case Select your camera.
You'll see the results from your custom fields in a table like the following example:
While this reports the full results from the drop-down list, sometimes you might just want to return data from the second or third level of the list such as the camera model, or whether it's rented or bought. In the next topics, you'll learn how to do this.
Reporting on Level 1 fields
Now, you'll create a standard calculated attribute that returns the name of the first level of your nested drop-down field. It does this by returning the text before the first :: in the field name.
To create the Level 1 attribute
- In Query builder, with the same query opened, open the calculations menu (
), then click Standard calculated attribute.
- On the Standard calculated attribute page, name the new attribute List level one.
- In the formula window, enter or paste the following formula:
IF (CONTAINS([Select your camera],"::")) THEN LEFTPART([Select your camera],FIND([Select your camera],"::",0)) ELSE [Select your camera] ENDIF
Replace [Select your camera] with your own custom field name.
- When you are finished, click Save.
Now, when you use this attribute, you'll only return results the first level of the results (before the first ::), in this case, Cameras.
Reporting on Level 2 fields
To find the second level, i.e. everything from the first to the second :: in the string, we need to take two steps. First, make another standard calculated attribute called ‘Level 2 Prep’ with a formula like:
IF (CONTAINS([Select your camera],"::")) THEN SUBSTR([Select your camera],FIND([Select your camera],"::",0)+2,LENGTH([Select your camera])) ELSE "Other" ENDIF
Replace [Select your camera] with your own custom field name. Change “Other” to the text you want to appear if there is no second level.
This creates a string from the first :: in the original to the end of the string. Now, you can make a second standard calculated attribute named List level two to just take the first part of that, like:
IF (CONTAINS([Level 2 Prep],"::")) THEN
LEFTPART([Level 2 Prep], FIND([Level 2 Prep],"::",0) )
ELSE [Level 2 Prep] ENDIF
You can now remove the original field and the Level 2 Prep attribute from the frame, which will leave you with a list of results for Level 1 and Level 2 in a hierarchy.
You can also go further!
Reporting on Level 3 fields
Finally, if you want to display your level 3 results, make a third standard calculated attribute.
Create an attribute called List level three to create a string from the second :: in the original to the end of the string:
IF (CONTAINS([Level 2 Prep],"::")) THEN
SUBSTR([Level 2 Prep],
FIND([Level 2 Prep],"::",0)+2,
LENGTH([Level 2 Prep]))
ELSE "Other" ENDIF
15 Comments
Hi Rob,
worked great for me regarding dropdown fields, thanks!
How about multi-select fields though? It's the same thing so I thought it would work the same way. But unfortunately it doesn't work for me...
Hi Fabio, I don't think this recipe would work with a multi-select field, but you should get some starting points to set up reports for multi-select fields at https://support.zendesk.com/hc/en-us/articles/360022182014-Reporting-with-custom-fields - Hope this helps!
Hi Rob,
Could you provide any details on reporting for Multi-select fields in Explore. Thre is nothing specific in the article you referred to earlier. Just general statement:
> Reporting on this field type is more complex than reporting for a drop-down field.
Hi Rob,
I used the steps listed in the article on my production site and get only a top level number for the total tickets returned. However, when I use the same steps in my sandbox, I see the expected breakdown of tickets by top level value. Any thoughts on what I could be doing wrong?
Hello Kevin Gervais,
I went ahead and created a ticket for you, so one of our experts can look into this potential issue more in-depth. You should get a response shortly on the progress of this matter.
Best regards.
Followed the tutorial, and it's not working for me, I have various errors running the query.
Ticket created: https://support.zendesk.com/hc/en-us/requests/5462838
Hi Jais,
Would you prefer that we try to respond to you here or to handle this issue in your ticket?
Hi Nicole S.
Considering I still didn't get an answer from support@ yes we can talk it over here.
Hi Jaïs,
It looks like you're creating this as a metric and not an attribute which is causing the error. Can you try creating as an attribute instead to see if that resolves the issue?
Let me know!
Indeed Brett Bowser I was working with calculated metrics instead of calculated attributes.
Thanks for correcting my mistake and for your answer.
Happy to help Jaïs!
Thanks for this very helpful guide. 🙌
Is it possible to create a custom attribute that includes all final level options across multiple fields? Here's the context:
We have 15 ticket forms and each form has its own topic field. I've created the queries outlined in this guide for each of those topic fields (example, another example). However, I would like to create one final query that simply gives the ticket count for every single topic across all 15 of those topic fields. Some have one level, some have two, and some have three. So, ideally we'd just see the final level topic in that query.
Would appreciate any guidance on whether this is possible. Many thanks.
Hi there, my brain my just be foggy but what if we have 4 "levels"? What would category 3 prep look like? I cant seem to sort out in the attribute why its pulling level 2 vs level 3 etc. Thanks.
John
Great tutorial Rob Stack, thank you so much.
I would like to ask the same question as John Burns, what would the attributes look like for a 4th level? I've tried and I just can't work the same magic!
Your help would be greatly appreciated.
Hi John Burns and Tom Montgomery, I had to do a little research to get the answer to this so apologies for the delay. Here's what I received back. I hope this helps!
LEVEL 3 PREP:
IF (CONTAINS([Level 2 Prep],"::")) THEN
SUBSTR([Level 2 Prep],
FIND([Level 2 Prep],"::",0)+2,
LENGTH([Level 2 Prep]))
ELSE "" ENDIF
LEVEL 3:
IF (CONTAINS([Level 3 Prep],"::")) THEN
LEFTPART([Level 3 Prep], FIND([Level 3 Prep],"::",0) )
ELSE [Level 3 Prep] ENDIF
LEVEL 4:
IF (CONTAINS([Level 3 Prep],"::")) THEN
SUBSTR([Level 3 Prep],FIND([Level 3 Prep],"::",0)+2,LENGTH([Level 3 Prep]))
ELSE "" ENDIF
Please sign in to leave a comment.