Number of tickets received during a specific period of time + division of those tickets into groups by tagsAnswered
I try to create a query that will show a table containing the following columns:
1. The number of tickets received during the period N1.
2. The distribution of those tickets by tags;
3. The number of tickets received during the period N2.
4. The distribution of those tickets by tags.
I tried using different date range calculated metrics along with a fixed metric or an ATTRIBUTE_FIX aggregator (that should not be affected by the selected attributes) but nothing seems to work. Is it possible to build such a query in Explore?
Any help would be appreciated. Thanks!
You should be able to see the number of tickets received on N1 and N2 using the date range calculated metrics, however I am not sure I understand what you mean by "the distribution of those tickets by tags".
Could you give me more details about with maybe an example ?
I would then be able to look for a potential solution.
Thibaut | Customer Advocate | EMEA
👉 Free Zendesk trainings 👈
I tried to photoshop a table for illustrative purposes:
The query should give me the following info:
1. A tag name (one row = one tag);
2. The total number of tickets received within the chosen period;
3. How many tickets with a tag were received within the chosen period;
Say, we received 1000 tickets in total on N1, 41 of those tickets have the "blue" tag, 38 tickets were tagged with "red", and so on.
Creating a data range calculated metrics helps to set the N1 and N2 ranges but I can't make the query count and show both the total number of tickets we received and the number of tickets by each tag in the same query. If I put a "ticket tags" attribute, it only counts how many tickets marked with a tag we received, in all columns.
Hope I managed to explain it. Thank you for your help!
In order to create this query you will first need to create a standard calculated attribute like this one:
With this formula:
IF INCLUDES_ALL([Ticket tags], "classical") THEN "Classical"
ELIF INCLUDES_ALL([Ticket tags], "electric") THEN "Electric"
ELIF INCLUDES_ALL([Ticket tags], "bass") THEN "Bass"
You will need to change the tags I used for the example with your own tags.
Then you will need to create two date range calculated metrics like this one :
Original metric: Tickets
Defined on: Ticket created
Then select the date range for N1, and reproduce the same for N2.
Finally you will need to create two advanced fixed metrics like this:
With this formula :
IF ([Ticket created - Date]>="2021-04-04"
AND [Ticket created - Date]<="2021-05-18")
THEN [Ticket ID]
You will need to change the dates to reflect N1 and on the second one, N2.
Then you can finally place those new metrics and attribute in your query to have something like this:
You can find here the related articles:
- Getting started with custom metrics and attributes
- Adding time and date calculated metrics
- Adding fixed calculated metrics
Have a great day :)
I tried creating those metrics but it never shows the total numbers and gives me an error about the SQL query exceeding 50000 rows. :( Is there any way to fix this?
This error would be due to Explore's limit being reached as explained here: Why do I receive a “Network error” in my query?
I would suggest you to reduce the date range of N1 and N2 in order to not go beyond the 50 000 rows, the limit can not be changed or modified so reducing the date range would be the best option.
Please sign in to leave a comment.