Explore comes with pre-built dashboards that give you key metrics about your business. However, every business is different, and eventually you might want a report that wasn't supplied with the product. That's where queries come in. Explore makes it easy to drag and drop metrics and attributes to make a new report.
While the built-in metrics and attributes give you great flexibility to create reports, eventually your business needs might require you to present information using metrics and attributes that are not included with Explore. At this point, you might be able to solve the problem by creating your own standard calculated metrics, and standard calculated attributes.
In this article, you'll learn the basics about how to construct your own custom metrics and attributes by working through some simple examples. Because the queries you can write are almost limitless, you are encouraged to experiment with the examples, and write your own queries.
This article contains the following sections:
Before you start
A query is a question you ask about the information stored in Zendesk. A metric is the information you want to measure against, usually a quantifiable thing like the number of tickets you have, or the number of ticket replies you have. An attribute slices your metric result by qualitative data like dates, groups, and tags. Here's an example:
You start with a metric; in this case the number of tickets you have.
This returns the number of tickets in your Zendesk account.
Next, you want to show the channels that each of these tickets came from. You do this by adding an attribute to the Columns panel.
You'll end up with a chart that looks something like this:
In this example, you want to expand this table by grouping the channels. For example, if the number of tickets in a channel is more than ten, label them "Big". If it's less than ten, label them "Small".
This would not be possible using the default Explore metrics and attributes. Later, you'll discover how to use a standard calculated attribute to create this table.
Preparing to create the standard calculated attribute
Previously, you learned about metrics and attributes that come with Explore and how they interact to present information in just the way you want it. To accomplish the aim of creating the table in the previous section, you'll need to create your own custom attribute. Custom metrics and attributes are known as Standard calculated metrics and Standard calculated attributes.
In this section, you'll learn how to use the formula window by creating a standard calculated attribute.
- For each ticket channel in Zendesk Support, count how many tickets used that channel
- If the number of tickets for that channel is over ten, return the text "Big"
- If the number of tickets for that channel is ten or less, return the text "Small"
Explore uses functions to perform operations on your data. They are a lot like simple commands used in a programming language, and you can use them together with the data from your Zendesk account to report the results you need.
- COUNT: Returns the number of values of a given attribute.
- IF THEN ELSE: This enables you to evaluate a condition.
Building your standard calculated attribute
Before you can create the query, you need to create the custom attribute that returns the number of tickets from each channel and returns a value "Big" or "Small" depending on how many tickets there are.
To build the formula
- In an Explore query with the Support: Tickets dataset open, open the calculations menu.
- On the Calculations panel, choose Standard calculated attribute.
- A new blank standard calculated attribute opens. Enter a name like Ticket channel big or small? (you'll use this name later when you choose your calculated attribute to add to a query)
- In the Formula field, start constructing the query. You can choose fields and formulas by typing them in, or selecting them from a list. In this article, you'll type in the formula. If you want to learn how construct queries using the Fields and Functions menus, see Writing Explore formulas. Type IF. You'll see that Explore gives you options of functions you can choose, so either press enter, or click IF in the list.
Explore completes the query and displays it in the Formula field.
The text strings in blue, _boolean_condition, _value_if_true, and _value_if_false are placeholders. You must replace these with the conditions and actions you want to take.Tip: For now, ignore the red error text. It's appearing because the formula is not yet complete and therefore incorrect. When the formula is complete, the red text will be replaced by a green tick. Explore checks the syntax of a formula is correct, but does not check the formula logic.
- Highlight the _boolean_condition placeholder. Begin to type the COUNT function. As you type, you'll see all the functions that include the COUNT function. Click COUNT(Tickets) to add the function to your formula.
- Now, add a condition to check whether the number of tickets returned value is larger than ten. To do this, the entire COUNT(Tickets) function should read (COUNT(Tickets)>10).
- Replace the _value_if_true, and _value_if_false placeholders with the results you want to return. Replace _value_if_true with "Big" and _value_if_false with "Small".
- In the Computed from dropdown, choose Ticket channel. This will ensure that tickets are only counted based on the ticket channel field.
The formula is complete. The logic is if the number of values in each ticket channel is larger than ten, the formula returns "Big" or else (if the result is ten or less, the formula returns "Small". Notice that the red error text has changed to a green tick to indicate that the formula syntax is correct.
- When you are ready, click Save to save the attribute and close the Standard calculated attribute panel.
Next, you'll test your new attribute by using it in a query.
Building your query
Now that you've created a standard calculated attribute, it acts just like any of the built-in attributes. It's also available to use in any of your queries, and can be used by anyone who has access to the dataset in which you created it.
To create a query using your standard calculated attribute
- In a new query using the Support: Tickets dataset, add the metric Tickets.
- In the Rows panel, click Add.
- Expand Calculated attributes, then click your standard calculated attribute Ticket channel big or small?.
Explore displays your results showing the number of tickets associated with "Big" channels (with more than ten tickets) and "Small" channels (with ten or less tickets).
You can make this table clearer by adding the attribute Ticket channel to the Rows panel.
Now it's become clear which channels had the most tickets and whether they fall into the "Big" or "Small" group.
Make sure to save your query before you move on.
Editing your formula
In this section, you'll continue to work with the standard calculated attribute you created previously. You'll add another test to the condition and experiment with returning different results than the text you used before.
- If the number of tickets for that channel are less than 4, return "Small"
- If the number of tickets for that channel are exactly 4, return "Medium"
- If the number of tickets for that channel are more than 4, return "Big"
To construct this query, you'll nest multiple IF THEN ELSE functions to perform the extra test. However, the basic structure of the formula is similar.
To edit the formula
- Open the query you saved in the previous section.
- In the Rows panel, click your standard calculated attribute Ticket channel big or small?
- In the attribute filter window, click the edit calculation button as shown in the screenshot below.
- The standard calculated attribute panel opens displaying your standard calculated attribute.
- Replace the formula with the following:
IF (COUNT(Tickets)>4) THEN "Big" ELIF (COUNT(Tickets)=4) THEN "Medium" ELIF (COUNT(Tickets)<4) THEN "Small" ENDIF
This is a useful way to avoid having to write multiple IF THEN ELSE functions. You could also use the SWITCH function to achieve a similar result.
- When you are finished, click Save.
- Explore automatically recalculates your query and displays the results.
You'll see that one of the ticket channels (Email) has four tickets so is shown as "Medium".
Here are some more formula examples that you can use or revise to your own needs.
|IF ([Ticket status]="Open") THEN [Ticket ID] ENDIF||Returns the ID of all tickets with a status of Open.|
|IF ([Ticket tags]="csat") THEN [Ticket ID] ENDIF||Returns the ID of all tickets with the csat tag|
|IF (CONTAINS([Ticket subject],"Tweet"))THEN [Ticket ID] ENDIF||Returns the ID of all tickets that contain the word "Tweet" in their subject line.|
|IF DATE_EQUAL([Ticket Created - Month],[Ticket Solved - Month]) THEN [Ticket ID] ENDIF||Returns the ID of all tickets that were not solved in the same month they were created.|
Explore has hundreds of included metrics and attributes, and a wide range of functions you can use in your formulas. Additionally, there are other types of calculated metrics and attributes for calculating dates, comparing times, and much more.
You're encouraged to experiment with creating your own calculated metrics and attributes. The following articles are a great reference to help you: