Subscription Commerce Analyst career guide

• Pure replenishment DTC

  Athletic Greens (AG1), Magic Spoon, Daily Harvest, Care/of, Trade Coffee, Pact Coffee, Quip. Mono-SKU or shallow SKU tree, monthly cadence dominant, classic skip/pause behavior. The cleanest version of the job — small data team (3–8), high analyst leverage, MRR walk every Monday.

• Telehealth + subscription

  Hims, Hers, Ro, Lemonaid. Subscription on top of a clinical encounter. More compliance overhead (HIPAA), more customer-success integration, broader product surface than pure consumables.

• Hybrid one-time + subscription

  Glossier, Olipop, Allbirds. Subscription is one motion among many. The interesting analytical question is the conversion from one-time buyer to subscriber and the LTV multiple between the two cohorts (Mission #17 territory).

• Subscription billing platform

  Stripe Billing, Recurly, Chargebee. You don’t analyze a single brand — you analyze thousands of merchant tenants on the platform. More scale, less domain depth. Often pays better than brand-side analytics; less satisfying if you wanted to influence a single brand’s product decisions.

• Curated box / subscription discretion

  Stitch Fix, BarkBox, FabFitFun. Different mechanics — keep-rate matters more than churn, item-level discretion lives at the box-shipment grain. Less SQL-window-function-heavy, more recommender-system support. Treat as adjacent, not equivalent.

1. 1

   Recruiter screen (30 min)

   Lightweight fit check. Be ready to explain why DTC subscription specifically (vs SaaS, vs generic e-commerce). Brand-side teams want to hear product affinity; platform-side wants engineering depth.

2. 2

   Hiring manager (45–60 min)

   Behavioral + domain depth. Expect a case-style walkthrough: "MRR is up but new subscribers are flat — what’s going on?" They’re testing whether you reach for plan changes (expansion), pause-vs-churn cleanup (resurrection in disguise), or pricing changes — fluently — without having to be prompted.

3. 3

   Technical SQL screen (60–90 min, usually take-home)

   Postgres-style schema with an event log and snapshot table. Common asks: write the MRR walk for the last quarter, build the 90-day cohort retention curve, identify the pause-shaped cancellations. Edge cases matter — "what happens to MRR when a sub pauses, and why does the walk not balance perfectly?"

4. 4

   Cross-functional round (lifecycle / growth / finance)

   A non-analyst asks you to defend a metric or recommend a campaign trigger. They’re testing whether you can hold your own with the people who will read your dashboards. Don’t hide behind technical answers — the right answer is usually framed in customer behavior.

5. 5

   Director / VP round

   Strategy and prioritization. Common ask: "If you had 6 weeks, what would you build first as our new analyst?" The good answer is always "the MRR walk and the cohort retention curve, automated with documentation, before any new analysis."

“Walk me through the MRR walk decomposition.”

Five components from the event log: new (created mrr_delta), expansion (plan_changed mrr_delta > 0), contraction (plan_changed mrr_delta < 0), churn (cancelled + churned mrr_delta), resurrection (resumed mrr_delta). Plus opening MRR (snapshot at start of period, active state) and closing MRR (snapshot at end). Equation: opening + new + expansion + contraction + churn + resurrection ≈ closing. The ≈ is because paused-state flux doesn’t generate a paused mrr_delta — that’s the residual you explain in the deck footnote.

“Why are events the source of truth, not the snapshot table?”

Snapshots are derived from events at warehouse-roll time. Snapshots typically retain 6–12 months; events are forever. Cohort retention on a 12-month-old cohort is impossible from snapshots alone. Snapshots are also vulnerable to drift from late-arriving events (event_ts < snapshot_date < posted_at). Events are the audit trail.

“How do you compute 90-day cohort retention from events?”

For each subscription, find the latest event with event_ts <= started_at + 90 days. Map the event_type to a state: created/renewed/skipped/resumed = active; paused = paused; cancelled/churned = terminated. Aggregate by signup-month cohort: percent of cohort whose latest event before m3 maps to "active". The trap: counting "paused" as active inflates retention by 3-5%.

“What’s pause-vs-churn cleanup, and why does it matter?”

Real warehouses misclassify 30–40% of voluntary cancels as permanent churn when the customer re-subscribes within 60 days. The cleanup is a self-join on customer history that finds (cancel, re-create) pairs with a short gap. Identify those subs, route them to a re-onboarding flow. Lifecycle teams typically rescue 15–25% of "churned" customers this way — high-leverage analytical work.

“A subscriber downgrades from Daily Greens Annual to Sleep Stack Monthly. How does that show up in your metrics?”

Three things at once: (1) NRR contracts because their MRR drops from $59 to $49, (2) plan_family migration counter ticks up (Daily Greens → Sleep Stack), (3) cadence migration counter ticks up (annual → monthly). They’re still retained for count-retention purposes, but the unit-economic picture changed. This is exactly why NRR ≠ retention rate.

“How would you reconcile the snapshot table against the event log on a single date?”

For each (subscription, snapshot_date) row, derive the event-state from events with both event_ts <= snapshot_date AND posted_at <= snapshot_date. Compare event-state to snapshot.state. Disagreements arise because of late-arriving events (event_ts <= snapshot_date but posted_at > snapshot_date) — the snapshot was generated before the late event landed. Mission #23 walks the full multi-CTE version.

• Looker LookML developer certification

  Nice to have

  Useful at brands using Looker as their BI layer. Free LookML training; certification cost is moderate. Demonstrates you can model the MRR walk in LookML, not just write the SQL.

• dbt Analytics Engineering certification

  Nice to have

  dbt is the modeling layer at most mid-stage+ DTC brands. The cert is signal but the portfolio of dbt models matters more. Build a public sample dbt project for a fictional subscription brand if you don’t have one already.

• Stripe Billing Implementation certification

  Nice to have

  Particularly useful if targeting platform-side roles (Stripe, Recurly, Chargebee). Demonstrates fluency with the event vocabulary the platform emits.

• Google Analytics 4 / GA4 certification

  Skip

  Free and short, but DTC subscription analytics relies less on GA4 than on the subscription event store. Build the muscle on Klaviyo / Segment instead.

• CFA Level 1

  Skip

  Subscription analytics is finance-adjacent but the CFA is overkill. The relevant comp work is the MRR walk + LTV:CAC computation; both are learned by writing the queries, not by exam.

• Computing MRR by summing event mrr_deltas instead of querying the latest snapshot. Events are flows; MRR is a stock. The latest-day snapshot is the canonical "MRR right now".
• Treating "cancelled" and "churned" as the same event. Voluntary vs involuntary terminations have different reason codes, different teams own remediation, different intervention strategies.
• Including "paused" subscriptions in the active count for retention. Paused customers aren’t paying right now; investor norm is "active and paying".
• Anchoring cohort retention on customer_id when subscription_id is the right grain. A customer who cancelled and re-subscribed is one customer with two distinct subscriptions; per-sub retention and per-customer retention answer different questions.
• Forgetting the status = ‘succeeded’ filter on fact_charges when computing revenue. Failed charges are billing artifacts, not revenue — including them inflates LTV by 5–15%.
• Building cohort retention from snapshots when events are the right source. Snapshots only retain ~6–12 months in most warehouses; older cohorts are unmeasurable from snapshots.
• Ignoring the gap between event_ts and posted_at. The snapshot-vs-event drift this causes is the most-asked senior question.
• Pivoting churn forensics output (reason_code as rows, event_type as columns). The retention team wants the long format; pivots make trend-over-time and team-routing harder.
• Confusing NRR with retention rate. Retention is count; NRR is revenue. They diverge sharply when plan changes happen.
• Over-indexing on dashboards early. The MRR walk is one dashboard you should obsess over; everything else is downstream of having that working correctly first.

The caseSQL Subscription Commerce path can’t hand you a Stripe Billing tenant or an AG1 production warehouse — but the schema is the right shape, the gotchas are the same gotchas (late-arriving events, pause-vs-churn misclassification, snapshot-vs-event drift), and the canonical missions force the SQL skills the hiring loop screens on. The Hard tier (M11–M15) is the closest approximation of the live SQL screen; the Expert and Master tiers (M16–M25) are the take-home territory. M25 — the full MRR walk in one query — is the question you’ll be asked in some form on every senior subscription analyst loop.