# Used Parts KPI — Part Type Tab: Complete Formula Reference
_Source: `routes/web.php` `/api/used-parts-kpi/part-type-breakdown` (state as of 4 Jun 2026, post-fix)._

## 0. Scope / row filter
A "row" = one `orders_items` line where:
- `is_hollander = 1`
- supplier in the used-parts set (PAMs 74, Counselman 75–78, PartsBox 79–82) **or** `supplier_id IS NULL` (Unknown), per the vendor dropdown
- `DATE(date_placed − 3 hours)` within the date range  _(NY→PT shift)_
- order **not** in test orders (864216, 869107)

---

## 1. Raw inputs (from SQL)
| Symbol | Source |
|---|---|
| `item_sale_price` | `orders_items.sale_price` |
| `core_charge` | `COALESCE(orders_items.core_charge, 0)` |
| `hl_qty` | `COALESCE(NULLIF(FLOOR(magento.qty_invoiced),0), FLOOR(magento.qty_ordered), orders_items.qty)` |
| `hollanderCount` | `MAX(1, COUNT(is_hollander items in the order))` |
| `order_shipping` | `orders.shipping` |
| `order_taxes` | `orders.taxes` |
| `order_subtotal` | Σ `item_sale_price` over the order's hollander items |
| `our_buy_price`, `supplier_price`, `supplier_tax`, `supplier_handling`, `supplier_shipping` | `orders_items_margin_detailed` |
| `credits`, `label_cost`, `additional_cost` | `orders_additional_costs` (see §3) |
| `adRate` | monthly Google ad rate (see §4) |

---

## 2. Per-row building blocks
```
Shipping Charged  hlShip   = order_shipping / hollanderCount
Part Sale Price   hlSale   = item_sale_price × hl_qty − (core_charge × 0.85)

Buy Price         buyPrice = our_buy_price − supplier_shipping
                             if buyPrice < 0  → 0
                             if buyPrice == 0 → supplier_price + supplier_tax + supplier_handling
                             if (Catalytic Converter AND shipped AND core>0):
                                 buyPrice = max(0, buyPrice − core_charge)

Shipping Cost     shipCost = round(supplier_shipping, 2)

Item Tax          itemTax  = order_taxes × item_sale_price / order_subtotal     (0 if subtotal 0)

Ad Spend          adBase   = (item_sale_price − core_charge × 0.85) + hlShip
                  adSpend  = adBase × adRate

CC Fee            ccBase   = item_sale_price + hlShip + itemTax
                  ccFee    = ccBase × 0.025          (2.5%)
Bolt Payment Fee  boltFee  = ccBase × 0.01           (1.0%)
```

---

## 3. Additional costs (from `orders_additional_costs`, per order-item)
```
credits         += |cost|   if cost_type ∈ {Supplier Credit, Carrier Credit}
label_cost      += cost     if cost_type = Return Label Cost
additional_cost += cost     otherwise

5-May rule (refund/pending only):
   if credits == 0 and buyPrice > 0 → credits = buyPrice   (assume full recovery)
   if credits > buyPrice            → credits = buyPrice   (cap at buy)
```

---

## 4. Monthly ad rate  (`_used_parts_monthly_ad_rates_for_year`)
```
adRate[month] = google_ads_monthly_spend[month] / used_parts_total_sales[month]
                fallback 0.10 if data missing
where used_parts_total_sales[month] =
      Σ (sale_price − core_charge×0.85 + order_shipping/hollanderCount)
      over ALL hollander items that month (all statuses)
```

---

## 5. Status branch → how a row contributes
Three financial states (statuses): **CANCEL** = {7, 33, 49}; **REFUND/PENDING** = {24, 34, 48, 12, 58, 57, 29}; **SHIPPED/VALID** = {6, 54} + any other (in-flight).

