Skip to main content

K-means: customer segmentation

Find groups of similar customers and interpret their profiles without mistaking cluster IDs for labels.

The practical problem and model​

A customer-success team wants to understand patterns of engagement before deciding which services or messages are useful to each group. The fixture describes nine customers by spend, recent sessions, and time since last activity.

K-means is unsupervised: there is no target label. It alternates between assigning each row to a nearby centroid and updating the centroids, seeking compact groups. k = 3 asks for three groups; it does not prove that three is the right number for a real customer population.

Cluster numbers are arbitrary identifiers. Interpret a cluster through its feature profile, not by assuming that cluster zero always means a particular type of customer.

Before you run​

Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.

Studio: Tutorial: K-Means Clustering — Customer Segmentation. Download the complete SQL.

Step 1: Create three visible behavior patterns​

The fixture separates high-spend active customers, moderately active customers, and low-spend customers with long absences.

CREATE CATALOG IF NOT EXISTS tutorial;

CREATE SCHEMA IF NOT EXISTS tutorial.segments;

CREATE OR REPLACE TABLE tutorial.segments.customers (
    customer_id     BIGINT,
    monthly_spend   DOUBLE,
    sessions_30d    DOUBLE,
    days_since_last DOUBLE
);

INSERT INTO tutorial.segments.customers VALUES
    -- High spend + active
    (1, 450, 28, 1), (2, 510, 25, 2), (3, 480, 22, 1),
    -- Mid spend + moderate
    (4, 120, 12, 7), (5, 140, 10, 9), (6,  95, 14, 5),
    -- Low spend + lapsed
    (7,  20,  1, 60), (8,  10,  0, 80), (9,  30,  2, 45);

Step 2: Train without a label column​

All three SELECT columns are features. The fixed seed and ordered input support repeatable fixture comparisons.

CREATE OR REPLACE MODEL tutorial.segments.kmeans_model
    (DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'kmeans'
OPTIONS (k = 3, max_iters = 100, tolerance = 1e-4, seed = 42)
AS SELECT
    monthly_spend,
    sessions_30d,
    days_since_last
FROM tutorial.segments.customers ORDER BY customer_id;

EXPLAIN MODEL tutorial.segments.kmeans_model;

Step 3: Inspect membership and profiles​

The first query returns nine assignments. The second summarizes each group so its business meaning can be assessed.

SELECT
    customer_id,
    monthly_spend,
    sessions_30d,
    days_since_last,
    CAST(tutorial.segments.kmeans_model(monthly_spend, sessions_30d, days_since_last) AS INT) AS cluster
FROM tutorial.segments.customers
ORDER BY cluster, customer_id;

SELECT
    cluster,
    COUNT(*) AS members,
    AVG(monthly_spend)   AS avg_spend,
    AVG(sessions_30d)    AS avg_sessions,
    AVG(days_since_last) AS avg_recency
FROM (
    SELECT *,
        CAST(tutorial.segments.kmeans_model(monthly_spend, sessions_30d, days_since_last) AS INT) AS cluster
    FROM tutorial.segments.customers
)
GROUP BY cluster
ORDER BY cluster;

What to check in the results​

Expect three groups with three members each. Compare these profiles even if cluster IDs are permuted:

ProfileAverage spendAverage sessionsAverage days since last
High-spend, active480251.33
Moderate118.33127
Low-spend, lapsed20161.67

All nine customer IDs should appear exactly once.

Adapt it to real data​

Scale features deliberately: dollar amounts, counts, and days have different ranges, and distance is sensitive to those ranges. Fit the scaling on the training population and apply it consistently.

Test cluster stability across samples and periods. Compare several values of k, inspect whether the groups are useful, and retain centroid/version metadata when assigning names. A cluster is a statistical grouping, not a causal explanation of customer behavior.

K-means reference

All tutorials · Setup and troubleshooting

Last updated on