Product Buying-Intelligence Layer — Design

# Product Buying-Intelligence Layer — Design

Date: 2026-06-11 Status: Approved (brainstorm) — pending spec review Repo: lead-contagion-project (Lyréco tenant), with a hybrid prove-then-extract path into ppi-core.

Goal

Give sales/marketing a buying profile per customer: each week, the Top-10 products bought — at individual-user grain rolling up to company, ranked by purchase frequency and units, stored as weekly snapshots so the week-over-week shift is visible. Add a basket layer that registers every ordered line (product, qty, timestamp) and its cart grouping into the timeline for cross-product analysis, and a firmographic/behavioral cluster overlay ("which cluster buys these") under two lenses.

Decisions locked in brainstorm

| Question | Decision | |---|---| | Profile grain | Both — individual user (ecom_user_id), rolled up to company (soldto_number) | | Ranking measure | Order frequency + units (quantity) — both stored & ranked. Spend stored, not ranked. | | Window / cadence | Weekly snapshot of a trailing window (default 13 weeks); appended → time-series of Top-10 lists | | Cluster overlay | Both lenses — emergent graph communities ("behaves alike") AND firmographic look-alike / BvD-SIC clusters ("is alike") | | Build approach | Hybrid — Lyréco-native now, written config-shaped; lift the generalizable compute into ppi-core once proven | | Source table | ecom_order_lines (orders) — order_number = the cart; frequency = distinct orders. Not invoices (they split/merge, no clean basket). |

Architecture — three layers

Layer 2 — Basket events (substrate, built first)

Reconciled with reality (2026-06-11 dependency dig): ecom_order_lines is already a TimescaleDB hypertable carrying soldto_number, ecom_user_id, product_reference, quantity, sales_amount, order_date, order_number (first-class — the cart id), order_channel_code, source_country. And compute_timeline already emits per-line ORDER_PLACED events (PK includes order_line_number; carries product_reference + quantity + amount, with order_number inside event_details). So a separate purchase_lines table was dropped as pure duplication.

What we build for layer 2: 1. Promote order_id to a first-class column on customer_timeline (lifted from event_details->>'order_number' on ORDER_PLACED rows) + index, so baskets regroup with a fast GROUP BY order_id instead of JSONB extraction. Populate it in compute_timeline; backfill existing ORDER_PLACED rows. 2. v_order_baskets — a thin view over ORDER_PLACED grouping by order_id, the basket substrate for cross-product / co-purchase analysis (complements existing product_co_occurrence / Neo4j :BOUGHT_TOGETHER).

Layer 1 — Weekly buying profile

buying_profile_weekly: `(scope_type ['user'|'company'], scope_id, source_country, snapshot_week, product_reference, orders_count, units_qty, sales_amount, rank_freq, rank_qty, window_weeks)`.

A weekly worker (invoked from daily_refresh, gated to fire once per ISO week) recomputes the Top-N (default 10) over a trailing window_weeks window (default 13) and appends the snapshot keyed by snapshot_week. Ranks by both orders_count (frequency) and units_qty; deterministic tie-break (product_reference asc) so snapshots are reproducible.

Layer 3 — Cluster overlay (dual lens)

cluster_buying_profile_weekly: `(cluster_source ['emergent'|'firmographic'], cluster_id, source_country, snapshot_week, product_reference, orders_count, units_qty, rank_freq, rank_qty, n_accounts)`.

Rolls company-level purchases up via an account→cluster map, run under both lenses. The rollup accepts any mapping table (the generalization that makes it liftable):

Firmographic — account_firmographic_community (account_number →

community_id, KMeans on BvD-SIC firmographic features, built by workers/community_match_soft.py). Populated today — wired in Plan 1.

Behavioral — account-grain Louvain communities. Does not exist yet

(Neo4j :EmergentCommunity nodes are empty); stood up in Plan 2, then plugged into this same rollup as a second mapping.

Decisions 2026-06-11: order_id promoted to a timeline column (not a new purchase_lines table); behavioral communities built now (Plan 2) so both lenses ship. Build split into two plans, each independently shippable.

Consumption

Dashboard script-backed report (logs/reports/*.json, `refresh_mode: script, PYTHONIOENCODING=utf-8`): weekly Top-10 per account/user and per cluster, with week-over-week shift indicators. XLSX export alongside, following export_wise_top10_by_family.py / refresh_prospect_intelligence_hub.py.

Hybrid extraction path

Layer-1 ranking/window logic and layer-3 rollup are written as clean, config-shaped units (pure functions over a query result + a spec object), so once proven on Lyréco data they lift into ppi-core as:

a buying_profile engine (top-N over a trailing window), and
a cluster_rollup overlay (top-N per cluster given an account→cluster map),

each gated by a parity golden test — the same path funnel/cohort took. Co-purchase maps onto the existing contagion / co-occurrence engine; not re-built here.

Testing

Hermetic unit tests on ranking, trailing-window selection, and tie-break

(fake row sets — no DB).

Parity gate: worker output == an independent direct SQL query over one

closed week, per the established discipline.

Data-flow & platform notes

Cross-DB: ecom_order_lines / customer_timeline / purchase_lines live

in TimescaleDB (:5434); ecom_products, taxonomy, cluster maps live in Postgres (:5433). No cross-DB joins — 2-step bridge (WHERE col = ANY(%s)).

Timeline volume: adding ~25M per-line PRODUCT_PURCHASED events to a

173M-row hypertable. Appends are safe; do NOT mutate columns on compressed chunks (cf. the decompression-cap incident — backlog #115).

Cadence: weekly snapshot worker is gated inside daily_refresh (runs only

on the first refresh of each ISO week) to avoid daily recompute.

Open dependencies (resolve at plan time)

1. Emergent community table — confirm exact Postgres name + freshness/refresh cadence of the account→community_id assignment. 2. Firmographic cluster table — confirm the prospect_lookalike account→ cluster map name and that it is current. 3. purchase_lines vs reusing ecom_order_lines — decide whether the substrate is a distinct hypertable or a thin view/projection over ecom_order_lines (it already holds the same columns); the timeline-event emission is needed either way.

YAGNI cuts

No spend ranking; no per-calendar-week buckets (too sparse for B2B); no new clustering model (reuse existing); no real-time — weekly batch only.