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
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 3Tip: 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