Use this Explore recipe to display tickets with a specific word or string in the subject field.
What you'll need
Skill level: Intermediate
Time required: 10 mins
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
In this procedure, you'll create a standard calculated attribute that returns "Has this word" if the ticket subject line contains the search string "support". If the subject doesn't contain the string then "Does not have this word" is returned. You'll then use this attribute to create your report.
To create the 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 [default], then click New query.
-
In the Calculations menu (
), click Standard calculated attribute.
- On the Standard calculated attribute page, give your new metric a name like “Contains”.
-
In the Formula field, enter or paste the following formula, replacing the word "support" with the word or phrase you want to search for (in lower case):
IF (CONTAINS(LOWERCASE([Ticket subject]),"support"))
Enter the search term in lower case only. However, the search results are not case-sensitive. In the example above, “Support”, “SUPPORT”, and “sUpPoRt” would all be found.
THEN "Has this word"
ELSE "Does not have this word"
ENDIFTip: 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 Tickets > Tickets, then click Apply.
- In the Rows panel, click Add.
-
From the list of attributes, choose Calculated attributes > Contains, Tickets >Ticket ID and Tickets > Ticket Subject, then click Apply. The query will match the following:
And you’re finished! The screenshot below shows an example of the finished result with your tickets grouped by whether or not their subject contains the word or phrase you specified.
21 Comments
Eric Gao I do not believe this recipe is accurate anymore. On step 11 (From the list of attributes, choose Calculated attributes > Contains), Rows does not have a "Calculated attributes" section to choose the new calculated attribute. The only area I saw the one I created was in "Metrics" but that's not the right section for that.
Is there a new way to get this metric?
Hi Jesse,
Once you have created the Standard Calculated Attribute as instructed in the recipe, you will see the "Calculated attributes" section under Rows:
Note - make sure that in Step 4 you are creating an attribute rather than a metric.
Thanks,
Eric
Eric Gao
Good call! I did metric instead of attribute. Thanks for that.
I notice there's some broken images on the guide as well you may want to look at.
Thanks!
Hi Jesse Asklund
Thank you for spotting the broken images! - These are now fixed.
Eric Gao,
Is it possible to do the same, but for a word in the ticket description, but split by end user vs agent / admin?
I guess I could use the data filter user role for splitting by agent / end user.
I am however looking at matching search strings from the HC - with tickets coming in.
Any ideas would be great.
Thanks
Bart
Hello Bart,
Yes, this is possible by replacing ticket subject with ticket description in the recipe above then adding user role to the rows above the newly created attribute. Let us know if you run into any issues setting this up, and we'll be happy to help.
Best regards.
Heya Devan - Community Manager,
Tried replacing subject with description and I don't get the description option. Am I missing something?
Hello! This is a really helpful article. With subject lines being very long, variable etc, is there a way to capture multiple words in the subject (a string and multiple individual words)? When I add individual custom attributes for each individual term and then add them each as a row, the "has this word" and "Does not have this word" get wonky. See screenshot and then the count of tickets is out to the right of these two columns. The example in the article has nice columns but I also don't see a common word among all of the subjects so I am wondering how that works?

If I want to see the number of tickets that have the following words in their subjects in one single chart, how would I accomplish that?
-edge
-machine
-jobs
-etc...
Hi Kaylee thanks for asking this question. I did some experiments and made it work like this:
I created an attribute to look for both of the words "shipping" and "returns" in the ticket subject. It looks like this:
You'll see from my results that this gives the required result:
You can add as many AND lines to the formula as you need. I hope this helps!
Thank you! I am curious if OR lines work the same way. Going to play with that now.
Thanks Kaylee let us know how that goes. I think it should work just fine.
I've added this recipe to the queue to do a couple of updates based on our discussions :-)
That's great! I havent been able to get this to work on my end (even with AND). Are you able to jump into email or on a call so I can share more specific information?
Hi Kaylee I've created a ticket based on your request so that one of the support team can work with you directly to get this working. You'll hear from someone soon. I hope this helps!
Devan Chandler checking back in on the query with description issue that Bart is seeing https://support.zendesk.com/hc/en-us/articles/360041681533/comments/360004551034
Any way to query description/comments? Tried to change the calculation formula but not able to find ticket description.
Hi Kyle - Explore currently cannot report on Ticket Comments or Ticket Description. We recommend checking out the Explore reference for a complete list of available metrics and attributes.
The good news is that it has been listed as a potential item on our future roadmap (timeline TBD) to have the ticket description available in our datasets.
Rob Stack thank you first for the very helpful article. I've tried this as well with strings in subjects, but do not get any results.
I've tried it in a more complex way:
But even when I remove the OR conditions I do not get results for "contains" and "others"
Am I doing something wrong, or does this simply not work with longer subjects? I don't want to use it as suggested above, because I want to add other IF ELSE conditions here (in several 5 mails in 5 languages)
Hi Fabian, first I tried this using your more complex version of the query, still with single search words:
IF (CONTAINS(LOWERCASE([Ticket subject]),"phone"))
OR (CONTAINS(LOWERCASE([Ticket subject]),"shipping"))
OR (CONTAINS(LOWERCASE([Ticket subject]),"message"))
THEN "Has this word"
ELSE "Does not have this word"
ENDIF
and this works for me.
Next, I made a second version of this using longer subjects:
IF (CONTAINS(LOWERCASE([Ticket subject]),"phone call from: john smith"))
OR (CONTAINS(LOWERCASE([Ticket subject]),"shipping costs"))
OR (CONTAINS(LOWERCASE([Ticket subject]),"please can you check the 3rd floor printer?"))
THEN "Has this word"
ELSE "Does not have this word"
ENDIF
and this also works. It does seem like you are doing everything correctly (I'm assuming you added it as a calculated attribute?)
Let me know if you don't get to the bottom of this and I'll happily open a ticket on your behalf.
Hello Rob Stack - exactly, it's a calculated attribute, but I do not get the result (everything is added to "other" in my case).
If you can create the ticket on my behalf and direct it to whoever can have a look this would be great - I'll send you then my example in the ticket, that someone can have a look then with account assumption.
Thank you very much,
Fabian
Hi Fabian Trattnig thanks for the reply. I've opened the ticket and you'll hear from someone soon. I've also cc'd myself on the ticket so I can make any doc updates if required. Thanks!
Hello Rob Stack - sorry to contact you here (as I do not have any mail address from you).
May I ask if you can speed up the ticket handling? Waiting for a first reply since 10 days now. Thanks
Hey Fabian -
Rob is awesome, but he unfortunately doesn't have the power to control our ticket queue, which has been experiencing high volumes and delays recently. If you have an urgent issue, we are encouraging users to initiate a chat with our support representatives for the fastest response.
Please sign in to leave a comment.