In this Explore recipe, you'll learn how you can take custom numeric fields in Explore (that are stored as metrics) and convert them into attributes with which you can slice another metric. Additionally, you'll learn how to do a reverse action to convert a text field attribute into a metric.
For more information about custom fields, see Adding custom fields to your tickets and support request form and Reporting with custom fields.
This recipe contains the following sections:
- What you'll need
- Converting a numeric field metric into an attribute
- Converting a numeric field attribute into a metric
What you'll need
Skill level: Intermediate
Time required: 15 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
- Some experience creating reports with Explore
- At least one custom numeric ticket field (see Adding custom fields to your tickets and support)
Converting a numeric field metric into an attribute
In this first example, you've set up a custom numeric ticket field in Support named Product code that contains a numeric code value for the associated product. Explore stores this as a metric named Product code. You want to produce a report showing all of your tickets and their associated product code. Because both the product codes and the number of tickets are both stored as metrics, this is not possible without doing some more work. In this example, you'll create a few calculated attributes that can be used to accomplish the required goals.
To create a standard calculated attribute using the product code metric
- In Explore, create a standard calculated attribute using the following formula:
VALUE(Product code)Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
- Name the attribute Product code attribute and save it.
You can now use this attribute to test product code values in your Explore reports. This example shows a column chart displaying how many of which product codes are associated with each ticket.
Additionally, you can revise the standard calculated attribute formula to further refine the product code results (for example, you might want to use the attribute as a filter). This formula returns one of the following values:
- >500 if the product code is higher than 500
- 300-500 if the product code is between 300 and 500
- 0-300 if the product code is between 0 and 300
IF VALUE([Product code])>500 THEN ">500"
ELIF VALUE([Product code])<=500 AND VALUE([Product code])>300 THEN "300-500"
Converting a numeric field attribute into a metric
In this example, you’ve created a custom user drop-down ticket field in Support named Net Promoter Score which is populated with the customer’s NPS. Explore stores this value as an attribute. You now want to look at the average score for your organization. To do this, you’ll create a standard calculated metric from the Net Promoter Score attribute.
To create a standard calculated metric using the Net Promoter Score attribute
- In Explore, create a standard calculated metric using the following formula:
NUMBER([Net Promoter Score])
- Name the metric Net Promoter Score and save it.
You can now add this custom metric to the Metrics panel using the average (AVG) aggregator to see your average Net Promoter Score. You can then slice this metric by other attributes like the ticket status or ticket assignee.
I've created a dropdown ticket field in support named star rating numbers. I want to calculate the average for the values (1 to 5) of this dropdown.
When I use NUMBER([star rating numbers]) I should be able to then add it as a metric and use AVG, but unfortunately, I get an abnormal result. Am I right in thinking that adding this Metric with the AVG aggregator should return the average of the dropdown values in all tickets?
We were able to replicate this behavior and the team is now looking into the issue with this function. Thanks for raising this with us!
While we're working on a fix for this, a workaround that you can use for this kind of report is to add any date filter to the query. Here's a sample on my account:
When I add a date filter – for example, Ticket created - Date – the query is able to calculate for the actual average from the metric values.
Hello, I have tried to convert a numeric field into an attribute using the function Value and I see this error :
I created a ticket on your behalf so that we can investigate on this further within your account. Thanks!
Please sign in to leave a comment.