Zendesk – Tracking Ticket Statistics
What is the Number of New tickets? What is the Number of Solved tickets?
These are some of the very basic metrics that you should track, regarding tickets. We will see the following:
- Number of new tickets
- Number of solved tickets
- Percentage of solved tickets
- Number of one touch tickets
All the above metrics make sense inside a time frame, which might differ depending on your business context. So, adjust the queries accordingly to calculate the above metrics for the time frame you are interested.
The number of one touch tickets is quite interesting as a customer support metric. It shows the number of tickets that your agents did extremely well, as the solution was given to just one response, but also it might indicate suitable candidates for documentation entries.
So, if you see a recurrent question which is easily answered, then it might be a good idea to create a new entry about this question to your FAQ if you don’t have one already.
Blendo will sync all of your Zendesk data into your data warehouse properly prepared to be used for your analytics purposes. Some of the tables we prepare are
ticket_events that we are going to use bellow.
tickets table has the following structure
ticket_events table has the following structure
Go here to find out the full schema of Zendesk expected data.
Number of New Tickets
SELECT count(CASE WHEN status ='new' THEN 1 ELSE NULL END) AS NEW FROM cont_tickets
Number of Solved Tickets
SELECT count(*) FROM cont_tickets WHERE status = 'solved'
Percentage of Solved Tickets
SELECT 100*CAST (tmp.solved AS decimal)/cast(tmp.total AS decimal) AS percentage FROM (SELECT count(CASE WHEN status ='solved' THEN 1 ELSE NULL END) AS solved, count(*) AS total FROM cont_tickets) tmp
Number of One Touch Tickets
SELECT DISTINCT count(*) FROM cont_ticket_events a, (SELECT count(child_events_comment_public) AS cnt, ticket_id FROM cont_ticket_events WHERE (child_events_event_type = 'Comment') GROUP BY ticket_id) tmp WHERE a.ticket_id=tmp.ticket_id AND tmp.cnt<=1