Customer support is an important function of your company, and although in the past was considered more of a hassle that was keeping profits down, now people have started to realize that support is part of the product and the overall experience that a customer has.
In this context, customer support can contribute to the following, if it is run successfully,
- Customer retention. By solving the problems that your customers have, you offer them a good reason to keep using your product.
- Customer acquisition. Good customer support during the onboarding process of a new customer increases the chances of converting her into a paying customer. Additionally, happy customers are more likely to become evangelists of your product.
- Product development. This is more apparent to startup founders, but it remains valid even when a company scales up. Customer support is a great channel for validating your product and a great source of information for your product roadmap.
But how can we offer excellent support to our customers? As with every other function in your company, from product development to marketing, the key is to start working on it early and try to understand what works for you and what not. The best way to do this is by measuring your activities and keeping track of a number of customer support metrics.
The good news is, that customer support is a function-rich of data that is waiting for you to take advantage of. All it takes is to connect your favorite customer support tools with a database, and then you can easily calculate and keep track of all the customer support metrics that are relevant to you.
In this post we will go through the most important customer support metrics, calculated on data coming from one of the most popular platforms for customers support, which is Zendesk. Using Blendo, you may load all Zendesk data into a database and keep them synced, a process that it takes just a few minutes to setup. Then the customer support metrics can be calculated easily, using the queries that we will see in this post.
Customer Support Metrics Categories
No matter if you just start having your first customers or you are a multinational company, the categories of customer support metrics that are relevant, are always the same. What change is the scale of the data and how deep you should go into it to actually get your insights?
These important categories are the following:
- Customer Experience. You should be able to understand what experience your customers perceive when they interact with your company through customer support. This is usually represented by a number of customer support metrics around Response Time.
- Customer Satisfaction. How satisfied are you customers from the support their getting? Sounds important, right?
- Workload. Your support agents are your hero but you need to take good care of them, at the end customer support is based on human interaction, and if one of the two participants is burned out, many bad things can happen. Additionally, as you scale up your business, your support should also scale and do this efficiently, these metrics will help you plan effectively.
So, based on these three categories we will have a number of customer support metrics, together with the queries that calculate them on data coming from Zendesk. Keep in mind that some metrics might overlap between the categories.
Customer Experience Metrics
Customer support is all about communicating effectively with your customers, this communication can significantly affect the process of resolving the issue reported by them. For this reason, it is important to understand the experience your customers are getting from your support.
There are plenty of metrics related to customer experience when it comes to support, but the most fundamental ones are related to the Response Time, the time that it takes for your customer support team to respond to the customer queries.
First Response Time
It is the time it takes for your team to answer to a new ticket or customer question, for the first time. This can be measured as the minutes or hours (hopefully you will not opt for days) elapsed between the creation of a new ticket from a customer and the first message that a customer support representative sent as a reply.
A few things that you should keep in mind when working with the First Response Time metric.
As a metric, it can easily be manipulated or misleading. For example, your ticketing system, like Zendesk, might automatically respond whenever a new ticket is created. This automatic response shouldn’t be counted towards the First Response Time.
You should consider your working hours, if you do not offer 24/7 support then a ticket that is created outside your working hours, shouldn’t be measured towards the overall First Response Time, you could offset the creation time to the beginning of your working day.
As in other similar customer support metrics, you should consider different views of it, more specifically, you should take into consideration the average, median, minimum and maximum first response time.
The Average First Response Time
SELECT avg(firstResponse) from
(SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
FROM
(SELECT a.time AS ftr, tmp1.created_at, a.ticket_id, a.metric
FROM cont_ticket_metric_events AS a,
(SELECT created_at, id
FROM cont_tickets)tmp1
WHERE (a.ticket_id=tmp1.id)
AND (a.time>tmp1.created_at)
AND a.metric = 'reply_time')tmp2
GROUP BY tmp2.ticket_id)tmp3
The Minimum First Response Time
SELECT min(tmp2.ftr-tmp2.created_at) AS MinfirstResponse
FROM
(SELECT a.time AS ftr,
tmp1.created_at,
a.ticket_id,
a.metric
FROM cont_ticket_metric_events AS a,
(SELECT created_at, id
FROM cont_tickets)tmp1
WHERE (a.ticket_id=tmp1.id)
AND (a.time>tmp1.created_at)
AND a.metric = 'reply_time')tmp2
The Maximum First Response Time
SELECT max(firstResponse) from
(SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
FROM
(SELECT a.time AS ftr, tmp1.created_at, a.ticket_id, a.metric
FROM cont_ticket_metric_events AS a,
(SELECT created_at, id
FROM cont_tickets)tmp1
WHERE (a.ticket_id=tmp1.id)
AND (a.time!=tmp1.created_at)
AND a.metric = 'reply_time')tmp2
GROUP BY tmp2.ticket_id)tmp3
The Median First Response Time
SELECT median(extract(epoch FROM firstResponse)/60)
FROM
(SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
FROM
(SELECT a.time AS ftr,
tmp1.created_at,
a.ticket_id,
a.metric
FROM cont_ticket_metric_events AS a,
(SELECT created_at, id
FROM cont_tickets)tmp1
WHERE (a.ticket_id=tmp1.id)
AND (a.time!=tmp1.created_at)
AND a.metric = 'reply_time')tmp2
GROUP BY tmp2.ticket_id)tmp3
First Resolution Time
Is the time it took until the ticket was marked as resolved by the customer support agent for the first time. We say first time because it is not uncommon for a ticket to be reopened by the customer with further questions.
The first resolution time metric is an indication of the efficiency of customer support. Ideally, we’d like none of the tickets to be reopened while reducing this time as much as possible. This time is also perceived by the customer, and thus it affects her experience.
The Average First Resolution Time
SELECT avg(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT min(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Maximum First Resolution Time
SELECT max(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT min(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Minimum First Resolution Time
SELECT min(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT min(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Median First Resolution Time
SELECT median(extract(epoch
FROM (first_resolution-first_activation))/60)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT min(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
Full Resolution Time
Another metric that usually works well together with First Resolution Time is the Full Resolution Time. This is the time between ticket creation and the timestamp of the latest change of the ticket status into “solved”.
Ideally, we’d like to see the two resolution times to be as close together as possible. A low Full Resolution Time which is close to the First Resolution Time, means that our team is reaching the holy grail of customer support which is to being able to solve the problems of our customers with the first communication that we have.
The Average Full Resolution Time
SELECT avg(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT max(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Maximum Full Resolution Time
SELECT max(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT max(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Minimum Full Resolution Time
SELECT min(first_resolution-first_activation)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT max(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
The Median (in minutes) Full Resolution Time
SELECT median(extract(epoch
FROM (first_resolution-first_activation))/60)
FROM
(SELECT min(TIME) AS first_activation,
ticket_id
FROM cont_ticket_metric_events
WHERE (TYPE='activate')
GROUP BY ticket_id)act
RIGHT JOIN
(SELECT max(TIME) AS first_resolution,
ticket_id
FROM cont_ticket_metric_events
WHERE TYPE='fulfill'
GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id
Requester Wait Time
Another important metric of customer experience is the time the client has to wait for a response from an agent.
If you think of customer support as a dialog between the customer and your company, every time the customer sends you a message it takes some time for the agent to respond back.
The total amount of this time is something that affects the overall experience that a customer has and thus it is important to keep track of it.
The Average Requester Wait Time
SELECT avg(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime
FROM
(SELECT ticket_id, sum(CASE
WHEN tmp.type='activate' THEN minutes
ELSE NULL
END) AS activations,
sum(CASE
WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
ELSE NULL
END) AS fulfillments
FROM
(SELECT ticket_id,
metric,
TIME,
TYPE,
now()-TIME AS minutes
FROM cont_ticket_metric_events
WHERE metric = 'requester_wait_time'
AND TYPE IN('activate',
'paused',
'fulfill') )tmp
group by ticket_id )tmp2
The Minimum Requester Wait Time
SELECT min(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime
FROM
(SELECT ticket_id, sum(CASE
WHEN tmp.type='activate' THEN minutes
ELSE NULL
END) AS activations,
sum(CASE
WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
ELSE NULL
END) AS fulfillments
FROM
(SELECT ticket_id,
metric,
TIME,
TYPE,
now()-TIME AS minutes
FROM cont_ticket_metric_events
WHERE metric = 'requester_wait_time'
AND TYPE IN('activate',
'paused',
'fulfill') )tmp
group by ticket_id )tmp2
The Maximum Requester Wait Time
SELECT max(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime
FROM
(SELECT ticket_id, sum(CASE
WHEN tmp.type='activate' THEN minutes
ELSE NULL
END) AS activations,
sum(CASE
WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
ELSE NULL
END) AS fulfillments
FROM
(SELECT ticket_id,
metric,
TIME,
TYPE,
now()-TIME AS minutes
FROM cont_ticket_metric_events
WHERE metric = 'requester_wait_time'
AND TYPE IN('activate',
'paused',
'fulfill') )tmp
group by ticket_id )tmp2
The Median Requester Wait Time
SELECT median(extract(epoch
FROM (tmp2.activations-tmp2.fulfillments))/60)
FROM
(SELECT ticket_id, sum(CASE
WHEN tmp.type='activate' THEN minutes
ELSE NULL
END) AS activations,
sum(CASE
WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
ELSE NULL
END) AS fulfillments
FROM
(SELECT ticket_id,
metric,
TIME,
TYPE,
now()-TIME AS minutes
FROM cont_ticket_metric_events
WHERE metric = 'requester_wait_time'
AND TYPE IN('activate',
'paused',
'fulfill') )tmp
group by ticket_id )tmp2
Customer Support Workload Metrics
While it is of paramount importance to track the experience of your customers as they interact with your support, it is equally important to also measure how the support “machine” of your company behaves. Again there are plenty of different metrics that can be tracked over the Zendesk data. Here we will focus on some of the most important ones.
Tickets By Group
As your company grows bigger, sooner or later you will need to create different groups of support agents.
You might have a group of first contact agents who will try to solve the issues as soon as possible. Then, as the severity of the problem increases, an engineer might have to get involved, she will be part of another group.
It is not uncommon to have different groups of agents inside the customer support function of a company and thus it is important to keep track of how each group performs.
This is not only about performance if too many tickets end up being handled by your engineers you might have to reconsider your documentation or check with the product team to see what’s going wrong.
Tickets By Group
SELECT count(group_id),
group_id,
tmp.name
FROM cont_tickets,
(SELECT name,
id
FROM cont_groups) tmp
WHERE tmp.id=cont_tickets.group_id
GROUP BY group_id,
tmp.name
Another useful and related metric is the number of new tickets per group. Again, adjust the query accordingly to the timeframe you’d like to track the creation of new tickets per group on Zendesk.
New Tickets By Group
SELECT count(group_id) as NewTickets,
group_id,
tmp.name
FROM cont_tickets,
(SELECT name,
id
FROM cont_groups) tmp
WHERE tmp.id=cont_tickets.group_id and status='new'
GROUP BY group_id,
tmp.name
How many tickets get passed between groups?
Related to the previous customer support metric, it is important to not only keep an eye on the tickets per group but also, to know how tickets are exchanged between them.
This metric will help you understand if there’s a bottleneck between your groups and if you have assigned the appropriate responsibilities to each one of them.
SELECT count(*) AS passed FROM cont_ticket_events WHERE child_events_group_id != ''
Basic Ticket Statistics
There are some very basic metrics that you should track, regarding tickets. These are 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.
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
Ticket Creation Time Distribution
Another very useful customer support metric is how the creation of new tickets is distributed in time. Is there a specific time where more tickets are created? Do you get a lot of tickets during weekends?
These questions are important when you need to plan your customer support workforce accordingly to your needs while maintaining good response times.
Ticket Creation Time Distribution
SELECT date_part('hour', created_at) AS hours,
to_char(created_at, 'day') AS day,
count(*) AS count
FROM cont_tickets
GROUP BY to_char(created_at, 'day'),
date_part('hour', created_at)
ORDER BY date_part('hour', created_at)ASC;
Ticket Distribution over Submission Channels
Nowadays, customer support happens across many different channels, you might exchange messages with your customer through social media and email at the same time.
So, it is important to track how tickets are distributed across the different communication channels that you maintain.
Ticket Distribution over Submission Channels
select via_channel, count(*) from cont_tickets group by via_channel
Keeping track of the ticket backlog
A commonly used term in customer support is the “backlog”. Which is a nice way of referring to the “work in progress” that a customer support team has? The size of the backlog indicates the efficiency of your team, and it might signal the need for adding new members to it.
Backlog
SELECT *
FROM cont_tickets
WHERE status != 'closed'
AND DATE_PART('day', now()-created_at)*24+DATE_PART('hour',now()-created_at) <=24
Ticket distribution per agent
So far we have covered the metrics related to the teams. But it is important to track the workload also on an individual agent level.
If for any reason you have agents who have more work than they can handle, you should act and rebalance their workload. There’s nothing worse than agents with a burnout trying to solve complex issues from your customers.
Ticket Distribution per Agent
SELECT a.name,
tmp.count
FROM cont_users AS a,
(SELECT count(assignee_id) AS COUNT,
assignee_id
FROM cont_tickets
GROUP BY assignee_id)tmp
WHERE tmp.assignee_id = a.id
Customer Satisfaction
Customer satisfaction is another important set of customer support related metrics that you should keep track of.
The best way of measuring customer satisfaction is through surveys that you send to your customers to give you feedback regarding the quality of the customer support they have received so far.
Zendesk offers a mechanism where whenever a ticket is closed a mail is sent to the customer to give feedback regarding the service, so there’s no excuse to not measure the satisfaction of your customers.
Customer Satisfaction
SELECT count(CASE
WHEN satisfaction_rating_score ='unoffered' THEN 1
ELSE NULL
END) AS unoffered,
count(CASE
WHEN satisfaction_rating_score ='offered' THEN 1
ELSE NULL
END) AS offered,
count(CASE
WHEN satisfaction_rating_score ='bad' THEN 1
ELSE NULL
END) AS bad,
count(CASE
WHEN satisfaction_rating_score ='good' THEN 1
ELSE NULL
END) AS good
FROM cont_tickets
Finally, you can also measure the customer satisfaction on a per agent level to see who interacts better with the customers or who might need some additional training.
Customer Satisfaction per agent
SELECT a.name,
tmp.unoffered,
tmp.offered,
tmp.bad,
tmp.good
FROM cont_users as a,
(SELECT assignee_id,
count(CASE
WHEN satisfaction_rating_score ='unoffered' THEN 1
ELSE NULL
END) AS unoffered,
count(CASE
WHEN satisfaction_rating_score ='offered' THEN 1
ELSE NULL
END) AS offered,
count(CASE
WHEN satisfaction_rating_score ='bad' THEN 1
ELSE NULL
END) AS bad,
count(CASE
WHEN satisfaction_rating_score ='good' THEN 1
ELSE NULL
END) AS good
FROM cont_tickets
GROUP BY assignee_id)tmp
WHERE tmp.assignee_id=a.id
Conclusions
We have seen a number of queries that can be used to calculate a set of customer support metrics that can give a good overall view of how our customer support operates. These metrics are easy to calculate and keep track of and are relevant to any size of the company. With these queries as the foundation, you can create some beautiful dashboards that can help you keep track of your customer support performance on a daily basis, but this is something that we will investigate in a future blog post.
So give a try to Blendo its free for 14 days, load your customer support data into a database and start measuring your customer support metrics. Let me know how it goes with your queries. Good luck.
