How to find customers who have stopped ordering, in Xero

You can do this by hand, and it is worth doing at least once. Here is the method, why it takes longer than it should, and what to do about the answer.

Why you can't just look at your sales figures

A customer leaving does not look like anything. Revenue is made up of hundreds of invoices, and losing one regular buyer is a rounding error this month and a serious problem by the end of the year. Nobody calls to say they have stopped buying — they simply stop, usually because a competitor turned up at the right moment or because something went wrong that you never heard about.

The information is all in Xero. The difficulty is that invoices are organised by date and by document, not by the thing you actually want to know: which customers have a pattern, and which of them have broken it.

The method

1. Export your sales invoices

Export at least two years of sales invoices — ideally to CSV so you can work in a spreadsheet. Two years matters: one year cannot tell you whether a quiet December is normal for a customer or the beginning of the end.

Keep the customer name, the invoice date and the amount. Include only approved and paid invoices; drafts and voided ones are not orders.

2. Build one row per customer

With a pivot table, get for each customer: the date of their last invoice, their total number of invoices, and their total value over the last twelve months.

That alone is illuminating. Most businesses find that a third of the names in their contact list have not bought anything for a year.

3. Work out each customer's normal gap

This is the step that matters, and the one a report cannot do for you. For each customer, calculate the typical number of days between their orders — the median gap, not the average, because a single long absence will drag an average around.

A customer who orders every fortnight and a customer who orders every quarter are both perfectly healthy. What tells you something is a customer who is well past their own normal gap.

4. Compare the current gap to the normal one

For each customer, calculate the days since their last order, then divide by their typical gap. Anything above roughly two is worth a look. A fortnightly buyer who has been silent for six weeks is three times their normal gap, and that is a phone call. A quarterly buyer at six weeks is not.

5. Rank by what they are worth, not by how late they are

Sort by annual value, not by days silent. The most overdue customer is usually a small one. You have time to ring three people this week, and they should be the three whose absence costs the most.

6. Check the ones who are still buying, but less

Separately, compare each customer's spend over the last ninety days with the same period a year earlier. A customer ordering just as often but spending forty per cent less is leaving slowly, and will not appear in any "last order date" report.

What to do with the list

Ring them. Not an email — a phone call, from someone they recognise. Do not open with a sales pitch; ask whether everything has been all right. A surprising number of lapsed customers have a specific, fixable reason: a late delivery, a price rise nobody explained, a contact who left, an invoice dispute that hardened into a habit.

Some will have gone for good. Some were never regular customers to begin with. The ones worth finding are the customers who did not decide to leave and simply drifted.

Why this is harder than it should be

The method above works, and takes most people a solid afternoon. The problem is not the first time — it is that the answer goes stale in a fortnight. By the time you have finished the list, a different customer has gone quiet.

Doing it once tells you what you have lost. Doing it every week is what stops you losing more, and almost nobody sustains that by hand.

Next: what Xero's reports will and won't tell you about your customers