📊 Customer Complaint Analysis

📕 Table of Contents

• Problem Statement
• Objective
• Tools Used
• Dataset Overview
• Key Findings
• Recommendations
• Project Presentation
• Project Learnings
• Installation and Usage
• Dashboard

❓ Problem Statement

One of the primary issues for a company is understanding and improving customer satisfaction with regard to the handling of consumer complaints. There is a need to identify factors contributing to dissatisfaction, delays in resolution, and the channels through which complaints are most effectively managed. This analysis aims to uncover these insights to enhance customer service and operational efficiency.

🎯 Objective

The objective of this project is to analyze consumer complaint data to identify trends and patterns that can inform strategies for improving customer satisfaction, reducing resolution times, ensuring timely responses, and minimizing disputes. This involves examining the performance of different companies, products, and complaint channels to pinpoint areas for improvement and best practices.

🛠️ Tools Used

• Analytical & Visual: Microsoft Excel
  
• Presentation: Microsoft Power Point
  

📅 Dataset Overview

• Data source: Internet
• Time period: 2013-2016
• Data size: dataset_consumer_complaints (14000, 18)
• Key columns: Company, Resolution time, Issue, Submitted via, product
• Calculated columns: count_of_issue, count_of_product, avg_issue_per_product, count_of_customer_dispute, count_of_timely_response, timely_response_percentage, overall_satisfaction, dispute_percentage, timely_response
• Data set Link

🔎 Key Findings

• Customer satisfaction rate: 77.17%
• Timely response rate: 98.40%
• Customer dispute rate: 22.47%
• Average resolution time: 2 days
• Average issues per product: 1261
• Top company based on satisfaction: 2233 Paradise Road LLC
• Bank of America: Highest number of complaints
• TransUnion: Highest average issues per product
• Web channel: Highest number of issue acknowledgments
• Mortgage products: Longest resolution time

💡 Recommendations

1. Bank of America should focus on reducing complaints and improving resolution quality, while Synchrony Financial should streamline its complaint resolution process.
2. U.S. Bancorp should analyze top-performing companies' practices and implement strategies to enhance their response time.
3. TransUnion should address the high number of issues per product through product improvements, testing, and enhanced customer support.
4. Companies should optimize their web channels, simplifying the complaint process and ensuring prompt responses to reduce disputes.
5. The postal mail channel requires process enhancements to improve timely responses.
6. Companies should allocate resources and training to handle mortgage-related complaints more efficiently.
7. Investigate the causes behind the decline in timely responses from 2015 to 2016 and address them accordingly.
8. Reinforce the factors that led to the decrease in complaints in 2016 and address the root causes of the rising complaints from 2013 to 2015.

📌 Project Presentation

Slides

The detailed presentation slides for this project can be found here

🧠 Project Learnings

1. Data Loading and Transformations.
2. Pivot table analysis.
3. Power Query and DAX.
4. Data visualization.
5. Conditional column.
6. Importance of data quality.
7. Data storytelling.
8. Sharpened analytical and problem-solving abilities.
9. Strengthened strategic planning and presentation skill.
10. Enhanced communication skills.

💻 Installation and Usage

• Microsoft Excel

📈 Dashboard