How to create a customer KPI dashboard in Excel to tracking performance?
In this article I will show you how to create in Excel a simple customer kpi dashboard for monitoring and reporting the actual status of customer center key performance indicators. The Main Objective building a one-page excel kpi dashboard for tracking the actual status of key performance indicators. In the situation of tracking customer performance, a KPI is necessary.
Here are the main KPIs for customer services performance:
Using these key performance indicators we can track and trace the overall service performance.
Time to answer: this performance dimension usually expressed in second this is the time from when a call is started until it is responded by a customer service agent. Using the average call time to answer telephone call is the base of calculation. Abandon Rate of incoming calls: we will handle this key performance indicator as a % of the number of callers who are cut off, before they touch an agent who answers their call. We can measure this indicator the following method: [numbers of abandoned calls while the customer waiting] / [total incoming phone calls]
First Call Resolution (FCR)
We can use this formula to calculate FCR value = [issue solved by first call] / [total issues] this is a very important dimension (my opinion it is hard to tracking), expressed as a percentage, the number of those calls and that they will be responded to at the first try during the call.
In this case do not require the customer to call back.
The Data Visualizations in Excel – Overview the fundamentals of customer kpi dashboard
Check the actual status of
• Time to answer for the selected week
• Abandon rate for the selected week
• First call resolution for the selected week
• The period (average of the branch) from the actual week to actual week + 3 months
How to Implement the results into our customer kpi dashboard in Excel?
I am divided this excel model into three separate worksheets for the excel dashboard, the main dashboard sheet, the input data and the calculation area. It is one of the easiest ways to build a clear and structured model. The implementation of the raw data and calculated value is very easy:
The main worksheet with the all figures is linked directly to the data table.
We will use spin button to display the results week by week.
When defining a key performance indicator (KPI) it is appropriate to notice the characteristics of the company. These indicators are perfect in areas of sales, marketing and IT. KPIs are based on business goals.
A business goal is such a high level objective, which is measurable and expressible in quantities that can be translated to a KPI with which a company can measure the aspects of a process to a predefined target.
In the next article we discuss the main indicators and the level of success can be determined by comparing the actual results.
Tracking Customer KPI Dashboard
With fixed duration the calculated KPI is based on a single duration. You must specify the start and end dates – for example from 1 January 2015. to 31 January. If you specify only a start date the KPI will be calculated from the specified date to the current date.
If you specify only the end date the KPI will be calculated from the introduction of the process to the specified end date.
The clear indicators of the company’s performance are the changes in the sales volumes. Management information systems provide to these changes’ availability in time and in proper structure.
When the exploration of relationships between sales data is typically a company’s inside task, the customer kpi dashboards mostly are capable to evaluate the performance only retrospectively.
The evaluation has two key objectives: evaluate, classify, rank and develop, change. In addition it can serve as a basis for promotion, pay raise, relocation or prehaps dismiss.