DTC Growth Dashboard
The analytics surface behind a scaling DTC brand — revenue, cohort LTV, and multi-touch attribution off a code-first warehouse. Genericized replica for Marea (DTC apparel · trailing 12 months).
Marea · Analytics
Revenue, retention & attribution
Total Revenue
$9.2M
Total Orders
108.4K
Avg Order Value
$85
Total Customers
71.8K
Repeat Rate
34%
Refund Rate
4%
Revenue & orders
Monthly, trailing 18 months
Top products
By revenue, trailing 12 months
How it's built
Production system: a code-first ecommerce warehouse — Python extractors pull Shopify + ad-platform data into BigQuery, transformed by 54 dbt models into revenue, cohort-LTV, and multi-touch attribution marts (Markov removal-effect and Shapley value implemented from scratch in SQL). Served via a FastAPI + Next.js analytics app. This is a genericized, read-only replica of those views.
The business behind the numbers: the eight-figure DTC brand I ran.
the case study
The system behind this demo
This demo replicates three views of a production analytics application: the operating picture for the DTC brand in the flagship case study. The real system is seven views plus a conversational analyst, served over the warehouse — the daily revenue read, the cohort reviews, the attribution comparisons, and the acquisition economics, rebuilt as software anyone on the team can open.
in practice
The operating picture, opened daily
The dashboard view is the morning read: revenue and orders against trend, AOV, refunds, top products over any date range. The cohorts view runs the monthly retention review — a heatmap of every cohort's life, LTV at 30, 90, and 365 days, repeat rates, and curves split by acquisition channel. The acquisition view holds the unit economics: CAC, LTV-to-CAC, first-order ROAS, and a payback timeline showing when each channel's cohorts cross breakeven.
The attribution view settles arguments. Five models — first-touch, last-touch, linear, Shapley, Markov removal-effect — rendered side by side per channel, because a channel that looks strong under last-touch and weak under Shapley is telling you where it sits in the funnel, and one confident vendor number would hide that.
And for everything the fixed views don't answer, the chat analyst: ask a question in English, watch it write and run read-only SQL against the warehouse, get an answer with the query shown. The person asking doesn't need to know the schema. The analyst does.
architecture
How it's put together
Seven views on one API
Dashboard, cohorts, attribution, acquisition, products, AI reports, and chat — Next.js pages with SWR data hooks over a FastAPI backend: 7 routers, 28 endpoints, Supabase JWT auth, per-IP rate limiting, and a TTL query cache keyed on the SQL itself.
Cohorts that don't lie
Retention renders as cohort-month × period with monthly, weekly, and yearly granularity. Young cohorts are excluded from lifetime LTV comparisons because they haven't had time to spend — the maturity-bias correction is documented methodology, not a footnote.
Attribution as a comparison, not a verdict
The model-comparison endpoint returns all five attributions per channel in one payload, plus journey-position analysis (does this channel open journeys, close them, or assist?) parsed from each order's touchpoint array.
Acquisition economics, channel by channel
New customers, spend, CAC, LTV at three windows, first-order ROAS, repeat rate, and payback timing — computed by joining customer first-touch facts against daily channel spend, the same numbers the annual growth model was built from.
A text-to-SQL analyst with a leash
The chat view streams a Claude analyst whose system prompt carries the full warehouse schema, metric definitions, and seven worked examples. Its SQL tool is read-only by construction: SELECT/WITH only, dangerous keywords rejected on word boundaries, comments stripped before validation, LIMIT injected if missing, 30-second timeout.
Long conversations that don't fall over
The analyst manages its own context in two stages: at 80K input tokens it clears old SQL results (keeping the five most recent), at 120K it compacts the whole conversation, preserving goals and findings while discarding raw rows. Ask it questions all day; it keeps up.
engineering highlights
The parts I'm proud of
The analyst can't hurt the data
Handing an LLM live SQL access is only reasonable if the tool itself makes damage impossible. Validation strips comments first, so a keyword can't hide inside one, then enforces a read-only grammar and bounds every result.
sql_clean = re.sub(r"--[^\n]*", "", sql) # strip comments first
sql_clean = re.sub(r"/\*.*?\*/", "", sql_clean, flags=re.DOTALL)
if not re.match(r"(?i)^(SELECT|WITH)\b", sql_clean):
return False, "Only SELECT queries are allowed."
dangerous = r"\b(INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|TRUNCATE|...)\b"
if re.search(dangerous, sql_clean, re.IGNORECASE):
return False, "DDL/DML operations are not allowed."
if not re.search(r"\bLIMIT\b", sql_clean, re.IGNORECASE):
sql_clean = f"{sql_clean} LIMIT {MAX_RESULT_ROWS}" # bound every resultContext management as configuration
The analyst's memory policy is declared, not improvised: tool-result clearing and full compaction each have explicit token triggers, and compaction instructions preserve what matters (the question being chased, findings so far) while discarding what doesn't (raw result rows).
context_management={"edits": [
{ "type": "clear_tool_uses",
"trigger": {"type": "input_tokens", "value": 80_000},
"keep": {"type": "tool_uses", "value": 5} },
{ "type": "compact",
"trigger": {"type": "input_tokens", "value": 120_000},
"instructions": COMPACT_INSTRUCTIONS }, # keep goals+findings, drop rows
]}One query shape serves every date range
stack
Built with
Next.js 15 + Plotly (frontend, ~7K lines) · FastAPI (~4.7K lines: 7 routers, JWT auth, rate limiting, TTL cache, connection pooling) · Claude analyst with streaming SSE and per-conversation token accounting · the marts of the code-first warehouse (BigQuery + 54 dbt models) underneath.
The demo above is a genericized, read-only replica of three of the seven views, with invented figures at believable scale. The business this instrumented is the flagship case study.