Why Equal-Spend Customers Land in Different Ecommerce Segments

Two customers have spent exactly the same amount, but a segmentation export puts one into the top customer group and the other into the next group. The query may be working exactly as written. Equal-sized buckets and equal treatment of tied values are different requirements, and they cannot always be satisfied together.

EcomToolkit recommends deciding what a customer segment means before choosing the SQL function that creates it. This guide focuses on NTILE, tied spending values and stable membership. All customer identifiers and amounts below are invented. They demonstrate query behaviour, not a recommended loyalty policy or a benchmark for customer value.

Table of Contents

• Decide whether the group is a quota or a threshold
• Reproduce the boundary with a small fixture
• Choose a tie policy that matches the promise
• Expect relative segments to move
• Check sparse groups and missing values
• Validate the export before activating it
• The EcomToolkit view

Decide whether the group is a quota or a threshold

A campaign team may need four similarly sized lists so each account manager has roughly the same workload. A loyalty team may instead promise the same benefit to everyone above a published spending threshold. These decisions require different grouping rules, even if both teams casually describe the result as a quartile.

NTILE divides an ordered partition into a requested number of buckets as evenly as possible. It is useful when row allocation matters. It does not promise that every row with the same spending value remains in one bucket. A boundary can cut through a tied group because the function allocates rows.

The PostgreSQL window-function reference documents NTILE and related ranking functions. Read the output as bucket membership over a defined row population. Do not interpret bucket four as a permanent monetary level or as proof that the customer is profitable.

Before running the query, specify one row per customer, a fixed measurement window, the currency basis and the treatment of cancellations and refunds. If the input contains one row per order, prolific shoppers can occupy several positions and distort both the group sizes and the spending distribution.

Reproduce the boundary with a small fixture

The example below deliberately includes several customers with identical spending. Customer ID provides a stable ordering within ties. Ascending spend means bucket four contains the highest-ranked rows in this fixture; reversing the order would reverse that convention, so document it clearly in the consuming report.

WITH customers(customer_id, spend) AS (
  VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 20),
         ('E', 20), ('F', 30), ('G', 40), ('H', 50)
)
SELECT customer_id, spend,
       ntile(4) OVER (ORDER BY spend, customer_id) AS bucket
FROM customers
ORDER BY spend, customer_id;

Each bucket contains two customers, but customers B through E share the same spending value across three buckets. This is not missing data or a rounding problem. It follows from the equal-sized allocation requirement. The customer ID makes the allocation reproducible; it does not make the commercial difference meaningful.

Run this kind of fixture before exporting production audiences. A large dataset can conceal the boundary because the overall counts look reasonable. A tiny example exposes the decision directly and gives marketing, analytics and customer service a shared explanation for apparently inconsistent treatment.

Choose a tie policy that matches the promise

If equal-spend customers must receive equal treatment, use a value-based policy. That could mean fixed thresholds approved by the business, or quantile cut points accompanied by an explicit rule for observations equal to a boundary. The resulting groups may have different sizes, which is a legitimate consequence rather than a defect.

RANK, DENSE_RANK and cumulative-distribution functions answer different questions. They can help represent tied values, but none should be substituted mechanically just because NTILE produced surprising groups. Define the desired membership for the small fixture first, then choose and test an implementation that produces that membership.

A customer ID is useful as a deterministic technical tie-breaker for workload allocation. It is much harder to justify as the deciding factor for a valuable benefit. If the business wants a lottery among tied customers, use a separately documented and reproducible allocation process rather than disguising an arbitrary order as customer value.

Separate spend from contribution where the decision depends on profitability. A high-spend customer with substantial returns can have a different economic profile from an equally high-spend customer with low service costs. Grouping is the last step of a measurement definition, not a replacement for that definition.

Expect relative segments to move

Relative groups depend on the population. A customer can move from bucket four to bucket three without any change in their own spending if enough other customers enter above them. A rolling measurement window adds another source of movement when older purchases fall outside the window.

For campaign execution, save the segment snapshot, calculation date and rule version. That makes it possible to explain why someone received an offer and to reproduce the audience later. Re-running today’s query against today’s customers is not a faithful reconstruction of last month’s campaign eligibility.

Distinguish useful movement from mechanical churn. Measure how many customers cross a boundary because their spending changed, because the eligible population changed, or because the rule changed. A membership transition report can reveal that an apparently dynamic retention programme is mainly reacting to recalculation noise.

This differs from the row sequencing covered in our SQL running-revenue guide. Both need explicit ordering, but segmentation also needs a commercial policy for what the boundary is allowed to mean. Technical determinism resolves only part of the problem.

Check sparse groups and missing values

A partition with fewer customers than requested buckets cannot populate every bucket. If a report requests ten groups for a market containing seven eligible customers, empty groups are expected. Do not manufacture placeholder customers or treat absent buckets as zero-spend observations just to produce a complete-looking chart.

Make the missing-spend policy explicit before ranking. Unknown amounts should not silently behave like confirmed zero spending. Depending on the analysis, quarantine incomplete customers, report a separate unknown group, or defer the audience until the data is complete. Publish the coverage count beside the resulting distribution.

Also check partitions. Buckets calculated separately within each country represent local position, not a common global spending level. A customer in the top local group may spend less than someone in a middle group elsewhere. Name the segment accordingly so teams do not mistake relative rank for comparable absolute value.

Validate the export before activating it

Count distinct customer IDs in the source and output, inspect duplicate IDs, and reconcile excluded rows. Then inspect customers immediately around every boundary, especially where a large number share a common purchase amount. Popular fixed-price products often create exactly the ties that a smooth distribution would hide.

Test a stable rerun against an unchanged snapshot. Membership should not move because the warehouse happened to read tied rows in a different order. Separately test an intentional rule change and preserve both versions so the business can distinguish recalculation from a real customer behaviour change.

Use our SQL customer-segment existence checks when the audience definition also depends on related orders or exclusions. Eligibility and ranking should be individually inspectable. A correct ranking over the wrong customer population still produces an incorrect campaign list.

The EcomToolkit view

A segment label is a business promise supported by a query. Use equal-sized buckets when allocation is the goal, and value-based boundaries when equal values must receive equal treatment. Save the snapshot so the decision remains explainable after the customer base changes.

An EcomToolkit analytics review can start with eight fictional customers and the expected outcome. Agreement on that small example is often more valuable than another dashboard showing perfectly balanced groups without explaining who belongs in them.