E-commerce & Retail Analytics
Shopify-vs-Stripe-vs-GA4 reconciliation, refund-aware revenue, RFM segmentation, repeat-purchase cohorts — the SQL DTC and retail analysts actually write.
30 missions|6 tables
- “What financial statuses exist?”
- “Top 10 products by units sold”
- “Repeat-purchase cohort retention”
- Shopify-Shaped Schema — Customers, products, orders, line items, with the financial_status / fulfillment_status fields you’ll see on day one of any DTC shop. Familiar to anyone who’s opened a Shopify export.
- Three-System Reconciliation — GA4 sessions and Stripe charges sit alongside Shopify orders with planted mismatches — partial refunds, currency conversion fees, failed captures, GA4 conversion-value drift. The signature mission ties all three together.
- Refund + Cohort + RFM SQL — Refund-aware revenue, repeat-purchase cohorts, RFM segmentation, abandoned-cart funnels, source/medium attribution. The full DTC analyst toolkit.
Subscription Commerce Analytics
The SQL patterns DTC subscription analysts at Athletic Greens, Hims, Olipop, and Magic Spoon write every week — MRR walk decomposition (new / expansion / contraction / churn / resurrection), cohort retention from event reconstruction, pause-vs-churn forensics (the gotcha that misclassifies ~40% of voluntary cancels in real warehouses), snapshot-vs-event drift reconciliation, NRR for cohorts that change plans, blended subscriber-vs-one-time LTV, and the full multi-CTE MRR walk capstone — against an isolated 24-month replenishment schema with 8 event types, derived daily snapshots, and deliberately-seeded late-arriving events. Modern unit economics for replenishment subscription, on a real-shape event store.
25 missions|8 tables
- “Active subscribers by country”
- “First event per subscription”
- “NRR by signup cohort”
- Event-Sourced Subscription Schema — Eight canonical event types (created / renewed / skipped / paused / resumed / plan_changed / cancelled / churned) on a real Recurly/Chargebee-shaped event log. Snapshots derived in the seed for the M1–M10 free tier; events drive the M11+ Pro tier.
- Pause-vs-Churn Forensics — The most underserved skill in subscription analytics. Real warehouses misclassify 30–40% of voluntary cancels as churn when they’re actually pause-shaped (resumed within 60 days). M20 is the cleanup mission; M22 catches resurrection. The pedagogy nobody else teaches.
- Snapshot-vs-Event Reconciliation — Late-arriving events (posted_at != event_ts) drive deliberate drift between the snapshot table and event-derived state. M13 surfaces it; M23 audits it end-to-end. Direct analog of the celebrated Shopify/Stripe/GA4 reconciliation in the ecommerce path.
Marketplace Seller Analytics
The SQL patterns marketplace seller-side analysts at Amazon / Etsy / eBay sellers actually write — Buy Box win-rate forensics, GMV-vs-payout drift detection, four-way settlement reconciliation (orders / fees / ads / refunds), listing-level dead-stock identification, ad-spend ROAS at ASIN grain, and cross-marketplace cannibalization — against an isolated 24-month seller dataset modeled on Amazon Seller Central + Etsy + eBay reporting shapes. Modern unit economics for marketplace sellers, on a real-shape settlement ledger.
25 missions|6 tables
- “Daily orders by marketplace”
- “Buy Box win-rate by hour of day”
- “Cross-marketplace cannibalization”
- Buy Box Dynamics in SQL — Marketplace sales depend on Buy Box ownership at the moment a customer views your listing. Win-rate by hour, loss-window revenue impact, defense playbook — the SQL patterns sellers run when their daily revenue cratered without a price change.
- GMV ≠ Payout — Marketplace fees, ad spend, fulfillment fees, and refunds all live in different report shapes. M16 reconciles four fee types to the daily payout; M21 is the three-way settlement capstone. The reconciliation skill every marketplace seller analyst hires on.
- Cross-Marketplace Cannibalization — You sell the same SKU on Amazon, Etsy, and eBay. Which one cannibalizes your DTC margin? M18 splits the cohorts; M22 ranks listings by all-in margin (gross − marketplace fees − ad spend − returns).
Omnichannel Retail Analytics
The SQL patterns omnichannel retail analysts at Target / Best Buy / DSG actually write — BOPIS conversion forensics, ship-from-store cost economics, web-vs-store cannibalization on identical SKUs, store-level CM2 by channel mix, regional inventory rebalancing, and the multi-channel store P&L composite — against an isolated 24-month dataset covering 50 stores, 4 channels, daily store-SKU inventory, and seeded gotchas around channel-mix margin spread and foot-traffic-but-no-purchase events. Modern omnichannel unit economics for retailers running brick + click in parallel.
25 missions|6 tables
- “Top stores by revenue”
- “Channel cannibalization detection”
- “Foot-traffic-to-purchase funnel by store”
- BOPIS Conversion Forensics — Buy-online-pickup-in-store has lower handling cost but consumes store labor. M7 builds the conversion rate; M17 attributes incremental lift vs counterfactual; M23 splits CM3 by region. The questions every BOPIS program manager faces.
- Ship-from-Store Economics — Stores fulfill ~12–20% of web orders for proximate customers. Cost spread across local carriers, weight bands, and zone density. M13 quantifies the spread; M19 optimizes regional rebalancing.
- Channel Cannibalization Detection — Same SKU sold via 4 channels (web-ship, web-BOPIS, store-walk-in, store-ship-from-store). M11 detects same-SKU dual-channel patterns; M22 audits cannibalization fully. The omnichannel-vs-DTC purity test.
Quick Commerce Analytics
The SQL patterns quick-commerce analysts at Instacart / DoorDash / Uber Eats actually write — substitution-rate impact on GMV, shopper batching efficiency, three-sided unit economics (shopper take + platform take + merchant take = customer pay), shopper attrition cohort analysis, surge pricing impact, and merchant churn forensics — against an isolated 24-month multi-sided marketplace dataset with ~200 shoppers, ~80 merchants, ~5K customers, ~40K orders, and ~6K substitution events. Modern multi-sided marketplace economics, on a real-shape three-party event ledger.
25 missions|6 tables
- “Shopper roster basics”
- “Substitution impact on GMV”
- “Multi-sided fee waterfall”
- Three-Sided Unit Economics — Shopper take + platform take + merchant take = customer pay. M21 builds the three-sided P&L; M18 walks the multi-sided fee waterfall. The unit-economic view that operators ship every quarter.
- Substitution Event Impact — Item out of stock at pick → shopper swaps with customer agreement. Customer pays original price, shopper picks cheaper sub, merchant gets paid for what they had. M11 quantifies GMV impact; M14 tracks customer-retention exposure; M25 optimizes substitution policy.
- Shopper Batching Forensics — One dispatch fulfills 2–3 orders. Naive per-order shopper economics double-count batched dispatches. M12 surfaces the bug; M16 builds shopper quality scoring with batch normalization.