Retention reporting does not fail loudly when you cross a million orders. The cohort table that used to return in two seconds starts timing out or silently truncating, predicted lifetime value stops reflecting orders placed today, and the repurchase curve you compute inside the ESP starts reporting a median that is too short. The fix is not a faster dashboard. It is moving the per-customer sequence math into a warehouse, keeping the ESP for activation only, and accepting a defined staleness budget instead of pretending the numbers are live.
Key takeaways
- Cohort tables break first because they are the only retention report that touches every order row at once. Precompute the customer order sequence instead of deriving it per query.
- Predicted CLV and similar model fields are batch outputs. Using them as same-day flow filters means filtering on yesterday's state.
- An ESP-native repurchase interval is biased short because it can only see customers who already repurchased. Right-censored customers have to be counted, not dropped.
- Extract full history once with a bulk export, then run incrementally on an update timestamp. Polling the standard endpoints for a million orders will exhaust your rate budget before it finishes.
- Push a small number of decision-ready fields back to the ESP nightly, each stamped with the time it was computed.
- Below roughly 250,000 lifetime orders this is premature. The trigger is query behaviour, not ambition.
What actually breaks first
The cohort retention table breaks before anything else, because it is the only common retention report that has to look at every order row in a single pass. A flow report reads one flow. A campaign report reads one send. A cohort table joins orders to orders, groups by acquisition month and by months-since-first-order, and does that across your entire history. At 200,000 orders that is cheap. At 1.5 million orders spread across 40 monthly cohorts, the same query is doing a self-join whose cost grows with orders per customer, not with orders.
The second thing to break is the definition layer. Once reporting gets slow, someone caches a result, someone else builds a segment off a stale property, and two versions of "repeat rate" start circulating. By the time an operator notices, the disagreement is three months old. If you already have numbers that will not line up between systems, fix reconciliation before you scale anything, because scale multiplies the disagreement (see reconciling Klaviyo revenue with Shopify numbers).
Why the cohort query times out now
It times out because the query is recomputing each customer's order sequence on every run, and that work is quadratic in orders per customer. Run EXPLAIN ANALYZE on the cohort query and look for two things: a nested loop join where the planner expected a few hundred rows and got a few hundred thousand, and a sort or hash spilling to disk. Both are the signature of "this ran fine at a tenth of the volume".
The fix is to compute the sequence once and store it:
-- one row per order, with sequence and gap, computed once
select
customer_id,
order_id,
processed_at,
row_number() over (partition by customer_id order by processed_at) as order_seq,
processed_at - lag(processed_at) over (partition by customer_id order by processed_at) as gap_from_prev
from fct_orders
where financial_status in ('paid', 'partially_refunded')
Materialise that output rather than wrapping it in a view. A materialized view stores the result and is refreshed on a schedule you control, which is the correct shape here: the answer for orders placed in 2023 will not change tonight. On BigQuery or Snowflake, partition the underlying order table by order date so the incremental refresh only scans recent partitions (BigQuery partitioned tables). Every cohort report then reads a table that already knows each order's position in its customer's history, and the self-join disappears.
Why LTV lags a day behind
Predicted lifetime value and similar model-derived fields lag because they are batch outputs, not event-driven ones. The ESP recomputes them on its own schedule after enough order history exists, so a profile that placed its third order this morning still carries the value computed from two orders last night. Nothing is broken. The mistake is operational: using a batch field as a filter on a flow that fires within minutes of an event.
The rule I apply is simple. Batch-computed fields are allowed in campaign segmentation and in flow filters that sit behind a delay of at least 24 hours. They are not allowed as the deciding condition on a same-day trigger. If a welcome or post-purchase flow branches on predicted value, that branch is reading a stale field for exactly the customers whose behaviour just changed, which is the population you cared about. The same caution applies to the model's category assumptions (when predicted CLV is wrong about your catalogue).
Write your own computed fields the same way, and always ship a companion timestamp property such as metrics_computed_at. When a report and a segment disagree, that timestamp answers the question in ten seconds instead of a day.
Why the repurchase curve has to leave the ESP
Because the ESP can only measure customers who already repurchased, and that produces a median interval that is systematically too short. Segment builders express conditions like "placed order at least twice" and "placed order in the last 90 days". They cannot express "the distribution of time between order one and order two, including customers who have not placed order two yet".
Those customers are right-censored. You know they have gone 140 days without reordering; you do not know whether they will reorder on day 160 or never. Drop them and your average interval collapses toward the fast repurchasers. On a store where the honest median is around 74 days, the ESP-native version routinely reports something in the 40s, and every replenishment flow built on it fires while the customer still has product on the shelf.
If your repurchase interval got shorter every quarter while nothing about the catalogue changed, you are almost certainly measuring survivors rather than customers.
The warehouse version counts each first-time buyer as an observation from their first order date, marks whether a second order occurred, and computes the share still unconverted at each day offset. That curve drives replenishment timing (timing from your own repurchase curve) and it drives engagement window sizing (sizing an engagement window by repurchase). Both decisions get worse in the same direction when the input is biased short.
The extraction layer that will not melt
Pull full history once with a bulk export, then go incremental. Paginating the standard order endpoints for 1.5 million orders will burn your entire API budget and still take days. Shopify's bulk operations run a GraphQL query asynchronously and hand back a JSONL file, which is the correct tool for backfill. After that, query incrementally on updated_at with a small overlap window, and respect the documented API rate limits, which are calculated-cost based on GraphQL rather than a flat request count.
On the ESP side, event history comes out through cursor-paginated endpoints governed by burst and steady rate buckets that vary per endpoint, described in the Klaviyo API overview. Two practical notes. First, back off on 429 using the retry header rather than a fixed sleep, or a long backfill will stall behind its own retries. Second, if one integration serves several brands, understand which quota it shares before you schedule all six backfills for 2am.
One modelling trap: updated_at moves when a refund or a tag changes, while processed_at is when the order happened. Use updated_at to decide what to re-fetch and processed_at to place the order in a cohort. Mixing them makes old cohorts quietly shift between refreshes, which is one of the harder bugs to see. Missing events are a related failure worth ruling out first (Shopify webhook and integration blind spots).
The three tables you actually need
Three tables cover almost every retention question an operator asks, and building more before these are trusted is wasted effort.
| Table | Grain | What it answers |
|---|---|---|
fct_orders | One row per order | Revenue, discounts, refunds, channel. Partitioned by order date. |
fct_customer_orders | One row per order, with sequence | Order number, gap from previous order, first order date. Feeds every cohort table. |
fct_repurchase_windows | One row per customer per transition | Days from order N to order N+1, censoring flag, cohort month. Feeds curves and window sizing. |
Build them as incremental models so each run processes only new and changed rows rather than rebuilding history (dbt incremental models). Give fct_customer_orders a full refresh on a monthly schedule, because refunds and merged customer records rewrite the past often enough to matter. Merging duplicates is worth doing before the first backfill, since a split customer looks like two one-time buyers and drags your repeat rate down.
Pushing numbers back without breaking sends
Send back the smallest set of fields a flow or segment can act on, nightly, in batches. The temptation after building a warehouse is to sync forty columns onto every profile. Resist it. Profile properties are activation inputs, not storage. Three or four fields cover most of it: expected days to next order, days since last order, order count band, and the computed-at timestamp.
Two operational rules that have saved me repeatedly. Write updates outside your main send window, because a mass property update can re-evaluate segment membership across your whole list and trigger flows you did not intend to trigger. And never let a nightly sync be the only writer of a field a flow depends on, since a failed job then leaves the flow reading a value that ages silently. If you are deciding where a given attribute belongs in the first place, that trade-off is its own decision (tags, metafields or a warehouse).
How to prove the new numbers agree
Run three reconciliation checks before anyone presents a warehouse number in a meeting. First, total orders and gross revenue by month, warehouse against the store admin, for the last 24 months. Anything over a 0.5 percent gap is a definition problem, usually test orders, cancelled orders or currency conversion. Second, distinct customer counts for a handful of ESP segments against the equivalent warehouse query on the same evaluation date. Third, repeat rate for one closed cohort, computed both ways, with the censoring rule written down next to the result.
Keep those checks as scheduled queries that fail loudly, not as a one-time exercise. At this volume the reports do not break with an error message. They drift. If you run several brands, expect the rollup to disagree with the sum of its parts for reasons that are about definitions rather than data (why rollup reports never line up).
Trade-offs and what I would do
The honest cost of moving this math out of the ESP is a pipeline someone has to own. You gain correct censoring, fast cohort tables and one definition of repeat rate. You take on an extraction job that breaks when an API version changes, warehouse spend, and a lag between an order happening and the number reflecting it.
My recommendation by stage. Under roughly 250,000 lifetime orders, do not build this. ESP-native reporting is close enough and the analyst time is better spent on flow logic. Between 250,000 and a million, build only fct_orders and fct_customer_orders, and compute the repurchase curve as a scheduled query rather than a modelled table. Past a million, build all three and treat the nightly sync back to the ESP as production infrastructure with alerting.
On staleness, pick a budget and publish it. Twenty-four hours is fine for cohort tables, repurchase curves and value bands, because none of those decisions change hour to hour. Anything that needs to be current inside an hour, such as recent purchase suppression or back-in-stock eligibility, should stay event-driven in the ESP and never depend on the warehouse. The failure I see most often is a team that builds a good warehouse and then routes a time-sensitive suppression through it, turning a reporting improvement into a sending incident.