Retention

The subscription sections run on analytics.loop_subscriber_performance, rolled to calendar months — 3 of them, covering 16 Jun 2026 → 5 Aug 2026 , of which 2 are complete calendar months, reconciled against analytics.loop_cancellations. The three cohort sections below run on analytics.ltv_retention and analytics.ltv_retention_by_order_index instead, at cohort-month grain, and those two marts disagree about what a missing figure means — each section says which rule it is under. The shortfall bridge at the foot of the page runs on analytics.loop_expected_orders, at billing-month grain, where nothing is missing at all and the whole question is what the gap between two known figures means. There is no date control above this line and there is not meant to be one: subscriber churn is a calendar-month rate, a cohort is a fixed group of customers followed forward, and every section states its own basis rather than having one chosen for it.

These are the retention cards from /company, and they are the same cards there. Every other flag on that page recomputes when its Window control moves; these four do not, because this page has no Window and the measures they rest on are not windowed quantities. Subscriber churn is a calendar-month rate, the shortfall bridge is a billing-month one, and the cancellation reconciliation is a statement about two marts over the whole of their history. Cutting any of them to an arbitrary thirty days would answer a question none of them asks.

Each card names the counts its rule divided and the bar it crossed, and none of them says what to do about it — that is the line ADR 0007 draws between a computed flag and generated prose. A month whose churn is not computable trips the churn rule in neither direction: June 2026 carries 4 cancellations against an opening base of 0, because trading began mid-month, so its rate is NULL rather than 0% or 400% — and a NULL is neither above the bar nor below it. The rule reads the latest complete month, and it would stay silent even if that month were June.

Where the tile carries no comparison, the card names the reason rather than hiding it, and the reason is read off the month before rather than written here. The card always reaches for the month immediately preceding the one it shows, never for the most recent month that happens to carry a number: comparing a month against something that is not the month before it, and saying nothing about having done so, is the failure mode the whole comparison discipline on /company exists to avoid.

The Identity column is the check, not a decoration. opening + new + reactivated + resumed − paused − cancelled = closing is stated to hold on every row of the mart, and this page tests it rather than trusting it: the fence recomputes the closing stock from all six terms and compares it against the mart's own, and the column writes the sum out so a reader can do it themselves. A row that failed would carry a cross, and its arithmetic would visibly not land on the mart's closing figure printed beside it.

The check holds on 3 of 3 rows of the table above.

A same-day create-then-cancel counts as both new and cancelled, and the two offset inside the identity. That is why new counts every creation rather than only the creations that end the day still active, and it is not something to correct for — correcting it would break the identity above and understate the number of subscriptions the business actually started.

Opening and closing are never summed. They are stocks: a count of subscriptions standing at an instant, restated on every date the mart carries. Summed across a month they count the same subscription once per day it survived, which is why each month takes its opening figure at its first date and its closing figure at its last. The movements are event counts and are summed, which is what makes the identity above an arithmetic claim rather than a coincidence.

Reactivated, Resumed and Paused are flat at zero across every month on this chart, so the base has moved 2 of the 5 ways the mart carries. They are kept on the chart and in the table because a movement that has not happened yet is a different thing from a movement that cannot.

August 2026 is a partial bar — the month has not closed, and the Basis column on the table above says which days it covers.

loop_cancellations is deliberately raw and ungrouped. Test and internal cancellations are included in it, and excluding them is the job of whoever reports on cancellation reasons — not of this reconciliation, which has to compare like with like and so filters nothing on either side. That is also why no cancellation_source split appears on this page: the involuntary versus voluntary split ('System' against 'Customer Portal') is a real question and this mart is where it is answered, but a split computed on a rowset that still carries test cancellations would put a misattributed percentage on screen. The reconciliation needs a count, and a count is safe against that.

That blended figure is the one this page refuses to print. 18 of 72 customers is arithmetically correct and describes neither cohort: the One-off half is 0 of 37 and the Subscription half is 18 of 35 — two different behaviours measured on one axis. A subscription renews on a schedule and a one-off purchase does not, so the average of the two is a number that is nobody's experience.

12 of the 18 cells above read "too early", which is what a 49-day-old business's cohort table is supposed to look like. The mart NULLs every behavioural measure on a period whose calendar month has not ended, so those cells are the mart declining to answer rather than answering zero — and the chart above breaks its lines in the same places instead of dropping them to the axis.

