← back to Designer Wallcoverings
Add gdrive-price-loader.py — load vendor cost from authoritative GDrive price lists
4c2234915fda800ce72cecc8a1cf2af838fe4107 · 2026-06-11 11:41:26 -0700 · SteveStudio2
Always-look-in-gdrive-first cost engine. Per-vendor explicit column mapping (no guessing).
Kravet: cost=NEW WHLS incl tariff, sell=max(cost/0.65/0.85, NEW MAP). Dry-run default;
--commit UPDATEs only matched existing catalog rows' cost/sell. 8191/8191 Kravet matches.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
A shopify/scripts/cadence/gdrive-price-loader.py
Diff
commit 4c2234915fda800ce72cecc8a1cf2af838fe4107
Author: SteveStudio2 <stevestudio2@SteveStudio2s-Mac-Studio.local>
Date: Thu Jun 11 11:41:26 2026 -0700
Add gdrive-price-loader.py — load vendor cost from authoritative GDrive price lists
Always-look-in-gdrive-first cost engine. Per-vendor explicit column mapping (no guessing).
Kravet: cost=NEW WHLS incl tariff, sell=max(cost/0.65/0.85, NEW MAP). Dry-run default;
--commit UPDATEs only matched existing catalog rows' cost/sell. 8191/8191 Kravet matches.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
shopify/scripts/cadence/gdrive-price-loader.py | 146 +++++++++++++++++++++++++
1 file changed, 146 insertions(+)
diff --git a/shopify/scripts/cadence/gdrive-price-loader.py b/shopify/scripts/cadence/gdrive-price-loader.py
new file mode 100644
index 00000000..a4ec270c
--- /dev/null
+++ b/shopify/scripts/cadence/gdrive-price-loader.py
@@ -0,0 +1,146 @@
+#!/usr/bin/env python3
+"""
+gdrive-price-loader.py — load vendor COST from authoritative GDrive price lists into dw_unified.
+
+WHY: Steve's rule "always look in GDrive first" — DW's signed wholesale agreements + current
+price lists live in Google Drive (rclone remote `gdrive:`), and they match the catalog tables
+exactly (e.g. Kravet's 2026-01 list = 8,191 exact SKU matches). This is the authoritative,
+non-guessed cost source — far better than scraping. Feeds the CNCP Price Coverage tracker.
+
+SAFE: dry-run by default (parses + reports, NO writes). --commit UPDATEs only the cost/sell
+columns of EXISTING catalog rows it matches by mfr_sku; never inserts, never touches Shopify.
+
+Per-vendor mapping is explicit (never guess a cost column — money). Add a vendor to VENDORS.
+
+Usage:
+ python3 gdrive-price-loader.py --vendor Kravet # DRY-RUN (default)
+ python3 gdrive-price-loader.py --vendor Kravet --commit # write cost/sell into dw_unified
+ python3 gdrive-price-loader.py --vendor Kravet --sample 10
+"""
+import argparse, glob, os, subprocess, sys, tempfile
+
+MARGIN = 0.65 * 0.85 # retail = cost / 0.65 / 0.85 (35% margin, 15% designer buffer)
+
+def retail_from_cost(cost):
+ return round(cost / MARGIN, 2)
+
+# Explicit per-vendor config. cost_basis/map come straight from Steve's confirmed mapping.
+VENDORS = {
+ 'Kravet': {
+ 'gdrive': 'gdrive:Kravet Price list 1-20-26.xlsx',
+ 'sheet': 'Sheet1',
+ 'sku_col': 'Item',
+ 'cost_col': 'NEW Wholesale Price (Incl Tariff)', # Steve 2026-06-11: cost = NEW WHLS incl tariff
+ 'map_col': 'NEW MAP (Incl Tariff)', # sell floored at MAP
+ 'eff_col': 'New Price effective date',
+ 'table': 'kravet_catalog',
+ 'table_sku': 'mfr_sku',
+ 'set_cost': 'cost_price',
+ 'set_sell': 'dw_sell_price',
+ 'set_eff': 'price_effective_date',
+ },
+}
+
+def col_index(header, name):
+ want = name.strip().lower()
+ for i, h in enumerate(header):
+ if h and str(h).strip().lower() == want:
+ return i
+ # fall back to startswith (xlsx headers sometimes truncate)
+ for i, h in enumerate(header):
+ if h and str(h).strip().lower().startswith(want[:18]):
+ return i
+ return None
+
+def num(v):
+ if v in (None, ''):
+ return None
+ try:
+ return float(str(v).replace('$', '').replace(',', '').strip())
+ except ValueError:
+ return None
+
+def main():
+ ap = argparse.ArgumentParser()
+ ap.add_argument('--vendor', required=True)
+ ap.add_argument('--commit', action='store_true')
+ ap.add_argument('--sample', type=int, default=8)
+ args = ap.parse_args()
+
+ cfg = VENDORS.get(args.vendor)
+ if not cfg:
+ sys.exit(f"no config for vendor {args.vendor!r}; add it to VENDORS")
+
+ import openpyxl
+ tmp = tempfile.mkdtemp(prefix='gpl-')
+ r = subprocess.run(['rclone', 'copy', cfg['gdrive'], tmp], capture_output=True, text=True)
+ files = glob.glob(os.path.join(tmp, '*.xlsx'))
+ if not files:
+ sys.exit(f"rclone could not fetch {cfg['gdrive']}: {r.stderr[:300]}")
+
+ wb = openpyxl.load_workbook(files[0], read_only=True, data_only=True)
+ ws = wb[cfg['sheet']] if cfg.get('sheet') in wb.sheetnames else wb[wb.sheetnames[0]]
+ rows = ws.iter_rows(values_only=True)
+ header = next(rows)
+ ci_sku = col_index(header, cfg['sku_col'])
+ ci_cost = col_index(header, cfg['cost_col'])
+ ci_map = col_index(header, cfg['map_col']) if cfg.get('map_col') else None
+ ci_eff = col_index(header, cfg['eff_col']) if cfg.get('eff_col') else None
+ if ci_sku is None or ci_cost is None:
+ sys.exit(f"could not locate sku/cost columns in header: {[str(h) for h in header]}")
+
+ recs = {}
+ for row in rows:
+ sku = row[ci_sku]
+ cost = num(row[ci_cost])
+ if not sku or cost in (None, 0):
+ continue
+ sku = str(sku).strip()
+ mp = num(row[ci_map]) if ci_map is not None else None
+ sell = max(retail_from_cost(cost), mp or 0)
+ eff = row[ci_eff] if ci_eff is not None else None
+ eff = str(eff)[:10] if eff else None
+ recs[sku] = (cost, round(sell, 2), eff)
+
+ print(f"[{args.vendor}] price-list rows with cost: {len(recs)}")
+ print(f" cost basis: {cfg['cost_col']} | sell = max(cost/0.65/0.85, {cfg.get('map_col')})")
+ print(" samples:")
+ for sku, (c, s, e) in list(recs.items())[:args.sample]:
+ print(f" {sku:<18} cost ${c:<9} -> sell ${s:<9} (eff {e})")
+
+ # write the matched records to a TSV and load via psql temp table
+ tsv = os.path.join(tmp, 'load.tsv')
+ with open(tsv, 'w') as fh:
+ for sku, (c, s, e) in recs.items():
+ fh.write(f"{sku}\t{c}\t{s}\t{e or ''}\n")
+
+ match_sql = (
+ f"CREATE TEMP TABLE _pl(sku text, cost numeric, sell numeric, eff text); "
+ f"COPY _pl FROM '{tsv}'; "
+ f"SELECT count(*) FROM {cfg['table']} t JOIN _pl p "
+ f"ON upper(trim(t.{cfg['table_sku']}))=upper(trim(p.sku));"
+ )
+ n = subprocess.run(['psql', '-d', 'dw_unified', '-At', '-c', match_sql],
+ capture_output=True, text=True)
+ matched = (n.stdout or '0').strip().splitlines()[-1] if n.returncode == 0 else f"ERR {n.stderr[:200]}"
+ print(f" exact matches in {cfg['table']}: {matched}")
+
+ if not args.commit:
+ print("\nDRY-RUN — no writes. Re-run with --commit to load cost/sell into dw_unified.")
+ return
+
+ # COMMIT: update only existing matched rows' cost/sell/eff columns
+ eff_set = f", {cfg['set_eff']} = NULLIF(p.eff,'')::date" if cfg.get('set_eff') else ""
+ upd = (
+ f"CREATE TEMP TABLE _pl(sku text, cost numeric, sell numeric, eff text); "
+ f"COPY _pl FROM '{tsv}'; "
+ f"UPDATE {cfg['table']} t SET {cfg['set_cost']} = p.cost, {cfg['set_sell']} = p.sell{eff_set} "
+ f"FROM _pl p WHERE upper(trim(t.{cfg['table_sku']}))=upper(trim(p.sku)); "
+ )
+ w = subprocess.run(['psql', '-d', 'dw_unified', '-c', upd], capture_output=True, text=True)
+ if w.returncode != 0:
+ sys.exit(f"COMMIT failed: {w.stderr[:400]}")
+ print(f"\n✅ COMMITTED: {w.stdout.strip().splitlines()[-1]} (cost+sell loaded into {cfg['table']})")
+
+if __name__ == '__main__':
+ main()
← ef219ca4 Kravet-family feed importer (authorized daily SFTP, no scrap
·
back to Designer Wallcoverings
·
vendors.js: add Kravet (costExpr cost_price) — cost loaded f 2d60072d →