Mutable CRM fields erase funnel history. This PASMO guide provides a lead-event schema, a status normalization matrix, and ingestion rules for duplicate and late webhooks.
A lead is marked Contacted on Monday, Qualified on Tuesday, and corrected back to New on Wednesday. A dashboard that reads only the CRM's current stage sees one row marked New. It cannot show the attempted contact, time to qualification, or rollback. That is how CRM funnel reporting becomes tidy and wrong at the same time.
Exporting more CRM fields will not recover the lost sequence. A useful reporting layer needs a small event table that records business activity as it happens, keeps provider-specific detail, and gives reports stable definitions. Below is a first-version schema, a normalization matrix, and the ingestion logic that keeps duplicate or late webhooks from distorting the funnel.
A CRM contact is designed to answer a current-state question: Who owns this lead now? What is the current pipeline stage? When is the next task due? Those fields are useful for selling, but they overwrite history. Once a stage changes, the previous value may disappear or survive only in a vendor-specific audit log that is difficult to join with calls, messages, forms, and experiments.
Funnel analysis asks different questions. How many leads received an outbound attempt? Which ones answered? How long did qualification take? Did variant A or B produce more booked appointments? Those answers depend on sequence, timestamps, and consistent event meaning.
This borrows one idea from Microsoft's Event Sourcing pattern: keep an append-only history. An SMB reporting layer does not need the full pattern. Microsoft notes that event sourcing adds complexity and is not justified for most systems. In PASMO's first version, the CRM stays the operational source of truth and one append-oriented table handles measurement.
The event table should describe an observable business occurrence instead of mirroring every CRM field. Google Analytics uses a comparable concept for digital behavior: an event name identifies the interaction, while parameters add context for reporting. Its official event guidance distinguishes standardized and custom events rather than treating the current page or user record as the whole history.
For a sales funnel, this is a practical PostgreSQL starting point:
CREATE TABLE lead_events (
id bigserial PRIMARY KEY,
lead_id text NOT NULL,
occurred_at timestamptz NOT NULL,
received_at timestamptz NOT NULL DEFAULT now(),
event_type text NOT NULL,
channel text NOT NULL,
direction text,
status text,
provider text NOT NULL,
provider_event_id text,
idempotency_key text NOT NULL UNIQUE,
experiment text,
variant text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);occurred_at tells you when the business activity happened. received_at tells you when your pipeline learned about it. Keeping both exposes delayed webhooks instead of quietly moving activity into the wrong reporting window. provider_event_id supports troubleshooting, while your own idempotency_key protects the table when a provider retries the same delivery.
Keep frequently filtered dimensions as columns. Put provider-specific details such as raw subtype, error text, call summary, or payload version in metadata. If every dashboard query must dig through JSON for channel, status, or experiment variant, the schema is too loose.
Provider status labels are transport vocabulary, not funnel definitions. A message marked sent was accepted for delivery; it was not necessarily delivered or read. A call marked completed may have reached a person, voicemail, or an automated system. Mapping all of those values to success produces an attractive dashboard with weak business meaning.
Create a controlled normalization matrix before building charts:
| Raw activity | Normalized event | Normalized status | Counts as funnel progress? |
|---|---|---|---|
| Outbound SMS accepted or queued | sms_sent | pending | No |
| Outbound SMS delivery confirmed | sms_delivered | delivered | Delivery only |
| Inbound SMS received | sms_received | success | Response |
| Outbound call ringing | call_started | ringing | Attempt only |
| Live person answered | call_answered | answered | Conversation |
| Call reached voicemail | call_completed | voicemail | Attempt, not conversation |
That final column forces a decision teams often avoid: which technical milestones actually mean progress? PASMO recommends separating transport milestones from business milestones. "Message delivered" and "lead replied" belong in different funnel steps. The same goes for "call connected" and "appointment booked." Otherwise, a change in provider behavior can look like a change in sales performance.
A duplicate is the same fact delivered more than once. A late update is a new fact about earlier activity. Treating both as duplicates loses information; treating both as new events inflates counts.
Build the idempotency key from stable inputs supplied by the provider, such as provider name, provider event ID, event type, and the specific status transition. Put a unique constraint on that key. PostgreSQL's INSERT ... ON CONFLICT can then reject an exact repeat or route it into a deliberate update path.
A blanket ON CONFLICT DO UPDATE rule creates another problem. If "queued" later becomes "delivered," overwriting the original row destroys the time spent between those states. Append the delivered event with its own key. Reserve updates for corrections to the same event record, and document which fields may change.
Late events should keep their original occurred_at and current received_at. Reports can attribute the activity to when it happened while a monitoring query flags ingestion lag. This distinction is especially important when a provider sends call summaries or final delivery states minutes after the initial webhook.
Connecting every chart directly to raw webhooks makes each dashboard responsible for interpreting provider data. Build a view that produces one reporting row per lead and calculates stable milestones from the event table: first outbound attempt, first delivered message, first response, first live conversation, qualification time, booking time, experiment assignment, and opt-out state.
The raw event table remains narrow and append-oriented. The view can evolve when the business changes its funnel definition. If leadership decides that voicemail should no longer count as contact, you update the reporting logic instead of rewriting historical events.
Start with a small acceptance set before trusting the dashboard:
For each case, write the expected events and expected reporting row before running the pipeline. If a routing issue appears after the event model is stable, PASMO's guide to auditing lead-routing rules is a useful next step.
If your funnel totals change depending on which CRM export someone opened, start with five real leads and write their expected event histories by hand. The gaps will show you which provider statuses, timestamps, and business rules the schema still needs. PASMO can then turn that contract into an ingestion pipeline and reporting view your sales team can inspect.