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.

Day basis: the marketplace day

Every Amazon order-derived table is keyed on the marketplace day (Pacific for Amazon.com; scripts/marketplace_days.py holds the map), never the UTC date of the purchase timestamp. Amazon's business reports, MRP, Sellerboard, and the Amazon Ads API all report on that day, so it is the only basis on which sales, fees, ad spend, and MRP verification line up per day.

  • raw.orders_detail.purchase_date = marketplace day of purchase_at.
  • raw.sales_daily 'API' rows are the aggregate of non-cancelled raw.orders_detail rows on that day. pull_orders.py replaces its whole window on every pull; it does not upsert, because Amazon drops never-confirmed pending orders from later reports and an upsert left the stale (asin, day) row behind (August 2026: 411 phantom units, about 3% of sales).
  • raw.sales_daily 'API_INTRADAY' rows and the provisional orders from pull_intraday_orders.py use the same day, so Today and Yesterday never disagree with the finalized rows about which day an order belongs to.
  • raw.financial_events and raw.financial_transaction_components posted_date are trigger-derived from posted_at on the marketplace day (migration 0117), so refunds, refund fee adjustments, and storage or service fees land on the day MRP and Sellerboard book them.
  • Yesterday uses actuals, not estimates. The intraday workflow pulls the last two days of Amazon financial events (refunds, fees, promotions) and the three Ads daily reports every hour (workflow_dispatch with actuals=true from the Mac mini dispatcher), then rebuilds the provisional days from them. Only the current day, and Yesterday for the hour or two before the first pull, carry fee and Ads estimates. refunds_estimated (migration 0118) is reserved and stays false.
  • scripts/verify_recent_profit_accuracy.py fails the intraday run when a prior-day profit row lacks fees because its orders sit on an adjacent day.

Migration 0116 (2026-09-22) rewrote the loaded data onto this basis and rebuilt the finalized 'API' rows from the orders. After it, Sticky and MRP agreed to the unit for 2026-09-01..07 (3,340 vs 3,339) and 2026-09-21 (590 vs 589). Before it, 30 of the 31 prior days carried "missing fees" rows and the Today surface hid Profit for every range.

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)

Operator-maintained purchase-order ETA

The nightly PO snapshot reads the SoStocked export for line-item state, then overrides est_arrival_date when Hanna's order workbook supplies an Expected Amazon delivery date for the same normalized PO number. The current input is the Christmas order tab in sheet 1bm9zOcofy2bTOiwtMAiBAgms7s4u5kWEtKwI9a3I55k; deployments can move the input with HANNA_ORDER_SHEET_ID and HANNA_ORDER_RANGE. The warehouse records the winner in raw.purchase_orders.est_arrival_source. Blank Hanna dates fall back to SoStocked; malformed or conflicting dates fail the pull rather than silently changing supply.

  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.