AI-Augmented Customer Segmentation (1M+ Transactions)
An end-to-end customer segmentation in SQL, where I used an AI-assisted workflow to generate, validate, and document the selection logic, then turned the segments into targeting recommendations.
The problem
An organisation with repeat transactions wants to stop treating its customer base as one audience. Which customers deserve protection, which deserve investment, and which are quietly walking away?
And once you know that: exactly who goes into the next campaign, with every inclusion and exclusion accountable? That second question is the unglamorous one this project takes seriously. Most segmentation portfolios stop at the segment chart. The real job is the selection: deterministic rules, suppression logic, deduplication, and an audit trail a stakeholder can sign off.
Approach
The dataset is UCI Online Retail II: 1,067,371 real e-commerce transactions from Dec 2009 to Dec 2011. The pipeline is six numbered SQL steps, each printing its own audit: load, sanity checks, clean, RFM scoring, segmentation, and a worked campaign selection.
Two decisions worth arguing with, both documented in the SQL: cancellations are netted against purchases rather than dropped (dropping them overstates customer value, and most published examples on this dataset do it anyway); and the roughly 22.8% of rows with no customer ID are excluded with a printed count, not silently.
The segments
Nine named RFM segments with value tiers. The headline is concentration: Champions are 24.9% of customers but 70.0% of net revenue, while roughly £1.27M of historic value sits in the two lapsed segments (At Risk and Can't Lose Them).
Nine segments, and where the money actually is
Column height is the segment's share of net revenue on one shared axis, with reference rings at 20, 40 and 60 per cent. Column area is its share of customers. Position around the circle is identity only and carries no magnitude. Drag to orbit; hover or click a column for its numbers.
Nine RFM segments, ranked by net-revenue share
| Segment | % of customers | % of net revenue |
|---|---|---|
| Champions | 24.9 | 70.0 |
| Loyal | 8.8 | 10.1 |
| At Risk | 9.6 | 6.3 |
| Potential Loyalists | 12.2 | 3.8 |
| Need Attention | 11.1 | 3.3 |
| Hibernating | 18.5 | 3.2 |
| About to Sleep | 10.5 | 1.5 |
| Can't Lose Them | 1.5 | 1.5 |
| New Customers | 2.8 | 0.3 |
Targeting recommendations
Segments are only useful if someone can act on them. Champions (70% of revenue): protect, don't pester; success is retention, not response rate. Loyal: the promotion pool for tomorrow's Champions. Potential Loyalists and New Customers: the lifecycle bet, a deliberate second-purchase journey in the first 90 days.
At Risk and Can't Lose Them (£1.27M historic value): the win-back campaign, worked in full. Hibernating (3.2% of value): suppress from paid channels, because the most actionable finding is often who not to spend on. The worked selection runs a brief through to a 625-customer audience with a per-rule suppression waterfall, priority-ranked by net value.
The AI-augmented workflow
I built this using an AI-augmented workflow I run professionally, reproduced on public data: I write the brief and own every definition, direct an LLM to draft and refactor the SQL, and trust nothing until an audit I designed, and it did not write, passes.
The methodology is evidenced by four real failures caught during the build: a type-inference crash on cancellation invoices, over a million silently-NULL timestamps caught by a pre-written count check, a genuine database optimiser bug isolated by bisection, and non-deterministic tie-breaking caught by a clean-room rebuild.
Why this project
I run high-volume audience selection and targeting for a UK charity's fundraising campaigns; that work is private. This repo reproduces its shape on public data: brief to selection spec to SQL to QA to recommendation, with an AI-assisted workflow wrapped around the SQL. Built fresh; nothing here is lifted from employer systems.