Lead-to-Opportunity CRM Pipeline

A design reference for the pre-sales funnel that qualifies an inbound party and hands off a priced pursuit to the sell side

1. Requirements

1.1 Functional requirements

• Capture an unqualified inbound party — a person, an organization, or both — as a single record from any acquisition surface (manual entry, an inbound email thread, a scheduled-appointment request, a “contact us” submission, a tagged campaign link) without requiring a paying customer to exist yet.
• Allow, but do not require, grouping several such inbound contacts under one account-level record before a sales pursuit is opened, for when more than one named person belongs to the same target organization.
• Enforce a configurable email-uniqueness rule across inbound contacts, and require every one to carry either a person’s name or an organization’s name.
• Track a monetary, multi-line sales pursuit against a party that may be the unqualified contact itself, the account-level grouping, or an already-billing customer — using one pursuit record type regardless of which of the three the party is.
• Represent pipeline position with a manager-configurable, free-text stage label plus an independently editable win-probability percentage; the two are not derived from one another.
• Fold a sales pursuit into a priced, sell-side document once its owner is ready to price it, without re-entering party or line-item data already captured on the pursuit.
• Feed the outcome of that priced document (ordered, cancelled, or lost) back into the pursuit so its status reflects reality without a second manual edit.
• Record why a pursuit was lost as a structured, taggable detail — named reasons and named competitors — rather than free text only.
• Support secondary acquisition and engagement surfaces (outreach campaigns, scheduled meetings, standing contracts, market-segment labels) that attach to or originate the same core records without each needing its own qualification funnel.

1.2 Non-functional requirements

• Idempotent linkage: attaching an already-linked contact to an account-level grouping a second time must not create a duplicate row for it there.
• Traceable history: every automatic status change writes a timeline comment, so pipeline movement is visible without a separate audit log.
• Low administrative overhead: pipeline stages, lost-reason taxonomies, and campaign schedules are simple label masters and settings an administrator edits directly, not a workflow engine that needs code changes to add a stage.
• Graceful degradation of the optional aggregation layer: a pursuit must work correctly whether or not its party was ever grouped under an account-level record.

1.3 Constraints

• One pursuit record type serves three distinct kinds of originating party (unqualified contact, account-level grouping, or existing customer) through a dynamically-typed reference field, rather than three separate downstream record types.
• The account-level aggregation layer sits beside the core funnel, not underneath it structurally — it carries no status field of its own and nothing in the derived-status machinery described below reads it.
• All pipeline configuration is edited through the same administrative surface as the rest of the system; there is no dedicated CRM configuration app.

2. High-Level Design

2.1 Component diagram

2.2 Data flow — from inbound contact to a priced pursuit

This is a short, branching procedure rather than a converging graph, so it is written as annotated steps:

1. A contact arrives through any acquisition surface. Each surface resolves an existing Lead by email before creating a new one, so a repeat inquiry from the same address does not spawn a duplicate funnel entry.
2. Qualification proceeds on the Lead itself. Its status field mixes manually chosen values (a plain “in progress” label) with values silently recomputed on every save from live existence checks — §3.2. No separate qualification record exists.
3. Grouping under a Prospect is optional and manual. A user can attach a Lead to a Prospect (or create one from it) at any point; nothing else in the funnel requires this, and a pursuit can be opened straight from an ungrouped Lead.
4. A pursuit is opened either from a Lead or a Prospect. The conversion copies contact details, and — for any target field left blank that shares a name with a source field — a generic field-mirroring step copies the value across without a hand-written mapping table for every field.
5. The pursuit accumulates optional priced lines, a stage label, and a win-probability percentage, all independently editable. Neither field constrains the other.
6. The pursuit is folded into a quotation once its owner is ready to price it formally — the conversion boundary this document stops at; quotation and sales-order mechanics beyond it belong to the sell-side pipeline.

3. Deep Dive

3.1 Data model

