← back to Designer Wallcoverings
Kravet master price loader: WHLS cost + MAP from GDrive master into kravet_master_price
5ba2c38730d860151e615d2e7f04bc26b0aa5304 · 2026-06-11 15:56:51 -0700 · SteveStudio2
Files touched
A shopify/scripts/cadence/kravet-master-loader.py
Diff
commit 5ba2c38730d860151e615d2e7f04bc26b0aa5304
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Thu Jun 11 15:56:51 2026 -0700
Kravet master price loader: WHLS cost + MAP from GDrive master into kravet_master_price
---
shopify/scripts/cadence/kravet-master-loader.py | 145 ++++++++++++++++++++++++
1 file changed, 145 insertions(+)
diff --git a/shopify/scripts/cadence/kravet-master-loader.py b/shopify/scripts/cadence/kravet-master-loader.py
new file mode 100644
index 00000000..2f59de7a
--- /dev/null
+++ b/shopify/scripts/cadence/kravet-master-loader.py
@@ -0,0 +1,145 @@
+#!/usr/bin/env python3
+"""
+kravet-master-loader.py — load Kravet/Lee-Jofa/Cole&Son WHLS cost + MAP + retail from the
+authoritative GDrive master exports into dw_unified.kravet_master_price.
+
+WHY: "always look in GDrive first." The Kravet/LJ master exports carry, per SKU:
+ - Item (mfr_sku, e.g. GWP-3716.155.0 / 100/1001.CS.0)
+ - WHLS Price (wholesale COST)
+ - MAP (minimum advertised price)
+ - Variant Price (the DW retail historically used)
+ - Unit of measure (we keep ROLL rows — these are sold-per-roll wallcoverings)
+This is the price source for the sample-only Kravet-family wallpapers that have no roll
+variant yet. We prefer master WHLS cost (run through the cost/0.65/0.85 formula, floored at
+MAP) and keep retail as a last-resort fallback.
+
+SAFE: dry-run by default. --commit upserts into its OWN new table kravet_master_price.
+Never touches *_catalog, never touches Shopify.
+
+Usage:
+ python3 kravet-master-loader.py # DRY-RUN (parse + coverage report)
+ python3 kravet-master-loader.py --commit # write kravet_master_price
+"""
+import argparse, glob, os, subprocess, sys, tempfile
+
+MARGIN = 0.65 * 0.85 # retail = cost / 0.65 / 0.85
+
+# Each source: gdrive path + sheet + 0-based column indices (verified 2026-06-11).
+SOURCES = [
+ {
+ 'gdrive': 'gdrive:Spreadsheets/OLD_MASTER Coding for KRAVET DATA - kravet／lj - 2021 (DWTK-500000-538418).xlsx',
+ 'sheet': '2022(UL)', 'rank': 2, # most recent
+ 'c_item': 56, 'c_whls': 66, 'c_map': 106, 'c_retail': 19, 'c_unit': 67,
+ 'c_brand': 59, 'c_pattern': 57, 'c_color': 58,
+ },
+ {
+ 'gdrive': 'gdrive:Spreadsheets/Kravet - Export in Increments of 5000.xlsx',
+ 'sheet': 'FInal - CODING', 'rank': 1,
+ 'c_item': 71, 'c_whls': 81, 'c_map': None, 'c_retail': 19, 'c_unit': 82,
+ 'c_brand': 74, 'c_pattern': 72, 'c_color': 73,
+ },
+]
+
+def num(v):
+ if v in (None, ''):
+ return None
+ try:
+ return float(str(v).replace('$', '').replace(',', '').strip())
+ except (ValueError, TypeError):
+ return None
+
+def cell(row, i):
+ if i is None or i >= len(row):
+ return None
+ return row[i]
+
+def main():
+ ap = argparse.ArgumentParser()
+ ap.add_argument('--commit', action='store_true')
+ ap.add_argument('--sample', type=int, default=8)
+ args = ap.parse_args()
+ import openpyxl
+
+ # mfr_sku -> dict(cost, map, retail, brand, pattern, color, src, rank)
+ recs = {}
+ for src in SOURCES:
+ tmp = tempfile.mkdtemp(prefix='kml-')
+ r = subprocess.run(['rclone', 'copy', src['gdrive'], tmp], capture_output=True, text=True)
+ files = glob.glob(os.path.join(tmp, '*.xlsx'))
+ if not files:
+ print(f" WARN could not fetch {src['gdrive']}: {r.stderr[:160]}"); continue
+ wb = openpyxl.load_workbook(files[0], read_only=True, data_only=True)
+ if src['sheet'] not in wb.sheetnames:
+ print(f" WARN sheet {src['sheet']!r} missing in {src['gdrive']}"); continue
+ ws = wb[src['sheet']]
+ it = ws.iter_rows(values_only=True); next(it, None) # skip header
+ n_roll = 0
+ for row in it:
+ unit = str(cell(row, src['c_unit']) or '')
+ if 'ROLL' not in unit.upper():
+ continue
+ sku = cell(row, src['c_item'])
+ if not sku:
+ continue
+ sku = str(sku).strip()
+ cost = num(cell(row, src['c_whls']))
+ mp = num(cell(row, src['c_map']))
+ retail = num(cell(row, src['c_retail']))
+ if not (cost or retail):
+ continue
+ n_roll += 1
+ prev = recs.get(sku)
+ # prefer the row with a cost; then the higher-rank (newer) source
+ better = (prev is None
+ or (cost and not prev['cost'])
+ or (src['rank'] > prev['rank'] and bool(cost) == bool(prev['cost'])))
+ if better:
+ recs[sku] = {
+ 'cost': cost, 'map': mp, 'retail': retail, 'rank': src['rank'],
+ 'brand': str(cell(row, src['c_brand']) or '')[:80],
+ 'pattern': str(cell(row, src['c_pattern']) or '')[:120],
+ 'color': str(cell(row, src['c_color']) or '')[:80],
+ 'src': os.path.basename(src['gdrive'])[:60],
+ }
+ print(f" [{src['sheet']}] ROLL rows scanned: {n_roll}")
+
+ withcost = sum(1 for v in recs.values() if v['cost'])
+ print(f"\nmaster ROLL SKUs: {len(recs)} (with WHLS cost: {withcost}, retail-only: {len(recs)-withcost})")
+ print("samples (cost -> formula retail, MAP floor):")
+ for sku, v in list(recs.items())[:args.sample]:
+ formula = round(v['cost'] / MARGIN, 2) if v['cost'] else None
+ sell = max(formula or 0, v['map'] or 0, v['retail'] or 0 if not v['cost'] else 0)
+ print(f" {sku:<18} cost {v['cost']} map {v['map']} retail {v['retail']} -> formula {formula} [{v['brand']}]")
+
+ if not args.commit:
+ print("\nDRY-RUN — no writes. Re-run with --commit to load kravet_master_price.")
+ return
+
+ # write TSV + load
+ tmp = tempfile.mkdtemp(prefix='kml-load-')
+ tsv = os.path.join(tmp, 'm.tsv')
+ with open(tsv, 'w') as fh:
+ for sku, v in recs.items():
+ fh.write('\t'.join('' if x is None else str(x) for x in
+ [sku, v['cost'], v['map'], v['retail'], v['brand'], v['pattern'], v['color'], v['src']]) + '\n')
+ ddl = """
+ CREATE TABLE IF NOT EXISTS kravet_master_price (
+ mfr_sku TEXT PRIMARY KEY, whls_cost NUMERIC, map_price NUMERIC, retail_price NUMERIC,
+ brand TEXT, pattern TEXT, color TEXT, source TEXT, loaded_at TIMESTAMPTZ DEFAULT now());
+ TRUNCATE kravet_master_price;
+ CREATE TEMP TABLE _m(mfr_sku text, whls_cost numeric, map_price numeric, retail_price numeric,
+ brand text, pattern text, color text, source text);
+ \\copy _m FROM '%s'
+ INSERT INTO kravet_master_price(mfr_sku,whls_cost,map_price,retail_price,brand,pattern,color,source)
+ SELECT mfr_sku,whls_cost,map_price,retail_price,brand,pattern,color,source FROM _m
+ ON CONFLICT (mfr_sku) DO NOTHING;
+ SELECT count(*) FROM kravet_master_price;
+ """ % tsv
+ w = subprocess.run(['psql', '-d', 'dw_unified', '-v', 'ON_ERROR_STOP=1'],
+ input=ddl, capture_output=True, text=True)
+ if w.returncode != 0:
+ sys.exit(f"COMMIT failed: {w.stderr[:400]}")
+ print(f"\n✅ COMMITTED kravet_master_price rows: {w.stdout.strip().splitlines()[-1]}")
+
+if __name__ == '__main__':
+ main()
← 191ba013 schu-reimport: heartbeat to CNCP Looping-LIVE each tick + ma
·
back to Designer Wallcoverings
·
Add Kravet roll-variant adder: idempotent, MAP-priced, audit 4e4cff4e →