How to build Customer Segments in SQL
Contents:
What Customer Segments actually answer
Your CRM lead drops a Slack message: "Lifecycle email open rates are flat at twelve percent — find me the segments worth treating differently." Headline metrics flatten across a mixed user base. A flat twelve percent rate hides forty percent among engaged buyers, seven percent among lapsed users, and one percent among trial users who never came back. Customer segmentation surfaces those gaps so marketing, pricing, and retention stop sending the same message to everybody.
A segment is a group of users who share an attribute the business can act on. A useful segmentation is not the cleverest cut on a slide; it is the cut that maps to a campaign a team will run next week. If nobody can name the action, the segment does not earn its place.
This pattern shows up in CRM and senior data analyst interviews at Stripe, Notion, DoorDash, Airbnb, and Netflix. The whiteboard question is some version of "write the SQL for our customer segments and tell me how you would activate each one". You need value, behavior, lifecycle, and RFM in your head, plus the trade-offs and the common mistakes that surface in production.
Value segmentation
Value segmentation answers "who pays us the most" and is easy to compute because the data sits in transactions. Sum paid revenue per user, then bucket users into deciles via NTILE. The top decile is whales, the next two are high-value, the middle is mid-value, the bottom half is low-value. Thresholds come from your distribution, not arbitrary dollar amounts — the recipe ports across products and currencies.
WITH user_value AS (
SELECT
user_id,
SUM(amount) AS lifetime_value
FROM transactions
WHERE status = 'paid'
GROUP BY user_id
)
SELECT
user_id,
lifetime_value,
CASE
WHEN NTILE(10) OVER (ORDER BY lifetime_value) = 10 THEN 'whale (top 10%)'
WHEN NTILE(10) OVER (ORDER BY lifetime_value) >= 8 THEN 'high-value'
WHEN NTILE(10) OVER (ORDER BY lifetime_value) >= 5 THEN 'mid-value'
ELSE 'low-value'
END AS value_segment
FROM user_value;NTILE(10) buckets users into ten equal groups ordered by lifetime value. Calling it twice in the same CASE is safe — most planners reuse a single window pass — but on older engines, extract it into a CTE column. The output value_segment is a label the activation team filters on directly: whales get a dedicated account manager, high-value users get a VIP cadence, and low-value users get a self-serve nudge.
The trap is treating refunds as zero revenue. Subtract refunds from SUM(amount) or filter them in WHERE. Otherwise a user who placed two thousand dollars of orders and refunded eighteen hundred shows up as a whale on the dashboard.
Behavior segmentation
Behavior segmentation answers "how engaged is the user" in a window short enough to react to. The recipe uses a thirty-day window because that matches campaign planning. Three signals carry enough information: distinct active days, average session length, and purchases. The CASE produces a power-user / engaged / casual / dormant ladder that lifecycle emails and push campaigns plug into directly.
WITH user_behavior AS (
SELECT
user_id,
COUNT(DISTINCT DATE(activity_time)) AS active_days_30d,
AVG(session_minutes) AS avg_session_min,
COUNT(*) FILTER (WHERE event = 'purchase') AS purchases_30d
FROM events
WHERE activity_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
)
SELECT
user_id,
CASE
WHEN active_days_30d >= 20 AND purchases_30d >= 3 THEN 'power user'
WHEN active_days_30d >= 10 THEN 'engaged'
WHEN active_days_30d >= 3 THEN 'casual'
ELSE 'dormant'
END AS behavior_segment
FROM user_behavior;The COUNT(*) FILTER (WHERE event = 'purchase') syntax is the Postgres and Snowflake idiom for a conditional count. On BigQuery use COUNTIF(event = 'purchase'), on Redshift use SUM(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END). Pick the dialect your warehouse runs.
The boundaries 20 / 10 / 3 are hand-picked, and they are. Production code reads percentiles off the active distribution and refreshes cut points monthly. Build the recipe so thresholds are easy to swap — ideally as dbt macro parameters. Hardcoding the numbers in ten places is the smell that haunts the next analyst.
Lifecycle segmentation
Lifecycle segmentation answers "where is this user in their journey" — brand-new signup, activated paying user, lapsed customer. The cut is built on a few binary facts: days since signup, active in the last seven days, ever paid. Compose them into a CASE ladder and you have the spine of every lifecycle email program.
WITH user_lifecycle AS (
SELECT
u.user_id,
u.created_at,
CURRENT_DATE - u.created_at::DATE AS days_since_signup,
EXISTS (
SELECT 1 FROM events
WHERE user_id = u.user_id
AND event_date::DATE >= CURRENT_DATE - 7
) AS active_7d,
EXISTS (
SELECT 1 FROM transactions
WHERE user_id = u.user_id
AND status = 'paid'
) AS ever_paid
FROM users u
)
SELECT
user_id,
CASE
WHEN days_since_signup < 7 THEN 'new (week 1)'
WHEN days_since_signup < 30 AND NOT active_7d THEN 'churning new'
WHEN active_7d AND ever_paid THEN 'active paying'
WHEN active_7d AND NOT ever_paid THEN 'active non-paying'
WHEN NOT active_7d AND ever_paid THEN 'lapsed paying'
ELSE 'dormant'
END AS lifecycle_segment
FROM user_lifecycle;The EXISTS (SELECT 1 FROM ... WHERE user_id = u.user_id) correlated subquery is the readable way to compute a per-user boolean. On Snowflake or BigQuery with clustering on user_id and date partitioning, the optimizer rewrites it into a semi-join that scans a small slice. If your warehouse cannot do that rewrite, replace each EXISTS with a precomputed boolean from a LEFT JOIN to a deduplicated CTE.
Lifecycle segments map directly to email tracks: new users get onboarding, churning new users get re-engagement, active paying users get cross-sell, active non-paying users get conversion pushes, lapsed paying users get win-back, dormant users get a sunset cadence. Edit the CASE until it matches your product — the structure of one boolean per business question stays the same.
RFM scoring
RFM stands for Recency, Frequency, Monetary — the oldest segmentation in direct marketing because it works on any transactional business. Score each user one-to-five on each axis, concatenate the scores into a three-digit code, and bucket the codes into campaign-ready segments like champions, loyal, at risk, and lost. The recipe uses NTILE(5) per axis to sidestep the percentile-versus-arbitrary-threshold debate.
WITH rfm_stats AS (
SELECT
user_id,
CURRENT_DATE - MAX(transaction_date)::DATE AS recency_days,
COUNT(*) AS frequency,
SUM(amount) AS monetary
FROM transactions
WHERE status = 'paid'
AND transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY user_id
),
rfm_scored AS (
SELECT
user_id,
recency_days,
frequency,
monetary,
6 - NTILE(5) OVER (ORDER BY recency_days) AS r_score,
NTILE(5) OVER (ORDER BY frequency) AS f_score,
NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm_stats
)
SELECT
user_id,
r_score, f_score, m_score,
r_score * 100 + f_score * 10 + m_score AS rfm_code,
CASE
WHEN r_score = 5 AND f_score >= 4 AND m_score >= 4 THEN 'champions'
WHEN r_score >= 3 AND f_score >= 3 THEN 'loyal'
WHEN r_score <= 2 AND f_score >= 3 THEN 'at risk'
WHEN r_score <= 2 AND f_score <= 2 THEN 'lost'
ELSE 'other'
END AS rfm_segment
FROM rfm_scored;The 6 - NTILE(5) OVER (ORDER BY recency_days) flip is the move that trips most juniors on a whiteboard. Smaller recency means the user bought more recently, which should map to a higher R score. NTILE(5) ascending puts smallest values in bucket one, so subtracting from six inverts the scale and lines recency up with frequency and monetary. Skip the flip and your champions segment is full of users who have not bought in eleven months.
RFM is intentionally coarse — 125 codes collapsed into a handful of buckets. That is a feature. For finer cuts, add a category axis like product line and run a per-axis RFM rather than expanding the score range.
Cross-segment view
A single segmentation rarely tells the full story. Real value shows up when you cross two cuts — value by behavior, lifecycle by channel, RFM by plan. Cross counts surface where high-LTV users actually live.
SELECT
value_segment,
behavior_segment,
COUNT(*) AS users,
AVG(lifetime_value) AS avg_ltv
FROM user_segments
GROUP BY value_segment, behavior_segment
ORDER BY users DESC;Materialize user_segments as a daily-refreshed table joining the per-user segments from earlier sections, keyed on user_id. A cross tab is then one GROUP BY away. These views also surface contradictions — a whale who is also dormant is the highest-priority retention call of the month, and that row only appears when you cross value and behavior.
Common pitfalls
The most common early mistake is choosing one segmentation and treating it as universal. Value drives pricing; behavior drives lifecycle emails; lifecycle drives activation programs. Forcing one cut into every campaign produces awkward edges — a dormant whale gets the same email as a new whale, and the team loses trust in segmentation within a quarter. Build all four cuts as columns on the same per-user table and let each program filter the cut it needs.
The second pitfall is shipping static segments. A user moves from new to active paying to lapsed paying over a year, and any segmentation that does not refresh keeps marketing to last quarter's user. Refresh nightly and treat segment assignment as state that ages, not as a label set at signup.
The third pitfall is too many segments. Above eight or ten, segments stop being actionable because the CRM team cannot maintain that many campaigns. For finer cuts, build a propensity model — it serves continuous scores without each cut needing its own campaign.
The fourth pitfall is arbitrary boundaries. Calling a user a whale at exactly one thousand dollars almost never matches the distribution. Use percentile cuts via NTILE, document the cut points, and refresh them when the distribution shifts. If stakeholders insist on a dollar threshold, compute both and keep the percentile cut as source of truth.
The fifth pitfall is segments without actions. A clever segment nobody campaigns against is dead weight. Pair every new segment with an activation — an email track, a discount tier, a support route, a feature flag. If you cannot name the action in five minutes, do not add the column.
Optimization tips
Materialize the per-user segmentation table rather than computing it at query time. A dbt model that refreshes overnight, keyed on user_id, with one column per dimension is the standard pattern on Snowflake, BigQuery, and Databricks. Dashboards read a small denormalized table and the heavy NTILE and EXISTS work runs once per day instead of once per hit.
The second lever is filtering input tables before window functions run. RFM and value segmentation only need the trailing twelve months of transactions; behavior segmentation only needs thirty days of events. Push those WHERE clauses into the earliest CTE and let partition pruning eliminate older data before the join. On a billion-row event log, the speedup is one to two orders of magnitude.
The third lever is partitioning the refresh by user cohort once the table passes a few hundred million rows. Refresh new and active users daily, lapsed and dormant weekly — the dormant slice rarely changes day to day. Most CRM-pipeline cost overruns come from refreshing cold data on a hot cadence.
Related reading
- How to calculate Cohort Retention in SQL
- How to calculate Cohort Revenue in SQL
- How to calculate Churn in SQL
- How to calculate Churn Risk Score in SQL
- How to find the Aha Moment in SQL
- SQL window functions interview questions
If you want to drill segmentation SQL questions like this daily, NAILDD is launching with 500+ SQL problems on exactly this pattern.
FAQ
Which segmentation should I build first?
Lifecycle, because it maps to the most expensive programs a CRM team runs — onboarding, activation, win-back, and sunset emails. Value is the second build because it informs pricing and account management. Behavior is third because it shapes engagement campaigns. RFM is a useful overlay once the first three are stable. Trying to build all four in one sprint produces shallow versions of each.
How often should I refresh segments?
For most B2C products, nightly — fresh enough that the CRM tool sends the right message in the morning, and load is bounded to one off-peak window. For high-frequency products like trading or food delivery, hourly is worth the cost. For B2B SaaS with long sales cycles, weekly is enough. Anchor cadence to how fast behavior changes, not to how often the dashboard reloads.
Should segments be exclusive or overlapping?
Within one dimension, segments must be mutually exclusive — a user is either a whale or high-value, never both. Across dimensions, overlap is the point: a user is simultaneously a whale, a power user, and an active paying lifecycle stage, and each label drives a different program. Storing dimensions as separate columns makes the overlap queryable.
Can a propensity model replace segmentation?
For prioritization, often yes. A churn propensity score gives a continuous ranking of at-risk users that beats any handcrafted at-risk segment for sorting outreach. For human-run programs that need named groups, segmentation still wins — campaigns are easier to plan against a labeled audience than a percentile cutoff. The mature setup is both: segments drive program structure, propensity scores drive per-user prioritization inside each segment.
What is the right number of segments per dimension?
Four to eight. Below four, segments are too coarse to drive different actions. Above eight, the CRM team cannot maintain distinct creative. If a stakeholder asks for fifteen, push back and ask which three campaigns will run differently — the conversation usually collapses back into the four-to-eight range.