Customer Segmentation with SQL and KMeans

Objective

You have customers and no labels. Nobody has tagged anyone "at risk" or "high value", and you don't want to invent thresholds by hand — the usual WHERE total_spend > 5000 cutoff is a guess that ages badly.

Clustering finds the groups that are actually in the data. This recipe builds an RFM table (Recency, Frequency, Monetary — the standard retail framing), trains KMeans on it in SQL, and then reads the assigned customers and their RFM values back, grouped visually by cluster ID, so you can name the segments yourself.

This needs no pretrained model and no network. Unlike the RAG and agent recipes, clustering is numerical training inside the engine. It runs on a CPU-only install with nothing configured.

Step 1: Build the RFM table

DROP TABLE IF EXISTS recipe_seg_customers;

CREATE TABLE IF NOT EXISTS recipe_seg_customers (
  customer_id   INT PRIMARY KEY,
  name          TEXT,
  recency_days  INT,
  frequency     INT,
  monetary      DECIMAL(10,2)
);

monetary is DECIMAL deliberately: the table preserves cents, and the ML feature frame converts those values to floating-point numbers for numerical training. Spend remains a real input rather than being silently replaced by a missing-value estimate. Numerical model calculations do not retain exact decimal arithmetic.

Step 2: Seed three behaviours that genuinely differ

Thirty customers in three natural groups — loyal high-spenders who bought recently, lapsed customers who used to buy, and steady low-value regulars.

INSERT INTO recipe_seg_customers (customer_id, name, recency_days, frequency, monetary) VALUES
 (1,'Alice Chen',        3, 42, 8450.00), (2,'Ben Okafor',       5, 38, 7920.50),
 (3,'Carla Reyes',       2, 47, 9100.25), (4,'Dmitri Volkov',    6, 35, 7310.00),
 (5,'Elena Fischer',     4, 44, 8780.75), (6,'Farid Haddad',     7, 33, 6950.00),
 (7,'Grace Lindqvist',   3, 40, 8200.00), (8,'Hassan Mboya',     5, 36, 7640.00),
 (9,'Ingrid Sorensen',   2, 45, 8990.00), (10,'Jonas Brandt',    8, 31, 6720.50),
 (11,'Keiko Yamada',   210,  4,  310.00), (12,'Liam Doherty',  245,  3,  275.50),
 (13,'Mariana Costa',  198,  5,  360.00), (14,'Noah Eriksen',  260,  2,  190.00),
 (15,'Olivia Nwosu',   221,  4,  295.00), (16,'Pavel Horak',   252,  3,  240.00),
 (17,'Qing Liu',        205,  5,  340.00), (18,'Rosa Jimenez', 238,  3,  265.00),
 (19,'Samir Patel',    268,  2,  205.00), (20,'Tove Nilsen',   215,  4,  320.00),
 (21,'Uma Krishnan',    28, 14, 1180.00), (22,'Viktor Novak',   31, 12, 1050.00),
 (23,'Wanda Zielinski', 25, 15, 1260.50), (24,'Xavier Dubois',  34, 11,  980.00),
 (25,'Yara Haddad',     29, 13, 1120.00), (26,'Zhang Wei',      36, 10,  940.00),
 (27,'Anja Bauer',      26, 14, 1205.00), (28,'Bruno Silva',    33, 11, 1010.00),
 (29,'Chiara Rossi',    30, 13, 1150.00), (30,'Diego Morales',  27, 15, 1240.00);

Step 3: Ground truth — look at the data before you model it

Always do this. If clustering tells you something the raw data contradicts, you want to know which one is wrong.

SELECT looks_like,
       COUNT(*)            AS customers,
       MIN(monetary)       AS min_spend,
       MAX(monetary)       AS max_spend
FROM (
  SELECT CASE
           WHEN recency_days <= 10  THEN 'bought recently, buys often'
           WHEN recency_days >= 190 THEN 'lapsed, barely bought'
           ELSE                          'steady, modest spend'
         END AS looks_like,
         monetary
  FROM recipe_seg_customers
) AS customer_groups
GROUP BY looks_like
ORDER BY max_spend DESC;

Expected — three groups of ten, well separated on spend:

That CASE is the thing clustering replaces. It works here because I chose the data; on real customers you don't know the cutoffs, which is the point.

Step 4: Train KMeans — note there is no target column

CREATE EXPERIMENT recipe_seg_kmeans AS
  SELECT recency_days, frequency, monetary FROM recipe_seg_customers
WITH (
  task_type           = 'clustering',
  algorithms          = ['kmeans'],
  n_clusters          = 3,
  optimization_metric = 'silhouette',
  max_trials          = 1,
  random_seed         = 42
);

No target_column. That is what makes this unsupervised — there is no answer to predict, only structure to find. A supervised recipe would fail without it; here it would be meaningless.

The response is one JSON column. The field to read is best_score, which is a silhouette value:

{"status":"success","best_model_id":"…","best_score":0.78…,
 "total_trials":1,"score_basis":"internal_validation", …}

Reading a silhouette score, because it is not an accuracy

Silhouette runs from −1 to +1 and measures how well-separated the clusters are: how close each point sits to its own cluster versus the nearest other one.

It is not a percentage and not an accuracy. A silhouette of 0.78 does not mean "78% correct" — there is nothing to be correct about. Presenting it as accuracy is the single most common way these results get misreported.

