How to spot a café wholesale account going quiet, before it is gone
A café almost never tells its roaster it is leaving. It orders a few days later than usual, then a little less, then not at all, and the roastery notices at the end of the quarter. Here is how to see it in the first week instead, with a spreadsheet you can build this afternoon.
Published 15 September 2026
Short answer. Measure each account against itself. Take the median number of days between its orders, divide the days since its last order by that, and call the café when the answer passes 1.5. Past 2.5 the account is drifting. Watch basket size too: a café that still orders on time but has cut its order to under 70% of its usual size has usually given part of it to someone else.
Why accounts go quiet
The reasons are ordinary, and most of them can be fixed if you hear about them in time:
- A new person behind the bar, or a new owner, who has a roaster they already like.
- Another roaster's samples. A guest coffee becomes a second hopper, and your share of the order shrinks.
- A price change the café found out about from the invoice. Write notice periods into your price list.
- A quality complaint nobody made. One inconsistent batch, a grinder that needed dialling in, and nobody called.
- Cash flow. The café is ordering less because it is selling less, or paying later.
- A closure. August, renovations, Christmas. These look exactly like churn in the numbers and are not.
Why "days since last order" is not enough
Most roasteries that track this at all sort their customers by the date of their last order. That list treats every account the same, and wholesale accounts are not the same:
| Account | Usually orders | Last order | Gap ÷ usual | Reading |
|---|---|---|---|---|
| Café Aurora | every 7 days | 19 days ago | 2.7 | Drifting. Call today. |
| Sol Bakery | every 14 days | 26 days ago | 1.9 | Slowing. Call this week. |
| Northside office | every 28 days | 19 days ago | 0.7 | Steady. Nothing to do. |
| Harbour Hotel | every 7 days | 4 days ago | 0.6 | On time, but its last three orders are half its usual size. Drifting. |
Sorted by last order, Café Aurora and the office sit next to each other at 19 days, and Harbour Hotel looks like your best account. Measured against their own rhythm, two of the four need a call and one of them is the account that looked healthiest.
The method in a spreadsheet
You need one row per order with the client, the date and the order total. An export from Shopify or your invoicing software will do, or a week of typing up the notebook. These formulas work in Google Sheets and in Excel 365.
1. The orders sheet
Name the sheet Orders. Column A is the client, B the order date, C the order total. Sort by client, then by date, oldest first. In D2, the gap since that client's previous order, then fill down:
=IF(A2=A1, B2-B1, "")2. The accounts sheet
On a second sheet, one row per client. Put the headings in row 1, the client names from A2 down, and fill each formula from B to H down its column.
- A · Client
Each client once. Get the list with this formula in A2, then paste it back as values so the sheet can be sorted:
=UNIQUE(FILTER(Orders!A2:A10000, Orders!A2:A10000<>""))- B · Orders
=COUNTIF(Orders!A:A, A2)- C · Last order
=MAXIFS(Orders!B:B, Orders!A:A, A2)- D · Usual gap
=IFERROR(MEDIAN(FILTER(Orders!D:D, Orders!A:A=A2, Orders!D:D<>"")), "")- E · Days since
=TODAY()-C2- F · Gap ÷ usual
=IFERROR(E2/D2, "")- G · Recent basket ÷ usual
=IF(B2<6, "", AVERAGE(CHOOSEROWS(FILTER(Orders!C:C, Orders!A:A=A2), -1, -2, -3)) / AVERAGE(FILTER(Orders!C:C, Orders!A:A=A2)))- H · Status
=IF(B2<3, "too new", IF(OR(N(F2)>=2.5, AND(G2<>"", G2<0.5)), "drifting", IF(OR(N(F2)>=1.5, AND(G2<>"", G2<0.7)), "slowing", "steady")))
Format E as a number and F and G to one decimal, then sort by column F, largest first. The top of that list is who to call.
3. Why these numbers
- The median, not the average. One three-week gap in a year of weekly orders drags an average up and hides the next real gap. The median ignores it.
- 1.5 and 2.5. Every account wobbles. A weekly café ordering on day 9 is not news; on day 11 it is worth a message; on day 18 it is worth a call.
- Three orders minimum. Two orders make one gap, which is not a rhythm. Leave new accounts alone until they have one.
- The last three baskets against all of them. One small order is a slow week. Three in a row is a decision the café made.
Four things the spreadsheet gets wrong
- Holidays. If your cafés close in August, every one of them reads as drifting on 1 September. Leave closure weeks out of the gaps, or ignore the sheet for the fortnight after.
- Weekday-only accounts. A bakery that orders every weekday has a usual gap of one day, so every Monday it looks three times late. Count only working days for accounts that never order at a weekend.
- One product quietly dropped. A café that stops buying your decaf but keeps the espresso does not show up in either number. For your largest accounts, check the product lines by eye once a month.
- It is only as fresh as the export. A sheet rebuilt at the end of the month tells you who drifted three weeks ago.
Signals that arrive before the gap does
Some accounts warn you in words rather than in numbers, weeks before an order is late. Treat each of these as a flag in its own right, and write it against the account the same way you would a missed order.
- A sudden interest in price. A café that has never mentioned price asking what you could do on the house espresso is usually a café that has been quoted by someone else. Answer within a day, in person if you can. Whether or not you move the price, you want to know who else is in the room. If green prices have fallen and the café has read about it, that conversation has its own answer.
- A guest slot that becomes a second house coffee. One rotating guest from another roastery is normal and healthy. A guest that is on the bar every week is a trial in progress.
- The order that shrinks but stays on time. Same Tuesday, 12 kg down to 8 kg, no explanation. The rhythm looks perfect and the basket is telling you something.
- A new owner, a new manager or a new head barista. The relationship was with a person, and that person has gone. Ask for an introduction before the new one goes looking.
- Slow payment from an account that always paid on time. Usually money, sometimes a dispute nobody told you about. Either way it is a conversation, not a reminder letter.
What to do when an account is flagged
Call. An email about an order that did not happen reads as a complaint; a phone call reads as a supplier who pays attention. Keep it short and ask rather than sell:
- "We noticed we haven't sent you coffee in a couple of weeks. Is everything alright at the café?"
- "How did the last bag of the house espresso go? Any trouble dialling it in?"
- "Is there anything we could be doing better, on the coffee or on delivery?"
Then write down what they said against the account. A closure, a new barista or a quality problem each needs a different answer, and the next person to call should know which it was.
Make it a routine rather than a rescue: fifteen minutes every Monday with the sorted list, calling from the top until the numbers drop below 1.5.
Questions roasters ask
- How do I know a café wholesale account is about to leave?
- Compare the days since its last order with the gap it usually leaves between orders. When the current gap is one and a half times its usual one, the account is slowing; at two and a half times, it is drifting. A basket that shrinks to under 70% of its usual size while the rhythm holds is the same warning.
- Why not just flag every café that has not ordered in two weeks?
- Because a fixed number of days is wrong for almost every account. Two weeks is alarming for a café that orders weekly and meaningless for one that orders monthly. Each account has to be measured against its own rhythm.
- How many orders does an account need before this works?
- Three at least, which gives two gaps to take a median from. Below that there is no rhythm yet, and flagging a new account is a false alarm. For basket size, wait for six orders.
- What should I do when an account starts drifting?
- Call within the week, and ask rather than sell: whether the last coffee was right, whether anything changed at the café. Most accounts that go quiet have a reason that can be fixed if it is heard early.
Where Order Book fits
Order Book runs this method for you, against every order as it arrives and again every night, so the list is never a month old. Each account is measured against its own median gap, with room for its usual variation: slowing past one and a half times its usual gap, drifting past two and a half, and lost once it is past four times and more than sixty days. Basket size is read the same way once an account has six orders. The closure periods you set for your roastery, and weekends for accounts that never order on one, are left out of the gaps, so August is not a false alarm.
The result is a call list sorted by who needs a call, with one sentence per café, such as "Café Aurora usually orders every 7 days — it's been 19", and the same list by email every Monday. Because the rhythm also says when each café is next due, a café that is due and has not ordered gets one email the evening before your roast-day cutoff.
Upload a CSV of past orders, or connect a Shopify store that already has a custom app, and the first call list is built from your order history. The first 30 days are free.