Rebuilding an Automotive
Aftermarket Operation from Data Up
HL Mando Aftermarket North America — owned the full PM + data engineering + ops strategy stack for a 1,500+ SKU catalog across Amazon VC, AAP, WHI, and internal ERP.
44.7%
Operating Margin (Jan-26)
+8.8pp YoY improvement
40%+
Reduction in missing
catalog attributes
~12hr
Weekly hours saved
via automation
5.2%
Return rate (Jan-26)
down from 7.1% avg
The Problem I Walked Into
Everything lived in Excel
Product master, pricing history, origin/FTA, monthly K-SOX reports — all in separate spreadsheets owned by individuals. No unique keys. No joins. No audit trail.
Knowledge = a person, not a system
Critical operations (catalog validation, margin modeling, Amazon claim resolution) depended entirely on specific individuals. One absence = workflow collapse.
4 platforms, 0 unified data model
Amazon VC, AAP, WHI, and internal ERP ran on disconnected data logic. Rejection rates were high, fitment errors were rampant, and no one could see the full picture.
~20hrs/week on manual ops
Monthly SmartSheet packages, Amazon Remittance reconciliation, K-SOX reports, and status tracking were all done by hand — every month, from scratch.
Technical PM
- → SKU full lifecycle ownership
- → Roadmap management across 4 platforms
- → Amazon rejection pattern analysis
- → Promotion planning & approvals
- → 2026 BSC 5-axis KPI design
- → Cross-functional coordination (Sales, Catalog, Engineering, Amazon VSP)
"The goal wasn't to fix the spreadsheets. It was to make spreadsheets unnecessary."
Data / Systems Architecture
- → ERD design (10-table relational model)
- → Product Status state machine (18 states)
- → MDM structure for Snowflake migration
- → ACES/PIES XML pipeline design
- → VIO-based Coverage Tier model
- → Replaced ~15 disconnected Excel files with single relational structure
Automation & GenAI
- → VBA / Python / UiPath workflow bots
- → LLM-assisted catalog validation layer
- → AI error explanation for Amazon/AAP rejections
- → GenAI defect/return cluster analysis
- → ASIN readiness scoring before platform submission
Key Deliverables
ERD & Relational Data Model
- 10-table model: Product, AIES, VIO, AM Parts, Pricing, Status, Returns, Promotions, Automation, Media
- Hub-spoke structure — Material as central entity
- Replaced ~15 disconnected Excel files
- Designed for Snowflake migration
0 → 1 architecture build
Product Status State Machine
- 18 distinct lifecycle states (PRP → REL → ATO → SUP → OBS…)
- Each state mapped to Alere, Evocat, APSG systems
- Inbound/outbound rules defined per state
- Foundation for pricing policy + inventory strategy
18 states · 3-system mapping
2026 Balanced Scorecard (BSC)
- 5 KPI axes: MDM Foundation, Process Automation, Business Impact, Cross-team Risk, AI Capability
- Each KPI has target, output artifact, and timeline
- Positions P&T as decision-enablement org
5-axis · 8 KPIs defined
Monthly P&L Analysis System
- Jan-26: Operating margin 44.7% (highest on record), return rate 5.2%
- Revenue decline correctly isolated as seasonal — not structural
- Structured implication analysis → Q1 operational priorities
Monthly · Exec-ready output
Automation & Workflow Reduction
- VBA + Python + UiPath: Remittance, K-SOX, SmartSheet automation
- Saved 10–15 hrs/week across the team
- LLM validation layer for catalog attribute completeness
- ASIN readiness scoring before platform submission
10–15 hrs/wk saved
Cross-functional Process Documentation
- Dispute & Credit Memo swimlane SOP (5-department, audit-ready)
- BFCM + BSS promotional deal proposals — 30–46 SKUs per cycle
- O-ring initiative: root cause → BOM update → KPI tracking
5 docs · end-to-end ownership
Business Impact — Jan 2026 P&L Snapshot
| Metric | 2025 Avg | Jan-25 | Jan-26 | YoY Delta | Reading |
|---|
| Gross Sales |
████ |
████ |
████ |
▼ −21.1% YoY |
Seasonal softness + reduced promo spend. Expected. |
| Return Rate | 7.1% | 6.2% | 5.2% |
▼ −1.9pp |
Catalog quality improvement reducing wrong-item returns. |
| COGS % | ~64% | 63.0% | 52.0% |
▼ −11.0pp |
Lower order volume reduced variable costs proportionally. |
| Operating Margin | ~31% | ~36% | 44.7% |
▲ +8.8pp |
Highest recorded margin. Structural efficiency gains showing. |
2026 Balanced Scorecard — PM Strategy Design
| Axis | KPI | Target | Outcome Artifact |
|---|
| Foundation | SKU Family redesign + MDM v1.0 | 1,500 SKUs migrated | DB Schema + Field Definition doc |
| Automation | Excel → system conversion (≥3 workflows) | ≥30% manual time reduction/month | Process Map + Automation ROI list |
| Business Impact | VIO-based coverage tier + price positioning | Top OEM 20–30% cross-mapped | Coverage Model + Competitor pricing matrix |
| Cross-team Risk | Eliminate single-person knowledge dependency | ≥5 core SOPs documented | SOP / Swimlane / Data Dictionary |
| AI Capability | AI/automation PoC in ≥1 area | 1 PoC shipped | Concept Doc / Demo / Notebook |
MDM Architecture — What I Designed
🧱
Product (Material) — Central Hub
SKU Family, Sub-family, Lifecycle Status, Attribute standards. Everything else spokes from here. Designed around Salesforce + Snowflake as storage layer.
1,500 SKUs · 10-table relational model
🚗
AIES / ACES — Fitment Layer
Vehicle compatibility data across Make/Model/Year/Option. Direct XML-to-DB pipeline replacing third-party CSV conversion. Millions of rows, monthly Evocat updates.
~1M+ fitment records · XML pipeline design
📊
VIO — Coverage Intelligence
Experian-sourced vehicle-in-operation data by state/segment. 220K+ rows per quarter. Drives Essential/Priority/Optional coverage tiers and new SKU prioritization logic.
220K rows/quarter · Tier model designed
🔗
AM Parts — Competitive Mapping
Competitor (FCS, DSM, KYB, Monroe) and supplier part number mapping. Foundation for price positioning, coverage gap analysis, and cross-reference accuracy.
Multi-million line OEM cross-reference
Product Status State Machine — 18-State Framework
Contributed to the documentation and system mapping of the full SKU lifecycle — defining each state's operational rules across ERP, catalog management, and marketplace platforms.
PRP — Proposed
Release in progress
REL — Released
Internal launch complete
ATO — Available to Order
Customer orderable
ENA — Electronically Announced
EDI announced
ANN — Announced
Paper announcement done
TUA — Temporarily Unavailable
Shipment suspended
SUP — Superseded
Replaced, sell stock
DCS — Discontinued
Being phased out
OBS — Obsolete
Fully end-of-life
WSL — While Supplies Last
Until stock depletes
FBO — Final Build Out
Last production run
OEO / RMO / CMO / WDO
Channel-exclusive states
Each state mapped across ERP, catalog management, and marketplace systems — with inbound/outbound transition rules defined per platform.
Project Documentation — Actual Work Samples
Proposals, process maps, and analyses authored during the role. Sensitive financial figures (absolute revenue, cost, and margin targets) are redacted per confidentiality requirements — structure and methodology are fully visible.
Redesigned the end-to-end Amazon remittance dispute workflow — from chaotic, individual-driven handling to a documented 5-column swimlane across Amazon, Operations, P&T, and Accounting.
- AS-IS: Invoice/re-issue date mismatches, no dispute tracking, no resolution SLA
- TO-BE: Dispute → Approve → Credit Memo → Record flow with monthly cadence (20th–25th)
- Shortage path: Automatic repayment review → submit dispute → quarterly re-dispute cycle
- Credit memo path: Amazon-issued vs. internal-issued, each mapped to correct accounting step
What This Shows
Cross-functional SOP ownership — built the process from scratch, made it audit-ready
Identified that Brake Master Cylinder products originally sold without O-rings were generating customer complaints and Amazon returns. Led cross-functional response from diagnosis to packaging BOM update.
- Root cause: Products sold as non-O-ring config; customer-installed configurations required it
- Action: Added O-ring kit to packaging at $0.20/unit (O-ring $0.10 + labor $0.10)
- Coordinated: HQ BOM update, Amazon listing correction, CS instruction update, packing instruction SOP
- KPI tracking: Return rate, Q&A volume, customer rating — all monitored monthly
| Metric | Before | After | Change |
|---|
| Return Rate | 14.06% | 11.34% | −2.73pp |
| Q&A Volume/mo | 39 | 12 | −69% |
| Product Rating | 3.9 | 4.2 | +0.3 |
| Monthly Avoided Loss | ~$108 / month | est. $1,298/yr |
Authored MSRP-based direct discount proposal for BFCM, selecting 30 SKUs across 11 product categories. Built pricing analysis comparing AS-IS margins against proposed funding requirements.
- Scope: 30 SKUs across Brake Caliper, Brake MC, Complete Strut, Ignition Coil, and 7 other categories
- Discount range: ~11.7% – 29.8% MSRP-based direct customer pricing
- Pricing rationale: Amazon Auto Pricing System + Amazon Account Manager guidance
- Margin impact: Per-category funding requirement calculated vs. Uriman cost baseline
| Category | SKUs | Avg. Amazon Price | Margin (AS-IS) |
|---|
| Brake Caliper | 4 | $83.46 | ████ |
| Complete Strut | 8 | $121.37 | ████ |
| Power Steering Pump | 1 | $189.99 | ████ |
| Grand Total | 30 | — | ████████ |
Result
Units sold: 41 · Amazon support cost & sales figures: ████████
Authored Best Deal (MSRP direct discount) proposal for Amazon Spring Sale. Enrolled 17 items; 29 additional SKUs placed on hold due to Star Rating Below Minimum — flagged and documented for Q2 recovery.
- Vendor code: 320QK · Deal type: Best Deal — MSRP Direct Discount
- 17 SKUs enrolled · 29 on hold (star rating threshold not met)
- Discount structure: 15%–37% range, varying by category (Strut series: 15%–37% for inventory clearance)
- Target inventory: 15–50 units/SKU per stock availability
- Complete Strut MSS/DSM series: discount rates set for inventory clearance, margin target: ██%
Status
Submitted via MBO approval flow · Results tracked in Apr 2026 report
Designed and authored the annual Amazon financial structure analysis — mapping Gross Sales → COGS → Contribution Margin with a full breakdown of cost categories unique to Amazon's billing model. All values configured to auto-update through data integration. (Absolute $ figures redacted per confidentiality)
P&L Structure Designed
| Category | Component | Amount |
|---|
| Gross Sales | Amazon channel total | ████ |
| COGS | Uriman cost + Amazon fees + Freight + Damage Allowance | ████ |
| Marketing | PPC + Promotions + Price Protection | ████ |
| Contribution Margin | Gross Sales − COGS − Marketing | ████ |
*COGS includes: Uriman purchase cost, freight, Amazon MDF (10% US), commission, damage allowance (1% US) — retroactive adjustments from Y25 included
2025 Key Observations
- Best month: Sep-25 — highest sales, optimal COGS ratio
- MDF: ~10% of sales · Freight: ~4% (auto-deducted via logistics contract)
- Margin variance: 10pp+ spread across same-period months → tracked to COGS ratio shifts
- Promotion total: $31,378 (avg $2,615/mo) — $1,057 was Price Protection, excluded from marketing
- Return rate: 7.1% of Gross Sales — reflected in COGS as actual returned item cost
Forward Direction (TO-BE)
Establish minimum margin threshold at supply cost → verify target margin before MSRP pricing. Optimize returns/marketing cost ratio per category.
Full ERD — Relational Data Model
Product (SKU) ← Central Hub
├── 1:N Fitment (ACES) → N:1 VehicleID (Year/Make/Model/Engine)
├── 1:N PIES Attributes → Attribute_Name / Value / Compliance_Flag
├── 1:N AM Parts → Competitor cross-ref (FCS, DSM, KYB, Monroe)
├── 1:N Pricing → MSRP / MAP / Cost / Amazon / AAP / MDF / Promo
├── 1:N Inventory → On_Hand / Allocated / In_Transit / Safety_Stock
├── 1:N Product_Images → URL / Angle_Type / A+ Flag
├── 1:N Returns_Data → Root_Cause / Return_Rate / Resolution_Type
├── 1:N AI_Validation_Log → AI_Tag / Correction / Confidence_Score
└── 1:N Automation_Tasks → Task_Type / Status / Output_File_Path
MSS (Part Number) → SKU · 1:1 ASIN · 1:N AAP_Item · 1:N WHI_Item
VIO (Coverage) ← Experian
└── 220K+ rows/quarter → Essential / Priority / Optional tier assignment
Marketplace Layer
├── Amazon_ASIN → Listing_Status / BulletPoints / Rejection_Code / Promotions
├── AAP_Item → BGP_ID / AAP_Category / Fitment_Summary
└── WHI_Item → WHI_Part_Number / Status / Fitment_Data
What This Work Says About How I Work
I don't just manage products — I build the systems that make them manageable. When I joined, the operation ran on Excel muscle memory. When I'm done, it runs on structure, data contracts, and automation that survives personnel changes. I write formal proposals. I design state machines. I ship automation. And I document everything — because "The Power of Record-Keeping" is what separates a product that scales from one that collapses under its own weight.
Operating Margin 44.7% ↑
Return Rate 5.2% ↓
~12hr/wk Automated
40%+ Missing Attributes Resolved