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. 525,000 orders, 20,000 customers, 24 months.
A $9.99 monthly tier buys free delivery and 5% off meals. It collects $377,612 against $1,756,003 of benefit given away, and recovery falls from 0.81 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.3% and the programme still loses $1.19M. Break-even sits at $46.46 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 delivery complaint raises the cancel rate by 16.3 points. A billing question lowers it by 5.8. Treating “contacted support” as one signal averages two opposite effects into noise. 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 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 | 154 | $1,588 | $1,957 | 0.812 |
| two to three / month | 300 | $5,904 | $17,357 | 0.340 |
| three or more / month | 4,455 | $369,890 | $1,736,688 | 0.213 |
Across the tier: $377,612 collected against $1,756,003 given away, a $1.38M net programme cost at 21.5% 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 239.3 days against 206.4, and 52.15% remain active against 41.84%. The lock-in is genuine. It does not cover the margin it costs:
| Tier | Months | Contribution / member-month | Lifetime value |
|---|---|---|---|
| basic | 6.88 | $126.80 | $843 |
| plus | 7.98 | $106.17 | $813 |
Paid members last 16% longer and earn 16% less per member-month, so lifetime value comes out close to a wash. That is the most uncomfortable version of the result. The programme is not destroying value outright, it is spending $1.38M in subsidy to move customers between two buckets worth roughly 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) | $377,612 | 21.5% | −$1,378,391 |
| $12.99 | $491,009 | 28.0% | −$1,264,994 |
| $14.99 | $566,607 | 32.3% | −$1,189,396 |
| $19.99 | $755,602 | 43.0% | −$1,000,401 |
| $24.99 | $944,597 | 53.8% | −$811,406 |
| $29.99 | $1,133,592 | 64.6% | −$622,411 |
| $46.46 (break-even) | $1,756,003 | 100.0% | $0 |
$14.99 leaves $1.19M on the table. Break-even sits at $46.46, 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 | $952,668 | 54.3% | $25.20 |
| Meal discount (5%) | $803,334 | 45.7% | $21.25 |
| Total benefit | $1,756,003 | 100% | $46.46 |
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.76M to $1.34M and lifts recovery from 21.5% to 28.1% at the current price. Holding that cap and moving the fee to $14.99 reaches 42.1%. 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, the difference is large and points in both directions. A customer whose box arrived damaged is leaving. A customer with a billing question is engaged and slightly more likely to stay.
| Contact category | No contact | Contacted | Delta |
|---|---|---|---|
| delivery | 51.52% | 67.82% | +16.30 pts |
| meal quality | 51.60% | 67.03% | +15.43 pts |
| billing | 52.30% | 46.55% | −5.75 pts |
| account | 52.15% | 53.64% | +1.49 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 real effects grew, delivery moving from +12.0 to +16.3 points, and the spurious ones collapsed toward zero, account moving from −1.0 to +1.5. Effects strengthening while noise shrinks 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 | 831 | 92.3 | 79.7 | 71.5 | 57.9 | 50.2 | 43.9 |
| 2024-10 | 893 | 93.7 | 82.3 | 69.2 | 56.7 | 49.6 | 43.1 |
| 2024-11 | 861 | 94.4 | 77.5 | 68.3 | 58.6 | 48.9 | 42.6 |
| 2024-12 | 909 | 90.2 | 79.4 | 72.7 | 62.0 | 52.4 | 46.6 |
| 2025-01 | 916 | 94.8 | 83.6 | 72.8 | 61.2 | 53.0 | 44.1 |
| 2025-02 | 776 | 91.6 | 78.9 | 72.3 | 62.5 | 53.6 | 47.5 |
| 2025-03 | 851 | 92.6 | 80.3 | 72.3 | 60.6 | 54.9 | 45.8 |
| 2026-06 | 868 | 94.8 | 82.4 | n/a | n/a | n/a | n/a |
| 2026-07 | 909 | 93.3 | 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 2026-07 cohort has
no month-3 number because it has not existed for three months. 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.
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, five 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: 525,143 raw orders, minus 120 duplicates removed in
staging, minus 80 orphans dropped by the inner join in
fct_orders.
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. Five 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.