The customer service infographic excel and spreadsheet dashboard will show how to track activities and show them using an easily readable dashboard.
Customer service is the act of taking care of the customer’s needs by delivering professional service. Previously we have touched this subject, and today we will show how to create a customer service dashboard using interactive drop-down lists, conditional formatting, and spark lines. Our secondary goal is to process a large amount of data with maximal efficiency.
Before we take a deep dive, it’s worth to create an initial layout. Check the draft below, and using this, we can build up the dashboard easily. If we need to build a dashboard, always draw a little preview sketch or use widgets, if we leave this step out, we quickly get lost on the sea of data.
Let’s see the initial conditions. In the example, the call center will provide six different products. These are Sosa Teletech, Globocorp, Cryptomantics, Kilosys, Stormy Design, Orange Corp. We have given these funny names for the marketed products. In reality, these, of course, don’t exist. PrimeFlux, High Falcon, DriftPulse, Bitter Thunder, Hollow Cobra, and FrostWard. The calls will be in 6 regions for simplicity. These will be Region 1-6.
Using Excel Tables in this Customer Service Infographic Excel Template
As first, we’ll create an Excel table that contains the data for the dashboard. See the column header area. Let’s see the initial data set:
From left to right can be seen the ID number and time of the call, name of the product, name of the region, the name of the client (company), duration of the call, a YES / NO flag that determines the success of the call, customer satisfaction index and name of the employee.
Key Performance Indicators (KPIs) in Customer Service Dashboard
We can analyze the following information on the dashboard header from left to right.
The first three rows on the top of the customer service dashboard have a double function. The data of the first row sums up the main indicators during the examined period. The values of the second and third rows are dynamic. Their value depends on which two indicators are highlighted in the given category (product, agent, etc.). We can see two agents, two products or two regions compared to performance. We can compare these indicators with the average values found in the first row. In the analysis, we will process information accumulated in almost one month. The 8170 calls can be seen in the second column, represents all the calls.
The trend is a mini chart was made with the help of spark lines that displays the whole time interval. Call duration(s) is a perfect index for examining the length of the calls, the definition of the resolved calls (%) is simple, contains the successful calls.
Customer satisfaction (%) is a perfect KPI in this field. With its help, we can measure customer satisfaction on a scale from 1 to 5.
Key Performance Indicator (KPI) Configuration for Customer Service Infographic
The KPIs can be configured freely using conditional formatting rules. Here we have used deliberately strict settings, for example regarding a successful call to be accepted the indicator must be over 95 %.
In this case the green circle shows that everything is OK. The general dashboard rules are valid here also, yellow is medium, red represents a bad value.
And finally let’s see the second part of the customer service dashboard that does the comparing analysis.
In the first part of the article we have said that here we can do, for example, comparisons product by product. On the figure below you can see an example of this.
The subject of the analysis is the ‘Driftpulse’ and ‘PrimeFlux’ products. With conditional formatting (and with some VBA programming) we have accomplished that the currently highlighted products separate from each other with the usage of the right colors.
We set the colors of the line charts to this.
What do we see on the picture ?
We analyze about the two chosen products the durations of calls in a given time period. As we can plainly see we displayed the achievements in daily break down and it is obvious that the longest call was 2,5 minutes in case of the ‘PrimeFlux’ product.
From the left side menu we can choose whatever we would like (Products, Region, Agent, Client Category), and this is also true about the right side menu (Call Duration, Calls, Resolved Calls), so we can be done with 12 kind of quick analysis only in seconds.
Final thoughts – Conclusion
We hope you find this guide very useful. And we don’t have to stress that by modifying data and categories you can reconstruct the dashboard to your liking. It has everything that a good report needs: data-visualization, key performance indicators, charts and drop-down-lists. We recommend to check our gauge visualizations too!
