Most B2B customers don't leave with an announcement. They move one part number to another supplier, then a second, and their orders arrive a little further apart until they stop. By the time anyone notices the revenue is gone, the decision is often a year old.
Each account's own ordering history says when this is happening. A customer that has ordered every five weeks for three years and hasn't ordered in eleven is telling you something, while a customer that orders twice a year is not. The check below compares each account with its own rhythm rather than with an average.
The rhythm of one account
For every account with enough history, take the gaps between consecutive orders and use their median as the expected interval. The median ignores the occasional rush order or long holiday gap better than the mean does.
with orders as (
select master_id, order_date,
order_date - lag(order_date) over (partition by master_id order by order_date) as gap_days
from invoice_headers
where order_date >= current_date - interval '3 years'
),
rhythm as (
select master_id,
count(*) as orders,
percentile_cont(0.5) within group (order by gap_days) as median_gap,
max(order_date) as last_order
from orders
group by master_id
)
select master_id, orders, median_gap,
current_date - last_order as days_since,
round((current_date - last_order) / median_gap, 1) as ratio
from rhythm
where orders >= 5
and current_date - last_order > 2 * median_gap
order by ratio desc;
An account is flagged when the time since its last order is more than twice its usual gap. The factor of two is a starting point. Raise it if the list is too long for the team to call, lower it if accounts are being caught too late.
A second check, on value
Some customers keep ordering on schedule while buying less each time. For those, compare the last 90 days of revenue with the same 90 days a year earlier, and flag accounts that have fallen below about 60%. Comparing with the same period last year removes most seasonal effects.
What to exclude
Project-based customers, which buy in irregular large orders, produce false alarms and are better tracked by the people who manage them. Blanket orders that release on a fixed schedule should be measured on releases rather than purchase orders. Accounts with fewer than five orders don't have a rhythm yet.
Making it useful
The list matters only if someone acts on it. We run the query weekly and send each flagged account, with its rhythm, its last order and its trailing revenue, to the person who owns the relationship. A short call that asks how things are going recovers more of these accounts than any discount.