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 reports 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 (seeGiving agents access to Explore)
- Ticket data in Zendesk Support
- Some experience creating reports with Explore
- At least one nested custom drop-down list. SeeOrganizing drop-down list optionsfor help.
Creating the custom drop-down list
In this example, you'll create a simple nested drop-down list for a camera shop. From theCameraslist, you can select one of three cameras,Camera 1,Camera 2, andCamera 3and select toRentorBuythe selected camera.
To create the drop-down list
- In Zendesk Support, click (
). - From theAdminmenu, clickManage>Ticket Fields.
- On theTicket Fieldspage, clickAdd field.
- On theSelect field typepage, chooseDrop-down.
- UnderTitle shown to agents, enter a name for the field likeSelect your camera.
- UnderField 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 articleto help you enter Explore formulas in your language. - Once you are finished, clickSave. 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 report
In this procedure, you'll create the basic report 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 report
- In Explore, click the reports (
) icon. - In the Reports library, clickNew report.
- On theSelecta Datasetpage, clickSupport>Support - Tickets, then clickStart report. The report builder opens.
- In theMetricspanel, clickAdd.
- From the list of metrics, chooseTickets>Tickets, then clickApply.
- In theRowspanel, add your custom field, in this caseSelect your camera.
你会看到你的定制字段的结果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 the report builder, with the same report opened, open the calculations menu (
), then clickStandard calculated attribute. - On theStandard calculated attributepage, name the new attributeList 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] ENDIFReplace[Select your camera]用您自己的自定义字段名。
- When you are finished, clickSave.
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" ENDIFReplace[Select your camera]用您自己的自定义字段名。改变“其他”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 namedList level twoto 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] ENDIFYou can now remove the original field and theLevel 2 Prepattribute 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 calledList level threeto 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
18 Comments
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@..., thank you so much.
I would like to ask the same question as@..., 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.
HiJohn BurnsandTom 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
Hey Team,
@...
Is there anyways we could report onjustthe level 3? Or would you recommend just hiding those lower levels in the chart configuration?
And could I potentially layer this with:https://support.亚博zendesk.com/hc/en-us/articles/360052540974-Explore-recipe-Excluding-tickets-closed-by-merge
Thanks!
@...
If I understand what you need correctly, it's actually pretty simple. Once you have setup all the levels above, just add "[name of level 3]" to the column or row and it will only show values that come under that category.
I hope that helps!
Thanks@...!
Essentially we want to just show the level 3, as its the "meat and potatoes" of our reporting.
Im curious if you'd know, could we modify the initial metric to just show solved tickets and exclude the "closed_by_merge" tag?
We want to use this as a way to track agents solved tickets, and I think a hybrid of this post andthis onewould do a great job.
Thanks!
Hey Theodore,
We do exactly that - we exclude the tag. It works like a charm.
I set this up almost a year ago and it took under an hour from what I remember. It's definitely worth doing.
To exclude the tag, open up your query and under "filters" add ticket tags then type closed_by_merge in the search bar. Make sure "Excluded" is selected.
Then, you can add "Level 3" in the Columns section and it will look something like this:
Note: in this example I also filtered by brand to reduce the sample size.
I hope that helps!
Thanks@...!
I've tried using this setup in the past, but after spot checking a few tickets, it would still include some that had the tag. Did you do any custom metrics on this query?
No problem@...,
I have noticed that too. We ignore that issue as the data gives us a good overview - a few additional merge tickets is not a huge problem to us. The solution in the article you shared above might be your best bet.
Maybe one of the Zendesk team can give some more insight into why the exclude tag feature is not 100% reliable.
Hi Tom Montgomery, I wasn't completely sure so I've opened a ticket so someone on the team can investigate. Hopefully, you'll here from someone soon.
Let's say I have three level 1 categories, website help, information update and Technical issue.
How would I roll up level 2s and level threes into the 3 main categories? Like the below
Hi there -
Related question here, what if you want to report on the aggregate of your top level categories and the first category is not a constant? For example, we have a Product Area field that has nesting implemented that looks like:
Product 1:: Sub Product :: Sub Sub Product (so on and so forth)
Product 1 :: Sub Product 2 :: Sub Sub Product 2
Product 2:: Sub Product :: Sub Sub Product
I want to be able to pull aggregated ticket volume for all the top level categories. The formula in this article presumes the top level category is the same for all entries - but in our case it is not. Any advise or solution is appreciated.
The formula described above should allow you to report on top level items, even if you have multiple top level categories. Here's a sample set of nested dropdown field values and how the data will look like in Explore.
Hi,
My custom field name isCategorywith by 5 categories, sub and sub-sub. I followed the formula:
IF (CONTAINS([Category],"::")) THEN
LEFTPART([Category],FIND([Category],"::",0))
ELSE [Category] ENDIF
But nothing appears only what looks like old tags. Any idea what else I can try?
We may need to take a look at the report saved in your Explore. Let me create a ticket for you.
Hi Gab,
I am trying to extract first level and second level and nothing appears in the report for me too.
It may be worth checking if you have another ticket or user field with an identical name. You can check out this guide:My custom ticket field attribute is not returning any results.
Let us know if this resolved the issue, or you canget in touchwith the Customer Care team if you need further help. Thanks!
Hello, is there a video on this or a deeper explanation?
I have a gigantic tree that has over 200+ combinations that are all connected through conditional formatting.
The tree is the contact reason a customer is contacting our support team about.
Essentially there's 1 field that breaks into 10 smaller fields, and each of those smaller fields have sub fields that can go 6 levels deep, but not all go that far, some of them just 3 or 4.
Is the top most field Level 1 and the lowest field level 6?
I'd like to set up a report that is similiar to a pivot table in Excel where each level rolls up to a larger level which rolls up to a larger level and finally up to the top most level
Pleasesign into leave a comment.