Explore recipe: Reporting on nested drop-down fields

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 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
• Creating the initial query
• Reporting on Level 1 fields
• Reporting on Level 2 fields
• Reporting on Level 3 fields

What you'll need

Skill level: Advanced

Time required: 20 minutes

• Zendesk Explore Professional
• 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

1. In Zendesk Support, click ( ).
2. From the Admin menu, click Manage > Ticket Fields.
3. On the Ticket Fields page, click Add field.
4. On the Select field type page, choose Drop-down.
5. Under Title shown to agents, enter a name for the field like Select your camera.
6. 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

7. 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

1. In Explore, click the query ( ) icon.
2. In the Queries Library, click New Query.
3. On the Choose a Dataset page, choose the Support: Tickets dataset.
4. In the Metrics panel, add the metric COUNT(Tickets).
5. 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

1. In Query Builder, open the calculations menu (), then click Standard calculated attribute.
2. Name the new attribute List level one.
3. In the formula window, enter 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.

4. 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