Zendesk – Tracking Ticket Statistics

Zendesk – Tracking Ticket Statistics

Question

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.

Schema

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 tickets and ticket_events that we are going to use bellow.

The tickets table has the following structure

The ticket_events table has the following structure

Go here to find out the full schema of Zendesk expected data.

Output

SQL Queries

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