☎️Call Center Operational Performance & CSAT  Optimization 

 1. Executive Summary

This project aims to evaluate call center operational performance during Quarter 1 to measure agent efficiency and Customer Satisfaction (CSAT). Using Excel, raw data containing 5,000 call logs was cleaned, standardized, and transformed—including converting talk duration into seconds for accurate metric aggregation. The analysis reveals that the Contract-related topic yielded the lowest CSAT score, and agent traffic volume directly impacted overall customer satisfaction. As a solution, I recommend redesigning the workflow for Contract-related issues alongside comprehensive training for all agents, as well as balancing the workload by reallocating a portion of the call queue from Jim to Steward. 

2. Business Problem

• Background: Management wants to evaluate agent performance during Quarter 1 to identify challenging topics that require additional training, as well as determine which agents qualify for rewards and which ones need further coaching.

• Business Impact: Slow First Response Time (FRT) and excessively long Average Handling Time (AHT) can lead to customer frustration, lower CSAT scores, and increased operational costs due to inefficient agent utilization.

• Primary Analytical Questions:

1. Which call topic generates the highest volume, and how does it impact Customer Satisfaction (CSAT)?

2. What is the correlation between agent workload (Total Calls & FRT) and individual CSAT performance?

3. Who are the top-performing and lowest-performing agents?

 3. Tech Stack & Data Preparation

• Tools: Microsoft Excel (Advanced Data Cleaning, Pivot Tables & Interactive Dashboard) .

• Excel Features & Methodology:

  • Data Cleaning & Standardization: Transforming short text indicators (Y/N) into standardized categories (Yes/No) and refining column header naming.

  • Feature Engineering: Converting talk duration format (AvgTalkDuration) into a numerical second value (Talk duration in seconds) using the IF function to enable accurate mathematical calculations.

  • Data Aggregation: Creating Pivot Tables to calculate key KPIs (Total Calls, Average CSAT, Average FRT, and Average Handling Time).

  • Data Visualization: Designing an interactive Executive Dashboard equipped with KPI Cards, Combo Charts (combined bar and line charts), and Slicers (Date, Topic, Agent, Answered, Resolved).

 4. Key Insights

• Overall Operational Performance: The call center recorded a total of 5,000 calls with an average satisfaction rating (CSAT) of 3.40 / 5.00, an average First Response Time (FRT) of 68 seconds, and an average Handling Time (AHT) of 225 seconds.

Topic Analysis:

• Streaming and Technical Support topics received the highest call volumes (each exceeding 1,010+ calls).

• Contract-related issues recorded the lowest average CSAT score (~3.38), despite having a lower call volume compared to other topics.

Agent Workload vs. Service Quality:

• Jim handled the highest number of calls (660 calls), but his CSAT score was 3.39. Conversely, Martha handled fewer calls (638 calls) while maintaining the highest CSAT score (3.47), followed by Dan (633 calls) with a CSAT score of 3.45. This indicates that keeping call loads below 640 calls represents a safe threshold for maintaining service quality.

• Joe recorded the longest average FRT (71 seconds) alongside the lowest CSAT score among all agents (3.33). On the other hand, Becky had the fastest average FRT (65 seconds) but had the second-lowest CSAT score (3.37). This proves that response speed alone does not guarantee customer satisfaction.

Top & Bottom Agent Identification:

• Top Performing Agent: Martha, achieving the highest CSAT score (3.47) despite handling the second-highest call volume (638 calls).

• Lowest Performing Agent: Joe, who had the longest average FRT (71 seconds) and the lowest CSAT score (3.33), despite handling the second-lowest call volume among all agents (593 calls).

5. Business Recommendations

Based on the dashboard insights, the following strategic recommendations are proposed to the Call Center Operations Team:

1. Workflow Redesign & Comprehensive Training for Contract Issues: Conduct a root-cause analysis on the Contract-related category. Since this topic has the lowest CSAT score, it is recommended to redesign the handling workflow and provide comprehensive training to all agents on contract scripts and case resolution to improve customer satisfaction.

2. Workload & Shift Balancing: Redistribute incoming call queues to prevent agent burnout. High-traffic agents like Jim should receive queue adjustments or additional support during peak hours by shifting a portion of his call queue to Steward to preserve service quality and prevent CSAT drops.

3. Rewards & Targeted Agent Training:

   • Rewards: Recognize and reward Martha to boost team morale and motivate other agents.

   • Targeted Training: Provide specialized training for Joe to improve his communication skills and response speed (FRT).