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

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

  1. In Explore, click the query () icon.
  2. In the Queries library, click New query.
  3. On the Choose a dataset page, click Support > Tickets > Support: Tickets [default], then click New query.
  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 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. 

Return to top
Have more questions? Submit a request

21 Comments

  • Jesse Asklund

    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?

    0
  • Eric Gao

    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

    1
  • Jesse Asklund

    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!

    1
  • Rob Stack
    Zendesk Documentation Team

    Hi Jesse Asklund

    Thank you for spotting the broken images! - These are now fixed.

    1
  • Bart

    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

    1
  • Devan - Community Manager
    Zendesk Community Team

    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. 

    0
  • Bart

    Heya Devan - Community Manager, 

    Tried replacing subject with description and I don't get the description option. Am I missing something?

    1
  • Kaylee

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

    0
  • Rob Stack
    • Edited
    Zendesk Documentation Team

    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!

    0
  • Kaylee

    Thank you! I am curious if OR lines work the same way. Going to play with that now. 

    0
  • Rob Stack
    Zendesk Documentation Team

    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 :-)

     

    0
  • Kaylee

    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?

    0
  • Rob Stack
    Zendesk Documentation Team

    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!

    0
  • Kyle Crouthamel

    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.

    2
  • Eric Gao

    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.

    2
  • Fabian Trattnig

    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:

    IF (CONTAINS(LOWERCASE([Ticket subject]),"do you need help activating your Tractive GPS device"))
    OR (CONTAINS(LOWERCASE([Ticket subject]),"benötigen Sie Hilfe bei der Aktivierung Ihres Tractive GPS Trackers"))
    OR (CONTAINS(LOWERCASE([Ticket subject]),"avez-vous besoin d'aide avec votre GPS Tractive "))
    OR (CONTAINS(LOWERCASE([Ticket subject]),"ha bisogno di aiuto per attivare il suo dispositivo Tractive GPS"))
    OR (CONTAINS(LOWERCASE([Ticket subject]),"Necesita ayuda para activar su dispositivo Tractive GPS"))
    THEN "contains" ELSE "others" ENDIF

    But even when I remove the OR conditions I do not get results for "contains" and "others"

    IF (CONTAINS(LOWERCASE([Ticket subject]),"do you need help activating your Tractive GPS device"))
    THEN "Natalie satisfaction" ELSE "others" ENDIF

    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)

    0
  • Rob Stack
    Zendesk Documentation Team

    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.

    0
  • Fabian Trattnig

    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

    0
  • Rob Stack
    Zendesk Documentation Team

    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! 

    0
  • Fabian Trattnig

    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

    0
  • Nicole S.
    Zendesk Community Team

    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.

    1

Please sign in to leave a comment.

Related articles

Powered by Zendesk