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	Mar 8 2026	Ace confirmed Skubana is no longer used	Confirm Dashboard Stock Details tab IMPORTRANGEs aren't returning real values
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.