Customer Segmentation Cube — Design Spec

# Customer Segmentation Cube — Design Spec

_2026-05-26 · workstation-lc · status: draft for review_

Problem

We have rich customer data (orders, web, email, offline, product affinity) and existing customer segmentations — firmographic_communities (the 60 customer communities), the emergent behavioural communities (community_memberships), and tenure cohorts. Last week we shipped segment_family_appetence (segment × product-family: online/offline lean, buy_with lift, view→cart) keyed by exactly those three segment_types.

We want to promote that into a full shrinkage cube that drives four go-to-market decisions — cross-sell, channel migration, churn/retention, and share-of-wallet/whitespace — without reinventing segmentation (reuse the existing communities) and without the "cubic" sparse-cell blow-up.

The unifying insight — one cube, four lenses

All four decisions are the same question: *how does this segment-cell compare to its peer-expected baseline?*

under-index a family vs peers → cross-sell
offline-heavy where peers index online → channel migration
spend/recency below own history or peers → churn
spend below peer-expected → whitespace / share-of-wallet

That "cell − peer-expected" deviation is exactly what core/shrinkage.value_oa computes (and norm_shrunk for distributions). So shrinkage is not just de-noising — the shrunk deviation IS the signal for all four. One cube with the union of measures; each decision is a ranked query over it.

Architecture

` PG :5433 (communities, segments, accounts, appetence) ┐ TS :5434 (ecom_order_lines, ga4, email) ├─► DuckDB (ATTACH, plan-2 pattern) ┘ │ fact: customer_interaction (1 row / order-line | touch) spine: existing community/segment membership rollup: segment × family × channel × value_tier × tenure_band shrinkage downstream: per-dim K from persistence │ segment_cube_cell (shrunk deviations: spend, lean, appetence, recency) │ 4 lens views (cross-sell / migration / churn / whitespace) │ dashboard panels (Customers tab) `

Reuse (almost everything is built)

Spine = existing firmographic / emergent / tenure segment_types (same resolvers segment_family_appetence already uses).
Measures = the appetence metrics already computed (online/offline lean, buy_with, view→cart) + spend/recency/frequency.
Engine = core/shrinkage.py (value_oa, norm_shrunk, k_from_persistence), the DuckDB cube pattern (core/cube.py), canonical channel normalizer (core/prospect_dims.py).
This cube subsumes segment_family_appetence (its segment × family table is the 2-D base slice).

Dimensions

segment_type × segment_id × product_family × channel × value_tier × tenure_band. Canonical, coarse, locked before cubing (gridiron lesson). value_tier from spend quantiles; tenure_band from tenure_cohorts; channel from the existing order-channel ('W' = web/online vs offline) normalized via core/prospect_dims.

Measures (each stored as a shrunk deviation-from-peer)

spend_oa — value_oa(cell_spend_mean, parent_spend_mean, n, K) → whitespace, churn, SoW.
channel_lean — shrunk online/offline mix (norm_shrunk) → migration.
family_appetence / buy_with — shrunk family share + lift → cross-sell.
recency / frequency — for the churn lens (engagement decay).
plus raw n_accounts, spend, penetration for sizing.

The four lenses (views, not separate builds)

| Lens | Ranked query over the cube | |---|---| | Cross-sell | families where the cell's appetence deviation is most negative (under-indexed vs peers) but the segment buys the parent world | | Channel migration | offline-lean cells whose families show positive online deviation for peer segments | | Churn | value×tenure cells with negative spend_oa + decaying recency | | Whitespace / SoW | largest negative spend_oa vs peer-expected, sized by n_accounts |

Components (each a separable unit)

1. workers/build_customer_interaction.py — customer fact at order-line/touch grain, keyed to existing segment membership + normalized dims. (Mirrors build_prospect_interactions.) 2. workers/customer_cube.py — DuckDB rollups (segment × family × channel × value × tenure) + downstream shrinkage via core/shrinkage. (Mirrors prospect_cube.) 3. core/value_tiers.py (small) — spend-quantile → value_tier, tenure → tenure_band (canonical, testable). 4. workers/segment_cube.py — writes segment_cube_cell (the shrunk-deviation cells) + per-dim K via dim_persistence (persistence measured on real multi-period order history — finally computable for customers). 5. Four lens views — SQL/queries ranking cells per decision. 6. Customers-tab panels — surface the lenses (extends the existing appetence panel rather than a new tab; customers already have a Customers tab).

Coverage / discipline

Lock canonical dims first; high-cardinality (region, fine product) collapses honestly.
Persistence→K measured here (multi-period order history exists) — not guessed.
Contagion overlay (Layer 4) is more meaningful for customers (:BOUGHT_TOGETHER) — still deferred to a later pass (droplet/OMEGA).

Testing

core/value_tiers.py — pure quantile/band unit tests.
shrinkage reused (already tested).
cube rollup → in-memory DuckDB fixture asserting a thin cell shrinks toward its parent (mirrors test_prospect_cube).
scorer integration smoke against live PG/TS.

Phasing (likely 2 plans)

Plan A: customer fact + value/tenure dims + the cube rollup + segment_cube_cell with shrinkage (the engine, extending appetence).
Plan B: the four lens views + Customers-tab surfacing.

Open items

DuckDB rollup volume — customer order-lines are 25.5M; materialize the expensive rollups, query-time GROUP BY for cheap cuts (plan-2 gotcha).
value_tier cut-points — global vs per-segment quantiles (decide in plan A).
emergent segment_type bridge (community_memberships.lead_id ↔ ecom_users.internal_lead_id) — confirm coverage (was thin for appetence).
Contagion lens — separate, later.

Credits

Same lineage as the prospect cube: reframe + shrinkage from lab-ovh (#1412) / gridiron (#1416, #1418). This spec extends segment_family_appetence (shipped 2026-05-22).