In this Explore recipe, you'll learn how to create a query that displays the percentage of tickets that were created by each channel.
What you'll need
Skill level: Easy
Time required: 15 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
Creating the query
In this section, you'll add the metrics and attributes needed to create the chart.
To create the query
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets, then click Apply.
- In the Columns panel, click Add.
- From the list of attributes, choose Ticket > Ticket channel, then click Apply. Explore displays a chart showing the number of tickets received from each channel.
- To display the percentage of tickets received from each channel, click the Visualization type (
) menu, then choose Pie. See Chart types for comparing values to total results for more information on using charts to evaluate total results.
Your report will look something like this:
Next steps
The next steps are optional if you want to display the data in a table format instead of a pie chart.
- Click Visualization type () > Table.
- Drag the Ticket channel attribute from Columns to Rows.
- Click Calculations (
), and then Standard calculated metric.
We need to create a standard calculated metric that is a duplicate of the Tickets metric. - Copy the metric below and paste it into the metric editor.
[Ticket ID]
- Name the metric # Tickets.
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > # Tickets, then click Apply. Ensure the metric aggregator is set to COUNT.
- From the Result manipulation (
) menu, click Result path calculation.
- On the Result manipulation page, change COUNT(Tickets) to the below settings:
Pattern: % of total
Path: On specified attribute
Attribute: Ticket channel - From the Result manipulation () page, click Totals.
- On the Totals page, enable Grand totals on > Rows and Subtotals on > Rows. Click Apply.
The query is complete. See the screenshot below for an example of the finished result.
18 Comments
I am trying to use this recipe to show the percent of tickets by organization. I am able to get the chart to show correctly. However, I would like to filter down to our top 10 organizations that submit tickets. When I filter down (using the top/ bottom result manipulation), the percentages recalculate to show the percent of total for just those 10 organizations instead of percent of all tickets. Is there a way to maintain the percent as a percent of total tickets?
Hi Laura,
Good day!
To maintain the percentage of all tickets, hit the 'aggregate filtered results' box in the setup, that should give you a 'bucket' of all the other channels, so the % stay correct, here's an example:
I hope this helps!
This is very helpful. Thank you. I was able to replicate this in a query with multiple groups and multiple statuses. However, only being able to set to rows OR columns limits the sub\grand total aggregation to either\or. So the column grand total percentages all equal 100% and the rows correctly show the broken down percentage, or vice versa. Is there a way to do this so that the percentage of total for the grand sum row&column both show the percentage of total?
Hi Donald,
Good day!
I apologize for the delay, but then the total wouldn't be the total of the results broken down by row or column? You can set the % to be % of the total on the whole result, though.
Is there a way to show a grouped stat?
For example, I want to know the ticket percentages in certain topics.
We built a contact form with a topic tree. There are a couple of lvl1 topics and they might or might not have deeper topics. Down to lvl3.
So I would like stats like this:
Lvl1 60%
L2 90%
L2. 10%
L2.. 10%
Lvl1. 32%
L2 70%
L3 80%
L3. 20%
Lvl1.. 8%
And so on.
Is it doable?
Sebastian, I cannot figure out how to get this in a single query. You can apply the above logic to a second row and see the breakdown of a next series of values, if that helps. Here I added a second row (a custom field we have on our forms) and just updated the result path calculation to be on Milestone instead of Ticket Channel.
Thank you, I definitely need to get a bit deeper into that topic. I wouldn't know at the moment how to rebuild.
But mostly diagrams are needed, so that might be a level more complicated.
Is it possible to add a custom metric that calculates the percentual change to a previous month.
For example:
Metric: Tickets
Columns: Ticket created month
Rows: Ticket channel
Now I can show a table but I also want to show the percentual change to the previous month.
Hey Sebastian, check out this recipe and see if you can modify for your metric and timeframe: https://support.zendesk.com/hc/en-us/articles/360025293494-Explore-recipe-Week-over-week-SLA-percentage-change
Hi!
I am looking for a recipe similar to this;
Week / year
Percentage of total tickets IS chats (and the % chat-tickets of total)
Any idea?
Hi Line,
I think that these steps will give you the query you want:
This will show the percentage of tickets created each week that are chat tickets
Hey team, I have been trying to make the following, but keep getting stuck, I have trouble stacking the figures on top of one another. Any ideas on how to make this here:
Hi Abhishek Dhivar, in Explore you should add your 'Ticket Channel' to Rows. By default it will show you a row selector, but can hold shift to select all the rows - and then you can actually hide the row selector by going to Chart Configuration, then Row Selector and then unchecking the 'Display row selector' checkbox.
Hi Team,
I have 3 custom fields that have dependency on another field, for instance, 1 controller field controls field A, B, C, this is set in ticket form condition.
Could someone give me an idea how to display this data in the explore in a similar behavior, so if I put this controller field as data filter, so then I would need only 1 another filter that shows only the options that are associated with the given value from controller field.
at the moment, I need to display all of those 3 fields in the dashboard, would be perfect if there is a way to make the dashboard more dynamic.
Hello Adnan,
Currently, it is not possible to display those three fields in a dashboard. I would recommend posting your use case in our Product Feedback forums so our devs can consider it for a future update.
Best regards.
Great article and exactly what we were looking for. We were able to use this to show the number and percentage of total tickets in the same table.
Thanks for the write-up.
How would you adapt this query for a multi-select ticket field?
I am trying to create a report showing the percentage of tickets that include different topics. The topics are selected via a multi-select ticket field, so a single ticket can have multiple topics.
Following this recipe, I was able to show the correct counts for each topic. However, the percentages are incorrect. The example below is reporting on 3 total tickets, so the percentages in the table should be 67% and 33%. Any ideas how to correct this?
Hi Jon,
I understand that you want to report on the percentage of tickets that have multiple topics chosen with the use of a multi-select ticket field.
Due to the way multi-select ticket fields are stored, a ticket with multiple topics/values chosen will always be returned multiple times throughout a query may it be a count of tickets for each topic or based on its percentages. There’s not a way to only count a ticket once across all multi-select values.
Hope I was able to clear this one out. :)
Please sign in to leave a comment.