Data Health — Grow Datahub

75%

Overall Health Score

GREEN

YELLOW

RED / MISSING

390K

ORDER ROWS

Net Revenue

$6.46M

Shopify $5.23M · Amazon $1.12M · Faire $110K

Contribution Margin

$2.67M

97.7% coverage · 3 channels

Ad Spend

$2.1M

Meta $1.57M · Google $326K · Amz $179K · Collabs $23K · 3,600 rows

Shipping Cost

$989K

95% matched · Dec 2023–Mar 2026

Products

186

150 with COGS · $0 unmapped

⚡	Data Field	Source	Rows	Coverage	Drift	Known Concerns	How to Verify / Close the Gap
●	Shopify Orders	Shopify CSV export → load_shopify.py	307,108	100%	0.3–1.5%	Returns tracked by order date (CSV) not refund date (Analytics), so monthly return timing can differ 8-10%.	Compare monthly gross to Shopify Analytics → Sales → Total Sales. Should be within 1.5%.
●	Amazon Orders	Amazon flat file CSV → load_amazon.py	82,469	100%	0.4–3%	3.4% of orders are multi-product where CSV captured partial line items (export issue). Settlement shows slightly higher gross.	Compare to Amazon Business Reports → Sales & Traffic. Monthly should be within 3%.
●	Shopify Discounts	Order CSV "Discount Amount" → proportional allocation	142,920	100%	<0.4%	Penny-perfect for 2025, within 0.4% for 2024. Proportionally allocated across line items in multi-item orders.	Compare monthly to Shopify Analytics → Discounts. Should be within 0.5%.
●	Shopify Returns	Order CSV "Refunded Amount" → capped at line gross	3,703	~90%	8–10%	CSV tracks by order date, Analytics by refund date. Timing mismatch. Capped at gross to exclude shipping refunds. 2026 partial.	Compare annual to Shopify Analytics → Returns. Monthly will drift; annual should be within 5%.
●	Faire Orders	Shopify CSV (tagged via @relay.faire.com email pattern)	1,626	100%	0%	67 customers, $122K gross, $110K net. Tagged as channel='faire' in fact_orders. Flows through Shopify but identified and separated.	Run: SELECT COUNT(*) FROM fact_orders WHERE channel='faire'. Cross-check gross vs Faire dashboard.
●	Amazon Returns	Amazon Returns Report CSV → fact_amazon_returns	1,867	96%	~5%	$26.1K total ($12.8K 2024, $10.9K 2025, $2.4K 2026 YTD). 122 returns unmatched (pre-2024 orders). Return rate 1.1-1.7% of gross.	Compare annual to Amazon FBA returns report. Yellow: may not capture all return types; 2026 partial.
●	Shopify Fees	Shopify Payments payout CSV (953 payouts)	—	100%	<0.1%	None. Actual rates per year: 3.11% (2024), 3.18% (2025), 3.14% (2026).	Spot-check: sum platform_fees ÷ gross_revenue by year. Should match rates above.
●	Amazon Fees (2025)	Settlement reports → fact_amazon_settlement_fees	33,264	94.6%	2.4%	34.0% blended (15% referral + 19.1% FBA). 5.4% unmatched orders use 34.0% average. fact_orders shows 34.8% — within $4K.	Download new settlement from Seller Central → Reports → Payments. Compare total fees.
●	Amazon Fees (2024)	Settlement reports → fact_amazon_settlement_fees	35,757	92%	~5%	39.9% blended. 8% unmatched use average. fact_orders shows 44.4% — gap due to CSV gross revenue being ~$21K lower than settlement gross.	Cross-check: SUM(total_fees) in settlements vs SUM(platform_fees) in fact_orders for 2024.
●	Amazon Fees (2026)	6 settlement files (Dec 16–Mar 10)	4,543	67%	est. 7%	67% at 24.8% actual rate, 33% (post-Mar 10) on 32.2% flat estimate. Fee trend: 39.9% → 34.0% → 24.8%.	Upload next settlement report from Seller Central. Each one closes ~2 weeks of estimates. ▸ Green when full Q1 covered.
●	Meta Ad Spend	Meta Ads Manager CSV → load_meta_ads.py	415	100%	2.5%	Weekly granularity causes bucket-alignment drift vs daily Windsor pulls. link_clicks used (not clicks all).	Compare monthly totals to Meta Ads Manager → Campaigns → Date filter. Penny-exact at monthly level.
●	Google Ad Spend	Google Ads console CSV → load_google_ads.py	89	100%	$0.00	None. Penny-perfect vs both Adzviser and Windsor pulls.	Compare to Google Ads → Campaigns → Overview. Should be exact.
●	Amazon Ad Spend (2025-26)	Amazon Ads console + Adzviser MCP	577	100%	<1%	2025: $105K from console (all 12 months). 2026 Q1: $13K complete (Jan 1-21 console export + Jan 22–Mar 22 Windsor). No gaps.	Compare to Amazon Ads console → Campaign Manager → date filter. Jan 1-21 from downloaded report.
●	Amazon Ad Spend (2024)	Amazon Ads console monthly export	525	100%	<1%	$59,455 actual from Amazon Ads console (Session 24). 110 campaigns. Previously estimated ~$82K — actual was $22.5K lower.	Compare to Amazon Ads console → Campaign Manager → Jan 1–Dec 31, 2024 date range. All months accounted for.
●	Shipping Cost	ShipStation CSV export → load_shipstation.py	293,340	95.4%	<5%	Shopify 99.4% matched ($988K). Amazon FBM only 100 rows (most Amazon is FBA — shipping in platform fees). 4.6% unmatched are likely cancelled/sample orders.	Compare monthly totals to ShipStation → Reports → Shipment Cost. Should be within 5% per month. Re-export monthly for ongoing coverage.
●	COGS (Shopify)	Nikita's BOM Excel → dim_products.cogs_per_unit	126 SKUs	98.3%	1–11%	BOM refreshed from Nikita's Purchasing Framework (March 2026). 126 products updated. Costs increased 1–11% (sprays ~1–2%, candles ~7–11%). Still Yellow: labor model is flat $1.16/unit (not task-level), Nikita confirmation pending.	Nikita to verify final COGS accuracy against actual costs. Build task-level labor allocation using production_log. ▸ Green when verified.
●	COGS (Amazon)	Derived: Shopify spray COGS + $1.00/pack → update_amazon_cogs.py	25 ASINs	100%	est. 5-15%	Rough estimate: component spray COGS from BOM + $0.50 labor + $0.50 materials per pack, split by units. Inherits BOM drift. Packaging cost is a flat estimate.	Build proper Amazon BOMs with actual pack materials cost per ASIN. Same BOM structure, just more precise packaging/labor. ▸ Green when BOMs built.
●	Contribution Margin	Calculated: net_rev − fees − shipping − COGS	380,759	97.7%	<5%	Shopify 47.0%, Amazon 14.2%, Faire 38.2%. Amazon compressed by platform fees (24-44%). Inherits COGS drift. 2.3% uncovered = free/sample items.	Spot-check: pick 5 high-volume SKUs, manually compute margin from source numbers. Should match within $0.50/unit.
●	Product Catalog	BOM Excel + map_amazon_packs + manual adds	186	100%	$0	Zero unmapped revenue. All ASINs mapped. 36 free/sample items included. New products need manual adds.	Run: SELECT product_id FROM fact_orders WHERE product_id NOT IN (SELECT product_id FROM dim_products). Should return 0.
●	Customer Data	Shopify order CSV → dim_customers	65,657	100%	0%	Shopify customers only. Amazon customers not trackable (no PII). Cohort-ready with order history.	Compare COUNT(DISTINCT customer_id) to Shopify Admin → Customers → total.
●	Finance (25 tables)	Nikita's Excel → load_finance.py, load_budget_and_launch.py	16,638	100%	<1%	P&L, BOM, cash flow, budget, seasonal launch, revenue projections. ⚠️ P&L has duplicate-label rows for gross/net — must filter by category column.	Compare P&L totals to QuickBooks monthly. Must filter by category to avoid 2x. See export_finance_json.py for correct patterns.
●	Windsor MCP	Windsor.ai ($99/mo) — 7 sources connected	—	7/7	—	Shopify row cap (~1,400/query — use weekly chunks). Amazon Ads has same 60-day lookback. Klaviyo revenue data flowing.	Test pull: Windsor get_data for Shopify, last 3 days. Should return order-level data. If empty, check connector status.
●	Klaviyo Attribution	Windsor MCP → Klaviyo connector (account WS9338)	15 months	100%	—	$3.5M attributed revenue (Jan 2025–Mar 2026). 51 active flows. Monthly data embedded in QW dashboard. Peak: Nov 2025 ($497K).	Compare monthly revenue to Klaviyo → Analytics → Revenue. Wired into Quadruple Whale dashboard.
●	Daily Aggregates	fact_shopify_daily (78 days) + fact_amazon_daily (733 days)	811	100%	<0.1%	Shopify: 2026 YTD, matches Analytics within $136. Amazon: Mar 2024–Mar 2026, matches Business Report within 0.1%.	Compare any day to Shopify Analytics or Amazon Business Report daily view.
●	Customer Cohorts	ETL: build_cohort_module.py → is_first_order, cohort_month, base_order_id	389,577	100%	2pp (2025)	190,553 base orders. 183K first / 206K repeat. 2025+ within 2pp. Session 24: 5,901 pre-2024 customers corrected, 2025+ now Green. 2024 still Yellow (sentinel date ~2023-01, approximate).	Compare returning rate to Shopify Analytics → Customers. 2025+ within 2pp. ▸ Yellow: 2024 still on sentinel dates pending full pre-2022 history export.