The period axis is derived, not chosen. It runs to the number of calendar months between the first and last cohort month — how long the oldest cohort has had to behave — so it widens on its own as trading history accumulates rather than being a figure typed into a source that a rebuild cannot move.

repeat_purchase_rate is one of three registry "repeat" metrics and is not a synonym for either other. repeat_order_share, the headline tile on /company, is repeat orders ÷ orders in a window — an order-mix measure that moves with acquisition volume as much as with loyalty, and a different thing entirely. The third name, on analytics.customers, is a registry name already taken by a definition this dashboard does not use and must not redefine, which is why it appears nowhere on this page or in either source file.

Nothing here is averaged across cohorts. repeat_purchase_rate is additive: false: the rows above are not summed, not averaged, and carry no total row. A cohort curve is a set of separate cohorts' experiences, and collapsing them to one number would reintroduce the blend the split exists to prevent.

A missing order_index row reads 0, and 3 of the 9 cells above are exactly that. The Basis column names every cell the mart never emitted, and each of them prints 0 rather than "too early" — nobody in that cohort has placed that order, which is a fact and not a gap in measurement. The section above, on analytics.ltv_retention, means the opposite by a missing figure and prints the words instead. Both marts are on this page on purpose: they are the two halves of one discipline, and reading either by the other's rule would put a false claim on screen.

period_complete on this mart hides nothing. It is descriptive — it flags a period still in flight and NULLs no measure — so a partial period's real customers are counted and shown. 3 of the 9 cells above rest on at least one in-flight period, carrying 37 customer-appearances between them, and every one of them is in the figures on this page. Appearances rather than customers, for the same reason as the paragraph below: that figure is customers summed across order_index, and a cohort with an in-flight cell at two order indices would count a customer who reached both of them twice. On ltv_retention the same customers' cohort periods are NULLed out entirely, which is the difference between a mart that flags incompleteness and one that withholds on it.

"Share of order 1" is arithmetic on this table, not a registry metric. It is this cohort's customers at an order index ÷ its customers at order 1, computed in the page fence so the reader can see the division. It is deliberately not called a repeat rate: repeat_purchase_rate is the cohort-period measure on the other mart above, repeat_order_share is the order-mix measure on the headline, and the third registry name is taken by a definition on analytics.customers that this dashboard does not use. Order 1 is the cohort's distinct headcount, because every customer has a first order and no customer has two.

customers is not additive across order_index, and here is the arithmetic that says so. Summed down the column these cells total 226 customer-appearances, against 189 customers who placed a first order — an excess of 37, which is the same people counted again at their second and third orders. That is why this table carries no total row and the chart above is grouped rather than stacked. The figure is additive across months_since and across the cohort cuts the source rolls up, which is what makes each individual cell sound.

It will not reconcile to ltv_retention.active_customers, and it should not be made to. The two marts count different things: active_customers is customers active in a period, and this is customers who reached an order index. Forcing agreement between them would mean redefining one of the two, which is the dbt project's decision and not this page's.

The chart carries the 3 of 16 cohort-channels that have both a spend to recover and a closed month 0 to divide it by, because those are the only ones with a threshold to cross. Of the rest, 8 were acquired with no spend at all — their CAC is a real £0.00 and its reference line is the axis itself — and 5 sit on a cohort month that has not closed, carrying £1,489.06 of spend with no acquired headcount to divide it by yet. Every one of them is on the table below, in full; none is dropped, and 0 of the 3 cohort-channels with a spend to recover have reached payback. The 8 zero-spend ones are deliberately not counted into that: their CAC is £0.00, so ltv >= cac holds at their first measured period and the table reads Month 0 for every one of them — arithmetic that is true and says nothing about paying an acquisition cost back.

† the cohort was acquired on fewer than 5 customers, so its CAC and LTV rest on a denominator too small to act on. The row is still shown in full — a thin figure is marked, never withheld. 3 of 16 rows on this table carry it.

This panel is cut by channel and by nothing else, and that is a correctness constraint rather than a layout one. marketing_cost is joined into analytics.ltv_retention from channel_reporting and attached to exactly one row per cohort month and channel, at month 0, NULL everywhere else. Cutting it by a further cohort dimension — product category, order type, order category, discount code — can drop that single row while leaving the cohort's customers in the group, and the sum then returns NULL: CAC and ROAS would read as a gap where the spend is still there and the filter threw it away. The repeat purchase rate section above is split by cohort_order_type, deliberately, because it divides two customer counts and neither is deduped onto one row. Same mart, same page, two different things it is safe to do to it.

