Brake Rotor Compatibility & Warehouse Optimization

Business Context

In the automotive aftermarket industry, we sell both individual components (e.g., brake rotors, pads, calipers, hubs, steering knuckles) and kits that bundle multiple components together.

From a commercial standpoint, kits are powerful:

• Higher average order value
• Better customer experience (complete solution in one purchase)
• Stronger product positioning

However, from a warehouse operations perspective, kits introduce complexity.

Kits are virtual bundles at the catalog level — but in the warehouse, inventory is stored and picked as individual SKUs. When a kit is sold, warehouse staff must physically collect each component from its respective storage location.

If compatible components are stored far apart, picking becomes slower and less efficient.

This project connects product data with warehouse optimization.

---

The Problem

We needed a structured, data-driven way to answer:

Which brake rotors are most frequently sold together with which pads, calipers, hubs, and steering knuckles?

Without structured compatibility analysis:

• Warehouse layout decisions relied on manual knowledge
• High-frequency combinations were not necessarily stored close together
• Picking time for kits was longer than necessary
• Operational efficiency was suboptimal

---

The Solution

This Python script:

1. Parses kit composition data
2. Builds compatibility relationships based on real kit co-occurrence
3. Enriches the relationships with 12-month sales data
4. Produces structured Excel outputs to support warehouse layout decisions

The result allows operations teams to:

• Place frequently paired components near each other
• Prioritize high-volume combinations
• Reduce walking distance for pickers
• Improve fulfillment speed
• Increase shipping efficiency

This is a direct example of data engineering driving operational performance.

---

Data Inputs

1️⃣ Sales File (CSV)

Columns:

• SKU – individual or kit SKU
• SALES – total sales over a defined period

Used to:

• Rank compatible components
• Quantify operational impact
• Prioritize layout decisions

---

2️⃣ Kits File (CSV)

Columns:

• Package Name – kit SKU
• Members – comma-separated list of member SKUs with quantities
• WMS Category – comma-separated categories aligned with Members

Compatibility is defined strictly by co-occurrence within the same kit.

---

Core Logic

• Quantities are parsed but ignored for compatibility mapping
• Categories are normalized using keyword matching:
  • rotor
  • pad
  • caliper
  • hub
  • knuckle
• Only relevant brake components are considered
• Missing sales values default to 0
• Pads are ordered by individual SKU sales (descending)

Compatibility is derived from actual bundled sales, not assumptions.

---

Output

The script generates an Excel file with three sheets:

---

📄 1. Rotor_Compatibility

One row per rotor SKU.

Includes:

• Compatible Pads
• Compatible Calipers
• Compatible Hubs
• Compatible Steering Knuckles
• sales_rotor_individual
• sales_kits_with_rotor
• sales_rotor_total
• kits_count

This sheet directly supports warehouse layout optimization.

---

📄 2. Pads_Sales

One row per pad SKU appearing in any kit.

Includes:

• Pad
• sales_pad_individual

Used to prioritize pad placement

---

📄 3. Rotor_Pad_Sales

One row per Rotor–Pad compatible pair.

Includes:

• Rotor
• Pad
• Pad_Sales

Used to prioritize pad placement relative to high-volume rotors.

---

Operational Impact

This project enables:

• Data-driven warehouse organization
• Reduced picking time
• Faster kit assembly during shipping
• Better alignment between catalog structure and physical inventory
• Improved order fulfillment efficiency

In simple terms:

We sell kits, but we store individual parts.
This script connects those two operational realities.

---

Technologies Used

• Python
• Pandas
• Regular Expressions (Regex)
• OpenPyXL

---

Example Scenario

If Rotor R12345 frequently appears in kits with Pads P67890 and P54321, and those kits represent significant revenue:

• These SKUs should be stored in nearby warehouse locations.
• Pickers can collect required components with minimal movement.
• High-volume kits become faster to fulfill.

This translates directly into operational efficiency gains.

---

Why This Project Matters

Many analytics projects stop at dashboards.

This project influences:

• Physical warehouse layout
• Human movement efficiency
• Shipping speed
• Operational cost structure

It demonstrates how structured product data and sales intelligence can directly improve logistics performance.