Segmenting Customers by Purchase Behavior

Marketing sends you a message: "Can you tell us who our VIP customers are? And who's about to churn?" You have an orders table. Each row is a transaction - customer ID, order date, amount. What they actually need is one row per customer with a tier label: VIP, Regular, At Risk, Churned.

The gap between transactional data and customer segments is surprisingly common. You need to aggregate per customer (total spend, order count, last order date), compute recency, then apply business rules to assign tiers. Here's the full pipeline in ExploreMyData.

What you're starting with

A CSV with columns like customer_id, customer_name, email, order_date, order_total, and maybe product_category. Some customers appear once. Some appear 200 times. The data spans two years.

Step 1: Collapse to one row per customer

The instinct is to reach for a window function so you keep every transaction and bolt the totals on beside them. Resist it. The Window Function panel here offers ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG and RUNNING TOTAL, and nothing else. There is no windowed SUM, COUNT or MAX to partition by customer.

You do not need one. A segment list is one row per customer by definition, so collapse first and score afterwards. Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group.

• Group by: customer_id, customer_name, email
• Aggregations: SUM of order_total, COUNT of order_total, MAX of order_date

Each aggregation row is a function dropdown followed by a column dropdown, in that order. There is no place to name the output, because the names are derived: order_total_sum, order_total_count, order_date_max. Column name, underscore, function name in lower case. Learn that rule once and you can predict every column the step produces.

Putting customer_name and email in the Group by list is deliberate: it carries them through to the output without needing an aggregate on them. It is safe as long as they really are constant per customer. If your CRM has two spellings of a name for the same ID, you will get two rows for that customer, which is a data-quality bug worth knowing about anyway.

Step 2: Turn today into a column

Recency is the churn signal, and recency needs today's date. Date Difference compares two date columns and offers no way to type a literal, so today has to become a column first.

Add a Add Column step from the Columns group. Name it today, expression CURRENT_DATE, type date. Every row gets the same value, and because it is CURRENT_DATE rather than a hard-coded string, the whole segmentation re-scores itself the next time you open the file.

Step 3: Days since the last order

Now Date Difference from the Date group has both columns it needs.

• First date column: order_date_max
• Second date column: today
• Unit: day
• Apply results into: New Column, named days_since_last_order

Order matters: first column then second, so a past order date against today gives a positive number. Swap them and every customer looks like they ordered in the future. Name the output too, because the default is date_diff and you will not thank yourself in three months.

Step 4: Assign customer tiers

Now the fun part. Another Add Column step, named customer_tier, type text, with a CASE expression that encodes the business rules:

• VIP: total spend over $5,000 and an order in the last 90 days
• Regular: an order in the last 90 days, spend under $5,000
• At Risk: last order between 91 and 180 days ago
• Churned: nothing for more than 180 days

CASE
  WHEN "order_total_sum" > 5000 AND "days_since_last_order" <= 90 THEN 'VIP'
  WHEN "days_since_last_order" <= 90  THEN 'Regular'
  WHEN "days_since_last_order" <= 180 THEN 'At Risk'
  ELSE 'Churned'
END

The branches are ordered on purpose. Sandra clears both the VIP test and the Regular test, and the first match wins, so she comes out VIP. Flip those two lines and every VIP in your file quietly becomes a Regular.

One more step is worth adding: Delete Columns on today, which has done its job and is now just a column of identical dates cluttering the export.

The complete pipeline

1. Group & Aggregate: one row per customer, with spend, count and latest order date
2. Add Column: today = CURRENT_DATE
3. Date Difference: days_since_last_order
4. Add Column: customer_tier
5. Delete Columns: drop today

Filter on "customer_tier" = 'At Risk' and hand that list to the retention team, or add a Select Columns step to trim to name, email, tier and spend before exporting. The thresholds are guesses until someone in marketing argues with them, so go back into the Step 4 card, change 5000 to 3000, and watch every tier re-score.

If you need the transactions too

Collapsing is the right default, but sometimes you want the tier stamped onto every individual order, for example to chart VIP revenue by month. You can have both, using two views over the same file.

1. Click the + at the end of the view tab strip. That adds "View 2" over the same data, with its own empty pipeline.
2. In View 2, build the Group & Aggregate step from Step 1 above, then the tier steps. This is your customer table.
3. Go back to View 1, which still holds every order, and add a Join step from the Data group. The table dropdown lists views, not just files, so pick the entry ending in "View 2".
4. Left key customer_id, right key customer_id. Use step 3 of the Join panel, "Columns to include from right table", to pull across just customer_tier and order_total_sum, otherwise you get a second copy of every column including the key.

Now View 1 is eight order rows each carrying its customer's tier, and View 2 is the four-row segment list. Same source file, two shapes, and editing the tier rules in View 2 updates both.

Segment your customers now →