English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore recipe: Finding tickets with a specific word in the subject

Eric Gao
  • Edited
Zendesk Customer Care

What's my plan?

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

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

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

  1. In Explore, click the reports () icon.
  2. In the Reports library, click New report.
  3. On the Select a dataset page, click Support > Support - Tickets, then click Start report.
  4. In the Calculations menu (), click Standard calculated attribute.
  5. On the Standard calculated attribute page, give your new metric a name like “Contains”.
  6. 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")) 
    THEN "Has this word"
    ELSE "Does not have this word"
    ENDIF
    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.
    Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
  7. When you are finished, click Save.
  8. In the Metrics panel, click Add.
  9. From the list of metrics, choose Tickets > Tickets, then click Apply.
  10. In the Rows panel, click Add.
  11. 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. 

Return to top

17 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

    CONTAINS(LOWERCASE([Ticket subject]),"booking failure") then it still gives a TRUE = has keyword, FALSE = these tickets don't have this keyword.

    I have to report on dozens of keywords in the subject line and would ideally like to put these in a table, but the TRUE/FALSE is messing up my table.
     
    How can I just say IF this ticket contains keyword, show the number of those tickets.
     
    I have went ahead and created a standard metric instead of the attribute, but now it shows me the tickets in a column and since I need to add dozens, they will not fit. Please see below.
     
    I do realise I need to make this more efficient and not rely on subject line, I am working on it, but I need to still report on the tickets in the system with certain subject lines.
     
    Help?
     
    Thanks,
    Dominic
    0
  • Chandra Robrock
    Community Moderator
    Most Helpful - 2021

    @... 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
    Zendesk Documentation Team

    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
    Zendesk Engineering
    @Karen 
     
    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
    • Edited

    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
    Zendesk Engineering
    Hi Mike,
     
    You can use the calculated metric below:
     
    IF ((CONTAINS(LOWERCASE([Ticket subject]),"word1")) OR(CONTAINS(LOWERCASE([Ticket subject]),"word2"))OR(CONTAINS(LOWERCASE([Ticket subject]),"dane")))
    THEN "Has this word"
    ELSE "Does not have this word"
    ENDIF
     
    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
  • Teresa

    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
    Zendesk Customer Care
    Hi Ryan,

    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
    Zendesk Customer Care
    Hi Nicole,

    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
    Zendesk Customer Care

    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
    Zendesk Customer Care

    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?

    IF (CONTAINS(LOWERCASE([Ticket subject]),"support") ) OR (CONTAINS(LOWERCASE([Ticket subject]),"accounts") )
    THEN "Has this word" 
    ELSE "Doesn't have this word"
    ENDIF

    0

Please sign in to leave a comment.

Related articles

Powered by Zendesk