Customer segmentation using only your invoices

Your accounting software already holds most of what you need to sort customers into groups worth treating differently. Export one line per invoice with the customer, date, product and amount. Add up spend, count orders and note what each customer buys. Then cut the list into three to five groups. The groups matter only if you will act differently on each, so the work that counts is the last column, what each group gets, and not the sums.

What do you export, and how do you clean it?

Take every invoice line for a period that covers your buying cycle at least twice. Keep five columns: customer name, invoice date, product or product group, amount before tax, and whether the invoice was later returned or credited.

Clean the names first. "Sri Lakshmi Traders" and "Sree Lakshmi Traders" are one customer, and a pivot will happily count them as two. If your product descriptions are messy, add a column and sort them by hand into five or six families. That column pays off later.

Which three measures?

On a second sheet, one row per customer, compute spend (the total), frequency (the number of separate invoice dates, not lines) and spread (how many of your product families the customer buys). A pivot table does all three in minutes. Add last order date if you want to see who has gone quiet; that is a lapsed-customer question, covered in how to reactivate dormant customers.

Where do you cut the groups?

Sort by spend and look for where the list changes character. Usually you will see a handful of large rows, a middle band and a long tail. Cut at the natural gaps, not at round numbers such as the top ten per cent, because your customers will not spread evenly. Then cross spend with frequency and spread to tell apart customers who look alike by spend.

A common pattern in supply trades has five groups. Anchors have the highest spend, frequent orders and several families. Steady regulars have mid spend and a regular rhythm but one or two families. Big-and-rare buyers have high spend, few invoices and one family. Small-and-many are cheap to keep and costly to serve by hand. One-timers bought once. Your list will have different names. If you cannot name a group in a phrase you would say aloud, merge it into its neighbour.

What does each group get?

Fill this in before any campaign.

For the anchors, the five-account version is in account management for your five biggest customers.

The wholesaler who wrote off Diwali buyers

Here is an invented case. A dry-fruit wholesaler in Amritsar exported eighteen months of invoices, around three hundred customers. In the pivot, forty names showed large spend, two or three invoices and one family: almonds and cashews in October. The owner labelled them "big and rare" and put them on the low-attention list, since they ordered nothing for nine months.

A clerk spotted the problem the following August. Most were gift-hamper makers, buying in bulk every year before Diwali, and three had stopped. A competitor had called on them in July with samples. Nobody at the wholesaler had phoned since the previous November.

The error was reading seasonal buying as lapsing. The owner kept the group but changed its handling: a call in July, not October, with the new season's samples, and a note of each buyer's usual order date. That year five of the forty did not return, and the owner could not say how many would have stayed without the calls. But the sheet now shows each buyer's window, and a dated reminder fires before it opens.

The middle of the sheet held a surprise. About fifty customers bought small amounts every month, and each bought only dates or only almonds. Crossing with spread gave a list of steady regulars who had never bought the other lines the shop stocked. They became calls from the counter. Each call mentioned what usually pairs with the customer's own purchase, with no discount.

What goes wrong?

Segmenting by spend alone. A big customer on thin margin and slow payment is not the same as one who pays on time; add margin or payment days if you can. Mixing channels: a dealer who buys to resell and an end user behave differently at equal spend, so separate them first. Doing it once: customers move between groups, so refresh the sheet when your buying cycle completes and look at who changed. And making too many groups. If you cannot remember them, your team will not either.

The method also fails when every customer buys a custom one-off job, because the invoices show no pattern. Then segment by industry or by how they found you, which you must add by hand.

Build the handling table first

Export, pivot, write the table. Do it before any campaign. Even if you do nothing else, the list of anchors and the list of narrow regulars tell you whom to call. If you would like help with the sort, tell us what you need; deciding where effort goes by customer group is one part of sales.