The spend here is marketing_cost, never total_spend. The mart carries both, and they are not the same column: marketing_cost is the cohort's acquisition spend attached once, while total_spend is a monthly figure repeated on every one of the mart's periods and declared agg: max precisely because summing it across them multiplies it. On a 36-period mart that is a factor of 37, and the result would look like a plausible CAC rather than an obviously broken one.

"CAC" here divides by acquired CUSTOMERS, and the CAC on /company/acquisition divides by new ORDERS. Both are the registry's cac; the denominator differs because the grain does. channel_reporting knows orders in a window and does not know which cohort they belong to, so its figure is the windowed one and is the right one for the acquisition table. ltv_retention is the only mart that attaches a channel's spend to the cohort it bought, which is what makes a true cost per acquired customer expressible at all — and it is the denominator the payback question needs, because it is customers who go on to buy again. The two figures will not match, they are not meant to, and neither is renamed to look like the other.

ltv and roas here divide cumulative_shopify_total_sales; the shipped ltv_retention metric block divides cumulative_net_revenue. The divergence is deliberate and recorded rather than reconciled: Sales is the house numerator for every spend-efficiency ratio across the warehouse (ADR 0008, dbt ADR-0025), and this dashboard is a consumer of that registry, not an exception to it — a page dividing by a different basis would put a figure on screen that the AI Brain contradicts when asked the same question in Slack. PRD044 §7 bars any dbt change, so the metric block is raised separately against the dbt repo; the column already exists on the mart, so nothing in dbt has to move for this page to read it. The size of the choice, over the cohort-channels measurable today: £2,439.13 of Sales against £1,970.64 of net sales, a difference of £468.49, which is net shipping and nothing else. Discounts and returns are already out of both bases — net_revenue is gross less discounts less returns, and Sales is that plus net shipping plus tax (CONTEXT.md, Sales) — and the tax line reads £0.00 while the dbt VAT gate is shut (ADR 0008, dbt ADR-0009). So the gap is the shipping S3 counts as sales, and it is the figure that grows the day that gate opens. sources/s3/company_cohort_ltv.sql records the same divergence in its header.

payback_period is the first period where LTV reaches CAC, equivalently where ROAS reaches 1×, and it cannot be written as a single expression: collapsing it puts an analytic function inside min(…), which BigQuery rejects outright. The fence therefore computes LTV and CAC per cohort, channel and period first, and takes the min(months_since) satisfying them in an outer step. A cohort where no period does reads "not yet reached", which is a measurement — it has not paid back yet — and is a different statement from "too early", which is the mart declining to answer at all. Both of those are a NULL payback_period, and so is a third case that is neither: a cohort the mart carries no marketing_cost row for has no CAC, so ltv >= cac is never satisfied and no period is returned. That one prints an em rule, the same as its CAC and ROAS cells, because there was no threshold to reach rather than a threshold that went unmet — the cell is resolved from the CAC's own state, so it cannot claim a payback verdict the row beside it says is uncomputable.

Three zeros on this table mean three different things, and none of them is missing data. A CAC of £0.00 is a channel nothing was spent on: a real zero with a real denominator behind it, and it must not be dressed up as too early. A ROAS of "no spend" is that same channel's return, which is undefined rather than infinite — there is no spend for the Sales to be a multiple of. And "too early" is a cohort whose month 0 has not closed, where ltv_retention has NULLed the size: the spend is known, the customers it bought are not counted yet, so the division has no denominator rather than a zero one. Which state a row is in is decided from the denominator, never from the spend, which is what keeps a £0 spend and an unripe cohort on opposite sides of the line.

The shortfall and the realised loss are two different figures and the page prints both. The shortfall is what was due to bill less what billed, and the mart says plainly that it is not a loss: deferred_out left this month for a later one and bills there, and pending has not yet had its chance to bill at all. Neither is money the business did not get, so neither belongs in a loss. Subtract both and what remains — bills that were skipped, cancelled, paused, failed in dunning, or that the mart cannot account for — is the month's realised loss. Anyone reading the shortfall as the loss overstates it by exactly the two buckets named in the strip above.