Lead The unqualified entry point. Required to carry a person’s name or an organization’s name. Status is a fixed list: Lead, Open, Replied, Interested, Do Not Contact as manually-chosen working states, plus Opportunity, Quotation, Lost Quotation, Converted as states recomputed on every save (§3.2). A duplicate-email guard is configurable off for organizations that intentionally allow repeat inquiries.

Prospect An account-level grouping with no status field at all. It holds two child tables — one row per attached Lead, one per Opportunity raised against it — kept as a denormalized mirror, not a live join: whenever a grouped Lead’s name, email, or status changes, or a linked Opportunity’s amount, stage, owner, probability, or close date changes, the owning save path writes the corresponding Prospect child row directly. A Prospect with no rows removes itself when its last Lead is unlinked.

Opportunity The pursuit itself. Its party reference is a dynamically-typed link that can point at a Lead, a Prospect, or an existing customer — one record type serves all three; only how display name and party lookups resolve differs by which is set. It carries an independent stage label, a win-probability percentage (no code path ties one to the other), optional priced line items, a lost-reasons multi-select, and a competitors multi-select meaningful only once the pursuit is marked lost.

Sales Stage A bare, manager-editable label list — a name and nothing else. No ordering, weighting, or probability mapping is attached to a stage anywhere in the pipeline; it is purely descriptive text.

Conversion boundary — Opportunity to Quotation The hand-off is a field-mapped copy, not a shared record. Which field carries the backlink depends on whether the Opportunity had priced line items: an item-less Opportunity is referenced from a single header field on the Quotation; an Opportunity with priced lines is instead referenced per line, since each Quotation line item carries its own reference back to the Opportunity it came from — the header field is left unset in that case. Both shapes exist so an informal, item-less pursuit and a fully priced one can share one conversion path.

3.2 Algorithm — derived status resolution

Lead and Opportunity share the same generic status-derivation engine used elsewhere in the system for other transactional records: a status is an ordered list of (candidate status, existence check) pairs, evaluated in reverse declared order so the last-declared candidate is checked first. The first check that passes wins; if none pass, the record keeps its current status.

The two records use this engine very differently:

• Lead recomputes on every save. Its validation step unconditionally re-runs the check list — Converted (a customer now exists) outranks Quotation (an active, non-lost quotation exists) outranks Opportunity (a non-lost opportunity exists) outranks Lost Quotation (a lost quotation exists and no active one does). A Lead can therefore never show “Opportunity” once a quotation has been raised against it, even if that opportunity is still open.
• Opportunity recomputes only when something outside it says so. Its own save path never calls the resolver; it is instead recomputed by name from specific trigger points — a linked quotation being submitted, cancelled, or declared lost — each seeding a candidate status that the same reverse-priority check confirms or overrides. Two other write paths bypass the resolver and assign status directly: a scheduled sweep closing pursuits left in “Replied” past a configurable number of days, and a bulk status-change action:

Declaring an Opportunity lost directly is blocked while any active quotation still exists against it — the guard exists specifically so a pursuit cannot be marked lost out from under a quotation that is still live.

3.3 Conversion contract (illustrative)

Every hand-off between funnel stages goes through the same shape of call: read one record, produce a draft of the target type, run a set_missing_values hook specific to the pair.

make_opportunity(Lead | Prospect) -> draft Opportunity
  copies contact fields; sets the dynamic party reference and its type
  to whichever of the two the call came from; leaves stage and
  probability at their defaults for the owner to fill in

make_quotation(Opportunity) -> draft Quotation
  copies party reference and contact details; re-prices header taxes
  and totals for the target company/currency; links back via the
  header field or per-line, depending on whether the source pursuit
  carried priced lines (§3.1)

make_customer(Lead | Opportunity | Prospect) -> draft Customer
  independent of the quotation path — can run directly from any of
  the three record types once a party is ready to be billed

