🚗 Classic Car Dealership Analytics

A data analytics project leveraging SQL, visualization, and machine learning techniques to analyze customer behavior, sales trends, and marketing performance for a classic car dealership.

This project was developed as part of Data Analytics Case Study 1 (DAMO-501-3).

---

📌 Project Overview

The project applies data from the Classic Models database to answer key business questions such as:

• Which customer demographics drive sales?
• What are the top-selling products and seasonal trends?
• How effective are marketing campaigns and employee contributions?
• How can predictive models enhance decision-making and marketing strategies?

---

🎯 Objectives

• Analyze customer demographics and regional credit limits
• Identify top-selling products and sales trends
• Examine seasonal and monthly demand patterns
• Evaluate employee performance and workload distribution
• Build predictive models (Regression, Classification, Clustering) to support strategy

---

📂 Dataset & SQL Queries

The project uses the Classic Models dataset with key tables: customers, orders, orderdetails, employees, offices, products.

Example Queries

-- Customer demographics by region
SELECT country, state, city, COUNT(customerNumber) AS num_customers,
       AVG(creditLimit) AS avg_credit_limit
FROM classicmodels.customers
GROUP BY country, state, city
ORDER BY num_customers DESC;

-- Top-selling products
SELECT p.productCode, p.productName,
       SUM(od.quantityOrdered * od.priceEach) AS total_sales
FROM classicmodels.orderdetails od
JOIN classicmodels.products p ON od.productCode = p.productCode
GROUP BY p.productCode, p.productName
ORDER BY total_sales DESC;

-- Monthly sales trends
SELECT YEAR(orderDate) AS year, MONTH(orderDate) AS month,
       SUM(od.quantityOrdered * od.priceEach) AS monthly_sales
FROM classicmodels.orders o
JOIN classicmodels.orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY year, month
ORDER BY year, month;

---

📊 Data Analysis & Insights

Customer Demographics

• USA, Germany, France = most customers
• USA customers had higher credit limits → focus on premium campaigns

Product Sales Trends

• 1992 Ferrari 360 Spider Red among top revenue drivers
• Recommendation: increase stock & promotions

Seasonal Order Trends

• Sales peak in June & December → plan targeted campaigns

Marketing Campaigns

• Some employees manage disproportionately high customer loads
• Office locations don’t always align with high-density markets

---

🤖 Predictive Models

1. Regression (Marketing Spend → Sales Growth)

• R² = 0.78 → strong predictor
• Suggests marketing spend and demographics drive sales trends

2. Classification (High-Value Customers)

• Decision Tree model
• Accuracy = 82%, Precision = 85%, Recall = 78%
• Identified high-value customers but recall can be improved with ensemble methods

3. Clustering (Customer Segmentation)

• Silhouette Score = 0.62 → well-defined clusters

• Segments:

  • Cluster 1: High spend, infrequent buyers
  • Cluster 2: Medium spend, frequent buyers
  • Cluster 3: Low spend, occasional buyers

---

✅ Key Recommendations

• Focus marketing on USA high-value customers
• Stock and promote top-selling models
• Launch seasonal campaigns before June & December
• Provide training/support for overburdened employees
• Use segmentation-driven marketing for targeted offers

---

⚠️ Strengths & Limitations

Strengths:

• Multi-model approach: Regression, Classification, Clustering
• Actionable insights on customer value & seasonal trends

Limitations:

• Regression didn’t include external factors like competitor pricing
• Classification model affected by imbalanced data
• Clustering limited to basic features → may oversimplify behavior

---

👨‍💻 Contributors

• Dhruv Patel (NF1001883) – dhruv.patel1883@myunfc.ca
• Vrund Patel (NF1007109) – vrund.patel7109@myunfc.ca
• Heta Chavda (NF1014555) – heta.chavda4555@myunfc.ca
• Dhruvi Desai (NF1008933) – dhruvi.desai8933@myunfc.ca
• Arpit Desai (NF1010039) – arpit.desai0039@myunfc.ca

📚 Instructor: Patty Zakaria 📖 Course: Data Analytics Case Study 1 (DAMO-501-3)

---

📂 Tech Stack

• SQL (MySQL) → Data extraction & queries
• Python (Pandas, Scikit-Learn, Matplotlib, Seaborn) → ML & visualization
• Excel / Power BI → Reporting & dashboards

---

📌 Conclusion

The Classic Car Dealership Analytics project demonstrates how data-driven decisions can optimize marketing strategies, increase sales, and improve operational efficiency.

By combining SQL, analytics, and predictive modeling, dealerships can enhance customer targeting, inventory planning, and overall performance.