ABC Call Volume Trend Analysis��

• By:- Pradnyashil A. Lonare

Project Work For Data Analytics

• Project Description:-

• For you final project we are providing you with a dataset of a Customer Experience (CX) Inbound calling team for 23 days. Data includes Agent_Name, Agent_ID, Queue_Time [duration for which customer 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.
• Let’s look at some of the most impactful AI-empowered customer experience tools you can use today:
• Interactive Voice Response (IVR), Robotic Process Automation (RPA), Predictive Analytics, Intelligent Routing���

• In a Customer Experience team there is a huge employment opportunities for Customer service representatives A.k.a. call center agents, customer service agents. Some of the roles for them include: Email support, Inbound support, Outbound support, 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 the existing customers or prospective customers for your business which are attended by customer care representatives. Inbound customer service is the methodology of attracting, engaging, and delighting your customers to turn them into your business' loyal advocates. By solving your customers' problems and helping them achieve success using your product or service, you can delight your customers and turn them into a growth engine for your business.�

​

• Business Understanding:-

• Advertising is a way of marketing your business in order to increase sales or make your audience aware of your products or services. Until a customer deals with you directly and actually buys your products or services, your advertising may help to form their first impressions of your business. Target audience for businesses could be local, regional, national or international or a mixture. So they use different ways for advertisement. Some of the types of advertisement are: Internet/online directories, Trade and technical press, Radio, Cinema, Outdoor advertising, National papers, magazines and TV. Advertising business is very competitive as a lot of players bid a lot of money in a single segment of business to target the same audience. Here comes the analytical skills of the company to target those audiences from those types of media platforms where they convert them to their customers at a low cost. �

• Approach:-

• Get the dataset from the given description.
• Open dataset in Excel.
• Try to understand the problem and given questions.
• Approach to familiar with the attributes and validate given data.
• Create table format and use count if, min, max, avg, sum and other statistical formulas.
• Analyzed data by using pivot table and pivot chart.
• Solve the given questions step by step and create interactive graphs.
• Create excel file in csv format and create report for presentation.
• Finally, I attached to my answers to the file.

​

• Tech-Stack Used:-

• Insights and Results:-

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

• All Calls:-

�

• Received Calls:-

• Insights:-

1. Time_bucket is measured in the rows and average of Call_seconds is measured in the values section and we put call_status in the filter section.
2. The total average of call time duration of all calls (Abandon, Answered, Transfer) is 139.53 Seconds.
3. The total average of call time duration of all calls which are answered by agents is 198.62 Seconds.
4. The average call time duration for all incoming calls answered by agents is the highest in between 10 am to 11 am and from 7 pm to 8 pm.
5. The average call time duration for all incoming calls received by agents is the least in between 12 noon to 1 pm.

​

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, …..)��

• Number of Calls vs Time

• Insights:-

1. We plotted Time_Bucket in the Rows and Count of customer_phone_no and Count of Call_Seconds In the values section.
2. The Customer call the most in between 11 am to 12 noon.
3. The customer call the least between 8 pm to 9 pm.

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.) ��

• Percentage and Average of Call Status:-

• Insights:-

• Total agents working can be calculated by average calls on a single day divided by total time spend by one man in a single day.

Total agents = 187.96/5 = 37.59

• If agents are working for 5 hrs a day and 60% calls are getting answered. If we want 90% of the calls to get connected, we apply unitary method to find how many more employee we want.

Total agents = 90*37.59/60 = 56.3 ~ 56 agents.

• First We created pivote table. Date and time is dragged down to the rows, call status to the columns, while taking count call duration in the values section.
• Then we calculated the average of abandon, answered and transfer by using the average Excel formula.
• 29% of the calls are abandoned, 1% is transferred, while 70% of the calls are answered in the daytime.
• Total agents required to answer the 90% of the calls per day is 56.
• The minimum number of agents required for each time bucket is calculated by 56*count of time.

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:�

• We first calculate the time distribution by dividing each calls 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:-

• The customers call the least in the evening. So the company can reduce the number of agents at that time for answering the calls.
• The company can hire 15 customer support agents for the night shift work.
• The company can shift some of the day workers for the night shift.
• The employees who are working 9am to 9pm. The manager can change some of the workers shift from 5am to 2pm and some workers from 2pm to 11pm to get the most calls answered.
• The company can make the employers divide into 3 parts too, so that the agents are always available 24/7.
• We found there were few outliers in the data. And if we have removed that outliers then the answered would have been different.

• Results:-

1. I learned how an analyst can make an impact in customer service department.
2. I learned how a company deals with the customers to give them the most satisfaction.
3. I got to know about the IVR duration, which is an AI tool, who answer the calls to get to know the customer exact question and then transfer it to the right agent to get the customer’s queries get answered.
4. This project was easy to get the answers as the data provided by the team have already calculated and converted the calls duration into seconds, so we do not have to spend time on it to calculate.
5. I learned about the behavioral analytics.

Thank You