Customer Segmentation
Built the system that lets a non-technical Customer Success rep build audiences like “shoppers who'd buy again if reminded” without writing a line of code or filing an engineering ticket — unifying four disconnected data sources into one query interface.
My Role
Owned architecture and product design — the unified data model, query composition system, and precomputation strategy (scheduled queries + views). Directed the query builder, materialization pipeline, and customer data model implementation with a small team (2-3 engineers, alongside other projects), while partnering directly with the CSM team on audience needs and monitoring BigQuery cost and query performance in production.
Customer Success wanted to run targeted campaigns — “remind shoppers who almost bought,” “reward loyal customers.” Customer signals lived in four disconnected systems: click-tracking, commerce, orders, loyalty. CSMs filed engineering tickets for each segment, blocking campaigns and capping productivity.
No Self-Service, No Unified Model
Segments were built ad-hoc for campaigns, manually combined by engineers. Each new segment added 3-5 days of latency. The loyalty program couldn't be queried against order data — there was no way to answer “loyalty customers who haven't ordered in 30 days.”
- Manual segment definition
- Engineering ticket per segment
- Disconnected data sources
- Campaigns blocked on engineering
- Couldn't combine customer signals
- CSM productivity capped
Self-Serve Query Builder on a Unified Model
Consolidated four source systems into one unified customer model in BigQuery, defining reusable customer dimensions (loyalty status, purchase frequency, engagement, churn risk). CSMs compose segments by stacking filters through a UI; scheduled queries precompute expensive derived subsets, and views join these with raw and dimension data.
Decisions & Tradeoffs
UI-First, SQL Later
CSMs weren't comfortable with raw SQL. A drag-and-drop builder got adoption faster than 80% of use cases, at the cost of less expressive queries. After 6 months of adoption, demand for SQL grew, so a “SQL mode” toggle was added.
No Query Approvals, Trust CSMs
Gave CSMs ownership of audience quality. Risk: a bad segment definition means a bad email campaign. Mitigated with a “Preview” step showing sample customers before deploy.
Point-in-Time Snapshots, Not Real-Time
Segments computed once daily at midnight — predictable cost and performance, up to 24 hours stale. Fine for retention campaigns that don't need fresher-than-daily data; didn't over-engineer for real-time.
Rollout & the Hard Part
Started with the first CSM, iterated on dimensions and UI, then expanded to the full team once query builder patterns stabilized. Within 6 months, 1,000+ segments were active.
⚠The Hard Part: No Single Source of Truth
A segment like “high-value recent customers with abandoned carts” required stitching data from three independent stores simultaneously: MySQL (order totals, campaign exclusions), ClickHouse (behavior events, recency windows, UTM tracking), and MongoDB (active shopping carts). No query layer bridges these three — the segment builder had to enforce consistency across federated filters rather than rely on a single normalized schema. Matching storage to question sometimes means accepting complexity at the integration layer when the question spans multiple systems.
Impact
Segment request → engineering ticket → 3-5 days became CSM query → self-service UI → minutes. Customer Success handled routine segment creation without engineering intervention, and data literacy grew across the CSM team.
What I'd change today: BigQuery's scan-based billing drove reporting cost concerns — I would have moved to ClickHouse earlier, or built segmentation there directly. I'd also build lineage tracking from day one, since dependencies between segments and queries grew complex fast.