A dbt project modeling a plant-based meal kit subscription business end to end, from raw landing tables through to marts a BI tool can point at. 523,000 orders, 20,000 customers, 24 months.
A $9.99 monthly tier buys free delivery and 5% off meals. It collects $383,696 against $1,788,961 of benefit given away, and recovery falls from 0.78 on light users to 0.21 on heavy ones. Free delivery is worth most to the people who order most, so they are the ones who buy it. See the economics →
Recovery improves to 32.2% and the programme still loses $1.21M. Break-even sits at $46.58 a month, because the benefit scales with usage while the fee is flat. This turns out to be a benefit-structure problem wearing the costume of a pricing problem. See the sensitivity →
A meal-quality complaint raises the cancel rate by 21.7 points and a delivery complaint by 16.3. A billing question moves it by under half a point. Treating “contacted support” as one signal averages a strong effect together with nothing at all. See the comparison →
Cohorts lose roughly 20% by month two and 30% by month three, then flatten. Censored cells are flagged so incomplete cohorts do not get read as churn. See the triangle →
The slowest operator in the project was a row_number() window in
a staging model at 1,932 ms, thirty times the range join I went looking for.
Materializing that one model as a table cut total build time
29%.
See the profile →
The shape of the work
Two of these needed a modeling decision before they became true. The membership question needed a ratio, because a profitable box hides an unprofitable programme and a simple profit test returns nothing. The churn question needed a confound removed, because the first version had the sign backwards on two categories. Both are written up on their tabs.
mart_membership_economics
The business runs a free tier and a $9.99 monthly tier that buys free delivery and 5% off meals. The question was whether the fee covers what the benefit costs. It does not, and the gap is widest on exactly the customers you would assume are the best ones.
| Plus members, by order frequency | Members | Fees collected | Benefit given | Recovery |
|---|---|---|---|---|
| one to two boxes / month | 173 | $1,818 | $2,333 | 0.779 |
| two to three / month | 271 | $4,925 | $14,043 | 0.351 |
| three or more / month | 4,495 | $376,713 | $1,772,584 | 0.213 |
Across the tier: $383,696 collected against $1,788,961 given away, a $1.41M net programme cost at 21.4% recovery. This is adverse selection behaving the way it does in the real world. The benefit is most valuable to heavy users, so heavy users are who self-select into paying for it.
Why the headline is a ratio
Asking whether a segment is unprofitable returns no on every row
here, because the boxes themselves make money. A profitable box hides an
unprofitable programme. Only fees measured against benefit given away
exposes it, which is why fee_recovery_ratio is the headline
column and is_loss_making sits underneath it as a footnote.
Paid members last 241.8 days against 204.6, and 51.84% remain active against 41.76%. The lock-in is genuine. It does not cover the margin it costs:
| Tier | Months | Contribution / member-month | Lifetime value |
|---|---|---|---|
| basic | 6.82 | $126.18 | $837 |
| plus | 8.06 | $106.70 | $827 |
Paid members last 18% longer and earn 15% less per member-month, so lifetime value lands within about 1% either way. That is the most uncomfortable version of the result. The programme is not destroying value outright. It is spending $1.41M in subsidy to move customers between two buckets worth very nearly the same.
Box economics are per order: revenue collected, less food cost, less what the delivery actually cost. The membership fee arrives per month. Folding a monthly fee into a per-order margin is the fastest way to get subscription unit economics wrong, so the two are computed at their own grains and combined only at the end, per member per month.
mart_membership_pricing
The obvious response to a programme recovering 23 cents on the dollar is to charge more. This asks how far that gets, and which half of the benefit is doing the damage.
| Monthly fee | Fees collected | Recovery | Net programme cost |
|---|---|---|---|
| $9.99 (current) | $383,696 | 21.4% | −$1,405,265 |
| $12.99 | $498,920 | 27.9% | −$1,290,041 |
| $14.99 | $575,736 | 32.2% | −$1,213,225 |
| $19.99 | $767,776 | 42.9% | −$1,021,185 |
| $24.99 | $959,816 | 53.7% | −$829,145 |
| $29.99 | $1,151,856 | 64.4% | −$637,105 |
| $46.58 (break-even) | $1,788,961 | 100.0% | $0 |
$14.99 leaves $1.21M on the table. Break-even sits at $46.58, more than four times the current price, because the average paid member takes 3.82 boxes a month and every one of them carries free delivery. The fee is flat while the benefit scales with usage, so the gap widens with exactly the behaviour the programme is designed to encourage.
| Component | Total | Share | Per member-month |
|---|---|---|---|
| Delivery absorbed | $970,204 | 54.2% | $25.26 |
| Meal discount (5%) | $818,757 | 45.8% | $21.32 |
| Total benefit | $1,788,961 | 100% | $46.58 |
Delivery is the larger half at 54%, and it is also the one with a structural fix available. A discount rate can only be lowered, which every member feels immediately. Free delivery can be capped, which only the heaviest users feel at all.
What the numbers actually recommend
Capping free delivery at two boxes a month drops the benefit from $1.79M to $1.37M and lifts recovery from 21.4% to 28.0% at the current price. Holding that cap and moving the fee to $14.99 reaches 42.0%. Neither reaches break-even alone, and together they close roughly half the gap while leaving the benefit intact for the light and moderate users who are already close to paying for themselves.
A limit worth stating plainly
This calculation is static. It contains no demand elasticity, so it assumes every member stays after a price rise. They would not, and the ones who leave first are the light users, who are the profitable ones. Every recovery figure above is therefore an upper bound and the true curve is worse. Modeling that properly needs a price-response estimate this dataset cannot supply.
mart_churn_drivers
Split by category, only two categories carry any signal. A customer whose box arrived damaged or inedible is leaving. A customer with a billing question is behaving like everybody else.
Account contact keeps a −5.3 point gap that the correction did not remove. With roughly 300 exposed subscriptions behind it, that sits inside the range chance produces, and it is reported here as an open question instead of a fifth finding.
| Contact category | No contact | Contacted | Delta |
|---|---|---|---|
| delivery | 48.16% | 64.48% | +16.32 pts |
| meal quality | 47.99% | 69.71% | +21.72 pts |
| billing | 48.82% | 48.38% | −0.44 pts |
| account | 48.90% | 43.65% | −5.25 pts |
A confound had to come out first
Exposure was originally defined as a ticket in the 30 days before a subscription ended. For a subscription that lasted ten days, that window reaches back before it existed, so short-lived subscriptions came out systematically unexposed. Short-lived subscriptions are also the ones that churned, which pushed every category toward “contact predicts staying.” The first run showed billing as strongly protective, and that was an artifact.
The fix clamps the window to the subscription start and restricts the comparison to subscriptions with a full 30 days of tenure. Afterwards the the real effects grew and the spurious one collapsed. Delivery moved from +12.4 points to +16.3 and meal quality from +17.8 to +21.7, while billing fell from a spurious −3.8 to −0.4. Real effects strengthening while spurious ones fall away is what removing a bias looks like. Introducing one moves the numbers the other way.
“Customers who ever contacted support churn more” is true of almost any dataset for a boring reason: longer-tenured customers have more opportunity both to contact support and to cancel. Scoping exposure to a fixed 30-day window before the span closes puts churned and active subscriptions on the same footing. Active subscriptions serve as the controls, censored at the analysis end date, so a customer who complained last week and is still here counts as exposed and did not churn.
mart_retention_cohorts
Percentage of each signup cohort still active. Read a row across to see one month's signups decay. Read a column down to see whether the business is getting better at keeping people.
| Cohort | Size | M1 | M2 | M3 | M6 | M9 | M12 |
|---|---|---|---|---|---|---|---|
| 2024-09 | 791 | 92.0 | 81.5 | 71.2 | 59.4 | 52.2 | 44.4 |
| 2024-10 | 915 | 93.7 | 80.3 | 67.7 | 56.2 | 48.7 | 42.4 |
| 2024-11 | 844 | 93.8 | 78.9 | 71.5 | 60.4 | 50.6 | 44.2 |
| 2024-12 | 856 | 93.2 | 79.9 | 71.3 | 57.4 | 49.3 | 42.8 |
| 2025-01 | 876 | 93.6 | 80.9 | 72.5 | 59.7 | 50.1 | 42.5 |
| 2025-02 | 834 | 94.4 | 82.2 | 73.7 | 63.3 | 56.1 | 48.0 |
| 2026-06 | 865 | 93.0 | 80.2 | n/a | n/a | n/a | n/a |
| 2026-07 | 906 | 93.2 | n/a | n/a | n/a | n/a | n/a |
Two ways this normally goes wrong
The denominator moves. Cohort size is fixed at month zero and carried across every cell, so retention always divides by the same number. Recomputing it per cell silently reports “still active among those still observed” instead of “still active out of everyone who joined,” which flatters every chart it touches.
Censored cells get read as churn. The newest cohorts carry
no month-3 number because they have not existed for three months, and the
gaps above are the flag doing its job. An earlier draft of this page filled
them from the raw column and produced a 1.8% month-2 figure sitting beside
a 93.2% month-1, which looks like collapse and is only partial data. Filling
those with zero would look like catastrophic churn when it is missing
observation, so is_fully_observed marks which cells are safe
to compare and the gaps above stay honest.
Every mark below is generated from the marts at build time. No charting library and no external requests: the page is self-contained, and keeping it that way was worth more than the convenience of a CDN import. Each chart has the same numbers in table form on its own tab.
One line per fully observed cohort in grey, the mean across them picked out. Cohorts move together, so the shape is the finding and the individual lines are context. Hover any point for the value.
Roughly a fifth of a cohort is gone by month two and a third by month three, after which the curve flattens. Only cohorts with a full twelve months are drawn, so no censored cell is plotted.
Fees collected as a share of benefit given away, by how often a paid member orders. The dashed line is break-even.
Light users nearly pay for themselves. The heaviest band recovers about a fifth, and it holds 4,495 of the 4,963 paid members, so it sets the blended result almost single-handedly.
Recovery against monthly fee, holding usage fixed. Both endpoints are marked: today's price and the price that would actually cover the benefit.
The line is straight because this calculation carries no demand elasticity. Every member is assumed to stay at any price, which nobody would, so the curve is an optimistic bound and the real one bends down as the light users leave first.
Change in cancellation rate after a support contact, against members who had none. Right of the line is worse.
Delivery and meal quality move the rate by more than sixteen points. Billing does not move it at all. Account sits five points to the good on about three hundred exposed subscriptions, which is inside what chance produces, so it is an open question and not a fifth finding.
Profiling this project produced two results that contradicted what I would have guessed, and one of them turned into a change worth keeping.
Node time totals 25.7 seconds while the wall clock reads 8.6, so parallelism is returning about 3x. The split is the surprising part: 147 tests cost nearly twice what all 16 models cost to build. Anyone tuning this project by reading model SQL would be optimising the smaller half.
EXPLAIN ANALYZE on the heaviest downstream query, expecting the
range join in fct_subscription_months to dominate:
| Operator | Time | ms | Rows out |
|---|---|---|---|
WINDOW row_number() dedup | 1932.0 ms | 522,957 | |
IE_JOIN the range join | 65.4 ms | 156,229 | |
PROJECTION | 41.8 ms | 522,957 | |
SEQ_SCAN read raw.orders | 24.4 ms | 522,957 | |
FILTER | 2.7 ms | 522,837 |
The range join costs 65 ms. The row_number() deduplication in
stg_orders costs 1,932 ms, thirty times more
than the thing I went looking for. And because staging was materialized as a
view, that window re-executed for every model and every test that read
stg_orders, which is most of the project.
The fix was one line
Materializing stg_orders as a table so the window runs once:
| Models | Tests | Node total | Wall | |
|---|---|---|---|---|
| staging as views | 13.27s | 23.07s | 36.34s | 11.03s |
stg_orders as a table | 9.22s | 16.46s | 25.68s | 8.58s |
| change | −31% | −29% | −29% | −22% |
Both halves fell by about a third, which is the confirmation the diagnosis was right: models and tests alike were paying for that window every time they read staging. Both rows were measured on the same dataset, one after the other, with only the materialization changed.
What it cost
The change is not free. Every model that reads stg_orders now
depends on a table that has to be built first, so
dbt run --select fct_orders in isolation reads stale staging
and silently processes nothing. The selector has to become
+fct_orders. As a view that was implicit, and losing it is a
real cost that a build-time number alone would not show.
| Model | Build time | ms |
|---|---|---|
| fct_orders | 2,471 ms | |
| stg_orders | 1,654 ms | |
| mart_retention_cohorts | 1,263 ms | |
| fct_subscription_months | 730 ms | |
| dim_customers | 607 ms | |
| int_subscription_spans | 316 ms | |
| stg_subscriptions | 276 ms | |
| stg_order_items | 250 ms | |
| int_customer_order_facts | 239 ms | |
| stg_support_tickets | 233 ms | |
| mart_membership_economics | 229 ms |
Three models are 58% of model build time. stg_orders sits
second by design: the cost moved there from everywhere else, which is the
whole idea.
fct_orders is the only incremental model, and after a week of
new deliveries lands it processes 7,586 rows instead of 530,343:
| Run | Time | Rows processed |
|---|---|---|
| full refresh | 2.32s | 522,757 |
| incremental | 0.25s | 7,586 |
| plus upstream stg_orders rebuild | 1.52s | 530,543 |
The model itself is 9x faster. Counting the upstream rebuild the honest figure is 1.77s against 2.32s, so the table materialization still wins, by less than the model timing alone suggests. On a full build or a test run it wins clearly.
Keeping this in proportion
The whole project builds in under nine seconds. Nothing here needed optimising, and a 1.9 second operator is not a production incident. What this section demonstrates is the method: measure before guessing, since the bottleneck sat in a staging model and not in the analytical SQL where I expected it, and check what a change costs as well as what it saves.
At warehouse scale the same reasoning has real money attached, because a view re-executed by fifty downstream consumers is billed fifty times.
DuckDB and dbt-core, running on a laptop. No warehouse account, no credentials, no cost. Seven staging models, two intermediate, seven marts, one SCD Type 2 snapshot, six singular tests. Every model, source, snapshot and column carries a description, so the generated data dictionary is complete.
order_id is unique. Empty-string regions collapse to
NULL, because empty and unknown are different things and only
one of them aggregates correctly.
The arithmetic closes: 522,957 raw orders, minus 120 duplicates removed in
staging, minus 80 orphans dropped by the inner join in
fct_orders, gives the 522,757 rows in the fact table.
Uniqueness, not-null and referential integrity say nothing about whether a number is right. A flipped sign on the membership discount, a credit applied twice, or COGS computed off the wrong base passes every one of them. Six singular tests cover the assertions that generic tests cannot express:
Staging and intermediate are views, because nothing queries them directly and rebuilding costs nothing. Marts are tables, because a dashboard hits them repeatedly and the build cost is better paid once.
fct_orders is incremental with delete+insert.
Append would be faster, but it assumes the incoming slice never overlaps what
is already stored, so a late-arriving delivery would be counted twice.
Deleting matching ids first makes re-running the same window idempotent.
Measured on roughly 500k orders: 1.93s full refresh against 0.47s
incremental.
DuckDB is a profile choice. The architecture does not depend on it, and the
models move to Snowflake or BigQuery by pointing profiles.yml at
a different adapter. Exactly two places would need editing, both commented in
place: the date_diff spelling, and the deduplication in
stg_orders, which could collapse to QUALIFY on
warehouses that support it.
The subscriptions source holds current state only, so a plan change
overwrites the old value and the history disappears. That makes “did
revenue rise because we raised prices or because people upgraded”
unanswerable. A snapshot writes dbt_valid_from and
dbt_valid_to alongside each version, so a point-in-time join can
ask what plan a subscription was on when a particular box shipped.
The data is simulated
Which makes the membership conclusion a consequence of its assumptions. Nothing here is a discovered fact about meal kits. It is most sensitive to COGS as a share of gross: at 42% the box margin is fat enough to swallow the subsidy entirely and the finding disappears, while at the 62% used here, closer to published meal kit gross margins, it holds. What the model provides is that the sensitivity is explicit and answerable.
Acquisition channel has no effect, and the data says so.
Active rates sit between 43.6% and 45.3% across all five channels, with
lifetime revenue between $1,952 and $2,046. This is an absence in the
simulation. It says nothing about whether channel matters in a real business,
since no channel effect was ever put in. The dimension stays in
dim_customers because a real business would segment on it, and
reporting the flat result seemed more useful than quietly dropping the column.
Also absent: pauses and reactivations, so each customer holds exactly one subscription. That means the singular test asserting no customer holds two overlapping subscriptions passes trivially. It is a correct test of a real business rule that this dataset cannot currently violate.
The exercise here is modeling and testing. Half a million rows is enough to make grain, incrementality and join correctness matter, and small enough to rebuild in twenty seconds.