By Michael Nocito, data analyst · Published August 8, 2026 By the end of this page you can turn a table of transactions into named customer groups, count and total each group, cross two segmentations into a matrix, choose between cut-offs you picked and cut-offs the data picked, and prove that every customer landed in exactly one group.
It is about twenty-five minutes, and every query and result below was run.
Here is what to do today, on the segmentation you already have.
Count the customers in each segment and add them up.
If the total is less than your customer count, some rows fell through the conditions and are sitting in a null segment nobody looks at.
If any segment has zero customers, the conditions are in the wrong order.
The short version: aggregate to one row per customer, add a expression that names the group, then group by that name.
Label first, group second, is the idea, so it gets the picture.
The original carries a diagram here.
In words: Three panels left to right, joined by arrows.
The first panel is a column of twelve horizontal bars of different lengths, standing for twelve customers and how much each spent, in no particular grouping.
The second panel repeats the same twelve bars in the same order, but each one now has a small coloured square attached to its left-hand end: three bars carry a dark square, four carry a mid-toned square and five carry a pale square, so every bar has exactly one square and no bar has none.
The third panel holds just three stacked blocks, one in each of the three shades, sized in proportion to how many bars carried that shade, and holding the figures 3, 4 and
5.
The picture shows that the grouping in the third panel is produced entirely by the squares added in the second, and that the twelve bars are still all accounted for.
Every result on this page is real.
Twelve customers, nineteen purchases and 2,430 in spend, loaded into DuckDB and queried.
Twelve is small enough to check every segment by hand, which is the only way to know a segmentation query is right before it meets a real customer base.
1.
Get to one row per customer first Segmentation is about customers, and the transactions table is about purchases, so the first move is always an aggregate.
Nothing about the labelling works until the grain is right.
Twelve rows, and they sum to 2,430, which is the whole purchases table.
Those three columns are the classic ingredients of a customer segmentation: how recently, how often, how much.
You do not have to use all three, and you do have to compute them before you can band them.
Do this as a named step rather than nesting it, so the customer count can be checked on its own; the argument for that is in subquery against CTE, and it matters here because everything downstream is divided by that count.
2.
CASE: the label is just a column reads a condition list from top to bottom and returns the value attached to the first condition that is true.
What comes out is an ordinary text column.
That is the whole idea and it is the thing worth internalising: the segment is not a special kind of object, it is a value computed per row, which means it can be selected, filtered, joined on, counted and, most usefully, grouped by.
The mechanics of the expression itself, including the difference between the simple and searched forms, are in the CASE guide.
Because it is computed rather than stored, the definition lives in the query.
That is convenient while exploring and a liability once three reports each contain their own slightly different copy, which is what section eight's reconciliation and the note about materialising it are for.
3.
Group by the label Put the in the select list and group by it, and the segmentation is done.
Three, four and five customers, adding to twelve.
Their spend adds to 2,430.
Check one by hand: High is C2 at 470, C1 at 350 and C7 at 300, which is 1,120.
And now the finding, which is the point of doing it at all: three customers out of twelve, a quarter of them, acc