Skip to content

eComHD Data Warehouse Inventory

Purpose: track what each sheet in 06 - Raw Data does, who maintains it, what consumes it, and what's stale.

Parent folder: 1Xnz53v265DzN7g9WEWtcsy50KEWU-Hmf (Supply Chain ops folder) Raw Data folder: 1pzCyyoXwECVhcw02B5WKI9j6yD8lZPIT Last reviewed: 2026-05-01

Active sources (live, feeding Dashboards)

06 - AWD Inventory (1Zj6vi…)

  • What: AWD inventory snapshot per ASIN — Available, Inbound, Reserved
  • Source: SP-API /awd/2024-05-09/inventory → Postgres raw.awd_inventory → Raw tab
  • Refresh: automated, nightly 6am via launchd com.stickymetrics.daily
  • Consumed by: MoM and RAVE Dashboards via AWD Inventory!A:D IMPORTRANGE → AA col
  • Last manual touch: N/A (fully automated)

02 - Amazon SC - Restock (13u0g4…)

  • What: FBA Restock Report (30 cols: ASIN, SKU, sales 30D, FBA inventory, days-of-supply, alerts, replenishment recs)
  • Source: SP-API GET_RESTOCK_INVENTORY_RECOMMENDATIONS_REPORT → Postgres raw.fba_restock → Raw tab. MDS Calculation tabs aggregate from Raw.
  • Refresh: automated, nightly
  • Consumed by: Dashboards Sales 30D, Amazon Inventory, FBA states (cols O, R-W, AA)
  • Last manual touch: N/A

07 - MRP Sales Plan (1kV6N_v…) — Claude MRP tab

  • What: Per-ASIN monthly sales (actuals Jan-Apr + forecast May-Dec) + 30/60/90/120/150/180D rolling totals
  • Source: Chronos-Bolt-mini ML forecast (lifecycle-aware + event-calendar) → Postgres forecasts.predictions → Claude MRP tab
  • Refresh: automated, nightly. Cols Q-V (30-180D rollups) auto-computed via LET formulas in the tab itself.
  • Consumed by: Both Dashboards' Forecast cols Q-V via IMPORTRANGE → MRP tab → VLOOKUP
  • Last manual touch: N/A

05 - SoStocked POs (1UQkj…)

  • What: Manual export of POs from SoStocked tool. 13 cols: order number, status, dates, units, ASIN, SKU, supplier, notes
  • Source: Someone exports from SoStocked → pastes into POs tab. "In Production" tab is QUERY-aggregated from POs where status='In Progress'.
  • Refresh: manual — needs ops team discipline to keep current
  • Consumed by: Dashboards' LEO column (Y); also synced to Postgres raw.purchase_orders nightly
  • Known gotcha: 8 Q4 2024 POs flagged as is_likely_orphan (pre-AWD-reconciliation, never auto-closed). Should be manually closed in SoStocked.

99 - Product Details (19F4Dp…)

  • What: Multi-tab internal sheet. Originally built for auto-price-changing (the Inv Out, Inv Low, Inv In Stock, In High price-threshold columns) — that workflow is DEAD as of 2026-05-01 per Ace. Auto-price-changing is no longer in use.
  • Still useful: Pack QTY, MOQ, Cost, vendor metadata if those columns are being maintained. Verify with ops team.
  • Source: Manual feed historically. Cadence unclear post-auto-pricing-deprecation.
  • Status: Mostly obsolete. Cost data is duplicated in Sellerboard SB Export (which is what raw.products.unit_cost syncs from). MOQ/Pack QTY may still be useful but possibly stale.
  • Note: Two SB Export tabs exist (standalone 00 - Seller Board - Product Export + tab inside this sheet) — identical, kept separate for downstream consumers per Ace 2026-05-01.

00 - Seller Board - Product Export (1OgWwG2L…)

  • What: Sellerboard ABC classification (Class A, Class B, Discontinue, Bundle, Relaunch, etc.) per ASIN + cost. 1,510 ASINs.
  • Source: Manual export from Sellerboard tool (no API)
  • Refresh: manual — see Sellerboard for fresh assignments. Last touched 2026-04-20.
  • Consumed by: Postgres raw.products.labels + unit_cost synced from this nightly. Twin tab inside 99 - Product Details.

Probably-obsolete (not archived per Ace 2026-05-01 — document and revisit)

