`dry-run-2026-08-24.md`

213 lines
# VCC Cost Backfill — Dry-Run Analysis
**Date:** 2026-08-24  
**Ticket:** TK-10785  
**Agent:** vcc-cost-backfill  
**Status:** READ-ONLY analysis. No writes performed.

---

## 1. Baseline (Before Backfill)

| Metric | Value |
|--------|-------|
| Total ACTIVE products in shopify_products (Mac2 mirror) | 89,904 |
| Products with cost_price > 0 | 3,975 |
| Products with cost > 0 | 103 |
| Products with ANY cost (cost_price OR cost) | 4,076 |
| Products missing cost | 85,828 |
| **Current cost coverage** | **4.53%** |

### Join Key Population (ACTIVE products)
| Key | Populated | Missing |
|-----|-----------|---------|
| dw_sku | 55,264 (61.5%) | 34,640 |
| mfr_sku | 63,857 (71.0%) | 26,047 |
| handle | 89,904 (100%) | 0 |

---

## 2. Vendor Catalogs with Cost Data

62 of 181 vendor catalog tables have at least one cost-related column. Key discovery:

- **mfr_sku join is more effective than dw_sku** — many catalog rows lack the `DW*-` prefix on their dw_sku field, but retain the vendor's original mfr_sku exactly as it appears in shopify_products.
- **kravet_authoritative_pricing is priority-1 for all Kravet-family lines** (per standing CLAUDE.md rule) — it covers Kravet, Cole & Son, GP & J Baker, Lee Jofa, Groundworks, and all umbrella brands via authoritative MAP from 2026 price-adjustment emails. `new_map / 1.5 = wholesale cost`.

---

## 3. Per-Source Backfill Analysis

Products that currently have ZERO cost and would receive cost data via each source (distinct product count):

| Source | Join Key | Cost Column | Products Fillable | Notes |
|--------|----------|-------------|-------------------|-------|
| **kravet_authoritative_pricing** | mfr_sku | new_map/1.5 (wholesale) | **12,242** | Priority-1 per CLAUDE.md; covers all Kravet-family brands |
| **wallquest_catalog** | mfr_sku | our_price | **2,489** | Malibu Wallpaper private label |
| **york_catalog** | mfr_sku | cost | **1,047** | Direct cost field |
| **brewster_catalog** | mfr_sku | cost | **525** | Direct cost field |
| **designtex_catalog** | dw_sku | our_price | **279** | dw_sku join works here |
| **romo_catalog** | mfr_sku | cost | **166** | Direct cost field |
| **anna_french_catalog** | mfr_sku | cost | **156** | Direct cost field |
| **thibaut_catalog** | mfr_sku | our_price | **125** | our_price is the cost proxy (cost col empty) |
| **groundworks_catalog** | mfr_sku | wholesale | **121** | Kravet-family, but also has own catalog data |
| **schumacher_catalog** | mfr_sku | cost | **115** | Some dw_sku matches too |
| **rebel_walls_catalog** | mfr_sku | our_price | **39** | our_price as cost |
| **carnegie_catalog** | mfr_sku | price | **26** | price field |
| **innovations_catalog** | dw_sku | our_price | **19** | dw_sku join works here |

**Note:** There is overlap between sources (e.g., a Kravet-family product may appear in both `kravet_authoritative_pricing` and `groundworks_catalog`). The distinct count below deduplicates.

---

## 4. Combined Projection (After Backfill)

| Metric | Value |
|--------|-------|
| Products currently with cost | 4,076 |
| Additional products fillable from 13 sources | **17,227** |
| **Total products with cost after backfill** | **21,303** |
| **New coverage %** | **23.7% of 89,904 ACTIVE** |
| Remaining gap (no vendor source found) | 68,601 products |

---

## 5. Recommended Backfill SQL (GATED — do not execute without approval)

### Phase 1: Kravet-family via kravet_authoritative_pricing

```sql
-- Gated: write to shopify_products.cost_price on Mac2 mirror
-- Wholesale = new_map / 1.5 per CLAUDE.md standing rule
UPDATE shopify_products sp
SET 
  cost_price = kap.new_map / 1.5,
  cost_source = 'kravet_authoritative_pricing',
  cost_unit_of_measure = 'yard'
FROM kravet_authoritative_pricing kap
WHERE kap.mfr_sku = sp.mfr_sku
  AND sp.status = 'ACTIVE'
  AND kap.new_map > 0
  AND (sp.cost_price IS NULL OR sp.cost_price = 0);
-- Estimated rows affected: ~12,242
```

### Phase 2: WallQuest (Malibu private label)

```sql
UPDATE shopify_products sp
SET 
  cost_price = vc.our_price,
  cost_source = 'wallquest_catalog',
  cost_unit_of_measure = 'roll'
FROM wallquest_catalog vc
WHERE vc.mfr_sku = sp.mfr_sku
  AND sp.status = 'ACTIVE'
  AND vc.our_price > 0
  AND (sp.cost_price IS NULL OR sp.cost_price = 0);
-- Estimated rows affected: ~2,489
```

