CRM Funnel Attribution

A Google Ads click happens in January. The lead fills out a form the same day. The deal closes in June. Five months separate the marketing event from the revenue event — and every decision made in between is based on incomplete data.

Most marketing teams optimize on the fast signal: leads, form submissions, demo requests. These arrive within hours of the click. They are measurable, attributable, and available at campaign-level granularity. The problem is that lead volume does not predict revenue.

The channels that produce the cheapest leads are often the channels that produce the worst pipeline. Display and broad-match search generate high lead volume at low cost. Sales works those leads for months and closes almost none. Meanwhile, a niche LinkedIn campaign produces fewer leads at higher CPL — but those leads close at 3x the rate with 2x the deal size.

You cannot see this without connecting CRM data to attribution. Lead volume alone tells you which channels are cheapest. Revenue data tells you which channels are most profitable. These are different questions with different answers.

Why you cannot just optimize on Closed/Won

The obvious response: skip the leads, optimize directly on revenue. This does not work either. Closed/Won data has a 3–6 month lag. By the time you see which January campaigns produced revenue, it is July. The campaigns have changed, the bids have shifted, and the feedback is too stale for daily optimization.

Closed/Won is also sparse. A B2B company might close 20–50 deals per month. That is not enough signal for campaign-level optimization — too few conversions to distinguish real performance differences from noise.

A B2B funnel is not one conversion. It is a sequence of stages, each with different timing, different volumes, and different signal quality:

• Lead — form submission or inbound inquiry. Arrives within hours of the click. High volume, low signal.
• MQL — Marketing Qualified Lead, meets a scoring threshold. Arrives within days. Moderate volume, moderate signal.
• HandRaiser — the lead explicitly requested sales contact (demo request, pricing page). Immediate qualification. Lower volume, high signal.
• Opportunity — enters the sales pipeline with a deal amount. Arrives weeks to months later. Low volume, high signal.
• Closed/Won — signed deal. Arrives months later. Lowest volume, highest signal.

Each stage is defined as a separate conversion in SegmentStream, each with its own custom SQL querying CRM tables in BigQuery. Every stage gets attributed independently — first-click, last significant click, and ML attribution run simultaneously across all stages.

Channel rankings flip at every stage

The cohort table reveals what lead-level reporting hides. Consider a January cohort of leads, viewed six months later across the full funnel. Google Ads — Brand Search leads the table at the Lead stage: highest volume, lowest cost. By Opportunity, LinkedIn has overtaken it. By Closed/Won, the ranking has flipped entirely.

HandRaiser is the stage that separates signal from noise. Leads that explicitly request sales contact convert to Opportunity at roughly 57%, compared to roughly 38% for non-HandRaiser MQLs. A channel that produces fewer leads but a higher share of HandRaisers is almost certainly a better investment — you just cannot see it from leads alone.

Multi-stage attribution makes these trade-offs visible. You can see which channels produce volume (leads), which produce intent (HandRaisers), and which produce revenue (Closed/Won) — all attributed to the same marketing touchpoints, in the same reports.

Every B2B attribution system faces the same tradeoff. A click happens in January. The deal closes in June. Where does attribution anchor its lookback window — on the click date or the close date?

• Anchor on close date (June) — a standard 90-day window looks back to March. The January click is outside the window. Attribution fails silently — the deal appears unattributed, or worse, gets credited to whatever retargeting campaign touched the lead in April.
• Anchor on lead date (January) — the 90-day window finds the click. But now reports show $50K of revenue in January that actually closed in June. The CFO sees phantom revenue. Monthly reporting breaks.

Most systems force you to pick one and accept the distortion. Extend the window to 180 days and you catch more clicks — but misattribute them to the wrong time period. Shorten the window and you get clean timing — but miss the clicks that actually drove the pipeline.

Decoupling conversion time from attribution time

SegmentStream separates the two timestamps that traditional systems conflate. Each funnel conversion has two dates:

