Classic Models Cars Database

Objective Business problem Approach Key findings Recommendations

Information about the dataset.

• This is a retailer of scale models of classic cars. The database contains typical business data such as customers, orders, order line items, products and so on. And the purpose is extract insights from this business dataset. The purpose of this case study is to provide to the business owner some insights from their customers.

Date dictionnary

Column Name	Type	Description
customers	INT	stores customer’s data.
products	INT	stores a list of scale model cars.
productilines	DATE	stores a list of product lines
orders	DECIMAL	stores sales orders placed by customers.
orderdetails	item	stores sales order line items for every sales order.
payments	item	stores payments made by customers based on their accounts.
employees	item	stores employee information and the organization structure such as who reports to whom.
offices	varch	stores sales office datat

• Count of tables: 8
• Count of rows: 3,864
• Count of columns: 59
• Missing values: Yes

Techncial approach

• (1) understanding the business context
• (2) get familiar with tables and do a data exploration and cleaning
• (3) identify key relationships with ER diagram
• (4) look for metrics and kIPs
• (5) understanding business processes and workflow
• (6) build reports from queries

These are the sections I will be looking for answers.

(1) Customer Insights

1. Who are the top 10 customers by total revenue generated?
   • Why: Identifies high-value clients for retention or upselling strategies.
2. What is the average order value per customer?
   • Why: Measures spending habits and customer profitability.
3. Which customers have not made any payments in the last 6 months?
   • Why: Flags potentially inactive customers for follow-up.

4. Which geographic region (from offices or customer addresses) generates the most revenue?

   • Why: Useful for regional performance analysis and marketing targeting.

(2) Employee & Sales Performance

1. Which employees (sales reps) have closed the most deals/orders?
   • Why: Identifies top-performing staff and supports bonus decisions.
2. What is the total revenue generated by each employee’s customers?
   • Why: Links employee performance to customer revenue.
3. Which employees have the highest number of inactive customers?
   • Why: Useful for evaluating account management effectiveness.
4. How many customers are assigned to each employee by region?
   • Why: Assesses workload distribution and potential rebalancing needs.

(3) Order and Product Analysis

1. Which products are the most frequently ordered?
   • Why: Inventory planning and product popularity tracking.
2. What is the total revenue per product line?
   • Why: Helps determine which product categories are most profitable.
3. Which products have declining order volumes over the last year?
   • Why: Identifies underperforming products for potential discontinuation.
4. What is the average delivery time per product line (order date vs. shipped date)?
   • Why: Monitors logistics efficiency by category.
5. What is the cancellation or return rate of orders per product or product line?
   • Why: Quality control and customer satisfaction metric.

(4) Operational and Strategic Insights

1. What is the month-over-month growth in total sales?
   • Why: Tracks performance trends over time.
2. Which office location generates the highest customer engagement (orders or payments)?
   • Why: Strategic planning and office-level performance measurement.
3. What is the reorder rate for each customer (how often do they place repeat orders)?
   • Why: Customer loyalty metric and basis for retention campaigns.