CRM funnel computation model, reached-based conversion, cohort velocity, multi-funnel homologation
Designed the CRM funnel computation standard for a multi-tenant reporting platform after identifying that adjacent-transition conversion breaks when leads skip stages (1→3). The model computes on each record's furthest stage reached, so counts stay monotonic and a skipping record is credited at every earlier stage; conversion, overall rate and drop-off derive from those reached counts. Conversion is measured by entry cohort with velocity (median time to each stage and to closed-won). Because clients run several pipelines under different business models, the design allows multiple funnels per client keyed by funnel_id with a cross-funnel Overview built on the mandatory Lead and Closed-Won anchors, keeps value-per-stage honest (shown only where deal amounts exist), and stays model-agnostic, purchases and AOV for ecommerce, SQL/opportunity/pipeline for B2B. Written as an authoritative spec (CLAUDE_V2 §26) with acceptance criteria and demonstrated in a working POC.
Compute on the furthest stage each lead reached, not on literal transitions, that is the only model that survives a stage skip.
Role: Sole data-model architect; wrote the authoritative spec and the POC
The Problem
CRM funnel reporting must work across many clients and business models (B2B, B2C ecommerce, services), each with different pipelines and stage names, and must show what a client can actually decide on, where deals stall and how much revenue each stage produces.
Leads do not move stage-by-stage: a record may jump stage 1 → stage 3, skipping stage 2. Computing conversion as adjacent transitions (1→2, then 2→3) makes the skipping record vanish and reappear, producing unreal numbers. Clients also run multiple pipelines homologated to standard stages, with some raw stages unmapped.
The model must be skip-invariant, cohort-correct, model-agnostic (purchases/AOV for ecommerce vs SQL/opportunity/pipeline for B2B), support multiple funnels per client with a cross-funnel Overview, surface value per stage only where deal_amount exists, and never leak PII, while staying consistent with an existing per-client-stage + anchor design.
Approach & Architecture
Specified a 'reached' (max-stage-reached) conversion model that is skip-invariant, a cohort-by-entry-date measurement with velocity (median time to each stage and to closed_won), a multi-funnel-per-client structure keyed by funnel_id with a cross-funnel Overview built on the lead/closed_won anchors, value-per-stage surfaced only where deal_amount exists, and funnel-agnostic KPIs. Documented as an authoritative section (§26) in CLAUDE_V2.md and demonstrated in the POC's CRM Funnel page with a working funnel/pipeline selector that filters every metric on the page.
Per record, max_stage_reached = max order index over its transition events mapping to a standard stage. Count at stage K = records with max_stage_reached ≥ K (monotonic). Conversion K→K+1 = reached(≥K+1)/reached(≥K); overall = reached(last)/reached(first); drop-off(K) = reached(≥K) − reached(≥K+1). A client can have multiple funnels (client_funnels grouping + funnel_id on stages + pipeline→funnel assignment); Overview aggregates funnels on the anchors. Value per stage = deal_amount summed for records reaching that stage, shown from the amount-bearing stage onward. Extends §24.4 (per-client funnel_stage_catalogue, lead/closed_won anchors via trigger, kind=standard/custom, migration 20260813000003_client_funnel_stages.sql).
Key Decisions & Trade-offs
Hardest Part
Reconciling skip-handling, multi-pipeline homologation, unmapped stages and model-agnostic value into one coherent, monotonic funnel that also drives a cross-funnel Overview on the mandatory anchors.
Technical Detail
client_funnels(funnel_id, client_id, name, is_default); client_funnel_stages(+funnel_id, order index, kind); pipeline→funnel assignment; fact_crm_lifecycle_events stays event-based; mart_crm_funnel computes reached counts + value + velocity per funnel per cohort.
Acceptance criteria written into §26: monotonic funnel; a skip case (1→3) counts at stage 2; Overview equals the sum of the client's funnels at lead and closed_won; value present only where deal_amount exists; velocity from timestamps; anchors hold per funnel_id against the service role; no PII; ratios from sums. verify-crm-funnel 20/20 already covers the §24.4 anchor guard.
Code
// per record: max_stage_reached = max(order index) over transition events mapping to a standard stage // count at stage K = # records with max_stage_reached >= K (monotonic) // conversion K->K+1 = reached(>=K+1) / reached(>=K) // overall = reached(last) / reached(first) // drop-off at K = reached(>=K) - reached(>=K+1)
// stage = [name, count, value(or null), medianDays]; lead + closed_won are the two mandatory columns
var CRM_DATA={ overview:{label:'All Pipelines',pipelines:[...],stages:[['Lead',6052,null,0],['Qualified',3514,null,7],['Opportunity',692,4380000,22],['Closed Won',411,1452000,44]]}, /* inbound, enterprise ... / };
// conversion, drop-off, %, overall all DERIVED from the counts arrayThe model was reasoned out interactively with Cowork/Claude (pressure-testing skip cases, multi-pipeline homologation, B2B vs B2C value), prototyped in the POC first for visual approval, then written as an authoritative spec section (§26) appended to CLAUDE_V2.md for a Claude Code agent to implement in the mart/serving layer.
Measured Results
Produced an authoritative CRM funnel model (reached-based conversion, cohort velocity, multi-funnel-per-client with a cross-funnel Overview, value-per-stage, model-agnostic outcomes) documented in CLAUDE_V2 §26 and demonstrated in a working POC page with a page-level funnel/pipeline selector that filters every metric. Not yet built in the backend; POC figures are synthetic.