← back to Kravet Sheet Sync 2026 04 20

SUMMARY.md

144 lines

# Kravet Sheet Sync — 2026-04-20 (Dry Run)

Source sheet: https://docs.google.com/spreadsheets/d/1Yk7Ip5nc9M6VDz7lZnaEFMI7SScIGuvXKkfPtwY8rLQ/

## Cost formula used

`cost = MAP / 2 + (Wholesale × Tariff% / 100)`
`shopify_price = MAP` (sell at MAP per user instruction)

Example: MAP 23.55, WHLS 15.70, Tariff 5% → cost 12.56, price 23.55.

## Scope

- Sheet total rows: 44,572 (Kravet group: Lee Jofa, Parkertex, Brunschwig & Fils, Kravet Design/Couture, Baker Lifestyle, Mulberry, G P & J Baker, Threads, Clarke & Clarke, Cole & Son, Scion, Winfield Thybony, Gaston y Daniela, Caroline Cecil Textiles, Lee Jofa Modern).
- Sheet wallcoverings (Use LIKE %WALL%): **10,184** rows.
- Catalog DWKK rows (`kravet_catalog`): 9,886. Live on Shopify: 4,927.

## Match key

Normalized mfr_sku = upper(SKU) with hyphens converted to dots and trailing `.0` stripped. This reconciles Kravet Design W-series that's stored `W3277-16` in catalog but `W3277.16.0` in sheet.

## Diff buckets (outputs in this directory)

| File | Rows | What |
|---|---|---|
| `kravet_archive_2026-04-20.csv` | **357** | DWKK products currently live on Shopify with no matching row in the full Kravet sheet. Candidates for Shopify `status: "archived"` per CLAUDE.md "Discontinued → ARCHIVE (never draft, never delete)" rule. Includes shopify_product_id for each. |
| `kravet_new_2026-04-20.csv` | **8,261** | Sheet wallcovering rows not yet imported as DWKK. DWKK- SKUs pre-assigned starting at **DWKK-140000** (above both `kravet_catalog` max 132868 and `dw_sku_registry` max 108190 to avoid collisions). Columns include computed cost + MAP retail, image/width readiness flags. 7,953 ready for activation (have both image + width); 308 missing image → hold as drafts. |
| `kravet_update_2026-04-20.csv` | **4,570** | Matched rows where catalog `price_retail` differs from sheet MAP. Informational — for next pass. |

## Display-Status observation

The sheet has only three Display Status values: `Active` (39,290), `Limited Stock` (3,736), `Outlet` (1,546). **No explicit Discontinued/Dropped status exists.** Archive detection therefore uses "absent from sheet" as the sole criterion, not a Display Status filter. All three statuses were treated as still-sellable for the new-import bucket.

## New-SKU assignment

- Prefix: `DWKK-`
- Numeric range reserved: **DWKK-140000 … DWKK-148260** (8,261 SKUs, contiguous)
- Sequence is deterministic (ordered by brand, pattern, color, normalized mfr_sku)
- Sample variants per CLAUDE.md rule: each new product needs `{DWKK-NNNNNN}-Sample` SKU at $4.25 — add at Shopify-creation time, not in this file

## Known edge cases surfaced

- 308 new-bucket rows have no image filename → keep as `draft` on Shopify until an image is sourced (CLAUDE.md: "Never activate without width AND image").
- All 300 no-brand archive candidates are Kravet Design W-series sample-only SKUs (dw_sku ends in `-Sample`). These are color codes Kravet has retired. Safe to archive.
- One archive row has `pattern_name = "Page Not Found"` — bad historical scrape, archive still appropriate.

## All sheet fields persisted to dw_unified (2026-04-20)

Per "PostgreSQL FIRST, THEN Shopify" rule, every field from the sheet is now stored before any Shopify metafield push.

**Table `kravet_sheet_fields`** (new, keyed by normalized mfr_sku):
- 44,524 rows (all distinct-normalized SKUs from the 44,572 sheet rows)
- All 54 sheet columns preserved verbatim, numeric fields typed (whls_price, map, tariff_pct, new_wholesale_price), plus computed `cost_computed` and `retail_computed`, plus `sheet_snapshot_date` and `last_synced_at`
- Indexes on mfr_sku_raw, brand, use, display_status, inventory_available
- Safe to re-run `04_persist_all_fields.sql` on every sheet refresh — drops and rebuilds

**Field fill rates** (for metafield-push planning):

| Field | Populated | Field | Populated |
|---|---|---|---|
| prop_65 | 44,524 (100%) | lead_time_days | 39,281 (88%) |
| ca_tb117 | 44,524 (100%) | direction | 36,717 (82%) |
| barcode | 44,524 (100%) | minimum_order_qty | 25,761 (58%) |
| memo_sample_available | 44,524 (100%) | durability | 25,065 (56%) |
| qty_on_hand | 44,524 (100%) | ufac | 21,062 (47%) |
| memo_sample_qty | 44,524 (100%) | notes | 14,459 (32%) |
| ship_from | 44,524 (100%) | wallcovering_area | 6,471 (15%) |
| weight | 44,193 (99%) | horizontal_half_drop | 4,580 (10%) |
| clean_code | 42,445 (95%) | prop_65_chemical | 2,583 (6%) |
| ab_1817 | 40,751 (91%) | | |

