← back to Vcc Cost Backfill
Add VCC cost backfill dry-run analysis for TK-10785
4bf2f27a48553e0d9a1aad698693977b39e91990 · 2026-08-24 04:14:22 -0700 · Steve Abrams
Read-only analysis of cost data backfill potential:
- Baseline: 4,076/89,904 ACTIVE products have cost (4.53%)
- 13 vendor catalog sources identified with joinable cost data
- Best join: mfr_sku (more reliable than dw_sku)
- Total fillable: 17,227 distinct products after dedup
- After backfill: 21,303 products with cost (23.7%)
- Largest source: kravet_authoritative_pricing = 12,242 products
- Full gated SQL provided; write to shopify_products is Steve-gated
(Kamatera-canonical table, blast_radius > 500)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Files touched
A .gitignoreA dry-run-2026-08-24.md
Diff
commit 4bf2f27a48553e0d9a1aad698693977b39e91990
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 24 04:14:22 2026 -0700
Add VCC cost backfill dry-run analysis for TK-10785
Read-only analysis of cost data backfill potential:
- Baseline: 4,076/89,904 ACTIVE products have cost (4.53%)
- 13 vendor catalog sources identified with joinable cost data
- Best join: mfr_sku (more reliable than dw_sku)
- Total fillable: 17,227 distinct products after dedup
- After backfill: 21,303 products with cost (23.7%)
- Largest source: kravet_authoritative_pricing = 12,242 products
- Full gated SQL provided; write to shopify_products is Steve-gated
(Kamatera-canonical table, blast_radius > 500)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
.gitignore | 5 ++
dry-run-2026-08-24.md | 212 ++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 217 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..b38eead
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,5 @@
+node_modules/
+.env*
+*.log
+.DS_Store
+tmp/
diff --git a/dry-run-2026-08-24.md b/dry-run-2026-08-24.md
new file mode 100644
index 0000000..be02b69
--- /dev/null
+++ b/dry-run-2026-08-24.md
@@ -0,0 +1,212 @@
+# 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*
(oldest)
·
back to Vcc Cost Backfill
·
VCC cost backfill: real transaction-wrapped apply-2026-08-24 b3d5e82 →