Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

29 Essential KPIs You Should Be Tracking

DZone's Guide to

29 Essential KPIs You Should Be Tracking

With these queries, it can be easy for you to keep track of your customer support performance on a daily basis.

· Performance Zone
Free Resource

Customer support is an important function of your company, and although in the past it 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 metric related to customer experience when it comes to supporting, 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

This 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.

There are 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 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

This 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 to solved.

Ideally, we’d like to see the two resolution times to be as close together as possible. A low full resolution time that 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. 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 * FROM conttickets WHERE status != 'closed' AND DATEPART('day', now()-createdat)*24+DATEPART('hour',now()-created_at) <=24 

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 on an individual agent level, as well.

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 

Conclusion

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.

Topics:
performance ,kpi ,application performance ,customer support

Published at DZone with permission of Kostas Pardalis, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}