DTC year-end capstone — board KPI summary

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

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.

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.

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.

Shopify-Shaped Schema

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.

Three-System Reconciliation

Refund-aware revenue, repeat-purchase cohorts, RFM segmentation, abandoned-cart funnels, source/medium attribution. The full DTC analyst toolkit.

Refund + Cohort + RFM SQL

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.

Interview + Job-Ready

Shopify says $284K. Stripe says $271K. GA4 says $309K.

Meet the orders table

What financial statuses exist?

Customers by country

Drop the voided / refunded orders

Orders over $200 by customer

Revenue by financial status

Orders by month

Repeat-customer rate

Average order value by channel

Products with zero orders

Top 10 products by units sold

Refund rate by product

Source/medium attribution — last-touch

Top 3 products per category by revenue

Refund-aware net revenue by month

Most recent order per customer

Shopify vs Stripe reconciliation

Repeat-purchase cohort retention

GA4 conversion-value vs Shopify revenue

Discount-driven AOV impact

RFM segmentation — customer scoring

Three-system revenue reconciliation

Abandoned-cart funnel by source

DTC growth dashboard

Board-ready DTC KPI summary

Repeat-buyer cohort retention

Multi-touch attribution — last-click vs first-click vs linear

GA4 vs Shopify drift forensics

FX-adjusted net revenue per channel

customer_id

email

first_name

last_name

accepts_marketing

country

created_date

total_orders

total_spent

ecom_customers

product_id

title

vendor

product_type

price

inventory_qty

ecom_products

order_id

order_number

financial_status

fulfillment_status

total_price

subtotal

shipping

discount_total

channel

processed_date

ecom_orders

line_item_id

quantity

ecom_line_items

session_id

source

medium

campaign

landing_page

device

session_start

session_end

pages_viewed

add_to_carts

purchases

conversion_value

ecom_ga4_sessions

charge_id

stripe_charge_id

amount

currency

status

captured

refunded

ecom_stripe_charges

DTC year-end capstone — board KPI summary

The Brief

You'll practice