Sheet Last touch Why probably obsolete Verify-before-archiving check
00 - Skubana - Stock Details (1Ebf6yJ…) retired 2026-05-05 Replaced by eHD - Transfer - HIEX - V3 (1P_gzPu-IIK_czOshRX8XmLYZtF3U4yfyDSVPBbG1Re4) → Stock Details tab. Same A-J schema. pull_warehouse_inventory.py cut over. Watch for Dashboard Stock Details IMPORTRANGEs still pointing at the old sheet ID — should be migrated.
99 - Skubana - Product Catalog Oct 2024 Same —
01 - SellerMobile - Forecast Jun 2025 Replaced by Chronos-Bolt forecast pipeline Check no Dashboard references remain
03 - Amazon SC - Inventory Report Jun 2025 Replaced by SP-API Restock pull —
04 - Amazon SC - Fee Preview Dec 2022 Abandoned 3+ years —
99 - Vendor Details Oct 2022 Abandoned —
99 - Product Details (price-threshold cols) — Auto-price-changing workflow dead per Ace 2026-05-01. Cost is now sourced from SB Export. MOQ/Pack QTY only useful if still being maintained — unclear. Audit if anyone still updates Pack QTY / MOQ here, or if those numbers should move to raw.products and be edited in Postgres directly.

Postgres canonical mirror (single source of truth)

Sheet Postgres table Status
06 - AWD Inventory raw.awd_inventory ✅
02 - Amazon SC - Restock raw.fba_restock ✅
07 - MRP Sales Plan forecasts.predictions (computed); actuals in raw.sales_daily ✅
05 - SoStocked POs raw.purchase_orders ✅
00 - SB Product Export raw.products (labels + unit_cost) ✅
99 - Product Details partial — title, brand, label, category in raw.products. MOQ/Pack QTY not yet ingested. ⚠
(new — no sheet) raw.inventory_ledger_summary — monthly per (ASIN, MSKU, FC, Disposition) ledger summary from GET_LEDGER_SUMMARY_VIEW_DATA. 18-month lookback; primary use is retroactive stockout detection (ending_balance == 0 → OOS month). Excludes DAILY/DETAIL views (out of scope; spec'd in ~/stickymetrics/prompts/inventory_ledger_agent.md). ✅

raw.inventory_ledger_summary notes

  • Migration: ~/stickymetrics/db/migrations/0017_inventory_ledger.sql (filed at 0017 because 0014-0016 were taken by the May 1 settlement / financial-events / sales-plan batch — the originating spec referenced 0014).
  • Puller: ~/stickymetrics/scripts/pull_inventory_ledger.py — supports --days N (rolling), --start/--end (window), --backfill (18mo). Chunks at 180 days. Idempotent via ON CONFLICT (seller_id, period_month, asin, msku, country, disposition, location).
  • Schedule: nightly via nightly_refresh.sh step [2b/10] with --days 60 (current month + prior).
  • Gotchas observed 2026-05-01:
  • Amazon's monthly aggregator lags. Empty result for month-just-ended is normal; data appears within ~1-2 weeks of month-end (assumption — verify after May data first appears).
  • Date format in TSV is MM/YYYY for monthly, MM/DD/YYYY for daily. parse_period_month handles both.
  • SP-API request_report → poll_report has no built-in retry on transient SSL/network errors. The puller wraps poll in poll_report_resilient which retries on URLError up to 10 consecutive failures. Concurrent SP-API pullers (e.g. running this alongside a financial-events backfill) make these errors common.

Unlocks (not built yet — flagged for Ace)

After this table is populated, two downstream improvements become possible: 1. derive_seasonal_multipliers.py can exclude months where ending_balance == 0 (or stockout-day count exceeds threshold) — fixes a known contamination issue affecting ~44 ASINs portfolio-wide (assumption — verify count from prior analysis). 2. compute_po_recommendations.py can use ledger-derived "true demand" (customer_shipments + lost-sales-during-OOS estimate) instead of raw sales_daily, which under-counts demand during OOS periods.

Don't build either yet — review the table first.

Things still manual (no API, can't automate the source)

  1. Sellerboard → SB Product Export — manual export. Sellerboard has no public API. Cadence depends on ops team. Risk: if labels drift, our Class A/B/etc. filters get wrong.
  2. SoStocked → SoStocked POs sheet — manual export. SoStocked has no public API. Risk: open POs missing or stale-status (we observed 8 orphan POs on first ingest).
  3. Manual Product Details updates (Pack QTY, MOQ, etc.) — usually static once set; low risk.

Recommendations (deferred per Ace 2026-05-01)

  1. Archive obsolete sheets to OLD/ — agreed in principle, deferred to a later cleanup pass.
  2. Define a refresh cadence for manual sources — e.g., "SB Product Export refreshed every Monday by Noman, SoStocked POs after each PO created/closed." Document in Playbook.
  3. Long-term: replace Sheets-based Dashboards with a Postgres-backed app (Streamlit / web dashboard). Sheets remain operational (PO creation, vendor comms) but analytical views move to a real frontend.