Data Analysis| Project 02: Call Center Data Analysis With SQL

Summary

This video script outlines a comprehensive data analysis process on a database table with 12 columns. It begins with visual inspection, noting issues like incorrect date formats and spaces in column names. The script guides through SQL commands to correct date formats, alter data types, and rename columns. It addresses nullifying blank values and checks for duplicates. The analysis includes calculating call percentages by reason, identifying peak call days, and assessing call durations. It concludes with customer sentiment analysis and service level assessments for call centers, providing actionable insights for improvement.

Takeaways

• 📊 The script involves working with a database table, focusing on data issues and their resolutions.
• 🗓️ There's an issue with the date format in the 'call timestamp' column, which needs to be corrected from 'month date year' to 'year month day'.
• 🚫 The 11th column has a name with spaces, which needs to be renamed for consistency.
• 🔄 The 'C Series score' is incorrectly identified as a 'capital deliber' data type and needs to be changed to 'integer'.
• 🔧 The 'current timestamp' is identified as a 'debt' type, which should be corrected.
• 🛠️ SQL safe updates are disabled for making changes to the table, a practice for safety during modifications.
• 🔄 The 'timestamp' column's date format is changed using the STR_TO_DATE function in SQL.
• 🔄 Data types are corrected for 'call timestamp' and 'C Series score' columns to ensure accuracy.
• 🔧 The 11th column, initially named with spaces, is renamed to 'CD in a minute' to avoid issues.
• ✅ Blank values in the 'C Series score' column are replaced with 'NULL' to maintain data integrity.
• 🔍 The script checks for duplicate values in the dataset and finds none, indicating data is ready for analysis.
• 📊 Various data analysis steps are performed, including calculating percentages of call reasons, identifying peak call days, and analyzing call durations.
• 📈 The analysis reveals that 'billing question' is the most common call reason with the highest percentage.
• 📉 Friday is identified as the day with the most calls, while Sunday has the least.
• 📊 The minimum, maximum, and average call durations are calculated, providing insights into call handling times.
• 🔎 Customer sentiment analysis shows a higher percentage of negative sentiments compared to positive.
• 📊 The final analysis examines call center performance by counting calls within and above service levels for different response times.

Q & A

What was the issue with the date format in the database?

The date format under the 'call timestamp' column was in the format of month-date-year, but it needed to be in the format of year-month-day.

How was the date format corrected in the database?

The date format was corrected by using the STR_TO_DATE function in SQL to convert the 'call timestamp' column to the desired format.

What was the problem with the 11th column in the database?

The name of the 11th column contained spaces, which needed to be renamed for consistency and proper SQL practices.

How were the data types issues in the 'C Series score' and 'call timestamp' columns addressed?

The data types were corrected by altering the table to change the 'C Series score' to an integer and the 'call timestamp' to a date type.

What was the issue with the values in the fourth column?

There were blanking values in the fourth column, which were replaced with NULL to accurately represent missing data.

How were duplicates checked in the database?

Duplicates were checked by counting the total number of rows and comparing it with the count of unique 'ID' and 'customer name' values.

What was the most common reason for calls according to the analysis?

The most common reason for calls was 'billing question', with a percentage of 71.2%.

Which day of the week had the most calls?

Friday had the most calls, while Sunday had the least.

What were the minimum, maximum, and average call durations?

The minimum call duration was 5 minutes, the maximum was 45 minutes, and the average call duration was 25 minutes.

What was the sentiment analysis result for the 'sentiment' column?

The sentiment analysis showed that negative sentiments were higher compared to positive sentiments, with positive sentiments being very low.

How were calls within or above the service level checked for each call center?

Calls within or above the service level were checked by grouping and counting the calls based on 'call center' and 'response time', then ordering the results by 'call center' and 'counts'.