### Phase 3: York, Brewster, Romo, Anna French, Schumacher, Thibaut, Groundworks

```sql
-- York
UPDATE shopify_products sp SET cost_price = vc.cost, cost_source = 'york_catalog'
FROM york_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.cost > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Brewster
UPDATE shopify_products sp SET cost_price = vc.cost, cost_source = 'brewster_catalog'
FROM brewster_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.cost > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Romo
UPDATE shopify_products sp SET cost_price = vc.cost, cost_source = 'romo_catalog'
FROM romo_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.cost > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Anna French
UPDATE shopify_products sp SET cost_price = vc.cost, cost_source = 'anna_french_catalog'
FROM anna_french_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.cost > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Schumacher
UPDATE shopify_products sp SET cost_price = vc.cost, cost_source = 'schumacher_catalog'
FROM schumacher_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.cost > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Thibaut (our_price as cost proxy)
UPDATE shopify_products sp SET cost_price = vc.our_price, cost_source = 'thibaut_catalog'
FROM thibaut_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.our_price > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

-- Groundworks (wholesale)
UPDATE shopify_products sp SET cost_price = vc.wholesale, cost_source = 'groundworks_catalog'
FROM groundworks_catalog vc WHERE vc.mfr_sku = sp.mfr_sku AND sp.status = 'ACTIVE'
  AND vc.wholesale > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);
```

### Phase 4: Designtex, Innovations (dw_sku join)

```sql
UPDATE shopify_products sp SET cost_price = vc.our_price, cost_source = 'designtex_catalog'
FROM designtex_catalog vc WHERE vc.dw_sku = sp.dw_sku AND sp.status = 'ACTIVE'
  AND vc.our_price > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);

UPDATE shopify_products sp SET cost_price = vc.our_price, cost_source = 'innovations_catalog'
FROM innovations_catalog vc WHERE vc.dw_sku = sp.dw_sku AND sp.status = 'ACTIVE'
  AND vc.our_price > 0 AND (sp.cost_price IS NULL OR sp.cost_price = 0);
```

---

## 6. Gate Memo

**Is this auto-executable?** NO — GATED.

Reason: `shopify_products` on Mac2 is a MIRROR of the Kamatera-canonical table. Per CLAUDE.md dw_unified split-ownership doctrine:
- **Mac2 is canonical for vendor staging tables (`*_catalog`)**
- **Kamatera is canonical for `shopify_products`**

A mass UPDATE to `shopify_products.cost_price` on Mac2 must be:
1. Validated against this dry-run count (done ✅)
2. Approved by Steve
3. Applied on Kamatera (the canonical instance) OR via a reconciled sync
4. If applied only on Mac2 mirror, coordinated with the next Kamatera sync

**Reversibility:** The backfill is reversible via:
```sql
UPDATE shopify_products SET cost_price = NULL, cost_source = NULL
WHERE cost_source IN (
  'kravet_authoritative_pricing', 'wallquest_catalog', 'york_catalog',
  'brewster_catalog', 'designtex_catalog', 'anna_french_catalog',
  'thibaut_catalog', 'groundworks_catalog', 'schumacher_catalog',
  'innovations_catalog', 'romo_catalog', 'rebel_walls_catalog', 'carnegie_catalog'
);
```

**Blast radius:** 17,227 rows — exceeds the ≤500 auto-execute threshold in the reversibility tier rules.

**Recommended apply path:** Steve approves → run on Kamatera canonical → Mac2 mirror inherits on next sync.

---

## 7. Key Findings for VCC / Victor

1. **mfr_sku is the best join key** — covers more products than dw_sku because many vendor catalog rows don't carry the full DW-prefixed SKU.
2. **kravet_authoritative_pricing is the single highest-value source** — 12,242 additional products, ~71% of all fillable, and it provides the correct MAP-derived wholesale cost.
3. **Even after full backfill, 68,601 ACTIVE products remain without cost** (76.3%). These are vendors whose catalogs have no price data at all (no cost column or zero values). Longer-term fix requires price-sheet imports or scraper enhancements.
4. **Thibaut cost column is empty** — 6,057 catalog rows, but `thibaut_catalog.cost = 0` for all. `our_price` is the usable proxy (represents DW cost basis, not retail).

---

## 8. Catalog Tables Still Missing Cost Data (sample)

These vendor catalogs have NO cost/price column at all and represent the remaining 68K gap:
- Most Kravet-sub-brands not in authoritative pricing sheet
- Arte, Dedar, Fromental, Elitis, Sanderson, Romo variants, etc.
- Solution: price-sheet import ingestion for each vendor

---

*Generated by vcc-cost-backfill agent, TK-10785, 2026-08-24*