Freshdesk KPIs: Tracking the performance of your customer support team

For quite some time, customer support has stopped being considered an optional service. Although in the past, businesses used to provide no real support to customers as they thought it was an inhibitor to their profit, nowadays the high competition has radically reversed this perception.

Currently, customer services are viewed as a mean of communication between a business and a customer before and after purchasing a product or a service. By efficiently supporting them, businesses can reap great benefits in the following areas:

• Customer Retention. By solving the problems that customers face, a business can increase the chances that they will keep making new purchases or continue to pay for certain services.
• Customer Acquisition. Offering customer support during the pre-selling phase increases the chances of converting the requester into a paying customer. In case the potential customer faces any difficulty with the product, customer support has to resolve any issues before even thinking about convincing him to buy.
• 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 know that our customer support is living up to customer expectations?

As with every backbone function in your company, the method usually followed is the ‘trial and error’. But while attempting to find what works best for you business and what doesn’t, it would be awesome to have some guidelines. Thus, the best way to generate relevant insight into your effort and bring your work to the next level 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 and thus there is a laundry list of items to keep track 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.

Depending on the business environment, there can be a large variety of performance indicator to consider and knowing where to start can be overwhelming.

To make it easier we have compiled a list of the most important customer support metrics, calculated on data coming from Freshdesk. Using Blendo, you may load all Freshdesk data into a database and keep them synced, a process that it takes just a few minutes to set up. 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 changes 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 as a product of an interaction with your company through customer support. Metrics like First Response Time or Full Resolution Time and a few more can help you evaluate the quality of the customer experience you are offering.
• Customer Satisfaction. Includes measures that evaluate whether the products and services you are offering meet or, even better, surpass customer expectations.
• 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 can construct a number of key performance indicators that relate to customer support, along with the SQL code that calculates them on data originated from Freshdesk. Keep in mind that some metrics might overlap between the categories.

Customer Experience Metrics

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 between when a customer submits a support ticket and when a customer support representative provides an initial response. This can be measured in minutes or hours, depending on your business. As Jamie Edwards advises “First reply time is more important than overall reply times because it’s an acknowledgment to the customer that their issue is being looked into.”

However, when calculating the First Response Time you must keep in mind that, at times, the results can be misleading. For example, you have to take into consideration whether your ticketing system, like Freshdesk, sends automated replies when a new ticket is submitted. In case it does, these replies must not be considered to be the ‘first responses’. Additionally, in case you do not offer 24/7 support you have to figure out how to handle tickets opened outside working hours. Most probably you will have to subtract non-working hours out of the total waiting time or otherwise, you will wrong the effort your support team is putting.

Lastly, apart from the average First Response Time which is the first obvious choice, you should also inspect the matter from other points of view by also computing minimum, maximum or median.

First Time Response (source = 0 corresponds to Reply type)

Select avg(first_response), max(first_response), min(first_response)
(select conv.ticket_id, min(conv.created_at - fd_tickets.created_at) as first_response
from
(select id, created_at, user_id, ticket_id
from fd_ticket_conversations
where user_id in (select id from fd_agents) and source = 0 )conv
left join fd_tickets
on conv.ticket_id = fd_tickets.id
group by conv.ticket_id)tmp

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 time and the time of the final (or most recent) change of status to solved.

Full resolution (assumed time of the last reply)

Select avg(last_response), max(last_response), min(last_response)
from
(select conv.ticket_id, max(conv.created_at - fd_tickets.created_at) as last_response
from
(select id, created_at, user_id, ticket_id
from fd_ticket_conversations
where user_id in (select id from fd_agents) and source = 0 )conv
left join fd_tickets
on conv.ticket_id = fd_tickets.id
where fd_tickets.status = 4 /*status = closed */
group by conv.ticket_id)tmp

Customer Support Workload Metrics

