Level 5 · Picking a churn window

The gap between orders

To decide when silence means “gone”, look at how long customers are normally silent. For every customer, sort their booked orders by date and measure the days from each one to the next:

date_diff('day',
  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date),
  order_date) AS gap

Harbor & Pine has 9,413 such gaps. Their median is 23 days, the 75th percentile 76 days, the 90th 182 days and the 95th 277 days. Most gaps are short, and a long thin tail stretches out to many months.

The window is a trade-off

Every window makes two kinds of mistake. Too short, and it flags customers who were never leaving: they were just in one of their longer gaps. Too long, and it flags lost customers so late that a win-back email arrives when they have already moved on. The 90-day window flags 58.1% of eligible customers; 180 days, 43.9%; 365 days, 24.9%. Same customers, same orders, very different rates.

A common rule of thumb is to put the window near the 90th percentile of normal gaps. Silence longer than nine in ten normal gaps is unusual enough to mean something, and you still react within the same season. Here that is 182 days, so 180 days.

Whose gaps?

Gaps are not spread evenly across customers. A business account with 100 orders contributes 99 gaps; a consumer with two orders contributes one. The 23-day median is mostly the rhythm of heavy buyers. Take one median gap per customer instead and the middle customer’s typical gap is 55 days, with a 90th percentile of 234 days. That is a good reason to set different windows per segment, or at least to check the window against the segment you will email.

Keep it fixed

Whatever window you pick, write it down and keep it. If churn is 44% this quarter with 180 days and 25% next quarter with 365 days, the business will celebrate an improvement that is only a change of definition.

Interview note

“How would you define churn for an e-commerce business?” is a common case question. A strong answer: there is no cancellation event, so define churn as no purchase in N days, choose N from the distribution of gaps between purchases, and only count customers who have been around for N days.