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 report
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 report
-
In Explore, click the reports (
) icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report.
-
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 report 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.
24 comments
Dominic
Hi all,
First off, thank you for this article, very cool!
I realise this a year later, but I'm wondering if anyone can help me.
I am trying to count tickets that haver certain keywords or strings in the subject line. If I create the attribute as per above, it always shows me "Has this word" "Does not have this word",
If I just use a simple
0
Chandra Robrock
@... You should be able to display the Metrics as a Row by clicking into Chart Configuration on the right-side menu and then Columns. When you have a Table visualization selected, you should see a checkbox to select next to the 'Metric on Rows' option.
0
Dominic
@..., many thanks! That helps a lot.
0
Dominic Jodoin
Thanks for the great tutorial! I was wondering if it's possible to do something similar but instead of looking for keywords in a Ticket's subject, I would like to look for a string in any of the comments (i.e. public or private) on a ticket. For example, I would like to report on the number of tickets containing a link to our documentation site. Thanks!
0
Rob Stack
Hi Dominic Jodoin, what you are looking for isn't currently possible, but it would indeed make a great feature. Can I suggest you add this to our feature requests section at https://support.zendesk.com/hc/en-us/community/topics/1260801325209
That will bring it to the attention of the product team and enable other customers to vote for it. Thanks!
0
Karen M.
Hello, I have been looking for an answer and couldn't find the a way to do this:
We have an specific e-mail address where some users are texting us and we need to forward it to the proper e-mail to create a Zendesk ticket (that's working and it's not a problem) , the issue is that Im trying to create a query where to list all the tickets that we have received and forwarded as mentioned.
If I search on the Zendesk views (using the magnifying glass) and I can see them all but I'm trying to create a query to have a list, I can't find a formula, metric, attribute, etc to accomplish this.
The e-mail is included in the body of the ticket, it doesn't show up as an asignee or submitter since it comes as a forwarded e-mail and the submitter would be one member of my team.
Hope I could explain myself and hope someone has an idea or workaround for this
0
Dane
This can easily be done by the use of triggers and tags. If the element you are looking for is in the body of the email, you can create a trigger with the following condition and action.
For every ticket that will be created with the email you have specified on your condition the trigger will add a tag to it. Once the ticket has been tagged, it will be easy to make a query in Explore.
Hope this helps.
0
Mike Talbott
Hi,
Is there a way to find tickets with more than one keyword in the subject? For example, I'd like to find all tickets that contain the words "train," "steam," or "engine" in the subject. Please advise. Thank you.
0
Dane
You can use the calculated metric below:
Hope this helps!
0
Ryan St. Wrba
Hello,
Does this work with custom text fields? I'm trying to use contains to find tickets with a specific text string in a custom text field, and it just seems to not work at all. Explore shows every ticket as not containing the string, even when they do.
0
Nicole
So this is great for a count, but how would one pull/build a report to show the actual ticket information within those tickets?
0
Gab Guinto
Have you checked if the string of text in your formula, example –
– is in lower case? If you're using the function 'LOWERCASE' just like in the sample above, the string should be in lower case.
0
Gab Guinto
Similar to the sample table above, you can add the attribute Ticket ID as slicer under Rows to see the list of tickets. You can add more attributes as Rows or Columns to the table if you need to see more information on these tickets.
0
Puneet Misra
I am looking for tickets with a specific keyword only, I am not interested in tickets that do not have it, what do I need to modify in the standard calculated attribute formula in this article to accommodate that?
Can I do the same for ticket comments?
Goal: Gather the # of tickets with a specific string in the subject and description fields.
0
Zsa Trias
Hello Puneet,
If you want to list only tickets that have the keyword in the subject, you can use the formula provided in this article to create the Standard calculated attribute. Once you have that created, you can use that as a filter in the report to list only tickets that fall under the category "has this word".
However, Explore currently does not pull comment text/content into its datasets. As a workaround, you can manually tag tickets according to text content (subject and description) or create a trigger to automatically tag them as tags can be reported in Explore: Reporting with tags
0
Gavin
Dane
I tried the method you linked here for using OR for multiple words, but it appears to be broken now with the new calculation restrictions on Explore. Is there another method?
0
Zsa Trias
Hello Gavin,
The formula should still work for multiple words.
Can you try the formula again and just replace the words within the quotation marks?
0
Anne-Flore Caire
Hello. I need to be able to make a formula with "Contains" an A word and "Does not contain" a B word. possible?
0
Salim Cheurfi
I recommend creating separated attributes for A&B words with the function CONTAINS(_text,_text_to_search) then you can use those attributes in your formula with the TRUE and False statement.
I hope this helps
0
Anne-Flore Caire
Thanks for the feedback.
So I looked for a solution because I needed to pass my condition in a measure and not an attribute, and I found a solution with a chain of if
0
Carlos Gomez
Hey Everyone.
I am looking for a way to create a recipe that allows me to count the word “Unbabel” on the internal comments of a zendesk ticket, ideally only on internal comments created by Agents
0
Alex Zheng
Unfortunately, Explore does not retain comment data. If you wish to access the comment data, you have the option to export your ticket data or retrieve it through the API. For more detailed information, please refer to the article available here.
0
Davis Van Luven
Hello! Requesting the ability to use Standard calculated attribute to search comments of all tickets for keywords. Thanks!
0
Myra Lee
Is there a way to report on the count of tickets that contains a specific word in the subject title but in each row or column, it shows the count for a different word?
For example, i want to report on the number of tickets for each specific word “apple”, “pear” and “orange” in the subject title. Thanks!
0