| Field | CANCEL | REFUND / PENDING | SHIPPED / VALID |
|---|---|---|---|
| Part Sale Price (`sales`) | 0 | `hlSale` | `hlSale` |
| Shipping Charged (`shipChg`) | 0 | `hlShip` | `hlShip` |
| Buy Price (`buy`) | 0 | `buyPrice` | `buyPrice` |
| Shipping Cost (`shipCost`) | 0 | `supplier_shipping` | `supplier_shipping` |
| Ad / CC / Bolt | kept | kept | kept |
| Label / Additional / Credits | 0 | kept | kept |
| Refund Deduction (`refundDed`) | 0 | `−(hlSale + hlShip)` | 0 |
| Cancel Revenue | `+= hlSale + hlShip` | 0 | 0 |
| **Net Margin** | `−(ccFee + adSpend + boltFee)` | `−buy − shipCost − cc − ad − bolt − label − additional + credits` | `hlSale + hlShip − buy − shipCost − cc − ad − bolt − label − additional + credits` |

`shipped_revenue_for_pct += hlSale + hlShip` **only** for SHIPPED/VALID rows (not cancel/refund/pending).

---

## 6. Aggregated columns (Σ over rows in the part type)
| Column | Formula |
|---|---|
| Rows | count of rows |
| Successful | count where status ∈ {6, 54} |
| Returned | count where status ∈ refund set |
| Pending Return | count where status = 12 |
| Cancelled | count where status ∈ {7, 33, 49} |
| Part Sale Price | Σ `sales` |
| Shipping Charged | Σ `shipChg` |
| **Total Sale Price** | Part Sale Price + Shipping Charged |
| Cancel Revenue | Σ cancel `(hlSale+hlShip)` — informational, **excluded** from Total Sale Price & margin |
| Refund Deduction | Σ `refundDed` |
| Core Charge | Σ `core_charge` |
| Buy Price | Σ `buy` |
| Shipping Cost | Σ `shipCost` |
| Ad Spend / CC Fee / Bolt Fee / Label Cost / Additional Cost / Credits | Σ of each |
| Net Margin | Σ `netMargin` |
| _shipped_revenue_for_pct_ | Σ `(hlSale+hlShip)` over SHIPPED/VALID rows |

---

## 7. Derived KPI metrics
```
Returns % (units)   = Returned / (Successful + Returned) × 100
Returns % ($ value) = |Refund Deduction| / Total Sale Price × 100

Net Margin %        = Net Margin / shipped_revenue_for_pct × 100

COGS  ($)           = Buy Price + Shipping Cost + Ad Spend + CC Fee
                      + Bolt Fee + Label Cost + Additional Cost − Credits
COGS Ratio %        = COGS / shipped_revenue_for_pct × 100        (null if denom = 0)
```
**Invariant:** because `Net Margin = shipped_revenue_for_pct − COGS`,
> **COGS Ratio % + Net Margin % = 100 %** at row, part-type, and TOTAL level.
A category that loses money on returns/cancels lands **above 100 %** COGS (flagged red ≥100, orange ≥90).

---

## 8. Per-transaction CSV %-columns (differ from the aggregate)
Each exported row uses its OWN sale as the denominator:
```
rowTotalSale  = sales + shipChg
Net Margin %  = netMargin / rowTotalSale × 100      (blank if rowTotalSale = 0)
rowCogs       = buy + shipCost + ad + cc + bolt + label + additional − credits
COGS Ratio %  = rowCogs / rowTotalSale × 100
                BLANK for Cancelled, Returned, and Pending Return rows
                (revenue was reversed/never collected → per-row % is misleading;
                 the impact shows correctly in the part-type aggregate instead)
```
For a **shipped** CSV row: `Net Margin % + COGS Ratio % = 100 %`.

---

## 9. TOTAL row & Unknown handling
- **TOTAL** = sum of all part-type buckets, then the §7 metrics recomputed on the totals.
- **Unknown** (unresolved part type) is **hidden** as a row but its revenue **is folded into the grand TOTAL** (to reconcile with the Hollander "Sales by Month" report); shown as the "Unresolved Items" footer.
- **Cancel Revenue** total shown as an informational footer ("of which…").