While it is of vital significance to track the experience of your clients as they contact your front desk agents for help, it is similarly imperative to likewise gauge how the support team acts. Again there are a lot of various KPIs that can be calculated from the Freshdesk data. Here we will center around probably the most critical ones.

Tickets By Group

As your company grows bigger, sooner or later you will need to create different groups of support agents that are responsible for handling different cases. Since the size of these teams can vary depending on the type and the severity of the inquiries they are handling it is important to know the demands of each group in order to distribute your manpower efficiently.

Furthermore, 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 assignee group

select group_id, count(*)
from fd_tickets
group by group_id

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

New (Open) Tickets by group

SELECT group_id, count(*)
from fd_tickets
where status = 1
group by group_id

 

Ticket Distribution over Agents

Apart from monitoring the team performance as a whole, it is equally important to keep an eye on the individual achievements of each one of your agents. This way you will be able to easily figure which agent to reward for his exceptional performance or who needs further training and encouragement.

Ticket distribution per agent

select fd_agents.contact_name, tmp.cnt
from
(SELECT responder_id, count(*) as cnt
from fd_tickets
group by responder_id) tmp
Left join fd_agents
on fd_agents.id = tmp.responder_id

Ticket Distribution over products

In case your company offers more than one products or services, it is important to be able to monitor which one of them troubles more frequently your customers so that o be able to proceed accordingly.

Ticket distribution per product

SELECT product_id, count(*) as cnt
from fd_tickets
group by product_id

 

Escalated Tickets

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 escalated from the lower towards the higher support tiers.

This metric will help you spot potential performance bottlenecks within a team and across different support groups, issues with the product documentation that need clarification or even flaws in your product that makes it troublesome for customers to use.

Tickets escalated & Tickets escalated due to  first response being breached

SELECT count(case when is_escalated = True then 1 else null end) escalated, 
       count(case when fr_escalated = True then 1 else null)fr_escalated
from fd_tickets

 

Basic Ticket Statistics

Apart from the more sophisticated metric, a good customer support team needs not to neglect the basic metrics when it comes to tickets.

These are the following KPIs:

• Number of new tickets
• Number of solved & fully resolved 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.

New Tickets

SELECT count(*)
from fd_tickets
where status = 1

Solved Tickets

SELECT count(*)
from fd_tickets
where status = 3 or status = 4

Fully Resolved Tickets

SELECT count(*)
from fd_tickets
where status = 3 or status = 4

Percent of tickets solved

SELECT 100 * solved/total
(SELECT count(case when status = 3 or status = 4 then 1 else null end)solved, 
        count(*) as total
from fd_tickets)tmp

Out of all basic KPIs, the most interesting is probably the number of one-touch tickets. This metric captures those case that your support team handled extremely effectively, providing the customer with the right solution or advice from the very first reply. This can have an ambiguous interpretation; Either your agents were extremely insightful or they faced a problem that is emerged very often. In the second case, it may be a good time to review your FAQ or documentation entries.

One touch tickets (1 reply and closed/resolved)

select replies.ticket_id, replies.number_of_replies, fd_tickets.status
from
(select ticket_id, count(case when source = 0 then 1 else null end) number_of_replies
from fd_ticket_conversations
group by ticket_id)replies
left join fd_tickets
on replies.ticket_id = fd_tickets.id
where replies.number_of_replies = 1 and (status = 3 or status =4)

 

Ticket Creation Time Distribution

Another very useful customer support metric is how the creation of new tickets is distributed in time. By monitoring this you can identify existing pattern and plan the distribution of your workforce more effectively by increasing the number of available agents during pick time.

Ticket Creation by day and time

SELECT date_part('hour', created_at) AS hours, to_char(created_at, 'day') AS day,
       count(*) as count
FROM fd_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, customers can interact with your business through multiple channels ranging from social media to email and phone.  So, it is important to track how tickets are distributed across the different communication channels that you maintain.

Tickets by submission method

SELECT source, count(*)
from fd_tickets
group by source