Level 4 · Churn without subscriptions

Nobody cancels a sofa

A subscription business knows exactly when a customer leaves: they cancel. A store does not. Its customers simply stop ordering, and the only evidence is silence. So churn has to be defined: a customer has churned if they have not placed a booked order for some number of days.

Here that window is 180 days, measured back from the as-of date, 2025-12-31. Subtract 180 days and you get 2025-07-04. A customer whose last booked order is before that date has churned:

COUNT(*) FILTER (WHERE last_at < DATE '2025-07-04') AS churned

Level 5 looks at how to choose the window. For now, take 180 days as given.

Who can churn at all

The denominator is the part people get wrong. A customer who first bought on 2025-11-20 has not had 180 days to go quiet. Counting them as “not churned” is like counting a newborn as “has never been to hospital”: true, but meaningless. Only customers whose first booked order is before the cut-off date are eligible:

WHERE p.first_at < DATE '2025-07-04'

Leave that filter out and the rate drops by about ten points, simply because many new customers arrived in the second half of 2025. A strong holiday season would then “reduce churn” on the dashboard without a single old customer coming back.

Only paid orders count as activity

Recency uses booked orders. A customer whose only recent order was cancelled, or is still pending, has not bought anything in the window. If you count those orders, a failed checkout keeps someone off the win-back list.

Splitting by segment

Business accounts at Harbor & Pine order often and in volume, so long silences are rarer for them. Consumers buy a sofa, then maybe a lamp, and then often nothing for a long time. A single churn rate blends both, and because consumers are most of the base, it mostly reports consumer behaviour. Split by the segment the action will target: the win-back email goes to people, not to an average.

Common mistakes

• Every customer in the denominator. New customers cannot have churned yet.
• Counting cancelled or pending orders as activity.
• Calling churned customers “lost”. Some come back after a long gap. Level 6 counts them.