This project analyzes inbound call center data to understand call volume patterns, average call duration, and workforce requirements. Using Microsoft Excel, the analysis supports manpower planning to improve customer service and reduce call abandonment rates.
This project focuses on Customer Experience (CX) analytics for the inbound calling operations of ABC Insurance Company. Using 23 days of call center data, the analysis examines call volumes, average call duration, queue times, and call status to identify customer demand patterns and optimize workforce planning.
The project uses Microsoft Excel to perform data analysis, visualize call trends, and develop manpower plans that help reduce the call abandon rate while improving overall customer experience.
ABC Insurance aims to improve customer experience by reducing abandoned calls and ensuring adequate agent availability throughout the day and night. The objective of this project is to analyze call center operations, identify peak call periods, and propose an effective manpower plan that maintains the maximum abandon rate at 10%.
The dataset was prepared for analysis through the following preprocessing activities:
- Reviewed and validated call records.
- Standardized time buckets for hourly analysis.
- Verified call duration and queue time values.
- Categorized calls based on call status.
- Organized the dataset for workforce planning and dashboard creation.
The analysis was performed on 23 days of inbound call center data, including agent details, queue time, call timing, call duration, and call status.
- Calculated the average call duration for each hourly time bucket.
- Identified variations in handling time throughout the day.
- Analyzed call volume across different time buckets.
- Created charts to identify peak and non-peak calling hours.
- Estimated the minimum number of agents required for each time bucket.
- Developed a staffing plan to reduce the abandon rate from approximately 30% to 10%.
- Estimated expected night call volumes based on business assumptions.
- Proposed manpower requirements for each night time bucket while maintaining the target abandon rate.
The manpower calculations considered:
- Agent working days per week.
- Monthly leave assumptions.
- Productive working hours.
- Average call handling time.
- Target service level and abandon rate.
- Microsoft Excel (2019)
- Pivot Tables & Pivot Charts
- Data Cleaning & Transformation
- Workforce Planning
- Call Volume Analysis
- Statistical Calculations
- Interactive Dashboard
- Data Visualization
- Call volumes were highest during specific daytime hours, indicating peak customer demand.
- Average call duration remained relatively consistent across most time buckets.
- Higher abandon rates were observed during peak hours due to insufficient staffing.
- Increasing agent allocation during high-volume periods can significantly reduce abandoned calls.
- Night call analysis demonstrated the need for dedicated staffing to improve customer experience outside regular business hours.
- Workforce planning based on historical call trends enables more efficient resource allocation and service quality.
This project demonstrates how Customer Experience (CX) analytics can be used to optimize call center operations through data-driven workforce planning. By analyzing call volume trends, average handling time, and staffing requirements, the project proposes practical manpower strategies that improve service levels and reduce call abandonment. Additionally, the project strengthened my skills in Microsoft Excel, operational analytics, workforce planning, and business-focused decision-making.