Step 5: Deploy and preview the assigned segments

DEPLOY MODEL recipe_seg_kmeans FROM EXPERIMENT recipe_seg_kmeans;

SELECT customer_id, name, recency_days, frequency, monetary,
       AUTOML.PREDICT('recipe_seg_kmeans', recency_days, frequency, monetary) AS segment
FROM recipe_seg_customers
ORDER BY customer_id
LIMIT 6;

Step 6: Review all customers together by segment

A cluster ID alone tells you nothing. Return the assigned customers alongside their original RFM values, ordered by the predicted segment:

SELECT customer_id, name, recency_days, frequency, monetary,
       AUTOML.PREDICT('recipe_seg_kmeans', recency_days, frequency, monetary) AS segment
FROM recipe_seg_customers
ORDER BY segment;

This query returns 30 individual rows, arranged in three runs of ten. Compare each run with Step 3: the recent high-spenders, steady regulars, and lapsed customers should each share a segment. The model received only the three numeric features, not the CASE labels or customer names.

These are the fixture profiles to recognise when reviewing those rows. The averages below summarise the fixture; they are not aggregate rows returned by the prediction query:

Prediction composition limit: use the top-level AUTOML.PREDICT form shown here. On this build, wrapping it in an outer GROUP BY or a CTE does not reliably aggregate actual predictions. For a production segment summary, take these returned assignments, materialise them with your application, and then aggregate the stored segment column. This recipe does not claim that nested prediction aggregation is available.

Cluster IDs are arbitrary. Which group gets 0, 1 or 2 can change with training data or initialisation. Never hard-code a cluster id into application logic, a dashboard filter, or a campaign rule. Name segments from their feature values and re-derive the mapping whenever you retrain.

Step 7: What if you don't know how many segments there are?

You usually don't. Train a few values of n_clusters and compare silhouettes:

CREATE EXPERIMENT recipe_seg_k2 AS
  SELECT recency_days, frequency, monetary FROM recipe_seg_customers
WITH (task_type = 'clustering', algorithms = ['kmeans'], n_clusters = 2,
      optimization_metric = 'silhouette', max_trials = 1, random_seed = 42);

CREATE EXPERIMENT recipe_seg_k4 AS
  SELECT recency_days, frequency, monetary FROM recipe_seg_customers
WITH (task_type = 'clustering', algorithms = ['kmeans'], n_clusters = 4,
      optimization_metric = 'silhouette', max_trials = 1, random_seed = 42);

Compare the three best_score values. On this data k = 3 should win, because three groups is what the data actually contains. Pick the k with the best silhouette that you can also explain to the business — a mathematically marginal gain is not worth a segment nobody can act on.

Step 8: When the groups aren't round — DBSCAN

KMeans assumes roughly spherical, similarly sized clusters and assigns every row to one. DBSCAN instead finds dense regions and leaves genuine outliers unassigned, which is what you want when some customers are simply odd.

CREATE EXPERIMENT recipe_seg_dbscan AS
  SELECT recency_days, frequency, monetary FROM recipe_seg_customers
WITH (task_type = 'clustering', algorithms = ['dbscan'],
      eps = 0.35, min_samples = 3,
      optimization_metric = 'silhouette', max_trials = 1, random_seed = 42);

eps is the neighbourhood radius in the transformed feature space. The default ML feature pipeline standardises these numeric columns, so 0.35 is a radius after scaling, not 35 cents or 0.35 days. Tune it again if you change the data or feature preprocessing. min_samples includes the point itself when identifying a dense core. DBSCAN needs no n_clusters; it discovers the count and labels noise as -1. This well-separated fixture need not contain noise.

Also available: mini_batch_kmeans (same idea, cheaper on large tables), hierarchical (nested structure, takes linkage), and gmm (soft, elliptical clusters via n_components; this build supports covariance_type = 'diag').

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_seg_customers;

Expected Outcomes

• Step 3 shows three groups of ten with clearly separated spend.
• k = 3 trains with status: "success" and a silhouette above 0.7.
• Step 6 returns 30 assigned customer rows in three segments of ten, matching Step 3's groups without receiving their labels.
• k = 3 scores better than k = 2 and k = 4 on this data.

Use it from your agent

• REST/SDK: POST /v1/query/execute runs every block here. Train nightly, then store the returned assignments before joining them onto your customer table for campaign targeting.
• Keep it fresh: when automating AUTOML.TRAIN, preserve the clustering task, numeric feature query, and algorithm options. Retraining can change segment IDs, so refresh the profile mapping and stored assignments together.
• Why in-DB: the customer table, the training, and the segment assignment run in one engine and one query language. Your application can name and persist the returned segments. No extract to a notebook, no model server, and no copy of your customer list on someone else's infrastructure.

Key Concepts Learned

• Clustering needs no target_column — that absence is what unsupervised means.
• Silhouette is a separation measure from −1 to +1, not an accuracy. Report it as such.
• Cluster IDs are arbitrary and unstable across retrains. Name segments from their profile; never hard-code an id.
• Choose n_clusters by comparing silhouettes and by whether the segments are explainable.
• KMeans assigns every row; DBSCAN can leave outliers unassigned, which is often the more honest answer.
• Stored DECIMAL amounts become numeric floating-point model features; numerical training uses spend without promising exact decimal model arithmetic.