Coastal Fabrication has been a customer for nine years. They still order every couple of weeks. Your inside rep talks to their buyer, Dave, and the calls are friendly. There has been no complaint, no pricing fight, no threat to leave. By every measure anyone actually looks at, the account is fine.
It is not fine. Eight months ago Coastal stopped buying their stainless fittings from you. The specialty alloy stock went next. What they still order is the commodity hardware, the easy boxes, the stuff anyone can supply. The high-margin items, the ones that made the account worth having, are coming from someone else now.
Nobody noticed, because nobody was looking at the right thing. The order count held steady. The relationship felt warm. And the total revenue dipped a little, but not enough to trip an alarm. So everyone kept treating Coastal like a healthy account while a competitor quietly took the part of it that mattered.
Here is the part that should worry you. This is the most expensive kind of account to lose, and the hardest to win back. Frederick Reichheld's research at Bain & Company, published in the Harvard Business Review, found that increasing customer retention by 5 percent raises profits somewhere between 25 and 95 percent, because the customers you keep spend more over time and cost far less to serve than the ones you have to go chase. A long-standing account that is quietly handing its best business to a competitor is exactly the kind you cannot afford to lose on autopilot. And you will, if the only thing you watch is whether they still order.
Now picture the same account, except once a month a report lands on the rep's desk that does not look at whether Coastal ordered. It looks at what they stopped ordering. And it flags this.
📉 ACCOUNT EROSION ALERT
Coastal Fabrication • Acct #C-2041
Rep: J. Alvarez | Customer 9 yrs | Order frequency: steady, every 12–16 days
🔴 THEY STOPPED BUYING THESE FROM YOU
• Stainless fittings. Ordered 14 times in the prior year. Zero orders in the last 8 months. Was ~$31k/yr.
• Specialty alloy stock. Ordered 6 times the prior year. Last order 5 months ago. Was ~$22k/yr.
• These two categories carried your best margin on the account. Both gone, while total order count never dropped.
📝 WHAT IS ACTUALLY AT STAKE
~$53k in annual revenue has already moved to a competitor, at your highest margin. The remaining commodity business is the easiest for them to take next. The CRM still shows this account green.
Same account everyone called healthy. The report read the basket instead of the relationship.
Nothing in that report came from a new system. No survey, no CRM field anyone has to fill in, no second tool. It all came from one place you already have: your order history. The trick is reading it for what is missing, not just what is there.
Below: the exact pattern to watch for, and the prompt to run the same analysis on your own accounts.
The Pattern Nobody Is Watching
This slips past everyone because the decay does not live in the numbers anyone watches. It lives one level down, in the order composition: which categories an account buys, and how that mix shifts over time. The tell is specific, and almost invisible without comparing year against year, account by account: steady order frequency plus a narrowing set of categories. Same number of orders, fewer kinds of things in them. Recency-based health scores read that account as green right up until the revenue total finally drops, by which point the competitor's foot is most of the way in the door.
What the Report Actually Looks For
You hand an AI assistant your order history and ask it to do the comparison no standing report does: for each account, line up the last several months of buying against the prior year, and surface where the mix has narrowed even though the ordering has not stopped. Four signals matter:
- Dropped categories. Product lines an account used to buy regularly and has not ordered in months, while their overall ordering continued. This is the loudest signal. Coastal's fittings going dark is the whole ballgame.
- Steady frequency, shrinking basket. Accounts ordering as often as ever, but with fewer distinct categories per order than a year ago. The relationship looks alive. The wallet share is leaving.
- Margin mix, not just dollars. Whether the categories an account is dropping are your high-margin lines or the commodity stuff. Losing the technical products while keeping the easy boxes is the dangerous version. The report should tell you which one is happening.
- How far along it is. An account that dropped one category last month is a different problem than one that has been narrowing for three quarters. Ranking by how advanced the erosion is tells your reps which calls to make first.
Every one of those signals is sitting in data you already export. The reason no one sees them is not that the data is missing. It is that reading it requires comparing this year's category mix against last year's, one account at a time, which is exactly the kind of tedious cross-tab nobody does by hand across a few hundred customers. That is the part to hand to an AI.
Build Your Own: The Account Erosion Prompt
Set this up as a Claude Project. Paste the instructions below into the project's custom instructions, then upload your order report as a project file (more on exactly what that report needs in the next section). Once it is built, you run it monthly by uploading a fresh export. Here is the instruction block to start from:
You are a revenue analyst for a distribution business.
You are given one file: a detailed, line-level order
history export. Your job is to find accounts that are
quietly eroding, customers who are still ordering at a
normal rhythm but have stopped buying whole categories
from us, especially our higher-margin lines.
THE FILE CONTAINS ONE ROW PER ORDER LINE, with columns:
account_id, account_name, order_date, product_category
(or product/SKU), quantity, line_total. If a margin or
product-class column exists, use it. If not, infer
likely high-value categories from price per unit and
tell me you did so.
FOR EACH ACCOUNT, COMPARE the most recent 6 months
against the 6 months before that, and report:
1. DROPPED CATEGORIES
- Categories ordered 3+ times in the prior period and
ZERO times in the recent period. List each, with its
prior-period revenue. This is the headline signal.
2. SHRINKING BASKET
- Distinct categories per order, recent vs prior.
- Flag accounts where order frequency held roughly
steady (within 20%) but distinct categories fell.
3. MARGIN EXPOSURE
- For each dropped or shrinking account, say whether the
lost categories skew high-value or commodity. Losing
high-value while keeping commodity is the urgent case.
4. STAGE AND SIZE
- Estimate annualized revenue already lost per account.
- Note how long the erosion has been underway.
OUTPUT:
- A ranked table, worst erosion first, with: account,
dropped categories, est. annual revenue at risk, and a
one-line read on what is likely happening.
- Then the 10 accounts I should call first, and why.
RULES:
- Steady total revenue is NOT proof an account is healthy.
Judge by category mix, not the dollar total.
- Do not flag seasonal categories as "dropped" if the gap
matches a normal seasonal pattern in the data. Say when
you suspect seasonality.
- If you are missing a column you need, tell me what to add
to the export rather than guessing.
Step 1: Pull the Report
This is the part worth getting right, and the good news is it is one export, not a data project. Every ERP and order system can produce a detailed sales or order report. You want the line-level version, at least 18 months of it, so the analysis has a full prior year to compare against. The single thing that makes or breaks this: one row per order line, not one row per order. A summary that shows “Coastal, $4,200, June” hides the exact thing you are hunting, because the erosion lives in which categories moved, not the order total.
The columns the analysis needs, all of which sit on the order line already:
- Account ID and name. So the analysis can group every line back to the customer it belongs to.
- Order date. This is what lets it split recent from prior and judge whether frequency held steady.
- Product category, or product/SKU. The most important column. Category is ideal, because that is the level erosion happens at. If you only have SKU, that works too; the analysis can roll SKUs up, and you can hand it your category list if you have one.
- Quantity and line total (extended price). So it can size what each dropped category was worth and rank accounts by revenue at risk.
- Optional but useful: a margin or product-class field. If your export carries margin or a class flag, the analysis can tell high-value erosion from commodity erosion directly. If it does not, the prompt has it infer likely high-value lines from price per unit, and it will tell you when it did.
Export it as a CSV or Excel file. Most systems already have a “sales detail” or “order line detail” report that gives you all of this. If yours does not break out a category column, that is the one thing worth asking whoever owns your ERP to add, because it is the field everything else hangs on.
Step 2: Run It, Then Run It Monthly
First setup runs about 30 minutes. After that it is a five-minute monthly habit:
- Create a Claude Project called something like “Account Erosion Check.” Paste the instruction block above into the project's custom instructions.
- Upload your order report as a project file. Ask it to run the analysis. Read the first output against two or three accounts you know well, and confirm the dropped categories it flags are real.
- Tune the thresholds if you need to. If it is flagging normal seasonal dips, tell it which categories are seasonal. If the “call first” list is too long, tighten what counts as erosion. This is the step people skip, and it is the one that makes the output trustworthy.
- Re-run it every month with a fresh export. Same project, new file. The accounts that show up two months running are the ones to worry about, and the list gives your reps a standing call sheet instead of a quarterly surprise.
One caution. This report tells you an account is slipping, not why. Do not let a rep open the call by announcing “our data says you stopped buying fittings.” The report is for you, to decide who to call. The call itself is still a normal conversation about how the account is going.
An Honest Take
Most account health scoring is theater, and I will say it plainly. It runs on recency and frequency because those are the easy fields to pull, and it produces a green dashboard that makes everyone feel covered. But recency and frequency are exactly the two numbers a quietly defecting customer keeps perfectly intact while they move their real money elsewhere. The one signal that would actually catch it, what is in the basket and how that has changed, is the one nobody builds into a standing report, because it needs line-level history and a year-over-year comparison that does not ship by default. So I would trust a rep who knows their twenty accounts cold over a CRM health field every single time. This report is not a smarter dashboard. It is a way to give that gut read to a team too big to know every account by heart, using data you are already sitting on
The Bottom Line
The accounts that hurt most are not the ones that leave loudly. They are the ones that never make a sound. No complaint, no cancelled account, no angry call. Just a basket that gets a little narrower each quarter while everyone keeps treating them as a win. You find out at the annual review, when the margin on a nine-year customer has quietly collapsed and the competitor is already entrenched.
You cannot fix what you cannot see, and your current reports are built to miss this exactly. The data that exposes it is already in your order history. It just has to be read for what stopped, not only what continued. That is one export and one prompt.
And there is a second payoff. Run this monthly and you stop reacting to defection and start catching it while it is still a foot in the door, when a single good conversation can still turn it around. That is a far cheaper save than winning the account back after it is gone.
One export. One prompt. Five minutes a month.
Pull the line-level order report. Run it against your accounts. The first list it gives you back will probably name two or three customers you would have sworn were fine.
👇 👇 👇
If your team is still processing orders manually, that's one more thing standing between you and catching those signals early.
Y Meadows is built for that.
Faster processing. Fewer errors. Less operational risk.
Want to see how we help teams automate order entry and surface the insights that keep accounts from slipping away?