← back to Wolfgordon Crawl
Add WG re-onboarding reconciliation (dry-run, normalized-SKU primary join)
9f1964cb1c3f305dc7a4fadd9086fffb824966e9 · 2026-06-19 09:29:06 -0700 · Steve
Files touched
Diff
commit 9f1964cb1c3f305dc7a4fadd9086fffb824966e9
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jun 19 09:29:06 2026 -0700
Add WG re-onboarding reconciliation (dry-run, normalized-SKU primary join)
---
reconcile.js | 186 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 186 insertions(+)
diff --git a/reconcile.js b/reconcile.js
new file mode 100644
index 0000000..2acf3fb
--- /dev/null
+++ b/reconcile.js
@@ -0,0 +1,186 @@
+#!/usr/bin/env node
+// ============================================================================
+// Wolf Gordon re-onboarding reconciliation (DRY-RUN, read-only).
+//
+// The wolfgordon.com site fully recoded its manufacturer SKUs, so the 1,068
+// legacy Shopify products (DWWG-AD10336, DWWG-ZIG-8856, ...) no longer match
+// the fresh catalog by SKU (~2 matches out of 1,068). The only durable join is
+// PATTERN NAME + COLOR NAME.
+//
+// This script:
+// 1. Pulls the fresh wolf_gordon_catalog (truth) keyed by normalized
+// pattern|color -> sequential dw_sku.
+// 2. Pulls the 1,068 legacy shopify_products (vendor='Wolf Gordon'),
+// extracts each row's pattern + color (color from title after " - ",
+// fallback to pattern_name field), normalizes, and tries to match.
+// 3. Reports: matched (re-mappable to a sequential DW SKU), unmatched legacy
+// (discontinue candidates), net-new catalog rows (no legacy peer).
+//
+// WRITES NOTHING. Pure analysis. Run: node reconcile.js
+// ============================================================================
+
+const { pool } = require('./scraper-utils');
+
+// Normalize a name for matching: lowercase, strip ®/™, collapse whitespace,
+// drop punctuation that varies between sources.
+function norm(s) {
+ if (!s) return '';
+ return String(s)
+ .toLowerCase()
+ .replace(/[®™©]/g, '')
+ .replace(/&/g, '&')
+ .replace(/[^a-z0-9]+/g, ' ')
+ .replace(/\s+/g, ' ')
+ .trim();
+}
+
+// Normalize a MANUFACTURER SKU for matching. The site reformatted SKUs
+// (space<->hyphen) and the legacy DW SKUs carried a trailing "_8" sample-roll
+// suffix (e.g. "BOS-4399_8"). Strip ALL non-alphanumerics AND a trailing "8"
+// that came from "_8" so "CCT-2670"==="CCT 2670" and "BOS-4399_8"==="BOS 4399".
+function nsku(s) {
+ if (!s) return '';
+ let x = String(s).toLowerCase().replace(/_8$/, ''); // drop sample-roll suffix
+ return x.replace(/[^a-z0-9]+/g, ''); // strip space/hyphen
+}
+
+// Pull the color out of a legacy Shopify title.
+// Titles look like: "Sparta - Oak | Wolf Gordon Wallcoverings"
+// or "SM8008 | Wolf Gordon Wallcoverings" (no color) or
+// "Ambient Design - AD10335 | Wolf Gordon Wallcoverings" (color is a code).
+function legacyColorFromTitle(title) {
+ if (!title) return null;
+ const head = title.split('|')[0].trim(); // "Sparta - Oak"
+ const idx = head.indexOf(' - ');
+ if (idx === -1) return null;
+ return head.slice(idx + 3).trim();
+}
+
+async function main() {
+ // ---- 1. Fresh catalog index ----
+ const cat = await pool.query(`
+ SELECT mfr_sku, dw_sku, pattern_name, color_name, collection
+ FROM wolf_gordon_catalog
+ `);
+
+ // Map normalized "pattern|color" -> [rows]. Collisions are tracked.
+ const catByPC = new Map(); // pattern+color
+ const catByPattern = new Map(); // pattern only (for color-blind fallback diag)
+ for (const r of cat.rows) {
+ const p = norm(r.pattern_name);
+ const c = norm(r.color_name);
+ const keyPC = `${p}|${c}`;
+ if (!catByPC.has(keyPC)) catByPC.set(keyPC, []);
+ catByPC.get(keyPC).push(r);
+ if (!catByPattern.has(p)) catByPattern.set(p, []);
+ catByPattern.get(p).push(r);
+ }
+
+ // ---- 2. Legacy Shopify rows ----
+ const leg = await pool.query(`
+ SELECT sku, mfr_sku, pattern_name, title, status
+ FROM shopify_products
+ WHERE vendor = 'Wolf Gordon'
+ `);
+
+ // Normalized-SKU index of the fresh catalog (primary join).
+ const catByNsku = new Map();
+ for (const r of cat.rows) {
+ const k = nsku(r.mfr_sku);
+ if (k && !catByNsku.has(k)) catByNsku.set(k, r);
+ }
+
+ const matchedBySku = []; // normalized mfr_sku aligns (PRIMARY, strongest)
+ const matchedByPC = []; // pattern+color hit a unique catalog row
+ const matchedByPCMulti = [];// pattern+color hit MULTIPLE catalog rows (ambiguous)
+ const unmatched = []; // no catalog peer -> discontinue candidate
+
+ for (const r of leg.rows) {
+ const legColor = legacyColorFromTitle(r.title);
+ const p = norm(r.pattern_name);
+ const c = norm(legColor);
+ const keyPC = `${p}|${c}`;
+
+ // (1) PRIMARY: normalized manufacturer SKU survivor?
+ const skuHit = catByNsku.get(nsku(r.mfr_sku));
+ if (skuHit) {
+ matchedBySku.push({ legacy: r, legColor, target: skuHit });
+ continue;
+ }
+
+ // (2) FALLBACK: pattern + color
+ if (c && catByPC.has(keyPC)) {
+ const hits = catByPC.get(keyPC);
+ if (hits.length === 1) {
+ matchedByPC.push({ legacy: r, legColor, target: hits[0] });
+ } else {
+ matchedByPCMulti.push({ legacy: r, legColor, targets: hits });
+ }
+ continue;
+ }
+
+ unmatched.push({ legacy: r, legColor });
+ }
+
+ // ---- 3. Net-new catalog rows (no legacy peer matched into them) ----
+ // A catalog row is "covered" if some legacy row matched it (by PC or sku).
+ const coveredDwSku = new Set();
+ for (const m of matchedBySku) coveredDwSku.add(m.target.mfr_sku);
+ for (const m of matchedByPC) coveredDwSku.add(m.target.mfr_sku);
+ for (const m of matchedByPCMulti) for (const t of m.targets) coveredDwSku.add(t.mfr_sku);
+ let netNew = 0;
+ for (const r of cat.rows) {
+ if (!coveredDwSku.has(r.mfr_sku)) netNew++;
+ }
+
+ // ---- Report ----
+ const line = '='.repeat(74);
+ console.log(line);
+ console.log(' WOLF GORDON RECONCILIATION — DRY RUN (read-only, no writes)');
+ console.log(line);
+ console.log(` Fresh catalog rows: ${cat.rows.length}`);
+ console.log(` Legacy Shopify WG products: ${leg.rows.length}`);
+ console.log(line);
+ console.log(' PRIMARY KEY: normalized mfr_sku (strip space/hyphen + trailing _8)');
+ console.log(' FALLBACK: normalized(pattern_name)+"|"+normalized(title color)');
+ console.log(line);
+ console.log(` [A] Matched by normalized mfr_sku: ${matchedBySku.length} <- safely re-mappable (PRIMARY)`);
+ console.log(` [B] Matched by pattern+color (UNIQUE): ${matchedByPC.length} <- safely re-mappable (fallback)`);
+ console.log(` [C] Matched by pattern+color (AMBIGUOUS): ${matchedByPCMulti.length} <- need a tiebreak`);
+ console.log(` [D] Unmatched legacy (discontinue cand.): ${unmatched.length}`);
+ console.log(` --------------------------------------------------`);
+ console.log(` legacy total = A+B+C+D = ${matchedBySku.length + matchedByPC.length + matchedByPCMulti.length + unmatched.length}`);
+ console.log(line);
+ console.log(` [E] Net-new catalog rows (no legacy peer): ${netNew}`);
+ console.log(line);
+
+ console.log('\n --- SAMPLE [A] normalized-SKU match (legacy DW SKU -> new sequential DW SKU) ---');
+ for (const m of matchedBySku.slice(0, 12)) {
+ console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(34)} -> ${m.target.dw_sku || '(no dw_sku yet)'} [${m.target.mfr_sku}]`);
+ }
+
+ console.log('\n --- SAMPLE [B] pattern+color match (legacy SKU -> new sequential DW SKU) ---');
+ for (const m of matchedByPC.slice(0, 12)) {
+ console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(34)} -> ${m.target.dw_sku || '(no dw_sku yet)'} [${m.target.mfr_sku}]`);
+ }
+
+ console.log('\n --- SAMPLE [C] ambiguous pattern+color (>1 catalog hit) ---');
+ for (const m of matchedByPCMulti.slice(0, 8)) {
+ console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'')).padEnd(30)} -> ${m.targets.map(t=>t.dw_sku||t.mfr_sku).join(', ')}`);
+ }
+
+ console.log('\n --- SAMPLE [D] unmatched legacy (discontinue candidates) ---');
+ for (const m of unmatched.slice(0, 15)) {
+ console.log(` ${m.legacy.sku.padEnd(16)} ${(m.legacy.pattern_name+' / '+(m.legColor||'(no color)')).padEnd(34)} status=${m.legacy.status}`);
+ }
+
+ // breakdown of unmatched by status (archived/deleted are expected to not re-map)
+ const ubs = {};
+ for (const m of unmatched) ubs[m.legacy.status] = (ubs[m.legacy.status]||0)+1;
+ console.log('\n --- [D] unmatched by Shopify status ---');
+ for (const k of Object.keys(ubs)) console.log(` ${k.padEnd(22)} ${ubs[k]}`);
+
+ await pool.end();
+}
+
+main().catch(e => { console.error(e); process.exit(1); });
← 946e786 Fix: send User-Agent on sitemap fetch (WG 403s UA-less reque
·
back to Wolfgordon Crawl
·
Assign sequential DW SKUs to 106 NULL WG catalog rows (DWWG- 8c28e07 →