Call-center performance analysis
How to assess the performance of the call-center?
The aim of this project was to analyze and visualize call center performance data using SQLite and Power BI. The project focused on importing, cleaning, and analyzing the data in SQLite to gain insights into the performance of the call center. The analysis included examining various metrics such as customer satisfaction scores, response times, call durations, and the distribution of inquiries across different call centers, channels, and reasons. The findings were then visualized in Power BI to provide a comprehensive and visually appealing representation of the call center’s performance.
Data import and cleaning
The Real World Fake Data open source dataset served as the source of the information used in this project. The particular dataset used for the call center investigation has 32,941 rows and 12 columns.
I initially made a database that corresponded to the CSV file holding the data in order to import it into SQLite. I noticed after importing the data that the CSV file’s headers were incorrectly assigned, with the first row being regarded as the headings. To solve this problem, I took the following steps:
- Created a backup of the database to ensure data integrity.
- Executed the provided code to remove the first row from the table, effectively eliminating the incorrect headings.
- Renamed the columns of the table to provide more descriptive and meaningful names for better understanding and analysis.
These actions allowed me to prepare the data for future analysis and visualization by cleaning and organizing it. I further modified the data in addition to the earlier stages. I added the “Date” column and modified its values based on the “call_timestamp” column that already existed. The “Date” column now contains the values from the “call_timestamp” column that were changed to the proper date format. I deleted the original “call_timestamp” field from the table once the change was finished.I counted the rows in the “CallCenter” table to get a sense of the scale of the dataset and to better understand the data. To ensure proper calculations and analysis, I also changed the format of some columns from text to integer.
Data analysis
I then looked at the unique values in other columns. Four different call centers, five different sentiment categories (neutral, very positive, negative, very negative, positive), four different touch points (call center, chatbot, email, web), three different response time categories (within SLA, above SLA, below SLA), and three different contact reasons (billing, service outage, payments) were all identified.
I determined the count and percentage of each category within each column to better understand how these categories are distributed. For instance, I discovered that 33% of the calls came from the Baltimore, Maryland call center, and 33% of the data’s conveyed sentiments were negative.
I then added a new column to the table named “day” to identify the busiest day for calls. I utilized the SQLite strftime method to retrieve the day from the “Date” column. I discovered that Friday was the busiest day, followed by Thursday, based on the total number of calls for each day.
I also used the “call_duration” column to get the minimal, maximal, and average call duration. Along with calculating the average score, I also established the minimum and maximum customer satisfaction levels.
The response time was then broken down by call center and call center and channel. These characteristics were used to classify the data, and I then counted how many instances fell into each category.
Visualization
I used Power BI to make the results visually appealing. I used the ODBC driver to connect my SQLite database to Power BI and retrieve the data from there. I could move on to developing the dashboard because SQL has already carried out the essential aggregations.
Key metrics are presented as cards in the dashboard’s upper area (see the dashboard at the end of this section). The overall number of questions received in October, the typical customer satisfaction rating, and the typical service time are all included in these measures. Given the 10-point scale, it is clear that the customer satisfaction rating is in the middle of the pack.
Billing-related subjects are the most frequently asked about, followed by payments and service interruptions. The call center and chatbots are the two most popular touchpoints. The pie chart shows that more than 62% of questions are answered within the specified service level agreement, while about 24% take longer than anticipated.
The graph shows which contact centers are the busiest, with Baltimore, Maryland, and Los Angeles, California, showing the highest call volumes and Denver, Colorado, having the lowest activity. The busiest day for all call centers is Friday, followed by Thursday, while Sunday is the least busy day.
The heatmap also shows how sentiment is distributed among various call centers.