make_request_for_quotation(Opportunity) -> draft sourcing request
make_supplier_quotation(Opportunity) -> draft supplier-side quotation
  buy-side siblings consuming the same priced line items, for
  pursuits that need to be sourced before they can be quoted

3.4 Error handling

• Duplicate contacts: the email-uniqueness check on Lead runs a direct existence lookup at validation time and throws with the names of the conflicting records; it is configurable off, not removable per-record.
• Missing identity: a Lead with neither a person’s name nor an organization’s name is rejected before any other validation runs.
• Premature loss: both the Opportunity-side and Quotation-side “declare lost” actions refuse to proceed while an active (non-lost, non-closed) quotation still exists for that pursuit — the loss action and the conversion action are mutually exclusive gates on the same fact.
• Cross-record consistency on save: the account-level grouping’s mirrored child rows are looked up and either updated in place or appended, never duplicated, each time the record that owns the canonical data is saved.

3.5 Satellite mechanisms (grouped)

Several supporting record families attach to the core funnel without running their own qualification logic. Campaigns and email campaigns schedule templated outreach against a Lead, a contact, or an email group, deriving their own progress status (Scheduled / In Progress / Completed / Unsubscribed) from elapsed days rather than delivery confirmation. Appointment booking creates or matches a Lead by email on a meeting request, enforces slot-capacity and holiday-calendar rules with a locking read so two concurrent bookings cannot both pass the same capacity check, and auto-assigns the agent who owns the party’s most recent Opportunity when one exists. Competitors and lost reasons are pure tagging vocabularies consumed only when a pursuit is marked lost. Contracts run their own signed/active/inactive lifecycle and fulfilment checklist independently, optionally pointing back at a quotation or other sell-side document rather than at the pursuit itself. Market segments are a bare classification label attached to Leads, Prospects, and Opportunities alike, with no logic beyond the label.

4. Scale and Reliability

• Load pattern: acquisition-surface writes (inbound email, appointment requests, campaign sends) are the frequent, bursty path; pursuit creation and conversion are comparatively rare, deliberate actions by a sales user.
• Duplicate-email lookup cost: the Lead uniqueness check is a direct filtered lookup, not an enforced database-level constraint — correct at moderate volume, but an extra query per save rather than a guarantee the storage layer gives for free.
• Account-grouping mirror cost: every status- or amount-relevant change on a grouped Lead or Opportunity does a lookup-then-write against its Prospect’s child table. This scales with how many pursuits are grouped, not with total pursuit volume, so it stays cheap unless grouping becomes the default rather than the exception.
• Scheduled sweeps: the stale-“Replied”-opportunity closer and the daily campaign-status refresh both iterate their full candidate set once per run — fine at moderate record counts, a batching candidate if either set grows very large.
• Booking concurrency: appointment slot capacity is protected by a locking read at booking time, so it degrades safely (rejecting a booking) rather than corrupting state under concurrent requests for the same window.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Unify the two Opportunity status-write paths. The scheduled stale-pursuit closer and the bulk status-change action both write status directly, bypassing the existence-check resolver that every quotation-triggered change goes through. As volume grows, these are the most likely source of a pursuit sitting in a status its own linked quotations no longer justify.
• Promote the duplicate-email guard to a real constraint once contact volume makes a per-save lookup query a bottleneck, or once the guarantee needs to hold even with the configurable check off.
• Reconsider the dual conversion-boundary linkage if reporting or bulk operations over “all quotations descended from a pursuit” become common — a single, always-populated backlink would remove the need to check two places.
• Attach a lightweight stage-to-probability convention (a soft default, not an enforced one) if pipeline forecasting starts to matter — probability is currently trusted entirely to manual entry with no relationship to the stage label beside it.
• Batch the scheduled sweeps (stale-opportunity closer, campaign-status refresh, expired-appointment cleanup) if any candidate set grows large enough that an unbatched pass becomes noticeable — none of them currently paginate.