ABC Call Volume Trend Analysis

This project involves analyzing an attached dataset of inbound calls from the insurance category, collected over a period of 23 days. The dataset includes information such as agent names, agent IDs, queue times (waiting duration before connecting to an agent), call times, time buckets, call durations, call seconds, and call statuses (abandon, answered, transferred). The objective of the project is to leverage the data to gain insights into the customer experience (CX) and provide valuable information to the organization. The CX team plays a crucial role in analyzing customer feedback and data, informing various aspects of the organization such as CX programs, digital CX, design and processes, internal communications, the voice of the customer (VoC), user experiences, CX management, journey mapping, nurturing customer interactions, customer success, customer support, handling customer data, and understanding the customer journey.

ABC Call Volume Trend Analysis(Excel project)

Project Description

The attached dataset is of Inbound calls of an ABC company from the insurance category consisting of a Customer Experience (CX) Inbound calling team for 23 days. Data includes Agent_Name, Agent_ID, Queue_Time [duration for which customers have to wait before they get connected to an agent], Time [time at which call was made by customer in a day], Time_Bucket [for easiness we have also provided you with the time bucket], Duration [duration for which a customer and executives are on call, Call_Seconds [for simplicity we have also converted those time into seconds], call status (Abandon, answered, transferred).

A customer experience (CX) team consists of professionals who analyze customer feedback and data, and share insights with the rest of the organization. Typically, these teams fulfil various roles and responsibilities such as Customer experience programs (CX programs), Digital customer experience, Design and processes, Internal communications, Voice of the customer (VoC), User experiences, Customer experience management, Journey mapping, Nurturing customer interactions, Customer Success, Customer support, Handling customer data, Learning about the customer journey.

Interactive Voice Response (IVR), Robotic Process Automation (RPA), Predictive Analytics, and Intelligent Routing are some of the most impactful AI-empowered customer experience tools we can use in this project.

In a Customer Experience team, there is a huge employment opportunity for Customer service representatives A.k.a. call center agents, and customer service agents. Some of their roles include Email support, Inbound support, Outbound support, and social media support.
Inbound customer support is defined as the call center which is responsible for handling inbound calls of customers. Inbound calls are the incoming voice calls of existing customers or prospective customers for our business which are attended by customer care representatives. Inbound customer service is the methodology of attracting, engaging, and delighting our customers to turn them into our business' loyal advocates. By solving our customers' problems and helping them achieve success using our product or service, we can delight our customers and turn them into a growth engine for our business.

For my Excel sheet -Pay 99 ₹ and fill the form.You will get my excel file link and support from my side.

Fill Form

Pay Now

Tech-Stack Used

Microsoft Excel 365:
It enables users to format, organize and calculate data in a spreadsheet. It organizes data in an easy-to-navigate way. We need not perform any complex mathematical functions. And it turns piles of data into helpful graphics and charts.
Microsoft Powerpoint 2021:It is used to make a report (PPT) to be presented to the leadership team.

Task

A. Calculate the average call time duration for all incoming calls received by agents (in each Time_Bucket).

ABC Call Volume Trend Analysis — Average of sum _sec analysis

Insights

In the given data analysis scenario, the Time_Bucket is measured in the Rows, the average of Call_Seconds is measured in the Values section, and the Call_Status is placed in the Filters section.
Throughout the data, the total average call time duration for calls answered by the agents is calculated to be 198.6 seconds.
Further analysis reveals that the average call time duration for incoming calls received by agents is highest between 10 am to 11 am and from 7 pm to 8 pm.
Conversely, the average call time duration for incoming calls received by agents is found to be the least between 12 noon to 1 pm.
These insights provide valuable information about the distribution of call durations throughout the day, helping to identify peak and off-peak periods in terms of call handling.

B. Show the total volume/ number of calls coming in via charts/ graphs [Number of calls v/s Time]. You can select time in a bucket form (i.e., 1-2, 2-3, …..)

Row Labels	Count of Customer_Phone_No	Count of Call_Seconds (s)
9_10	9588	8.13%
10_11	13313	11.28%
11_12	14626	12.40%
12_13	12652	10.72%
13_14	11561	9.80%
14_15	10561	8.95%
15_16	9159	7.76%
16_17	8788	7.45%
17_18	8534	7.23%
18_19	7238	6.13%
19_20	6463	5.48%
20_21	5505	4.67%
Grand Total	117988	100.00%

Insights

In the given data analysis scenario, we plotted the Time_Bucket in the rows and measured the count of Customer_Phone_No and count of Time in the Values section. We expressed the count of Time as a percentage of the column total.
Based on the analysis, it was observed that customers make the highest number of calls between 11 am to 12 noon. This time period sees the highest customer engagement and interaction.
On the other hand, the analysis also revealed that customers make the least number of calls between 8 pm to 9 pm. During this time, there is a decrease in customer call activity, possibly due to various factors such as dinner time or reduced availability of customer service.
These insights provide valuable information about customer call patterns throughout the day, highlighting peak and low activity periods. This information can be used to optimize staffing and resources to ensure efficient customer service and satisfaction.

C. As we can see current abandon rate is approximately 30%. Propose a manpower plan required during each time bucket [between 9am to 9pm] to reduce the abandon rate to 10%. (i.e., We must calculate minimum number of agents required in each time bucket so that at least 90 calls should be answered out of 100.)

Assumption

Total Working Hours by the company	9 Hrs
Break	1.5 Hrs
IT downtime	0.5 Hrs
Meetings	1 Hrs
Actual working hour by the agent	5 Hrs

Assumption

Here is a step-by-step guide with a table to help users understand the calculation and insights for reducing the abandon rate to 10%:

Step 1: Calculate the Total Agents Required

To determine the total number of agents required, we can use the formula:

Total agents = (Average calls / Time per person).

Given the following information:

Average calls on a single day: 187.96
Total time spent by one person in a single day: 5 hours

Using the formula, we find:

Total agents = 187.96 / 5 = 37.59

Therefore, the total number of agents required based on the average calls per day and agent working hours is approximately 38.

Step 2: Calculate Additional Agents Required

To achieve a 90% call connection rate (instead of the current 30% abandon rate), we need to calculate the number of additional agents needed. We can use the unitary method to find the approximate number of agents required.

Given the information that 100 calls result in 30% abandoned calls and we want to reduce the abandon rate to 10%, we can calculate:

Additional agents required = (Abandon rate difference / Abandon rate) * Total agents

Abandon rate difference = 30% - 10% = 20%

Using the formula, we find:

Additional agents required = (20% / 30%) * 38 ≈ 25.33

Therefore, approximately 26 additional agents are required to achieve a 10% abandon rate.

Step 3: Manpower Plan for Each Time Bucket

Now, let's create a table to show the manpower plan required during each time bucket (between 9am to 9pm) to reduce the abandon rate to 10%. We'll calculate the minimum number of agents required in each time bucket so that at least 90 calls should be answered out of 100.

Time Bucket	Count of Call Sec	Count of Call Sec	Manpower Required
9_10	8.13%	0.085	10 (approx.)
10_11	11.28%	0.116	12 (approx.)
11_12	12.40%	0.127	13 (approx.)
12_13	10.72%	0.116	12 (approx.)
13_14	9.80%	0.106	11 (approx.)
14_15	8.95%	0.095	10 (approx.)
15_16	7.76%	0.084	9 (approx.)
16_17	7.45%	0.074	8 (approx.)
17_18	7.23%	0.074	8 (approx.)
18_19	6.13%	0.063	7 (approx.)
19_20	5.48%	0.053	6 (approx.)
20_21	4.67%	0.053	6 (approx.)
Total	56

ABC Call volume Analysis

Insights

To determine the total number of agents required, we can use the formula:
Total agents = (Average calls / Time per person).
Given the following information:
Average calls on a single day: 187.96
Total time spent by one person in a single day: 5 hours
Using the formula, we find:
Total agents = 187.96 / 5 = 37.59
To achieve a 90% call connection rate (instead of the current 60%), we calculate the number of additional agents needed. Applying the unitary method, we find that approximately 56 agents would be required.
Therefore, the total number of agents needed to achieve a 90% call connection rate is approximately 56.

How we calculated this

We began by creating a pivot table where we placed Date & Time in the Rows section and Call Status in the Columns section. We then calculated the average of abandoned, answered, and transferred calls using the average Excel formula.
The analysis revealed that 29% of the calls were abandoned, 1% were transferred, and 70% were answered during the daytime.
To ensure that 90% of the calls are answered each day, a total of 56 agents are required.
The minimum number of agents required for each time bucket can be calculated by multiplying 56 by the count of time, which was calculated in a previous question.
This information provides insights into call handling efficiency, the distribution of call statuses, and the required staffing levels for effective customer service.

D. Let’s say customers also call this ABC insurance company at night but didn’t get an answer as there are no agents to answer, this creates a bad customer experience for this Insurance company. Suppose for every 100 calls that a customer made from 9 Am to 9 Pm, a customer also made 30 calls in the night between intervals [9 Pm to 9 Am] and the distribution of those 30 calls is as follows:

The total number of agents required is calculated by

Average call daily (9am - 9pm)	5130
For night (9pm - 9am)	1539
Additional Hours required	76.41135
Additional agents	15

Night call (9pm - 9am)	Call Distribution	Time Distribution	Agent required
9pm - 10pm	3	0.10	2
10pm - 11pm	3	0.10	2
11pm - 12pm	2	0.07	1
12pm - 1am	2	0.07	1
1am - 2am	1	0.03	1
2am - 3am	1	0.03	1
3am - 4am	1	0.03	1
4am - 5am	1	0.03	1
5am - 6am	3	0.10	2
6am - 7am	4	0.13	2
7am - 8am	4	0.13	2
8am - 9am	5	0.17	3
Total	30	1	19

ABC Call volume analysis

We first calculated the Time Distribution by dividing each call's distribution by total calls i.e., 30.
The number of agents required for each time bucket is calculated by 15 * Time Distribution
15 is calculated above by dividing the additional hours required to answer the night calls by 5
(actual working hours of agents).
Also, while calculating, the round figure is taken into consideration as there cannot be 1.5 men working.

Insights

Based on the analysis, it was observed that customer calls are least frequent in the evening. This presents an opportunity for the company to optimize its workforce by reducing the number of agents during that time for call handling.
To cater to the night shift, the company can consider hiring 15 dedicated customer support agents who are available during the night hours.
Another option is to shift some day workers to the night shift, ensuring continuous coverage and efficient call handling throughout the day.
By adjusting the shift timings of the employees, such as having some workers from 5 am to 2 pm and others from 2 pm to 11 pm, the company can maximize the number of calls answered during peak hours.
To ensure round-the-clock availability, the company can divide its workforce into three shifts, enabling agents to be available 24/7 to address customer queries and concerns.
It is important to note that during the analysis, outliers were identified in the data. Removing these outliers could potentially lead to different outcomes and answers, as they may have influenced the results.
These insights provide the company with actionable strategies for optimizing workforce allocation, enhancing customer service efficiency, and ensuring continuous availability to address customer needs.

Results of ABC Call Volume Trend Analysis (excel project)

Throughout this project, I have gained valuable insights into the impact of an analyst in the customer service department. It is evident that a company strives to ensure maximum customer satisfaction through effective customer-handling strategies.
One of the notable tools used is the Interactive Voice Response (IVR) system, which employs AI technology to address customer queries by identifying their specific concerns and routing the calls to the appropriate agents for resolution.
The analysis of the provided data was made easier by the pre-calculated time buckets and call duration converted into seconds, saving time and effort in calculations.
Additionally, I have delved into the realm of behavioral analytics, which involves studying customer behavior patterns to identify trends, preferences, and opportunities for enhancing the overall customer experience.
Overall, this project has provided me with valuable knowledge and insights into the dynamics of customer service and the role of an analyst in optimizing customer satisfaction.