Call Center Data Analysis Using Microsoft Excel
How to use Call Center Data to Improve Customer Satisfaction
Introduction
In today’s data-driven world, businesses across various industries rely heavily on data analysis to make informed decisions and improve their operations. One area where data analysis plays a crucial role is in call centers, which serve as a vital touchpoint for customer interactions. In this beginner-friendly portfolio project, we’ll explore how to analyze call center data using Microsoft Excel.
I will discuss some of the key metrics that you should track when analyzing your call center data.
Key metrics to track:
- Call volume: This metric tells you how many calls your call center receives each day, week, or month. It is important to track call volume so that you can make sure that you have enough agents to handle the workload.
- Average handle time: This metric tells you how long it takes your agents to handle each call. A long average handle time can indicate that your agents are not efficient, or that they are spending too much time on each call.
- Customer satisfaction: This metric tells you how satisfied your customers are with your call center service. It is important to track customer satisfaction so that you can identify areas where you can improve.
- SLA Compliance: Determine the percentage of interactions that fall within, below, or above the defined Service Level Agreement (SLA) response time.
- Channel Distribution: Examine the distribution of inquiries across different communication channels to identify popular channels.
- Reason Analysis: Explore the most common reasons for customer inquiries, helping prioritize areas for improvement.
- Sentiment Analysis: Analyze customer sentiment to gauge overall satisfaction and identify trends in positive/negative feedback.
- Geographic Insights: Evaluate call distribution across cities and states to identify potential regional issues or opportunities.
Steps involved: Data Gathering, Data Cleaning, Data Manipulation, Data Analysis, Data Visualization
Data Gathering:
Data Source: The data obtained for this project is from data.world, it is a Real World Fake Data for practice purpose and is publicly available.
The Data can be found here!
Meta data: This is a daily data for the month of October 2020 which contains 32942 rows and 12 columns, and the file extension is a .csv file.
Data Cleaning:
Loaded the downloaded dataset into Microsoft Excel, duplicated it onto another sheet, which is named “working_sheet” to preserve the integrity of the original data and prevent any irreversible changes.
Cleaning and preparing the data for analysis. This means removing any errors or outliers, and formatting the data in a way that makes it easy to analyze.
The column ‘call_timestamp’ had mixed formatting both text and date.
To format, select the entire column E, Go to Data ribbon > Text to Columns > choose ‘delimited’ > click Next > click Next> under ‘column data format’ select ‘date format’ and change it to ‘MDY’ from the drop-down list > click Finish.
I checked the remaining columns and they didn’t require any data cleaning.
Data Manipulation:
To perform a daily trend analysis a new column has been inserted with the help of shortcut keys ‘CTRL Shift +’ and named the new column as “day”. To extract the day, I used the DAY function. The formula used is =DAY([@[call_timestamp]]).
Data Analysis:
Using Pivot tables and Basic Statistics, we calculated metrics like
Primary KPIs:
Secondary KPIs:
Here are a few more pivot tables through which I created visuals for the Dashboard.
Data Visualization:
Once I have collected the data, I used them to visualize in a way that is easy to understand. This will help to identify trends and patterns in the data. Generated bar charts, Pie chart, Line chart and Map to compare different KPIs and line graphs to display call trends over time.
To make the Dashboard interactive I have used Slicers for Channel, Center and Days
link to interactive dashboard.
Drawing Insights:
The most important part of call center data analysis is drawing insights from the data. This means identifying the areas where the call center is performing well and the areas where it can improve.
- The most common reason for customer calls is the Billing Question.
- On an average it usually takes 25 minutes or more than 25 minutes to resolve customer issues.
- The customer satisfaction score for the call center is 6.
- Sentiment Analysis indicates a negative sentiment pointing to areas of concern or dissatisfaction.
Recommendations
- Focus on improving customer satisfaction. This is the most important goal of any call center. By analyzing customer feedback, you can identify areas where your call center is falling short and make improvements. For example, you may find that customers are often waiting too long on hold, or that they’re not getting the information they need from agents.
- Invest in better training for agents. This could involve providing agents with more training on products and services, as well as training on how to handle difficult customer interactions.
- Use a call center software that provides real-time data analytics. This will help you to identify problems as they happen and make changes to improve call center performance.
- Create a culture of customer satisfaction. This means making sure that all employees are focused on providing excellent customer service.
- Increase agent productivity. This can be done by reducing the amount of time agents spend on each call, or by increasing the number of calls that each agent can handle per day. You can also improve agent productivity by providing them with the right tools and training.
Key Takeaways:
By completing this project, I not only gained practical data analysis experience but also developed a portfolio piece that demonstrates my abilities to potential employers. With the power of Microsoft Excel and a willingness to learn.
Happy Analyzing!
