Consumer goods company · via Accenture · 2024
Customer Data Cleansing & RFM Segmentation at Scale
AI Engineer
Client engagement. Names, data, and identifying details are withheld; the architecture is described at a generalized level.
Problem
The company sells several brands across multiple online marketplaces. Customer phone numbers, emails, and addresses were inconsistent, and there was no repeatable way to segment customers by value and engagement.
Approach
I packaged cleansing and segmentation as parameterized notebooks that run headless in Docker, read from the data warehouse, and write results to S3, so they could be scheduled instead of run by hand.
Architecture
Hover a component to trace its connections · drag to pan · use + / − to zoom
- Source
- Processing
- Model
- Agent / optimizer
- Output
How It Works
- Cleanse contactsPhone numbers are validated and normalized to E.164; emails are fixed, domain-corrected with fuzzy matching, and checked via DNS/MX; shared or dummy values are flagged.
- Standardize addressesAbbreviations are expanded, and n-gram matching against a public postal-code and administrative-region database corrects district, city, province, and zip code.
- RFM featuresRecency, frequency, and monetary value over a rolling 12 months, overall and per product category, after filtering out bundles and gifts.
- Cluster77 scaled K-means models across marketplaces, brands, and categories, validated with silhouette scores.
- Label with an LLMCluster statistics go to Claude on AWS Bedrock with few-shot prompts and Pydantic-structured output, mapping clusters to New, Potential Loyalist, Loyal & Champion, or Churning.
- Publish & trackSegments land in S3 as Parquet; period-over-period comparison flags reactivated customers and segment shifts.
What I Built
- Containerized cleansing jobs for phone, email, and address data.
- Per-brand, per-category RFM segmentation with LLM-generated segment labels.
- Segment-shift reporting between periods.
- Affiliate tiering (XGBoost plus simulation) and next-purchase prediction with survival analysis.
Impact
Replaced manual, one-off segmentation with a repeatable pipeline the client could schedule, and gave marketing teams segment names they could act on directly.