E-commerce & Retail Analytics Path

Shopify says $284K. Stripe says $271K. GA4 says $309K.
Three systems, three numbers, and the CFO wants reconciliation by Friday.

The SQL patterns DTC and retail analysts actually write — Shopify order financial-status filtering, refund-aware revenue rollups, GA4 source/medium attribution joins, Stripe charge-vs-order reconciliation (the canonical "Shopify says X, Stripe says Y" problem), product-mix and category waterfall reports, repeat-purchase cohort retention, RFM segmentation, abandoned-cart funnels, and the multi-system reconciliation that closes the books at quarter-end — against a realistic Shopify + GA4 + Stripe dataset with planted mismatches. Written for career changers — DTC analytics roles (E-commerce Analyst, Retail Analyst, Growth Analyst) at brand-side companies pay $70–95K entry-level and hire on portfolio work like this.

See All Missions

30 missions (25 free · 5 Pro Master) · 6 tables · Shopify + GA4 + Stripe reconciliation

How It Works

1

Read the briefing

A Slack message from your manager

2

Explore the schema

5 tables in a star schema

3

Write your query

Full SQL editor with autocomplete

4

Get expert feedback

Graduated hints, not just pass/fail

Why This Path

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.

Interview + Job-Ready

Every mission maps to a published e-commerce / retail analyst prompt (Shopify, Stripe, Klaviyo, Glossier, Warby Parker, Allbirds, Wayfair, Etsy). Pair with Shopify Plus / Stripe certifications and you have a hiring edge.

The Missions

Each mission is a real request from someone at the company. Difficulty increases as you go.

Starter5 missions
1
Meet the orders tableFrom: Head of Growth
What financial statuses exist?From: Senior Analyst, Marketing
Customers by countryFrom: Head of Growth
Drop the voided / refunded ordersFrom: Senior Analyst, Marketing
Orders over $200 by customerFrom: Head of Growth
Easy5 missions
Revenue by financial statusFrom: CFO
Orders by monthFrom: Senior Analyst, Marketing
Repeat-customer rateFrom: Head of Growth
Average order value by channelFrom: Director, E-commerce
Products with zero ordersFrom: Director, E-commerce
Medium5 missions
Top 10 products by units soldProFrom: Director, E-commerce
Refund rate by productProFrom: CFO
Source/medium attribution — last-touchProFrom: Senior Analyst, Marketing
Top 3 products per category by revenueProFrom: Director, E-commerce
Refund-aware net revenue by monthProFrom: CFO
Hard5 missions
Most recent order per customerProFrom: Senior Analyst, Marketing
Shopify vs Stripe reconciliationProFrom: CFO
Repeat-purchase cohort retentionProFrom: Head of Growth
GA4 conversion-value vs Shopify revenueProFrom: Director, E-commerce
Discount-driven AOV impactProFrom: CFO
Expert5 missions
RFM segmentation — customer scoringProFrom: Head of Growth
Three-system revenue reconciliationProFrom: CFO
Abandoned-cart funnel by sourceProFrom: Senior Analyst, Marketing
DTC growth dashboardProFrom: Head of Growth
Board-ready DTC KPI summaryProFrom: CFO
Master5 missions
Repeat-buyer cohort retentionProFrom: Director, E-commerce
Multi-touch attribution — last-click vs first-click vs linearProFrom: Head of Growth
GA4 vs Shopify drift forensicsProFrom: Senior Analyst
FX-adjusted net revenue per channelProFrom: CFO
DTC year-end capstone — board KPI summaryProFrom: Director, E-commerce

The Database

A Shopify-shaped DTC schema with GA4 sessions and Stripe charges alongside, sized for a realistic mid-stage brand: 50 customers, 25 products, 100 orders across paid / refunded / partially_refunded / voided / pending statuses, 240 line items, 200 GA4 sessions across organic / paid / direct / email / social sources, and 100 Stripe charges with planted reconciliation mismatches — partial refunds, failed captures, currency-conversion fees, and GA4 conversion-value drift. The three-system data shape every DTC analyst sees on day one.

ecom_customers (50)ecom_products (25)ecom_orders (100)ecom_line_items (240)ecom_ga4_sessions (200)ecom_stripe_charges (100)

●dimension tables  ● fact tables

Three systems, three revenue numbers, one CFO who wants the answer Friday

Write the refund-aware revenue, the source/medium attribution, the Shopify-vs-Stripe reconciliation, and the RFM segmentation that ties the books to the brand.

Looking for something different?

|