This repository contains an in-depth analysis of a car repair service company, with a special focus on supplier performance, customer satisfaction, and overall sales growth. The analysis is driven by data visualizations and insights generated through Power BI dashboards.
The dataset belongs to a company in the automotive maintenance industry. The business model involves collecting vehicles from customers, outsourcing the repairs to third-party suppliers, and delivering the repaired vehicles back. The company operates in multiple regions and has been active for over six years. This analysis aims to uncover insights to optimize business performance, focusing on the following areas:
- Sales Growth: Analyze yearly and monthly sales patterns to identify trends.
- Supplier Comparison: Compare the performance of suppliers based on revenue, customer satisfaction, and job delivery times.
- Product and Service Analysis: Analyze which car models and job types drive the most revenue and customer satisfaction.
This data analysis provides insights into the car repair company’s performance across multiple metrics such as sales growth, supplier efficiency, and customer satisfaction.
- Supplier Performance: Supplier SP004 generates the highest revenue, even though he does not maintains the fastest job completion time, but has above average customer satisfaction. Supplier SP009 Has the highest average customer rating and slightly better job completion time but generated less revenue due to the number of jobs and amount per jobs.
- Sales Trends: Significant increases in sales were observed in August and September, coinciding with seasonal factors such as the rainy season.
- Product and Service Trends: The Toyota Camry was the most serviced car model, and specific job types were linked to higher customer satisfaction.
- Optimize Supplier Performance: Prioritize assigning jobs to Supplier SP009, who performs the best in terms of job completion time and customer satisfaction.
- Targeted Marketing: Capitalize on the increased demand during the rainy season (August and September) with targeted campaigns.
- Improve Service for Lower-Rated Suppliers: Address areas of improvement for suppliers with lower performance, such as Supplier SP004 & SP005.
The data structure consists of the following tables:
- Customers: Contains customer demographics and details.
- Cars: Includes car model information.
- Jobs: Lists job types and repair details.
- Suppliers: Supplier details such as job completion time, revenue per job, and ratings.
- Sales: Revenue, job completion times, and other sales metrics.
Objective: Analyze overall sales growth over the years and identify key months of high sales activity.
Key Insight:
- Sales tend to increase in August and September, coinciding with the rainy season, which typically leads to more vehicle repairs.
Objective: Analyze sales and job distribution across different regions.
Key Insight:
- The Omdurman region has the highest demand for car repair services, followed by Khartoum.
Objective: Analyze the car models and types of repairs that contribute the most to revenue.
Key Insight:
- The Toyota Camry is the most serviced car model, contributing significantly to total revenue.
- Engine repairs and bodywork jobs are associated with higher customer satisfaction compared to other job types.
Objective: Compare the performance of suppliers based on total revenue generated, average job completion time, and customer satisfaction ratings.
-
Supplier SP003:
- Total Revenue: 1.6 M (Highest Revenue).
- Customer Rating: 2.88/5 (lowest rating).
- Average Job Time: 31.8 days (moderate completion time).
-
Supplier SP009:
- Total Revenue: 1.48 M (Moderate revenue generation).
- Customer Rating: 3.09/5 (highest rating).
- Average Job Time: 31.24 days (better than average completion time)
-
Supplier SP001:
- Total Revenue: 1.47 M (Slightly Below average).
- Customer Rating: 3/5 (moderate rating).
- Average Job Time: 32 days (longest completion time).
Objective: Analyze customer satisfaction ratings based on the car models serviced and the suppliers used.
Key Insight:
- Customers tend to give higher satisfaction ratings for repairs on Kia and Toyota.
- There is a clear trend showing that as the average job time increases, customer satisfaction ratings decrease.
-
Optimize Supplier Performance:
- Focus more jobs towards Supplier SP009, as they have the best performance in terms of customer ratings and good job time to increase the revenue from it.
- Consider improving Supplier SP004 through additional training to reduce job time and enhance customer service.
-
Target Marketing During Rainy Season:
- Invest in targeted marketing campaigns in August and September to take advantage of increased demand during the rainy season.
-
Improve Service for Low-Rating Suppliers:
- Address the issues with suppliers who are receiving lower customer satisfaction ratings, particularly SP001.
- Assumption 1: Data is generated using Python to mimick real world data.
- Assumption 2: Outlier data points were excluded unless patterns were consistently observed.
- Download or explore the Power BI dashboard Power BI Service Link containing:
- Main Dashboard - Sales Overview.
- Supplier Analysis Dashboard.
- Car Model Analysis Dashboard.
Below are snaps from the dashboards
- /Updated_carReapairCompanyData(1): Contains sample datasets used for the analysis.
- /Car Maintenance Project Preview: Power BI dashboard files for Power BI Desktop.
- Download the dataset from the repository or open the Power BI link in Power BI Service.
- Open the .pbit files in Power BI Desktop to explore the interactive dashboards.