ABC CALL VOLUME TREND ANALYSIS
Vallabh Ashtekar
CONTENTS
ABC Call volume Trend
2
PROJECT DESCRIPTION
A customer experience team consists of professionals who analyze customer feedback and data, and share insights with the rest of the organization.
This project aims to increase the rate of solving customer problems by analyzing the data set.
We have to analyze the dataset to get the meaningful insights from the raw data.
APPROACH
1] Download the dataset.
2] Understand the data set.
3] Go through the tasks mentioned.
TECH STACK USED :
INSIGHTS :
Calculate the average call time duration for all incoming calls received by agents (in each Time Bucket).
Crypto: investing & trading
6
INSIGHTS :
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, …..)
Crypto: investing & trading
7
INSIGHTS :
As you 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. You have to calculate minimum number of agents required in each time bucket so that at least 90 calls should be answered out of 100.)
Crypto: investing & trading
8
For 1st of Jan I have taken the total sum of call duration in sec is 676664
By converting it into hrs I got 187.96
We can say that total duration of calls on each day is approximately 187.96 hrs.
From the assumptions we can say that an agent works for 5 hrs in a day.
So, the number of agents required for working 187,96 hrs is :- 187.96 / 5 = 37.59
Total 60% of calls are answered out of 100% we have to make it 90% of the total calls should be answered.
So, we have to reduce the abandon rate to 10%, for that :- 90*37.59/60 = 56.39
We need total 56 agents in order to reduce the abandon rate upto 10%.
Crypto: investing & trading
9
Total 56 agents are required to increase the answered call percentage.
The distribution of the agents to each time bucket according to the total calls duration in each bucket.
INSIGHTS
Let’s say customers also call this ABC insurance company in night but didn’t get answer as there are no agents to answer, this creates a bad customer experience for this Insurance company. Suppose every 100 calls that customer made during 9 Am to 9 Pm, customer also made 30 calls in night between interval [9 Pm to 9 Am] and distribution of those 30 calls are as follows:
Crypto: investing & trading
10
Crypto: investing & trading
11
This are the number of calls abandon, answered, transferred on each day.
Average of total abandon calls is 1496.
Average of total answered calls is 3585.
Average of total transferred calls is 49.
Average of total calls is 5130.
Crypto: investing & trading
12
Average calls daily(9am-apm) are 5130.
30% of daily average calls are night calls(9pm-9am) are :- 0.3*5130 = 1539
As we want 90% of the night calls to be answered we have to multiply avg. of daily night calls and avg. of duration and 0.9.
Also to convert it into the hrs divide it by 3600
So, the additional hours required :- 1539*198.6*0.9/3600 = 76.41
From the assumption we can clearly say that one agent works for 5 hrs.
So, additional agents required:- 76.41/5 =15
Crypto: investing & trading
13
We need 15 additional agents to reduce the abandon rate of the night calls to the 10%.
The distribution of the agents to each shift according to the total calls distribution in each shift.
By adding this much agents to each shift we can reduce the abandon rate of night calls to 10%.
RESULTS
Call duration is high at morning time and night time.
Number of calls increases from 9-12 and the gradually decreases.
To reduce the abandon rate to 10% we need 56 agents more in a company.
To reduce the abandon rate of night calls to 10% we need 15-19 agents more in a company.
THANK YOU
Vallabh Ashtekar
Drive link for Excel sheets :