• Conversion time — when the business event happened. The deal closed on June 15. Reports, exports, and revenue dating use this timestamp. It matches the CRM and the P&L.
• Attribution time — when to anchor the lookback window. The lead was created on January 10. The attribution engine uses this timestamp to find the marketing session that brought the lead — within a standard 90-day window.

In the custom conversion SQL, the two dates map to two fields:

• created (maps to conversion_time) — the close date, June 15
• attribution_time — the lead creation date, January 10

Result: the $50K Closed/Won appears in June reports (correct revenue timing), attributed to the Google Ads click from December (correct marketing credit). No window compromise. No phantom revenue.

Automatic session lookback

The pipeline reads the actual attribution_time values in each processing batch and extends its session loading range accordingly. If a June Closed/Won has a lead from January, the pipeline loads sessions from October (90 days before January) through June. A 2-week MQL and a 6-month enterprise deal are handled by the same system, with no manual configuration.

Two report perspectives

Reports expose both timestamps:

• By conversion time — revenue appears when the deal closed. Matches the P&L. Use this for executive reporting and finance reconciliation.
• By click time — revenue appears when the marketing click happened. Shows which month's campaigns produced revenue. Use this for campaign optimization and budget planning.

Both perspectives come from the same underlying data. No duplication, no reconciliation needed.

CRM data is not static. Deal amounts change. Stages revert. Records are updated weeks or months after creation. Attribution that treats CRM data as write-once will drift out of sync with reality.

Three scenarios happen regularly:

• Value update — a $50K deal is renegotiated to $75K. The conversion record needs the updated amount, and every report that includes this deal needs to reflect the new value.
• Stage reversion — a Closed/Won deal reverts to Closed/Lost. The conversion should disappear from reports entirely — it is no longer revenue.
• Refund or credit — post-close adjustments reduce the deal value to zero. Functionally identical to a reversion — the conversion value drops to zero and the record is removed.

Adjustment windows

Each funnel conversion has an adjustment window — the number of days the system re-queries CRM data to catch retroactive changes. Typical values:

• MQL / HandRaiser — 14-day window. Stage qualification rarely changes after two weeks.
• Opportunity — 30-day window. Deal amounts and stages shift during active negotiation.
• Closed/Won — 180-day window. Enterprise deals can be renegotiated, clawed back, or credited months after close.

When the adjustment window is active, the custom conversion SQL re-runs for the extended date range. A MERGE operation handles three cases:

• New record in source — insert. A new deal was created or backdated.
• Value changed — update. The deal amount or qualification flag changed.
• Record disappeared from source — delete. The deal reverted, was disqualified, or was removed from the CRM.

Attribution automatically re-runs for the affected date range. Reports always reflect the current CRM state — not a stale snapshot from the day the deal first appeared.

Gross margin attribution

Some businesses attribute gross margin instead of revenue. The conversion SQL returns margin as the value field instead of deal amount. The same adjustment window and MERGE pattern apply — when margins are recalculated, the updated value propagates through attribution automatically.

CRM funnel data is not just for backward-looking reports. It is the training signal that makes predictive models accurate.

Two capabilities in the SegmentStream Measurement Engine depend directly on Closed/Won data as their ground truth:

Lead Scoring

Lead Scoring predicts which leads will convert and what the deal will be worth. The ML pipeline trains two models: a classification model (will this lead convert?) and a regression model (what is the expected deal value?). Both need labeled training data — leads with known outcomes.

That labeled data comes from CRM funnel conversions. The leadScoringTargetConversionsSQL queries the same CRM tables that produce Opportunity and Closed/Won conversions. Without these funnel stages in the system, the ML pipeline has no ground truth to train on — it would learn from noise instead of outcomes.

The trained model scores new leads daily and generates synthetic conversions that feed back into attribution. Early-stage leads get a predicted value before the deal closes — giving campaigns credit for pipeline quality, not just lead volume.

LTV Scoring

LTV Scoring predicts the lifetime value of a customer at acquisition. The model needs historical revenue data per customer to learn the relationship between early signals and long-term value. Closed/Won conversions — with their accurate deal amounts and close dates — provide that foundation.

