A retention brief asks for customers who bought a coffee machine but have not bought replacement filters. A simple join produces several rows for frequent purchasers, while a hurried exclusion removes people for the wrong time period. The resulting audience looks plausible because every row has a valid customer identifier, yet it answers a different question.
EcomToolkit recommends translating this type of brief into explicit existence conditions before calculating audience size. The goal is one customer row with clearly defined qualifying and disqualifying evidence. This guide uses a fictional purchase ledger; it does not imply that a purchase segment is automatically eligible for marketing contact.
Table of Contents
- Define what counts as a qualifying purchase
- Use existence when the question is yes or no
- Keep the outer table at the promised grain
- Treat missing identity and missing history explicitly
- Separate segment membership from contact eligibility
- Make performance a measured database question
- The EcomToolkit view
Define what counts as a qualifying purchase
Start with the business meaning of bought. Does an authorised but unpaid order qualify? What about a cancelled order, a fully returned item or a test transaction? Decide whether the relevant unit is an order, a line item or a retained product after returns. Write those rules beside the query.
Next define the product group. A filter category may contain accessories that are not compatible with every machine. If the campaign is about replenishment, product compatibility is part of the segment definition. A broad category label is not sufficient evidence that the customer bought the relevant replacement.
Finally establish the date window and timezone. A purchase during September and a purchase at any point in the customer’s history are different conditions. Use a clear start boundary and an exclusive end boundary so adjacent reporting periods do not overlap at midnight.
| Fictional customer | Machine purchase | Filter purchase | September machine, no lifetime filter |
|---|---|---|---|
| A | Two paid orders in September | None | Included once |
| B | One paid order in September | One in August | Excluded |
| C | One cancelled order in September | None | Excluded |
| D | One paid order in August | None | Excluded |
The table is a miniature acceptance test. A query that returns A twice, includes B or admits C does not match the stated brief, regardless of how efficiently it executes.
Use existence when the question is yes or no
The PostgreSQL subquery documentation describes EXISTS as a test of whether a subquery returns any rows. That corresponds directly to a question such as whether a customer has at least one qualifying purchase. The outer customer record need not expand into one row per matching order.
The following illustrative query assumes one customer per row in customers and a simplified purchase_lines table containing customer_id, product_group, status and purchased_at. Production field names, timestamps and status rules must be mapped to the actual warehouse model.
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM purchase_lines AS p
WHERE p.customer_id = c.customer_id
AND p.product_group = 'machine'
AND p.status = 'paid'
AND p.purchased_at >= '2026-09-01'
AND p.purchased_at < '2026-10-01'
)
AND NOT EXISTS (
SELECT 1
FROM purchase_lines AS f
WHERE f.customer_id = c.customer_id
AND f.product_group = 'filter'
AND f.status = 'paid'
);The exclusion deliberately has no September date restriction. It asks whether any qualifying filter purchase exists in the available history. If the actual brief says no filter purchase since the machine purchase, the query needs a relationship between those timestamps rather than a global calendar window.
Keep the outer table at the promised grain
EXISTS does not make duplicate customer records disappear. If the outer customers table contains the same customer_id twice, both outer rows may qualify. Establish uniqueness in the customer dimension or choose an approved canonical customer source before applying the conditions.
Avoid fixing that upstream problem by casually adding DISTINCT to every audience query. DISTINCT can hide duplicate rows without explaining which identity attributes are authoritative. Two records might have different countries, consent states or account owners even when the selected identifier happens to match.
Our guide to SQL join fanout covers the wider issue of row multiplication. Here the practical distinction is that existence expresses a membership condition. A join remains useful when you actually need the matching purchase rows and intend to aggregate or inspect them.
Count the returned rows and distinct customer identifiers as separate checks. They should agree when the promised output is one row per customer. Retain a sample of qualifying purchase evidence so another analyst can explain why an individual record entered the audience.
Treat missing identity and missing history explicitly
A guest purchase with no resolved customer identifier cannot match the illustrated equality condition. That does not establish that the customer never bought a filter. It establishes that the available key did not connect the purchase to the customer record used by the query.
Similarly, an imported store may have only two years of purchase history. Label the segment as having no observed qualifying filter purchase in that available period. Do not present a limited warehouse observation as proof about the customer’s lifetime relationship with the business.
Use a separate diagnostic count for purchases with missing identities and for records that failed ingestion. If those gaps are substantial, consider whether the audience should be held back or its interpretation narrowed. The right response depends on the consequence of a mistaken inclusion, not a universal missing-data threshold.
NOT EXISTS is often easier to reason about than a NOT IN expression when nullable keys are present, but that does not repair incomplete data. Read our SQL NULL reporting guide for the underlying three-valued logic. Keep the identity-quality assessment beside the segment, not hidden in a technical appendix.
Separate segment membership from contact eligibility
A purchase-based segment identifies a commercial relationship in the data. Contact eligibility is another set of controls, based on the channel, applicable rules and the store’s approved policies. Preserve that separation so a useful analytic audience does not silently become an instruction to send messages.
For internal analysis, the team may need only anonymous counts by machine model and age of purchase. An activation export might require additional approved filters and a minimum set of fields. Avoid copying full customer records merely because the query can return them.
Make the handoff reproducible. Record the run timestamp, purchase-data cutoff, product-group mapping version and the exact window interpretation. Someone reviewing tomorrow’s audience should be able to distinguish genuine new purchases from late data arrivals or a changed category map.
| Control | Question it resolves | Failure signal |
|---|---|---|
| Unique outer customer key | Is the output one row per person or account? | Row count exceeds distinct keys |
| Included and excluded fixtures | Does the query match the written brief? | Expected customer enters or disappears |
| History coverage note | What does never mean in this dataset? | Unqualified lifetime claim |
| Missing-identity report | Which purchases cannot be connected? | Unknown records treated as non-buyers |
| Activation eligibility step | Can this analytic record be contacted? | Segment exported without channel controls |
Make performance a measured database question
The query’s logical form is only part of its execution cost. Indexes, table sizes, data distribution and the database optimiser affect the plan. Do not promise that EXISTS is always faster than a join, or assume that the written order of conditions is the order in which the database evaluates them.
Review the plan against realistic data, preferably in a suitable analysis environment. A customer identifier and purchase timestamp may be useful access paths, but the right design depends on the warehouse. Maintain a correct reference result while testing alternatives so a faster query does not quietly alter membership.
For scheduled segments, monitor runtime, returned customers and the data cutoff together. A sudden audience collapse can come from an ingestion delay even when the query succeeds. A growing audience can reflect a broken exclusion feed rather than increased demand for replenishment.
Keep a stable small fixture with repeat purchasers, cancelled orders, historical exclusions and missing keys. Run it when business rules change. Those records exercise the actual meaning of the segment more effectively than checking only that the production query returns a nonzero number.
The EcomToolkit view
A reliable audience begins with a sentence precise enough to test. EXISTS and NOT EXISTS can express that sentence without multiplying purchase rows, but identity, history and purchase status still determine whether the answer is useful. Treat the SQL as an implementation of an agreed commercial definition.
Bring the brief, a small anonymised fixture and the warehouse grain to an EcomToolkit analytics review. The first deliverable should be an explainable segment, with contact eligibility handled as its own explicit step.
For the next analytical step, read our guide to NTILE customer segmentation and the treatment of equal spending values.
