SM Cross-Check + Full Backfill¶

UPDATE 2026-05-04: Two follow-up data fixes shipped¶

After the original 5-day reconciliation (below), a 10-random-day MRP/SB/SM cross-check surfaced two major data-quality bugs in SM that caused profit to be overstated by 3-4x in monthly rollups:

Fix 1: MCF (Non-Amazon) shipments contaminating sales¶

Problem: SP-API GET_FLAT_FILE_ALL_ORDERS includes off-Amazon FBA shipments (S01-prefixed orders, marketplace='Non-Amazon'). They have item_price=$0 because customer paid outside Amazon, but consume FBA inventory → SM was charging COGS without revenue. 398 days had some contamination; worst was 2024-08-14 (1,755 phantom units, -57% margin vs MRP/SB's +18%).
Fix: Migration 0030_profit_daily_exclude_mcf.sql subtracts MCF units/sales/COGS from agg.profit_daily. MRP and SB exclude these natively.
Impact: 8/14/24 went from -57% margin to +33% margin (matches MRP/SB).

Fix 2: MRP_BACKFILL ad spend was ~30% of true (the big one)¶

Problem: The original MRP_BACKFILL load came from MRP's "Advertising Performance" XLSX export, which captures only attributed/recovered ad spend, not the full Advertising line. For pre-2026-01-26 dates, SM ad spend was 70% under-counted vs MRP and SB (which agreed to the dollar). 21 months × ~70% under-count = ~$271K of ad spend missing → profit overstated by exactly that much.
Fix: New scraper scripts/backfill_ad_spend_mrp_dashboard.py pulls per-(child-ASIN, day) ad spend directly from MRP's Profit Dashboard. Stored as ad_product='MRP_DASHBOARD' in raw.ad_spend_daily. Migration 0031_ad_spend_unified_prefer_mrp_dashboard.sql makes the unified view prefer this source over MRP_BACKFILL.
Backfill: 634 days × ~40s/day = ~7 hours. Completed 2026-05-04. 111,217 rows, 418 distinct ASINs, $486,406 total ad spend (vs previous MRP_BACKFILL $215K).
Bug found mid-backfill: Browser session degraded after pagination, MRP profile got into a bad state, login detection had a false-positive on "Email" sidebar text. Fixed mrp_login.py detection: broader sidebar nav signals, only flag bounce when URL is /login AND body has both Email AND Password. Cleared profile, restarted with --resume; recovered the 3 failed days from the disruption.

Final state across 24 months (May 2024 → Apr 2026)¶

Before: $824K profit / 23.9% margin (overstated — missing $271K of ad spend)
After: $555K profit / 15.9% margin (matches MRP/SB reality)
10-day cross-check totals: SM $6,442 / 14.4% — MRP $1,936 / 4.4% — SB $2,458 / 5.5%

Remaining residual: refund timing (definitional, not a bug)¶

SM attributes refunds to original purchase date (via order_id → orders_detail.purchase_date). MRP and SB attribute refunds to refund-posted date. On any single day, the two definitions can disagree by hundreds of dollars. Visible most starkly on 2025-11-11: SM $435 refunds vs MRP $1,199 vs SB $1,211. Across longer windows the totals converge. Both definitions are defensible — purchase-date attribution is more conservative for forecasting; posted-date is better for cash-flow tracking. Documented and accepted as-is.

Original Profit Reconciliation — 5-day Sample (Apr 15-19, 2026)¶

Date: 2026-05-03 Source: Sticky Postgres agg.sales_daily_unified + agg.ad_spend_unified_asin_daily + raw.financial_events + raw.orders_detail + raw.products + raw.product_skus Reference: MRP Profit Dashboard (https://app.myrealprofit.com/dashboard) child-ASIN view, custom range Apr 15-19 Pipeline: ~/stickymetrics/scripts/pull_mrp_profit_dashboard.py (new today) + ~/stickymetrics/scripts/load_orders_detail.py (new today) Scripts/SQL referenced: - Migration: db/migrations/0026_orders_detail.sql - Recon SQL (final): sql/profit_recon_v5.sql - Comparison driver: scripts/profit_recon.py

TL;DR¶

Sticky reproduces MRP's per-(ASIN, purchase_date) profit within ±3% on every major category (Sales, COGS, FBA fee, Referral fee, Refunds). Aggregate 5-day profit lands $403 high on a $2,703 base (+14.9%). Outside the ±2% target — but the residual is now isolated to four definitional gaps, not data errors.

	Sticky 5d	MRP 5d	Δ	Δ %
Sales	$17,029	$16,914	+$115	+0.7%
Ad spend	$1,710	$1,919	-$209	-10.9%
COGS	$2,334	$2,315	+$19	+0.8%
FBA fee	$7,222	$7,273	-$51	-0.7%
Referral fee	$1,158	$1,192	-$34	-2.9%
Other fees	$0	$7	-$7	-100%
Refunds	$1,275	$1,265	+$10	+0.8%
RefundFee	-$80	$0	-$80	n/a
Promotions	$305	$0	+$305	n/a
Adjustments	$0	-$56	+$56	n/a
Profit	$3,106	$2,703	+$403	+14.9%

Key fix: financial_events.order_id → orders_detail.purchase_date¶

Versions 1-3 tried to bucket fees by posted_date. That was wrong: Amazon's financial events lag the order's purchase date by 1-2 days for charges, and 2-4 weeks for refunds. MRP attributes everything to purchase_date, so we must do the same.

v4 / v5 fix: new table raw.orders_detail (one row per Amazon order × SKU) loaded from GET_FLAT_FILE_ALL_ORDERS_DATA_BY_ORDER_DATE_GENERAL. This gives us a per-order purchase_date that we join to financial_events on amazon_order_id. Every fee/refund now lands under the correct purchase day.

Without this join: profit gap was +$5,303 (+196%). With it: +$403 (+14.9%). This is the load-bearing change for the whole reconciliation effort.

Remaining residuals (in order of size)¶

1. Promotions: +$305 (Sticky over MRP)¶

Sticky counts Promotion events from raw.financial_events ($304.82 over Apr 15-19). MRP's Discounts line reads $0 — meaning MRP either nets promotions into the Sales line (but our $115 sales gap is much smaller than $305) or doesn't track them at all.

Decision: keep counting promotions as expense in Sticky. MRP undercounts here. The conservative read is the right one for forecasting trustworthy profit.

2. Ad spend: -$209 (Sticky under MRP)¶

Sticky's agg.ad_spend_unified_asin_daily only attributes spend to ASINs. ASINs that had ad spend but no sales in our 5-day window are dropped when we left-join to sales. MRP totals all ad spend regardless of attribution.

Fix (planned): build the agg.profit_daily view with a UNION between (a) per-ASIN profit rows and (b) a "non-attributed" row carrying unsold-ASIN ad spend + AdjustmentEventList. That delivers a full aggregate match.

3. RefundFee: -$80 (Sticky over MRP)¶

Sticky's RefundFee bucket nets Fee.RefundCommission ($19) − Fee.Commission returned ($95) − Fee.ShippingChargeback returned ($3) = $80 net cost. MRP appears to fold this into either Refunds or Referral Fee — we couldn't isolate where.

Decision: keep as a separate Sticky-only line. It's a real cost, MRP's silence here doesn't change the underlying truth.

4. Adjustments: +$56 (MRP shows it, Sticky misses)¶

AdjustmentEventList rows in raw.financial_events have no order_id, no sku, no asin. They can't be attributed to a (asin, purchase_date) tuple via our join. MRP shows -$56 in Adjustments at the aggregate level.

Fix (planned): in agg.profit_daily, add a "non-attributed" row per day for the AdjustmentEventList sum. Same pattern as the unsold-ASIN ad spend fix.

5. Per-day timezone shift (per-ASIN noise, cumulative ~zero)¶

Top per-ASIN deltas show pairs like B076J7JD37 4/15 Sticky=$35 / MRP=$210 and B076J7JD37 4/16 Sticky=$216 / MRP=$48. The 2-day sum matches; only the day boundary is off. Sticky uses UTC purchase_date; MRP uses (probably) Amazon marketplace timezone (PT for US). For 5-day windows this is irrelevant; for single-day comparisons it's noise.

Decision: document and accept. Per-day reconciliation tolerance should be ±5% or wider; 5-day-and-up windows tolerance should be tight (±2%).

Coverage caveats¶

COGS: 153 of 154 active ASINs in this window have unit_cost set (one $6 ASIN missing). Effectively complete.
Storage Fee: MRP shows $298 in our window. Sticky has raw.settlement_events (last 90 days only) but the storage-attribution to ASINs is monthly granularity. We currently don't allocate storage in profit_recon_v5.sql. For longer windows (e.g. monthly) this matters; for a 5-day window it's $298 / 5 ≈ $60/day spread thin. Minor.
Orders detail load: loaded from data/raw/ecomhd_us/orders_2026-04-01_2026-04-29.tsv (already saved by nightly pipeline). For long-term backfill we'll need this same TSV across all 28 months.

Path to ±2% target¶

The four residuals above explain $654 of the $403 net delta (some offsetting). To reach ±2% on the 5-day window:

Build agg.profit_daily view with non-attributed rows for ad spend (unsold ASINs) and adjustments (no order_id) → closes ~$265 of the gap.
Make Promotions match MRP convention. Either net promotions out of Sales (changes sales basis) or accept the residual as a Sticky-only conservative bias.
Document RefundFee handling — likely keep Sticky-only since it's a real cost.

After (1), expected residual: ±5% on profit (Promotions + RefundFee remaining). Tight enough to ship agg.profit_daily and run the 850-day backfill.

Files / artifacts¶

MRP pulls: ~/stickymetrics/data/profit_recon/mrp_profit_*.json (5-day window + 5 per-day files)
Per-order detail: raw.orders_detail populated for Apr 1-29 (11,947 rows)
SQL versions: sql/profit_recon_v1.sql … v5.sql
Migration: db/migrations/0026_orders_detail.sql

Next steps (next session)¶

Build agg.profit_daily view using v5 logic + non-attributed UNION rows.
Backfill raw.orders_detail across Jan 2024 → today (28 months × pulling per-month).
Run profit calc across all 850+ days, save to raw.profit_daily_history.
Sanity-check monthly rollups against MRP screenshots Ace has already saved.