● Green — validated, safe for reporting ● Yellow — directional, annotate in output ● Red — missing or placeholder, never report

DuckDB totals (Session 31 — March 26, 2026): 389,577 order rows (305K Shopify + 82K Amazon + 1.6K Faire) · 3,600 ad spend rows ($2.1M) · 186 products (150 with COGS) · 65,657 customers · 73,564 settlement fees · 25 finance tables (16,638 rows) · 811 daily aggregate rows · 1,867 Amazon returns · Cohort module (190K base orders, first/repeat tagged). 38 tables total. Three channels operational. Zero date gaps. Zero unmapped revenue. 97.7% contribution margin coverage. Zero Red fields remaining.

Cross-Validation — DuckDB vs. QuickBooks / Source Systems

These checks compare fact_orders (order-level data) against finance_pnl_monthly (QuickBooks P&L). Known gaps are documented — the goal is to track variance, not eliminate it (QB includes items like reimbursements and Shop Cash that aren't in order data).

Check	Source A	Source B	A	B	Delta	Status	Notes
2024 Net Revenue	fact_orders	finance_pnl (QB)	$2,655,577	$2,733,805	$78,228 (2.9%)	● Expected	QB includes shipping revenue ($35K) + Shop Cash + Amazon reimbursements ($137K). Net of offsets aligns. R46-R48.
2025 Net Revenue	fact_orders	finance_pnl (QB)	$3,323,249	$3,387,837	$64,588 (1.9%)	● Expected	Same QB adjustments. Lower gap because Amazon reimbursements smaller in 2025.
Shopify fee rate	fact_orders	Payouts export	3.1% (2024)	3.11%	~0%	● Match	Actual from 953 Shopify payouts. Consistent 2024-2026.
Amazon fee rate 2025	fact_orders	Settlement reports	34.6%	34.0%	0.6%	● Close	94.6% matched to per-order settlement data. Small gap from gross revenue rounding.
Amazon fee rate 2024	fact_orders	Settlement reports	41.8%	39.9%	1.9%	● Gap	fact_orders gross ($734K) lower than settlement gross ($754K) by ~$20K. 8% of rows use avg rate.
2025 Total Ad Spend	fact_ad_spend	QB Marketing	$1,091,580	—	—	● Loaded	Meta $847K + Google $139K + Amazon $105K. Reconciled with QBO: Media $1,002K + Amazon Ads $112K = $1,114K. Gap: $22K = Collabs/Influencer (not in fact_ad_spend).
2024 Amazon Ad Spend	fact_ad_spend	Amazon Ads Console	$59,455	$59,455	$0 (exact)	● Match	Console monthly export, 525 rows, 110 campaigns (Session 23). Previously estimated ~$82K, actual $22.5K lower.
G&A table integrity	finance_ga_detail	Audit (Session 19)	265 rows	—	$947K COGS in G&A	● Issues	Warehouse Rent + Labor landed in G&A from Excel layout. 2 garbage names. 4 ambiguous items. See N6.

Last validated: Session 31 (March 26, 2026). See DECISIONS.md R46–R50 for full reconciliation notes.

Data Freshness — Last Update Timestamps

Automated monitoring of table update frequencies. Each data source has a target refresh interval (e.g., orders every 30 days, ad spend every 7 days). Green = fresh, Yellow = aging (within 2x limit), Red = stale (beyond 2x limit). Sorted by freshness status.

—

FRESH

—

AGING

—

STALE

Last checked

●	Table	Source	Last Updated	Age	Max Age	Priority	Status
Loading freshness data...

Data freshness checks are auto-generated by etl/data_freshness.py. See BACKLOG.md for data refresh scheduling.

What's Next — Closing Remaining Gaps

P0 — Recently Closed (No Red Fields)

✓Amazon 2024 ad spend — Console export (Jan 1–Dec 31, 2024). 525 rows, $59,455 actual (Session 24). Upgraded to Green. March 25.Done

P1 — Improves Precision (Yellow → Green)

Dan/NikitaUpdated BOM per SKU — Complete COGS database from costing system. Replaces current Yellow BOM data with verified RM costs and task-level labor.Soon

Dan/NikitaAmazon BOMs — Build proper pack-level BOMs with actual packaging/labeling materials cost per ASIN. Replaces current $1.00/pack flat estimate.Soon

DanAmazon 2026 settlement (post-Mar 10) — Upload remaining Q1 settlement reports. Currently 67% matched to actuals (24.8%), rest on 32.2% flat estimate.Soon

DanCohort backfill (pre-2024) — Partial backfill done Session 24: 5,901 customers corrected. Remaining gap: full pre-2022 history export. 2024 still Yellow (sentinel date ~2023-01 approximate).Soon

Recently Closed

✓QBO reconciliation bridges documented — Revenue 2-3%, ad spend 0.5-4.6%, COGS structural diff. See docs/QBO_Reconciliation_Bridges.md. March 26 (Session 31).Done

✓Scale variance analysis — Annual -$4,406 (0.8%), BOM rates embed overhead. No split needed. March 26 (Session 31).Done

✓AOV by Order Number + Customer Frequency panels fixed — Correct order-level computation, time/channel filtering. March 26 (Session 31).Done

✓ShipStation shipping costs — 108K shipments, $989K carrier fees, Dec 2023–Mar 2026. 95% Shopify match. Upgraded to Green. March 23.Done

✓Amazon derived COGS — 25 ASINs updated via spray component COGS + $1.00/pack packaging. Amazon CM coverage 64% → 100%. March 23.Done

✓Amazon 2026 settlement — 6 reports loaded, 24.8% actual fee rate. March 23.Done

✓8 unmapped ASINs — All added to dim_products. 186 products, zero unmapped. March 23.Done

✓Amazon 2026 Q1 ad spend — Jan 1-21 loaded from console. Full Q1, $13K. Upgraded to Green. March 23.Done

✓Klaviyo attribution wired — $3.5M revenue across 15 months (Jan 2025–Mar 2026). 51 flows. Embedded in QW dashboard via Windsor MCP. March 24.Done

✓Customer cohort module — 190K base orders, first/repeat tagging, cohort_month. 2025+ validated within 2pp of Shopify Analytics. March 24.Done

✓Faire tagged as third channel — 1,626 orders from 67 customers ($122K gross, $110K net) identified via email pattern. March 24.Done

Ongoing Pipeline — Keeping Data Current

Windsor MCP handles live pulls for orders (Shopify, Amazon), ad spend (Meta, Google, Amazon Ads), GA4 sessions, and Klaviyo email attribution — all on demand during Cowork sessions. The items below are the manual feeds that still need human action.

DanAmazon Settlement Reports — Download from Seller Central → CSV upload → per-order fee replacement. Each upload closes ~2 weeks of fee estimates.Bi-weekly

NikitaFinance Excel — Updated P&L/BOM/cash flow → load_finance.py + export_finance_json.py → Finance dashboard refresh.Monthly

DanShipStation Export — CSV export for shipping cost refresh. No MCP connector available.Monthly

DanShopify Order CSV — Quarterly full export to catch any new products, format changes, or refunds. Load via load_shopify.py.Quarterly

Data Sources & Health

P0 — Recently Closed (No Red Fields)

P1 — Improves Precision (Yellow → Green)

Recently Closed

Ongoing Pipeline — Keeping Data Current