**kravet_catalog upsert**: the 9,113 DWKK rows whose mfr_sku matches the sheet had their overlapping spec/price fields refreshed (width, repeats, origin, composition, finish, collection, price_trade, price_retail, price_trade_new, price_effective_date, tariff_pct, cost_price, unit_of_measure, application, roll_length, plus two new tracking columns `sheet_display_status`, `sheet_inventory_status`, `sheet_last_synced_at`). 357 live catalog rows had no sheet counterpart — these are exactly the archive bucket.

**Suggested Shopify metafield mapping** (to apply on a later push):

| Sheet field | Shopify metafield |
|---|---|
| prop_65 | `global.Prop-65` |
| prop_65_chemical | `custom.prop_65_chemical` |
| prop_65_effect | `custom.prop_65_effect` |
| ca_tb117 | `global.CA-TB117` |
| ufac | `global.UFAC` |
| ab_1817 | `custom.ab_1817` |
| direction | `global.Direction` |
| minimum_order_qty | `global.v_prods_quantity_order_min` |
| order_increment_qty | `global.v_prods_quantity_order_units` |
| horizontal_half_drop | `global.HORIZONTAL_HALFDROP` |
| lead_time_days | `global.Lead-Time-in-Days` |
| ship_from | `global.Ship-From` |
| memo_sample_available | `global.Memo-Sample-Available` |
| clean_code | `global.Clean-Code` |
| durability | `global.Durability` |
| weight | `global.Weight` |
| barcode | variant.barcode |
| qty_on_hand | inventory (not metafield) |
| notes | `custom.vendor_notes` |

## Files

```
/Users/stevestudio2/Projects/kravet-sheet-sync-2026-04-20/
├── kravet_sheet_2026-04-20.csv            # original download, 44,572 rows
├── kravet_archive_2026-04-20.csv          # 357 rows to archive
├── kravet_new_2026-04-20.csv              # 8,261 new DWKK prep rows
├── kravet_update_2026-04-20.csv           # 4,570 retail-price drift rows
├── 01_stage_sheet.sql                     # CSV → PG staging table
├── 02_build_diff.sql                      # initial diff (wallcover-filtered match)
├── 02_build_diff_v2.sql                   # match against full sheet
├── 03_renorm_and_rediff.sql               # final: hyphen→dot normalization
└── SUMMARY.md                             # this file
```

PG artifacts on Kamatera (`dw_unified`):
- `kravet_sheet_import_2026_04_20` (staging)
- `kravet_diff_archive_2026_04_20`
- `kravet_diff_new_2026_04_20`
- `kravet_diff_update_2026_04_20`

## Phase 1 scope (wallpaper / murals / wallcovering only)

All 8,261 NEW-bucket rows are already scoped to sheet Use='WALLCOVERING'. Type1 breakdown within that set: PRINT 5,159 · VINYL/FAUX LEATHER 1,523 · GRASSCLOTH 1,103 · LINEN 200 · SILK 179 · MURALS/PANELS 133 · TEXTILE 126 · SPECIALTY 82 · FOILS 54 · ACOUSTIC 40 · BORDERS 19 · WEAVE 16 · EMBROIDERY 13 · WOOL 7 · (blank) 1,530. No fabric Use categories are included.

## Full Monty enrichment status (enrichment_tracking.vendor_code='kravet')

Joined by normalized mfr_sku:

| Bucket | Rows | P1 scraped | P3 AI | P5 VCC | P6 rooms | P9 repeats | P10 match | Ready |
|---|---|---|---|---|---|---|---|---|
| Live DWKK on Shopify | 4,933 | 100% | 100% | 100% | **36%** | **0%** | **0%** | 100% |
| NEW bucket (to import) | 8,261 | **6%** | **6%** | **0%** | **0%** | 0% | 0% | **0.01%** |

**Answer to "have these gone through the Full Monty":**
- **NEW 8,261** → No. 7,756 of them (94%) have never even been scraped. Only 505 have partial Full Monty through Phase 7 (spin), and only **1** has reached "ready" state. They need to be scraped + run through the Update SKU agent (port 9750) before push to Shopify.
- **Existing live 4,927** → Mostly yes for phases 1-5 and 7-8, but: Phase 6 (room settings) done for only 36%, Phase 9 (repeat classification) and Phase 10 (duplicate match) done for **0 products**. If "Full Monty" means all 10 phases green, none of the Kravet catalog is fully complete.

## Next step (not executed per dry-run directive)

Per your choice "Prepare CSVs only" — no Shopify writes were performed. When you're ready to push:
1. **Before the new-import push**: run the NEW 8,261 through Full Monty. The 7,756 unscraped need phase 1 (scrape) → phase 2 (specs) → phase 3 (AI) → phase 4 (Silas) → phase 5 (VCC). That's the existing Update SKU agent at `http://45.61.58.125:9750/`. Submit as batch: `POST /api/batch {prefix:"DWKK", skus:[...]}`. Budget accordingly — at current pipeline throughput this is days of work, not minutes.
2. **Archive pass**: iterate `kravet_archive_2026-04-20.csv`, PUT `product.status=archived` on each `shopify_product_id`. No Full Monty needed.
3. **New pass**: after Full Monty finishes on each batch, register DWKK- via sku-registry, create Shopify product with Sample variant, apply draft/active gate, set MAP as price + computed cost, write MFR SKU metafields.
4. **Backfill Phase 6 room settings** on the existing 3,154 live DWKK products missing them (orthogonal to this import).
5. **Update pass** (optional): reprice matched products to current sheet MAP.