Call Center Data Analysis
Tags: analytics, analyst, callcente, powerbi
Published: 2022-12-15 11:10:29

**Introduction**
I have been provided with data set of a call center for the month of October, 2020. I am tasked with finding the call trend over the month and generating insights from the given data set. Some questions that will be answered from the data will include;

- Total number of callers over the month.
- Number of callers through various channels.
- Customer sentiments.
- Reasons for customer calls.
- Total calls by response time.
- Call trend over the 31 days of the month.
- Average call duration.
- Average satisfaction score.

**Data Preparation**
The data came in a .csv format and formatted as shown in the diagram below;

![Raw Data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/go9g4dxavrzb1u983vym.JPG)

In order to make the data look more presentable, the column headers were made to standout. Also, to find the Call Trend for the month, I had to extract the the Days from the Call timestamp column as shown below;

![Call Day](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ecv3gabmwc31xn326yjz.JPG)

The diagrams below show the .csv file in Power Query and the modified data set respectively;

![Raw Data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ilaxyq3lm2eiqknwyahs.JPG)

![Modified Data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lf9zr5lcko3ap0eibnbp.JPG)

**Data Modelling**
So as to find the trend of calls for each day of the month, a Date table was created using the CALENDARAUTO function as shown below;

![Date table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/amqw1vy2ohtgnvdflqxo.JPG)

A relationship was then created between the  Date table and the Call center data.

![Data modelling](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vonp7py2d8qu0sdy72ku.JPG)

**ANALYSIS**
I had to first, find the Key Performance Indicators - Total number of callers over the month, Average satisfaction score, Average call duration - using DAX functions. The diagrams below show how I was able to find these KPIs using DAX and the Card visual of Power BI;

-**Total Calls**: The COUNT function was used to count the total calls, as opposed DISTINCTCOUNT that would count the distinct number of callers (i.e ignore if a person had called more than once)

![Total Calls - DAX](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j7cj2abp74slkd0b892q.JPG)

![Number of total calls](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e3jrhm4patltng7lwxl1.JPG)

-**Average Satisfaction Score**: The AVERAGE function was used to find the average.

![Average Satisfaction Score - DAX](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a1bv8criwb0nuti35iql.JPG)

![Average Satisfaction Score](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d4gnztynykqb31dyhx2b.JPG)

-**Average Call Duration**

![Avg. Call Duration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i4w25h7hwk1z35hz1scq.JPG) 

![Avg. Call Duration - Viz](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yvh953agzyfmsodcffxv.JPG)

-**Total calls by Channels**: this analysis shows the number of customers that contacted the Call center over the month through various communication channels.

![Total Calls by Channel](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ty37ucg9mfv31v46yb8a.JPG)

-**Total Calls by Sentiment**: this shows the number of callers by what they think about the services that were being rendered to them.

![Total callers by Sentiment](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2rab8ejrsrkkdq0w9ujr.JPG)

-**Percentage of Total Callers by Reasons**: this analysis shows the percentage of the total callers that contacted the call center based on their reasons for calling.

![Percentage of Total Callers by Reasons](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/grsrzbcywu8fy2k31gr9.JPG)

-**Total Calls by Response time**: 
![Total Calls by Response time](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y9upuk9v2q5tvhymh91b.JPG)

-**Total call trend by Day**: this chart shows the number of calls received each day of the month, from 1st to 31st of October.
![Total call trend by Day](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c5r0xuf51o2nij48cxx3.JPG)

**INSIGHT**
﻿At "10,639", Call-Center had the highest Total Calls and was 61.79% higher than Web, which had the lowest Total Calls at 6,576. Customers preferred to call the Call center, rather than using the Web or E-mail. This could be because they can get INSTANT response to their queries instead of the waiting time it would take to get a response via the E-mail. Call-Center accounted for 32.30% of Total Calls. The Response Time "Within SLA" had 20,625 Total Calls, "Above SLA" had 4,168, and "Below SLA" had 8,148. 71% of the Total Calls were from customers who called to make inquiries on "Billing". Over 17,000 callers were negative/very negative about the service they got over the month, possibly due to the fact that they were over-charged. It is highly recommended that the organization looks into the rate at which customers are being charged for services. 
Three (3) filters were also added to filter out the number of callers from;
- each call channels 
- each states 
- region 

![Filters](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pum3k7h024rud8grjylb.png)

**DASHBOARD**
![Call Center Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k1p9tddwzy63ij7neo1e.JPG)

Thank you for your time. This report can be found [here](https://drive.google.com/file/d/1cZ9bcWrfvn3pJEiDAFB0ngQ7A2OlZd1S/view?usp=sharing).
Images on the dashboard were gotten from [Flaticon](www.flaticon.com)
Dashboard background was designed with Microsoft PowerPoint and be downloaded [here](https://docs.google.com/presentation/d/1wU6eBdaVLZcTuSw_PsSNLE8WSQAIx8lz/edit?usp=sharing&ouid=107210796143614581784&rtpof=true&sd=true)
The Call Center dataset can be downloaded [here](https://drive.google.com/file/d/1GpFi_g1OaTc1OpdV4IgIks5dqhSLGNIA/view?usp=sharing)