You can create custom metrics and attributes to use in your query that help you present your business data just how you want it. To do this, you'll need to learn how to write formulas. Formulas perform calculations and manipulations on your data, and help you evaluate conditions.
Formulas are generally constructed with metrics and attributes that contain your Zendesk information and functions that perform operations on that information.
In this article, you'll learn the basics about writing formulas and see some examples of the power they can bring to your business analytics.
Building a formula
You previously learned that a typical Explore formula is made up of metrics or attributes and functions. In this section, you'll jump right in and learn the process by creating a formula.
For this example, you'll create a formula that displays only the tickets that were received through the email channel.
To create a formula that displays tickets from the email channel
- In Explore, open the calculations menu (
).
- Click Standard calculated attribute. A new blank attribute opens.
- Enter a name for the attribute like Tickets received by email.
- In the formula window, start entering your formula. There are a number of ways you can do this, but for now, under functions, click Add.
- Search for IF THEN ELSE Conditional expression. Either use the search box, click Filter to scope the values down to the Logical category, or scroll through the list until you find the function you want.
- Next to IF THEN ELSE Conditional expression, click +.
Explore adds a template IF THEN ELSE formula to the window. The values in the formula shown in blue (beginning with an _ character) are placeholders that you must replace with the values you need.
Tip: Notice the error message prefixed with () above the formula. This indicates a problem and its location in the formula. For now, you can ignore the error. It's appearing because your formula is still using placeholders.
- Next, you'll replace _boolean_condition with the Explore attribute you want to test. Either highlight _boolean_condition and then choose Ticket channel from the Fields menu, or amend the formula manually to add the attribute.
- Now, add the rest of the condition. To search for the string "Email", we add that to the existing condition.
Your formula should now read IF ([Ticket channel]="Email") THEN _value_if_true ELSE _value_if_false ENDIF
- Next, add what you want to do if the condition is true, in this case return the ID of the ticket. Replace _value_if_true with [Ticket ID]. Notice that as you type [Ticket ID] that Explore automatically suggests matches to what you are typing. If the metric or attribute you want is shown, hit return to immediately complete entry of it.
- The ELSE clause shown in the template ticket can be used to provide an alternative action that will apply if the first condition is not true. In this case, we don't need it, so remove ELSE _value_if_false.
- You've now completed the formula. If all is well, you'll see something like the screen below. Additionally. note that the error has gone and is replaced with a green check mark to indicate that the formula is valid.
- Finally, click Save. The attribute you created can now be accessed by any of your Explore queries that use the same dataset. You can find it by clicking Add in any attribute panel and expanding Calculated attributes.
Adding metrics and attributes to your formula
It's likely that most of the formulas you write with Explore will use metrics or attributes from the dataset you are currently using. In the example above you used the attribute Ticket channel, but there are many hundreds more you can choose from.
While it can feel intimidating at first, most of these metrics and attributes have descriptive names that will help you to find what you need. Also, experiment with creating basic reports in Explore to learn more about what the various metrics and attributes do.
You can find a full list of all the metrics and attributes you can use in the Understanding Explore datasets.
To add a metric or attribute to your formula
- In the Explore formula window, click Select a field.
- Either scroll through the list, or type the first few letters of the metric or attribute you want, to filter the list.
- Click the metric or attribute you want and it is added to the formula. In this case, Ticket ID was chosen. Note how it is surrounded with brackets [].
The brackets indicate to Explore that this is a metric or attribute. An alternative method to enter a metric or attribute is to simply begin typing. Explore recognizes the brackets, and suggests the appropriate values to choose from.
If you want to add an aggregator to your metric or attribute, enter the text in capitals before the first bracket, for example COUNT[Ticket ID].
Adding functions to your formula
To add functions to your formula, click Add under Functions or type in a function name. As with metrics and attributes, Explore suggests auto-complete results for any functions you type in.
When you click Add, the Functions window opens. This window is useful if you are not sure which function is applicable to the calculated element you want to create. All functions in the window contain a brief explanation of their purpose. For a list of all functions and many examples, see Explore functions reference.
If you are looking for a specific function, you can search for functions or filter by function type.
- In the Functions window, click Filter.
- Type in a category or select a category from the drop-down list.
- Click the x next to the category name to remove the filter.
28 Comments
How do you use Attribute values?
For example I want to filter a certain value of an attribute (filter out deleted tickets), in GoodData it looked like this: "Ticket Status <> Deleted"
Hey Quentin!
Deleted tickets are already excluded from the Support Tickets dataset so any metrics you make there do not need any additional modifications. If you use the Ticket Updates dataset, deletion events will be included there.
Thanks Molly for the explanation.
But how do you filter by status for example? If I want to see only open tickets in my metric? Something like [Ticket Status]=Open?
Hi Quentin, there are two options.
1. Without a custom metric. Add the Ticket Status attribute to a panel and then click it. You can include, or exclude the values you want. In your case, you’d select only Open.
2. Use a custom attribute. You are correct, in your suggestion, but you do need quotes, for example:
[Ticket Status]="Open"
Hope this helps!
Perfect Rob!
Thanks for the tip :)
How can I create a formula to show the end-user phone number in a Query? Currently, there is a row value for End-user email, but not for end-user phone number.
Paul
Unfortunately the user's phone number is not part of the dataset so you cannot report on it using Explore.
In Support, you would have to copy the phone number into a custom user field and then use the custom field in Explore.
Can you add a comment in your calculated field/metric?
Hello Gef Faelden,
My recommendation would be to use a datatip to accomplish this. I've shared a link below to an article that explains how to implement these and its range of uses.
Working with datatips
Best regards.
Hi,
Comparing to the Insights, how to express metric formula with this logic in Explore?
I can find the Ticket Tags in Fields but I can't find the tag "category_id_175" that i need.
Regards.
Hi there! I think you'll want to look at Reporting on ticket tags in Explore for help writing this metric.
Specifically, you could write a standard calculated metric with the following formula:
IF ([Ticket tags]="category_id_175") THEN [Ticket ID] ENDIF
Then filter your query for date ticket created. Unlike in Insights, you can just type the entire formula rather than selecting elements from a menu. Additionally, you can report on this ticket tag without a custom metric, as outlined in the first article on reporting on ticket tags.
I used to have several custom metrics in insights that gathered categories and their sub-categories and listed them as single item to be used in some pie charts.
For example we have a root category called business process and I want to count any tickets that fall within that category. the tags are business_process, business_process::requests, business_process::requests::email, etc
I cannot seem to find a way to duplicate the insights expression below:
SELECT # Tickets WHERE Custom Attribute Label Category [list] [Category] LIKE "%BUSINESS_PROCESS%"
Any help would be greatly appreciated. Thanks!
Hey David! I think you're looking for the recipe here: Explore recipe: Reporting on nested drop-down fields
Hi Madison,
I tried what you suggested in your link, and I was certainly able to create a new query that combined the nested fields into a single item. Thank you. My trouble now is that I'm unable to combine multiple of these similar queries into a single pie chart. Essentially I want to create a pie chart that lists all of the tickets for every category and their nested drop-down lists, but have each separate category represented by a single number. See the image below for an idea:
In the past I did this with a custom metric, so how do I do this now with a query instead?
Thanks for any guidance you can provide!
Got it, thanks David, and sorry for the delayed response! I actually recommend using an Attribute Group instead if you just want to report on the level one nest of the field.
Hi,
I am trying to replicate this Insight recipe on explore and is having difficulties this the AND OR condition
Insight Recipe - SELECT # Solved Tickets WHERE (Chat Type IN (Complete::Follow up, Complete::New Enquiry)) OR ((Chat Duration >20) AND (Chat Type NOT IN (Invalid::B2B/Solicitations/Ads, Invalid::Client Request, Invalid::Career/Employment/Internship Enquiry, Invalid::Duplicate Ticket, Invalid::Language Barrier, Invalid::Network/System/Technical Error/Downtime, Invalid::Not Business Related Topic, Invalid::Nuisance/Lewd, Invalid::Offline Message/No Entry URL/No Chat Agent, Invalid::Out of Coverage/International, Invalid::Research/Homework/Assignment, Invalid::Test Chat)))
Any help would be much appreciated
Thanks
Hi folks,
I have here a specific case and was hoping someone could advise.
In my query I want to see all tickets that don't contain tags 'tag_1', 'tag_2' and 'tag_3'. I have a Custom attribute that excludes those tickets. However, as a part of my query I also want to include the tickets that contain both tags 'tag_1' and 'tag_4'.
Would I be able to run such query? If so, would be grateful for the advice on how can I do it. Tried few things but none worked.
Thanks!
Hi Vladimir,
You should be able to create a calculated metric that counts tickets based on the tags they do or don't have.
Something like this:
☝️I haven't tested this, so you should definitely verify that it works before relying on it for reporting.
I hope this helps.
Hi Jacob
Thanks for getting back to me. That didn't work, but that's because I've just realised that there are other conditions to the query that make impossible to pull the data into one chart. I'll just stick with two separate charts as we previously did. Thanks for checking this for me though!
Hello!
Is there a way to add a wildcard into a formula? As a hypothetical, say I have a custom field that lists the country name and I want a report that filters just countries starting with "united" (United States, United Kingdom, etc). Is there a symbol I can use, such as below:
IF ([Country]="united%") THEN [ticket_ID] ENDIF
Cwilbur
You can use the STARTSWITH(_text,_text_to_search) function to do that.
IF STARTSWITH([Country],"united"]) THEN [ticket_ID] ENDIF
There are a few other similar text functions such as:
The full list of text functions is here.
Hi, I'm trying to replicate the following Insights custom metric in Explore and can't seem to get the formula correct. Any advice?
I'm trying to create a query that is basically - MUST HAVE Tag 1, and then display all other tags.
So end result is showing me how many tickets have tag 2, have tag 3, have tag 4, etc. but filtered on only those that ALSO have tag 1.
Hi Erin Miller. I believe this custom metric will help. It's from the article Reporting with tags.
Thank you! I am confused though, because I don't want to have to list out all the possible tickets there can be (tag 2, tag 3, tag 4) as those are ever changing. So its really "everything with tag 1" but then what I'm trying to do is count the number of tickets of each other type that has ticket 1. Does that make sense?
Hi Erin Miller, here's another way around to try this. It should be a good starting point to get to what you need.
Create two standard calculated attributes.
The first has the formula:
IF INCLUDES_ALL([Ticket tags], "Tag 1") THEN [Ticket ID] ENDIF
And the second has the formula:
IF INCLUDES_ANY([Ticket tags], "Tag 2", "Tag 3","Tag 4") THEN [Ticket ID] ENDIF
(you might want to create three copies of that one so you can report on each tag separately).
Now, add the Tickets metric to the metrics panel and the two (or more) attributes you created to the Rows panel. You'll end up with something that looks like this:
You can then add further attributes to give you more information. I hope this works as a starting point for what you need!
No, this still has the same problem. There is not a finite list of Tag 2, Tag 3, Tag 4... that I want to detail out in this procedure, as I do not want to rebuild this report every time more tags are are created it would be unscaleable the way we use zendesk.
What I'm really looking for is:
Show me all tickets with tag 1. Then DISPLAY FOR ME the number of tickets that have each and every other tag we have available. so end result will be:
100 tickets total with Tag 1. of those 100 tickets:
30 tickets have tag 4
15 tickets have tag 19
17 have tag 21
etc
Please sign in to leave a comment.