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. The hardest product I built.

1,000+ segments4 source systemsbuilt on the platform ↑

BigQuery · Google Datastream · MySQL · Materialised views · Query builder UI

My Role

Architecture & design — designed unified data model, query composition system, and materialized view strategy
Core implementation — built query builder, materialization pipeline, customer data model
Product partnership — worked with CSM team to understand audience composition needs, iterated on UI
Production ownership — monitored BigQuery costs, query performance, materialization latency

Context

Customer Success team wants to run targeted campaigns ("remind shoppers who almost bought", "reward loyal customers", "re-engage"). Customer signals live in four systems: click-tracking, commerce, orders, loyalty. Currently CSMs file engineering tickets for each segment. Blocks campaigns and doesn't scale.

Problem

Segments are built ad-hoc for campaigns, manually combined by engineers. No way for CSMs to compose complex filters without SQL. Each new segment requires engineering work, adding 3-5 days of latency. Customer loyalty program can't be queried against order data. No way to answer "loyalty customers who haven't ordered in 30 days".

BEFORE
  • Manual segment definition
  • Engineering ticket per segment
  • Disconnected data sources
WHY IT MATTERED
  • Campaigns blocked on engineering
  • Can't combine customer signals
  • CSM productivity capped

Architecture

Consolidates four source systems into one unified customer model in BigQuery. Defines reusable customer dimensions (loyalty status, purchase frequency, engagement, churn risk). CSMs compose segments by stacking filters through a UI. Query builder generates SQL, materialized views cache results for exports.

CLICK-TRACKING · COMMERCE · ORDERS · LOYALTY
                ↓
UNIFIED CUSTOMER MODEL (BigQuery)
                ↓
DIMENSIONS (loyalty_status, purchase_freq, engagement)
                ↓
QUERY BUILDER (CSM UI)
                ↓
MATERIALIZED VIEWS (daily cache)
                ↓
CAMPAIGNS

Key Decisions

  1. Consolidated, not federated
    Costs: second data copy and continuous replication. Benefit: one query language, genuinely possible joins across sources.
  2. Normalize at analytics layer, not ingest
    Mirror of platform's ingest decision. Same modelling work, moved to where mistakes are cheap to fix.
  3. Precomputed daily, not live
    Right for campaign cadence. Recommendations needed live serving; segmentation didn't.

Execution

Rollout

Started with first CSM, got feedback, iterated on dimensions and UI. Expanded to full team once query builder patterns stabilized. Within 6 months, 1,000+ segments active.

The Hard Part

Unified customer model required consolidating conflicting definitions. What's "churn"? 30 days without order? 60? Cross-platform or per-store? Built dimension tables with multiple definitions, let CSMs pick. Learned CSMs needed explainability: why is this customer in this segment? Added audit trail.

Impact

1,000+ SEGMENTS
Built by CSMs without engineering involvement

Why It Mattered

  • CSMs unblocked. Campaigns launched in days, not weeks.
  • Every marketing campaign ran through segmentation.
  • Data literacy grew within CSM team.

Scale

1,000+ active segments · 4 source systems unified · zero engineering involvement after launch · CSMs building segments independently

What I'd Change Today

What I'd Preserve

The consolidated data model and query builder pattern were right. CSMs needed self-service.

What I'd Change

BigQuery's scan-based billing drove reporting cost concerns. Would have made the move to ClickHouse earlier, or built segmentation there directly. Also would have built lineage tracking from day one (which segments depend on which queries), as dependencies grew complex.