← back to Dw Yolo Loop
Google feed scoping: live-Shopify-driven eligibility gate + calibration proof + SCOPE.md
5de672646abe699d1adccf189f37ee2466d52c13 · 2026-06-15 08:07:16 -0700 · Steve Abrams
- calibrate-price.mjs proves dw_unified mirror price is stale (NULL/$4.25 -> live roll $34-$205)
- feed-eligibility.mjs: read-only live-Shopify gate (roll-price floor, sample segregation,
private-label leak guard, Kravet MAP floor); emits feed-clean.tsv + exclusions + report
- sample-only products (only -sample variant) correctly excluded from Google feed
- nothing submitted to Google; opt-out + channel publish remain Steve-gated
Files touched
M .gitignoreA scripts/google-feed/SCOPE.mdA scripts/google-feed/calibrate-price.mjsA scripts/google-feed/feed-eligibility.mjs
Diff
commit 5de672646abe699d1adccf189f37ee2466d52c13
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jun 15 08:07:16 2026 -0700
Google feed scoping: live-Shopify-driven eligibility gate + calibration proof + SCOPE.md
- calibrate-price.mjs proves dw_unified mirror price is stale (NULL/$4.25 -> live roll $34-$205)
- feed-eligibility.mjs: read-only live-Shopify gate (roll-price floor, sample segregation,
private-label leak guard, Kravet MAP floor); emits feed-clean.tsv + exclusions + report
- sample-only products (only -sample variant) correctly excluded from Google feed
- nothing submitted to Google; opt-out + channel publish remain Steve-gated
---
.gitignore | 4 +
scripts/google-feed/SCOPE.md | 86 +++++++++++++
scripts/google-feed/calibrate-price.mjs | 65 ++++++++++
scripts/google-feed/feed-eligibility.mjs | 207 +++++++++++++++++++++++++++++++
4 files changed, 362 insertions(+)
diff --git a/.gitignore b/.gitignore
index b157c45..a9cd0c9 100644
--- a/.gitignore
+++ b/.gitignore
@@ -11,3 +11,7 @@ uploads/
*.bak
.newwall-ref/
audit.progress.log
+.env*
+data/google-feed/*.tsv
+data/google-feed/exclusions.json
+data/google-feed/run-full.*
diff --git a/scripts/google-feed/SCOPE.md b/scripts/google-feed/SCOPE.md
new file mode 100644
index 0000000..742abed
--- /dev/null
+++ b/scripts/google-feed/SCOPE.md
@@ -0,0 +1,86 @@
+# Clean Google Merchant Center feed — scope & plan
+
+**Merchant Center ID:** 146735262
+**Trigger:** Google's "automatic product additions" will auto-list ~30,229 crawled
+storefront products on **2026-06-18** unless opted out by **2026-06-17**.
+**Decision (Steve, 2026-06-15):** opt out of the blanket auto-add; build a
+deliberate, price-validated feed instead.
+
+---
+
+## 1. The core problem the feed solves
+
+Google's crawl-based auto-add reads the storefront product page. Every DW product
+carries two variants:
+
+| variant | example price | role |
+|---|---|---|
+| roll / sellable | $34.55–$205 | the real product |
+| `*-sample` | **$4.25** (memo) | swatch only |
+
+Shopify renders the page headline as "from $4.25", so a blanket crawl-add would
+**advertise wallcoverings at $4.25** — and for Kravet-umbrella lines that is also a
+**MAP violation** (Kravet enforces MAP = wholesale × 1.5; advertising below it risks
+the dealer account). The clean feed sends Google the **roll price**, never the sample.
+
+## 2. Architecture decision: LIVE-Shopify-driven (not the mirror)
+
+`scripts/google-feed/calibrate-price.mjs` proved the `dw_unified` mirror `price`
+column is stale/sample-contaminated:
+
+| mirror says | live Shopify roll price |
+|---|---|
+| NULL (41,987 active rows) | $34.55 … |
+| $4.25 (12,106 active rows) | $81–$205 … |
+
+15/15 of each sampled group had a correct live roll price. **Scoping off the mirror
+would wrongly drop ~54k correctly-priced products.** Therefore the feed price is read
+**live from Shopify** (the roll variant's price). The mirror is still used for the one
+thing it's good at: the Kravet **cost** for the MAP floor check (and even that is
+sparse → flagged "unverified", never a false exclude).
+
+## 3. Eligibility gate (per product)
+
+Built on the existing fail-closed validator (`scripts/preflight/rules.json`). A
+product is **eligible** iff ALL pass:
+
+- has a product image
+- has a **roll** (non-`-sample`) variant with a price **≥ floor ($10)**
+- roll price is not exactly $4.25 (sample-trap)
+- no **private-label upstream** name in title/vendor/tags/handle
+ (`command54, wallquest, chesapeake, nextwall, seabrook, brewster, desima, carlsten,
+ nicolette mayer`) — note: repped brand names (Kravet, Thibaut, Schumacher…) ARE
+ allowed; DW is an authorized dealer, so they belong in the `brand` field.
+- title contains no `wallpaper` / `unknown`
+- Kravet-family: roll price ≥ wholesale×1.5 when cost is known (else flagged, not dropped)
+
+**Sample-only products** (only a `-sample` variant, no roll) are correctly **excluded** —
+there is no sellable unit to advertise. This is the single largest exclusion class and
+it is *by design* (see MEMORY `425-sample-price-policy`).
+
+## 4. Output (local artifacts — nothing submitted)
+
+`node scripts/google-feed/feed-eligibility.mjs --floor=10` (read-only) writes:
+
+- `data/google-feed/feed-clean.tsv` — Merchant Center primary feed of eligible products
+ (roll price, public DW link, featured image, brand, condition, GTIN-less mpn).
+- `data/google-feed/exclusions.json` — every excluded product + reason (auditable).
+- `data/google-feed/report.json` — counts + reason breakdown + top eligible vendors.
+
+## 5. Recommended delivery path (Steve-gated — NOT yet done)
+
+Two options to get the clean set to Google:
+
+- **A — Shopify-native (recommended).** Keep the existing *Google & YouTube* sales
+ channel as the single feed; do not upload a competing custom feed (avoids duplicate
+ items). Use the eligibility report to **unpublish the excluded set from the Google
+ channel** (sample-only + sub-floor + leaks). Least new infra; uses authoritative live
+ data; inventory/availability stay automatic. Bulk channel-publish changes are Shopify
+ **writes → require Steve sign-off.**
+- **B — Supplemental TSV feed.** Upload `feed-clean.tsv` to Merchant Center as a feed.
+ Full control over exactly what lists, but must be hosted/refreshed and reconciled
+ against the Shopify app feed to avoid duplicates.
+
+**Status:** opt-out (the urgent 06-17 action) is Steve's UI step. This feed work is the
+deliberate follow-on. Submitting/publishing anything to Google is **gated** — these
+scripts only read Shopify and write local files.
diff --git a/scripts/google-feed/calibrate-price.mjs b/scripts/google-feed/calibrate-price.mjs
new file mode 100644
index 0000000..ddb0565
--- /dev/null
+++ b/scripts/google-feed/calibrate-price.mjs
@@ -0,0 +1,65 @@
+#!/usr/bin/env node
+// calibrate-price.mjs — READ-ONLY. Compare dw_unified mirror price vs LIVE Shopify
+// variant prices for a sample of products, to decide whether the clean Google feed
+// must be driven by live Shopify data (yes if the mirror is stale).
+// No writes. No status changes.
+import fs from 'node:fs';
+
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const VER = '2024-10';
+const envTxt = fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (envTxt.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1]?.trim();
+if (!TOKEN) { console.error('no token'); process.exit(1); }
+const URL = `https://${SHOP}/admin/api/${VER}/graphql.json`;
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+async function gql(query, variables) {
+ for (let attempt = 0; attempt < 6; attempt++) {
+ const res = await fetch(URL, { method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables }) });
+ const j = await res.json();
+ if (j.errors) { if (JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000*(attempt+1)); continue; } throw new Error(JSON.stringify(j.errors)); }
+ return j.data;
+ }
+ throw new Error('throttle');
+}
+
+const ids = fs.readFileSync('/tmp/cal_ids.csv','utf8').trim().split('\n')
+ .map(l => { const [grp, gid] = l.split(','); return { grp, gid }; }).filter(x => x.gid);
+
+const Q = `query($ids:[ID!]!){ nodes(ids:$ids){ ... on Product {
+ id title status vendor
+ variants(first:20){ nodes { title sku price } } } } }`;
+
+const isSample = (v) => /(sample|memo|swatch)/i.test([v.title, v.sku].join(' '));
+
+(async () => {
+ const data = await gql(Q, { ids: ids.map(x => x.gid) });
+ const byId = new Map(ids.map(x => [x.gid, x.grp]));
+ const rows = [];
+ for (const p of data.nodes) {
+ if (!p) continue;
+ const grp = byId.get(p.id);
+ const vs = p.variants.nodes;
+ const roll = vs.filter(v => !isSample(v)).map(v => parseFloat(v.price)).filter(Number.isFinite);
+ const samp = vs.filter(v => isSample(v)).map(v => parseFloat(v.price)).filter(Number.isFinite);
+ rows.push({ grp, id: p.id.split('/').pop(), title: (p.title||'').slice(0,42), vendor: p.vendor,
+ rollPrices: roll, samplePrices: samp,
+ rollMax: roll.length ? Math.max(...roll) : null });
+ }
+ console.log('mirror_grp | live_roll_max | live_sample | vendor | title');
+ console.log('-'.repeat(90));
+ for (const r of rows) {
+ console.log(`${r.grp.padEnd(5)} | ${String(r.rollMax ?? 'NONE').padStart(9)} | ${String(r.samplePrices[0] ?? '-').padStart(7)} | ${(r.vendor||'').slice(0,18).padEnd(18)} | ${r.title}`);
+ }
+ const nullGrp = rows.filter(r => r.grp === 'NULL');
+ const p425Grp = rows.filter(r => r.grp === 'P425');
+ const fmt = (g) => {
+ const withRoll = g.filter(r => r.rollMax && r.rollMax > 10).length;
+ return `${withRoll}/${g.length} have a live roll price > $10`;
+ };
+ console.log('-'.repeat(90));
+ console.log('mirror NULL :', fmt(nullGrp));
+ console.log('mirror $4.25 :', fmt(p425Grp));
+})();
diff --git a/scripts/google-feed/feed-eligibility.mjs b/scripts/google-feed/feed-eligibility.mjs
new file mode 100644
index 0000000..443a225
--- /dev/null
+++ b/scripts/google-feed/feed-eligibility.mjs
@@ -0,0 +1,207 @@
+#!/usr/bin/env node
+/**
+ * feed-eligibility.mjs — Build a CLEAN Google Merchant Center feed candidate set
+ * from LIVE Shopify data (not the stale dw_unified mirror), applying Steve's hard
+ * rules + a price floor + sample-variant segregation + private-label leak guard.
+ *
+ * WHY live-driven: calibrate-price.mjs proved the mirror `price` column is stale
+ * (mirror NULL/$4.25 -> live roll $34–$205). Pricing the feed off the mirror would
+ * wrongly drop ~54k correctly-priced products and could advertise the $4.25 SAMPLE
+ * variant. So the feed price = the ROLL variant's price, read live.
+ *
+ * OUTPUT (local files only — NOTHING is submitted to Google):
+ * data/google-feed/feed-clean.tsv — Merchant Center TSV of ELIGIBLE products
+ * data/google-feed/exclusions.json — every excluded product + reason
+ * data/google-feed/report.json — counts + reason breakdown + samples
+ *
+ * READ-ONLY against Shopify (GraphQL queries only). No writes, no status changes,
+ * no channel publish. Submitting/uploading the feed is a separate Steve-gated step.
+ *
+ * USAGE: node feed-eligibility.mjs [--limit=N] [--floor=10]
+ */
+import fs from 'node:fs';
+import path from 'node:path';
+
+const SHOP = 'designer-laboratory-sandbox.myshopify.com';
+const VER = '2024-10';
+const PUBLIC_DOMAIN = 'https://designerwallcoverings.com';
+const GOOGLE_CATEGORY = 'Hardware > Building Materials > Wallpaper'; // Google taxonomy 503739
+const args = Object.fromEntries(process.argv.slice(2).map(a => {
+ const [k, v] = a.replace(/^--/, '').split('='); return [k, v === undefined ? true : v];
+}));
+const LIMIT = args.limit ? parseInt(args.limit, 10) : Infinity;
+const FLOOR = args.floor ? parseFloat(args.floor) : 10;
+
+const envTxt = fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8');
+const TOKEN = (envTxt.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m) || [])[1]?.trim();
+if (!TOKEN) { console.error('no token'); process.exit(1); }
+const URL = `https://${SHOP}/admin/api/${VER}/graphql.json`;
+const sleep = (ms) => new Promise(r => setTimeout(r, ms));
+
+// ---- cost map for Kravet MAP floor (from mirror; sparse is OK -> flagged unverified) ----
+const COST = new Map();
+for (const line of fs.readFileSync('/tmp/cost_map.tsv', 'utf8').trim().split('\n')) {
+ const [gid, cost] = line.split('\t');
+ if (gid) COST.set(gid, parseFloat(cost) || 0);
+}
+
+// ---- rule constants ----
+// Kravet-umbrella brands: must be >= wholesale x 1.5 (MAP). Showing the brand name is fine (DW is authorized).
+const KRAVET_FAMILY = ['kravet','lee jofa','groundworks','brunschwig','cole and son','cole & son',
+ 'gp j baker','gp & j baker','colefax','clarke and clarke','clarke & clarke','mulberry','threads',
+ 'baker lifestyle','andrew martin','nicolette mayer','aerin','barclay butera','thom filicia'];
+// PRIVATE-LABEL upstreams — these names must NEVER reach a customer surface (the feed).
+// Source: dw-leak-scanner / MEMORY. NOT the repped brands (Kravet/Thibaut/etc are shown by name).
+const PRIVATE_LABEL_LEAK = ['command54','command 54','wallquest','chesapeake','nextwall','next wall',
+ 'seabrook','brewster','desima','carlsten','nicolette mayer'];
+
+const norm = s => String(s == null ? '' : s).toLowerCase();
+const isSample = v => /(sample|memo|swatch)/i.test([v.title, v.sku].join(' ')) || /-sample$/i.test(v.sku || '');
+
+async function gql(query, variables) {
+ for (let attempt = 0; attempt < 8; attempt++) {
+ let res, j;
+ try {
+ res = await fetch(URL, { method: 'POST',
+ headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
+ body: JSON.stringify({ query, variables }) });
+ j = await res.json();
+ } catch (e) { await sleep(1500 * (attempt + 1)); continue; }
+ if (j.errors) { if (JSON.stringify(j.errors).includes('THROTTLED')) { await sleep(2000 * (attempt + 1)); continue; } throw new Error(JSON.stringify(j.errors)); }
+ const t = j.extensions?.cost?.throttleStatus;
+ if (t && t.currentlyAvailable < 400) await sleep(1200);
+ return j.data;
+ }
+ throw new Error('exhausted retries');
+}
+
+const QUERY = `
+query($cursor:String){
+ products(first:50, after:$cursor, query:"status:active"){
+ pageInfo{ hasNextPage endCursor }
+ nodes{
+ id handle title status vendor tags productType
+ featuredImage{ url }
+ mediaCount{ count }
+ variants(first:30){ nodes{ title sku price } }
+ widthGlobal: metafield(namespace:"global", key:"width"){ value }
+ widthCustom: metafield(namespace:"custom", key:"width"){ value }
+ widthDwc: metafield(namespace:"dwc", key:"width"){ value }
+ }
+ }
+}`;
+
+function evaluate(p) {
+ const reasons = [];
+ const title = p.title || '';
+ const tagsBlob = (p.tags || []).join(' ');
+ const ctx = norm([p.vendor, title, tagsBlob, p.handle, p.productType].join(' '));
+ const variants = p.variants?.nodes || [];
+ const rolls = variants.filter(v => !isSample(v)).map(v => parseFloat(v.price)).filter(Number.isFinite);
+ const rollPrice = rolls.length ? Math.max(...rolls) : null;
+ const img = p.featuredImage?.url || null;
+ const hasImg = !!img || (p.mediaCount?.count || 0) > 0;
+ const width = (p.widthGlobal?.value || p.widthCustom?.value || p.widthDwc?.value || '').trim();
+
+ // ---- hard gates (exclude) ----
+ if (!hasImg) reasons.push('no_image');
+ if (rollPrice == null) reasons.push('no_roll_variant_price');
+ else if (rollPrice < FLOOR) reasons.push(`roll_price_below_floor_${rollPrice}`);
+ if (rollPrice === 4.25) reasons.push('roll_price_is_425_sampletrap');
+
+ // private-label leak -> never customer-facing
+ for (const tok of PRIVATE_LABEL_LEAK) {
+ if (ctx.includes(tok)) { reasons.push(`private_label_leak:${tok}`); break; }
+ }
+ // banned words
+ if (/\bwallpapers?\b/i.test(title)) reasons.push('title_says_wallpaper');
+ if (/\bunknown\b/i.test(title)) reasons.push('title_says_unknown');
+
+ // Kravet MAP floor (exclude only if we can PROVE it's below MAP; else flag)
+ const flags = [];
+ const isKravet = KRAVET_FAMILY.some(k => ctx.includes(k));
+ if (isKravet && rollPrice != null) {
+ const cost = COST.get(p.id) || 0;
+ if (cost > 0) {
+ const mapFloor = cost * 1.5;
+ if (rollPrice + 1e-6 < mapFloor) reasons.push(`below_kravet_map_${rollPrice}_lt_${mapFloor.toFixed(2)}`);
+ } else {
+ flags.push('kravet_map_unverified_no_cost');
+ }
+ }
+
+ return { id: p.id.split('/').pop(), handle: p.handle, title, vendor: p.vendor || '',
+ rollPrice, img, width, eligible: reasons.length === 0, reasons, flags };
+}
+
+function tsvRow(r) {
+ // Merchant Center primary feed columns (tab-separated)
+ const desc = `${r.title}${r.width ? ' — ' + r.width + ' wide' : ''}`.replace(/\t|\n/g, ' ');
+ return [
+ r.id, // id
+ r.title.replace(/\t|\n/g, ' '), // title
+ desc, // description
+ `${PUBLIC_DOMAIN}/products/${r.handle}`, // link
+ r.img || '', // image_link
+ 'in stock', // availability
+ `${r.rollPrice.toFixed(2)} USD`, // price (ROLL, not sample)
+ r.vendor, // brand
+ 'new', // condition
+ r.id, // mpn
+ GOOGLE_CATEGORY, // google_product_category
+ 'Wallcovering', // product_type
+ ].join('\t');
+}
+
+(async () => {
+ const outDir = path.join(process.cwd(), 'data', 'google-feed');
+ fs.mkdirSync(outDir, { recursive: true });
+ const eligible = [], excluded = [];
+ const reasonTally = {}, flagTally = {}, vendorEligible = {};
+ let cursor = null, hasNext = true, scanned = 0;
+
+ while (hasNext && scanned < LIMIT) {
+ const data = await gql(QUERY, { cursor });
+ const page = data.products;
+ for (const p of page.nodes) {
+ if (scanned >= LIMIT) break;
+ scanned++;
+ const r = evaluate(p);
+ for (const f of r.flags) flagTally[f] = (flagTally[f] || 0) + 1;
+ if (r.eligible) {
+ eligible.push(r);
+ vendorEligible[r.vendor] = (vendorEligible[r.vendor] || 0) + 1;
+ } else {
+ excluded.push({ id: r.id, handle: r.handle, title: r.title, vendor: r.vendor, rollPrice: r.rollPrice, reasons: r.reasons });
+ for (const reason of r.reasons) {
+ const key = reason.replace(/:.+$/, '').replace(/_\d.+$/, '').replace(/_[0-9.]+_lt_[0-9.]+$/, '');
+ reasonTally[key] = (reasonTally[key] || 0) + 1;
+ }
+ }
+ }
+ hasNext = page.pageInfo.hasNextPage;
+ cursor = page.pageInfo.endCursor;
+ if (scanned % 1000 === 0) process.stderr.write(` scanned ${scanned} | eligible ${eligible.length} | excluded ${excluded.length}\n`);
+ }
+
+ // write feed TSV
+ const header = ['id','title','description','link','image_link','availability','price','brand','condition','mpn','google_product_category','product_type'].join('\t');
+ fs.writeFileSync(path.join(outDir, 'feed-clean.tsv'), header + '\n' + eligible.map(tsvRow).join('\n') + '\n');
+ fs.writeFileSync(path.join(outDir, 'exclusions.json'), JSON.stringify(excluded, null, 2));
+
+ const topVendors = Object.entries(vendorEligible).sort((a,b)=>b[1]-a[1]).slice(0,20).map(([v,c])=>({vendor:v,count:c}));
+ const report = {
+ generated_at_note: 'live Shopify pull, read-only',
+ shop: SHOP, api_version: VER, price_floor: FLOOR, public_domain: PUBLIC_DOMAIN,
+ scanned, eligible: eligible.length, excluded: excluded.length,
+ exclusion_reasons: Object.fromEntries(Object.entries(reasonTally).sort((a,b)=>b[1]-a[1])),
+ advisory_flags: flagTally,
+ top_eligible_vendors: topVendors,
+ sample_exclusions: excluded.slice(0, 25),
+ };
+ fs.writeFileSync(path.join(outDir, 'report.json'), JSON.stringify(report, null, 2));
+
+ console.log(JSON.stringify({ scanned, eligible: eligible.length, excluded: excluded.length,
+ exclusion_reasons: report.exclusion_reasons, advisory_flags: flagTally }, null, 2));
+ process.stderr.write(`WROTE ${outDir}/{feed-clean.tsv, exclusions.json, report.json}\n`);
+})();
← 6967315 Color-SEO landings: SEO title + meta description + editorial
·
back to Dw Yolo Loop
·
feed-eligibility: leak-check on feed-emitted fields only (ti 257f9f8 →