Without CRM revenue data, LTV predictions are based on first-purchase value alone. With it, the model learns which lead characteristics (source channel, company size, industry) predict higher lifetime revenue — and that signal flows into budget optimization.

Attribution needs a link between the CRM record and the website session that brought the lead. Without it, the deal is visible but unattributed — revenue exists in the CRM but no marketing channel gets credit.

The Identity Graph handles this through deterministic matching across four signal types. Each signal has different coverage and reliability. CRM conversions use all four, layered from strongest to broadest.

Anonymous ID

The strongest signal. SegmentStream's SDK sets an anonymous_id cookie on every website visitor. When a lead fills out a form, a hidden field captures the anonymous_id and writes it to the CRM record. The custom conversion SQL passes it as client_id, and attribution links the deal directly to the session that produced it.

This is a deterministic one-to-one match. No inference, no probability. The CRM record carries the exact cookie value from the session that created it.

Email Hash

When the CRM has the lead's email address, the system generates a SHA-256 hash and matches it against email_hash values in the identity graph. If the same email was submitted on the website — newsletter signup, checkout, account creation — the anonymous sessions from that email are linked to the CRM lead.

Email hash also enables cross-device stitching. Marketing emails include the hash in URL parameters (ehash). When the recipient clicks on a different device, the SDK captures the hash and links the new session to the existing identity.

User ID

CRM systems that sync a contact or account identifier — Salesforce's ConvertedContactId, HubSpot's contact ID — can match against logged-in website sessions where the same user_id was set. This covers returning visitors who are already authenticated.

IP and Timing

When no direct identifier exists, the identity graph falls back to exact IP address matching within a bounded time window. If the CRM lead's IP address matches a website session's IP within a 3-day window, the sessions are linked. This is a deterministic match on an exact key with a time constraint — not a statistical guess.

IP matching has known limitations. Shared office networks produce false positives. Mobile carrier IPs rotate. But as a fallback when stronger signals are unavailable, it recovers sessions that would otherwise go unmatched.

The stitching order matters

The identity graph runs Union-Find (connected components) across all signals daily. A single lead may connect to multiple anonymous sessions across devices — the form submission on desktop, the initial ad click on mobile, the email click on a tablet. Attribution sees the full cross-device journey, not just the session where the form was submitted.

For implementation details on how the identity graph resolves these signals, see the Identity Graph page.

Everything described above runs inside the SegmentStream MCP server. Two queries demonstrate the full pipeline: a Closed/Won attribution report by channel, and an individual user journey showing the gap between the original click and the revenue event.

Closed/Won attribution by channel

get_report_table returns a standard attribution report filtered to the Closed/Won conversion. The output shows cost, conversions, revenue, and ROAS by channel — with attribution anchored on lead creation date, revenue dated to close date. You see which channels produced pipeline that actually closed, not just pipeline that entered.

Compare this with the same report filtered to Lead or MQL. The channel rankings shift — often dramatically. Channels with the lowest cost-per-lead may have the highest cost-per-closed-deal, and vice versa. The multi-stage view makes these trade-offs visible in a single query.

User journey: 5-month click-to-close gap

get_user_journey traces an individual lead's path from first click to Closed/Won. The output shows every session, every touchpoint, and every funnel stage transition — with the identity signals that connected them.

A typical B2B journey: Google Ads click in January (anonymous session), form submission in January (identity stitched via anonymous_id), MQL in February, Opportunity in March, Closed/Won in June at $50K. Five months, multiple devices, one attributed journey. The December click gets credit for the June revenue.

What changes in practice

Teams that connect CRM funnel data to attribution typically see three shifts:

• Budget moves toward quality — channels that produce expensive leads but high close rates gain budget. Channels that produce cheap leads with low close rates lose it. The reallocation is data-driven, not intuition.
• Quarterly reconciliation replaces monthly guessing — Closed/Won data validates (or contradicts) the lead-based optimization decisions made months earlier. Teams stop arguing about channel quality and start measuring it.
• Predictive models get calibrated — Lead Scoring and LTV Scoring produce meaningful predictions because they train on real revenue outcomes, not proxy metrics.