← back to Designer Wallcoverings
bucketB-longtail: add shopify_products.mfr_sku BRIDGE to unlock priced catalogs (Kravet 0->1231, GP&J Baker 0->168, Marburg 0->90) that recovered 0 last pass for lack of a join key
46d518bf42b294558506d3ea0e4c46a5b0839c36 · 2026-06-22 17:29:15 -0700 · Steve
Files touched
M shopify/scripts/bucketB-longtail-roll-variants.js
Diff
commit 46d518bf42b294558506d3ea0e4c46a5b0839c36
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jun 22 17:29:15 2026 -0700
bucketB-longtail: add shopify_products.mfr_sku BRIDGE to unlock priced catalogs (Kravet 0->1231, GP&J Baker 0->168, Marburg 0->90) that recovered 0 last pass for lack of a join key
---
shopify/scripts/bucketB-longtail-roll-variants.js | 83 ++++++++++++++++-------
1 file changed, 58 insertions(+), 25 deletions(-)
diff --git a/shopify/scripts/bucketB-longtail-roll-variants.js b/shopify/scripts/bucketB-longtail-roll-variants.js
index c88a6dab..e064604f 100644
--- a/shopify/scripts/bucketB-longtail-roll-variants.js
+++ b/shopify/scripts/bucketB-longtail-roll-variants.js
@@ -170,14 +170,39 @@ function enrichVendor(table, cols, isKravet, work) {
// single-suffix-strip base: drop one trailing -<token> (e.g. R13041-5 -> R13041)
// candidate keys to match catalog.dw_sku against: base_sku, suffix-stripped base
+ //
+ // *** mfr_sku BRIDGE (2026-06-22 — load-bearing change) ***
+ // The worklist itself carries NO mfr_sku, so last pass's mfr join was dead. We bridge
+ // each worklist row to the LIVE mfr_sku in shopify_products (backfilled ~92% by the
+ // fmpro recrawl), keyed on the GID tail = split_part(shopify_id,'/',5). The catalogs
+ // for the big priced lines (Kravet/Lee Jofa/GP&J Baker/Hollywood/China Seas/Marburg/
+ // Sandberg/Designers Guild/Maya) key on mfr_sku (their dw_sku is the DW SKU, which the
+ // vendor-SKU worklist base never matches) — so without this bridge they recover 0.
+ // Match is EXACT case-insensitive + a single suffix-strip on mfr_sku. NO fuzzy/pattern.
const sql = `
-WITH wl(shopify_id, base_sku, mfr_sku) AS ( VALUES
+WITH wl(shopify_id, base_sku, wl_mfr) AS ( VALUES
${values}
),
+-- bridge: pull the live mfr_sku from shopify_products by the GID tail
+sp AS (
+ SELECT split_part(shopify_id,'/',5) AS gid_tail, NULLIF(btrim(mfr_sku),'') AS live_mfr
+ FROM shopify_products
+ WHERE mfr_sku IS NOT NULL AND btrim(mfr_sku) <> ''
+),
wlk AS (
- SELECT shopify_id, base_sku, mfr_sku,
- regexp_replace(base_sku, '-[A-Za-z0-9]+$', '') AS base_strip
- FROM wl
+ SELECT w.shopify_id, w.base_sku,
+ -- worklist's own mfr wins if present, else the live bridge fills it
+ COALESCE(NULLIF(btrim(w.wl_mfr),''), s.live_mfr) AS mfr_sku,
+ (w.wl_mfr IS NULL AND s.live_mfr IS NOT NULL) AS mfr_via_bridge,
+ regexp_replace(w.base_sku, '-[A-Za-z0-9]+$', '') AS base_strip
+ FROM wl w
+ LEFT JOIN sp s ON s.gid_tail = w.shopify_id
+),
+wlk2 AS (
+ SELECT *,
+ lower(mfr_sku) AS mfr_lc,
+ lower(regexp_replace(mfr_sku, '-[A-Za-z0-9]+$', '')) AS mfr_strip_lc
+ FROM wlk
),
-- freshest catalog row per dw_sku and per mfr_sku (dedup duplicate dw_sku rows)
cat AS (
@@ -192,36 +217,40 @@ cat_by_dw AS (
FROM cat WHERE dw_sku IS NOT NULL AND dw_sku <> ''
ORDER BY dw_sku, ctid DESC
),
+-- catalog mfr keyed case-insensitively for the bridge join
cat_by_mfr AS (
- SELECT DISTINCT ON (mfr_sku) mfr_sku, rec_price, disc, cwidth, cimg
+ SELECT DISTINCT ON (lower(mfr_sku)) lower(mfr_sku) AS mfr_lc, rec_price, disc, cwidth, cimg
FROM cat WHERE mfr_sku IS NOT NULL AND mfr_sku <> ''
- ORDER BY mfr_sku, ctid DESC
+ ORDER BY lower(mfr_sku), ctid DESC
),
-- count DISTINCT prices a worklist row could see across all match paths (ambiguity guard)
matches AS (
- SELECT w.shopify_id,
- d_exact.rec_price AS p_dw, d_exact.disc AS disc_dw, d_exact.cwidth AS w_dw, d_exact.cimg AS i_dw,
- m_exact.rec_price AS p_mfr, m_exact.disc AS disc_mfr, m_exact.cwidth AS w_mfr, m_exact.cimg AS i_mfr,
- d_strip.rec_price AS p_strip, d_strip.disc AS disc_str, d_strip.cwidth AS w_str, d_strip.cimg AS i_str
- FROM wlk w
+ SELECT w.shopify_id, w.mfr_via_bridge,
+ d_exact.rec_price AS p_dw, d_exact.disc AS disc_dw, d_exact.cwidth AS w_dw, d_exact.cimg AS i_dw,
+ m_exact.rec_price AS p_mfr, m_exact.disc AS disc_mfr, m_exact.cwidth AS w_mfr, m_exact.cimg AS i_mfr,
+ d_strip.rec_price AS p_strip, d_strip.disc AS disc_str, d_strip.cwidth AS w_str, d_strip.cimg AS i_str,
+ m_strip.rec_price AS p_mstrip, m_strip.disc AS disc_mstr, m_strip.cwidth AS w_mstr, m_strip.cimg AS i_mstr
+ FROM wlk2 w
LEFT JOIN cat_by_dw d_exact ON d_exact.dw_sku = w.base_sku
- LEFT JOIN cat_by_mfr m_exact ON m_exact.mfr_sku = w.mfr_sku
- LEFT JOIN cat_by_dw d_strip ON d_strip.dw_sku = w.base_strip AND w.base_strip <> w.base_sku
+ LEFT JOIN cat_by_mfr m_exact ON m_exact.mfr_lc = w.mfr_lc AND w.mfr_lc IS NOT NULL AND w.mfr_lc <> ''
+ LEFT JOIN cat_by_dw d_strip ON d_strip.dw_sku = w.base_strip AND w.base_strip <> w.base_sku
+ LEFT JOIN cat_by_mfr m_strip ON m_strip.mfr_lc = w.mfr_strip_lc AND w.mfr_strip_lc IS NOT NULL AND w.mfr_strip_lc <> '' AND w.mfr_strip_lc <> w.mfr_lc
)
SELECT shopify_id,
- -- chosen price: exact-dw, then exact-mfr, then suffix-strip
- COALESCE(p_dw, p_mfr, p_strip) AS rec_price,
+ -- chosen price: exact-dw, exact-mfr, dw-suffixstrip, then mfr-suffixstrip
+ COALESCE(p_dw, p_mfr, p_strip, p_mstrip) AS rec_price,
-- chosen source label
CASE WHEN p_dw IS NOT NULL THEN 'catalog:dw_exact'
- WHEN p_mfr IS NOT NULL THEN 'catalog:mfr_exact'
+ WHEN p_mfr IS NOT NULL THEN (CASE WHEN mfr_via_bridge THEN 'catalog:mfr_exact_bridge' ELSE 'catalog:mfr_exact' END)
WHEN p_strip IS NOT NULL THEN 'catalog:dw_suffixstrip'
+ WHEN p_mstrip IS NOT NULL THEN (CASE WHEN mfr_via_bridge THEN 'catalog:mfr_suffixstrip_bridge' ELSE 'catalog:mfr_suffixstrip' END)
ELSE 'none' END AS price_source,
-- discontinued if the CHOSEN match path is discontinued
- COALESCE(disc_dw, disc_mfr, disc_str, false) AS disc,
- COALESCE(w_dw, w_mfr, w_str) AS cwidth,
- COALESCE(i_dw, i_mfr, i_str) AS cimg,
- -- ambiguity: the distinct non-null prices visible across the three match paths
- ( SELECT count(DISTINCT x) FROM (VALUES (p_dw),(p_mfr),(p_strip)) v(x) WHERE x IS NOT NULL ) AS price_variants
+ COALESCE(disc_dw, disc_mfr, disc_str, disc_mstr, false) AS disc,
+ COALESCE(w_dw, w_mfr, w_str, w_mstr) AS cwidth,
+ COALESCE(i_dw, i_mfr, i_str, i_mstr) AS cimg,
+ -- ambiguity: the distinct non-null prices visible across the four match paths
+ ( SELECT count(DISTINCT x) FROM (VALUES (p_dw),(p_mfr),(p_strip),(p_mstrip)) v(x) WHERE x IS NOT NULL ) AS price_variants
FROM matches;`;
const out = pgQuery(sql).trim();
if (!out) return [];
@@ -322,7 +351,7 @@ function loadWorklist() {
for (const vc of vendorCfgs) {
const work = allWork.filter(w => w.vendor === vc.vendor);
const pv = { vendor: vc.vendor, table: vc.table, kravet: !!vc.kravet, bRows: work.length,
- rolls: 0, needsPrice: 0, discontinued: 0, ambiguous: 0, alreadyRoll: 0, failed: 0 };
+ rolls: 0, rollsViaBridge: 0, needsPrice: 0, discontinued: 0, ambiguous: 0, alreadyRoll: 0, failed: 0 };
report.perVendor[vc.vendor] = pv;
if (!work.length) { console.log(`— ${vc.vendor.padEnd(26)} 0 worklist rows`); continue; }
@@ -343,10 +372,10 @@ function loadWorklist() {
if (e.price_variants > 1) { // ambiguous multi-price collision → never guess
unpricedAll.push({ ...row, price_source: 'ambiguous' }); pv.ambiguous++; pv.needsPrice++; continue;
}
- if (e.price_source.startsWith('catalog') && e.rec_price > 0) { pricedAll.push(row); pv.rolls++; }
+ if (e.price_source.startsWith('catalog') && e.rec_price > 0) { pricedAll.push(row); pv.rolls++; if (/_bridge$/.test(e.price_source)) pv.rollsViaBridge++; }
else { unpricedAll.push(row); pv.needsPrice++; }
}
- console.log(`• ${vc.vendor.padEnd(26)} B=${String(pv.bRows).padStart(5)} priced=${String(pv.rolls).padStart(4)} needsPrice=${String(pv.needsPrice).padStart(5)} disc=${String(pv.discontinued).padStart(3)} ambig=${pv.ambiguous}`);
+ console.log(`• ${vc.vendor.padEnd(26)} B=${String(pv.bRows).padStart(5)} priced=${String(pv.rolls).padStart(4)} (bridge=${String(pv.rollsViaBridge).padStart(4)}) needsPrice=${String(pv.needsPrice).padStart(5)} disc=${String(pv.discontinued).padStart(3)} ambig=${pv.ambiguous}`);
}
console.log(`\n=== enrichment complete: ${pricedAll.length} priced(recoverable rolls) ${unpricedAll.length} needs-price ${discontinuedAll.length} discontinued ===\n`);
@@ -480,9 +509,13 @@ function loadWorklist() {
console.log(`\n=== SUMMARY (bucketB-longtail) ===`);
console.log(JSON.stringify(report.totals, null, 2));
console.log('\nper-vendor:');
+ let totalBridge = 0;
for (const v of Object.values(report.perVendor)) {
- console.log(` ${v.vendor.padEnd(26)} B=${String(v.bRows).padStart(5)} rolls=${String(v.rolls).padStart(4)} needsPrice=${String(v.needsPrice).padStart(5)} disc=${String(v.discontinued).padStart(3)} ambig=${v.ambiguous||0}${v.error?' ERR:'+v.error:''}`);
+ totalBridge += v.rollsViaBridge || 0;
+ console.log(` ${v.vendor.padEnd(26)} B=${String(v.bRows).padStart(5)} rolls=${String(v.rolls).padStart(4)} (bridge=${String(v.rollsViaBridge||0).padStart(4)}) needsPrice=${String(v.needsPrice).padStart(5)} disc=${String(v.discontinued).padStart(3)} ambig=${v.ambiguous||0}${v.error?' ERR:'+v.error:''}`);
}
+ report.totals.rollsViaBridge = totalBridge;
+ console.log(`\nmfr_sku BRIDGE unlocked ${totalBridge} of ${pricedAll.length} priced rows (rows that would have recovered 0 without the live-mfr join).`);
console.log('\nbudget:', JSON.stringify(report.budget));
if (REPORT) { fs.mkdirSync(path.dirname(REPORT), { recursive: true }); fs.writeFileSync(REPORT, JSON.stringify(report, null, 2)); console.log(`report -> ${REPORT}`); }
})();
← fe5c078b 5.1: REVERT horizontal filter (broke collection layout - nav
·
back to Designer Wallcoverings
·
Correct MOQ step: double-roll (step 2) is the exception not 98797c9e →