August 2026 is a month in flight, and its bar is labelled as one. A live month is not "every bill due this month": it is every bill due up to today, plus every later bill in the month that Loop has already told us moved away. Its bar therefore grows through the month, and it is not comparable like-for-like against a closed one — which is why no figure in this section is differenced against another month, and why the marker sits in the axis category rather than in a footnote.

Skipped, Paused and Pending — not yet due have never carried a bill in any month on these charts, so their bands are absent from every bar. They are kept in the legend and in the table for the same reason the movements chart keeps its empty series: a bucket nothing has landed in yet is a different thing from one nothing can land in.

Unexplained carries money on the value chart and no bill on the count chart, and that is not a contradiction. The two residuals measure different things. Every expected bill landed in a named bucket, so nothing is left over to count — but a bill that matched can still settle for a different amount than it was expected to, and that difference has nowhere else to go. It is the value side saying a bill billed for less than it was due, not a bill nobody can account for.

The Identity column is the check, not a decoration. expected = matched + skipped + cancelled + paused + deferred out + payment failed + pending + unexplained holds on every row of the mart, and this page tests it rather than trusting it: the fence recomputes the expected count from all eight terms and compares it against the mart's own, and the column writes the sum out so a reader can do it themselves. It is an identity rather than an approximation because the mart's ladder precedence — skipped, then cancelled, then paused, then deferred out, then payment failed, then pending, then unexplained — puts every bill in exactly one bucket, so the buckets never overlap and never leave a bill uncounted.

Matched and Actual are two different columns and the table prints both. matched is the identity term: expected bills that were matched to a real order. actual is what billed in the month, and it is not in the identity — an order Loop never expected would raise it without raising expected. They land on the same figure in every month above, which is exactly why both are shown: a reader can see the two names agreeing rather than take one standing in for the other on trust.

The Shortfall checks column is the value side's version of the Identity column above. The shortfall has two definitions on this mart — what was due to bill less what billed, and the seven buckets added together — and they are the same figure only if the decomposition is complete. Both are recomputed in the fence and compared against the mart's own shortfall_value_gbp, to the penny rather than to the bit: these are floats, and July's seven buckets sum to £427.94 plus a residue in the sixteenth decimal place, which is exact to every digit anyone can spend. The count identity beside it is over integers and is tested for equality outright.

Neither table carries a total row, and this panel is never shown as an all-time figure. expected_value is not summable across months: a bill that moves out of one month is expected in the month it left and in the month it lands in, so adding the months together counts it twice — and so does any month total built from it, the shortfall and the realised loss included. The last column is the exception the mart names. Due to bill, less deferred out removes exactly the bills that will be expected again somewhere else, and it is the one figure here that survives being added up.

deferred_out is revenue that moved, and it is not called anything else on this page. It is a bill that left this month for a later one and bills there. It sits inside the shortfall because the shortfall is a gap between two months' arithmetic, and it sits outside the realised loss because nothing was lost. pending is the second of the two: a bill that has not yet had its chance to bill at all, which is a statement about the calendar and not about a customer.

deferred_in is on neither chart, and that is deliberate. It is bills that arrived from an earlier month, so it is outside the count identity, outside the shortfall decomposition and outside the residual — the mart's decomposition answers "what happened to the bills due this month", and an arrival from elsewhere is not one of them. Adding it to a bar would break the identity above in the one direction a reader could not see, so it is printed here instead, at its real size.

Nothing on this page is suppressed. Every figure either prints or names the reason it cannot: a month whose opening base is zero reads "not computable" in the Churn column and carries the reason in Basis, a month still in flight says which days it covers, and a cohort period the mart has NULLed reads "too early" with the reason beside it. Suppression is reserved for figures that would be actively misleading (CONTEXT.md, Small-n marking), and a rate with a zero denominator is not misleading once it is labelled — printing 0% or 100% in its place would be. The zeros in the order-index table are the one place a 0 is printed for a figure the mart did not emit, and they are labelled too, because there a 0 is the measurement. The £0.00 CACs in the cohort LTV table are a third case again: a figure the mart did emit, which is zero because nothing was spent, and which would be a lie either as "too early" or as a blank. The shortfall bridge is the one section with no such state at all — nothing on loop_expected_orders is NULL and no bill is missing from it — so its whole discipline is naming rather than labelling: the shortfall and the realised loss are both fully known, and the only way to be wrong there is to call one of them by the other's name.

The deployed site is static with no runtime connection to BigQuery, so this rebuild is the data refresh, and the stamp above is the only on-page evidence that the numbers moved.