The business question
Marketing wanted to understand whether higher-intent digital activity was translating into commercial pipeline. The complication was structural: one lead could interact with several high-intent campaigns before converting, while Salesforce held one downstream opportunity value. If that opportunity value was attached to every interaction, pipeline contribution looked far larger than reality.
The key modelling decisionSeparate interaction-level influence from opportunity-level value. Interactions can be many-to-one; opportunity amount should be counted once at the appropriate commercial grain.
Model design
I structure the reporting model in four layers so interaction-level influence stays separate from opportunity-level commercial value.
1. Event layerWeb and campaign events with user/session identifiers.
2. Lead layerCRM lead IDs, source, intent and lifecycle stage.
3. Opportunity bridgeLead-to-opportunity mapping, preserving many touches to one opportunity.
4. Reporting layerMeasures for influence, unique pipeline and funnel conversion.
Core logic
The reporting layer keeps two measures side by side: influenced opportunity touches and deduplicated opportunity value. This makes the commercial picture useful without pretending attribution is cleaner than it really is.
Technical proof
The SQL below is a synthetic portfolio example of the grain-control logic. It preserves every high-intent interaction but assigns opportunity value once, at the opportunity grain.
EventsUser/session interactions
LeadsCRM lifecycle and source
BridgeMany touches → one opportunity
OpportunityUnique commercial value
ROW_NUMBER() OVER (
PARTITION BY opportunity_id
ORDER BY interaction_ts, interaction_id
) AS opportunity_value_row
CASE
WHEN opportunity_value_row = 1 THEN opportunity_amount
ELSE 0
END AS deduplicated_pipeline_amount
Unique Pipeline :=
SUMX(
VALUES(Opportunity[Opportunity ID]),
CALCULATE(MAX(Opportunity[Amount]))
)
Representative portfolio logic only. No employer code, schema names or production data are reproduced.
Funnel view
The funnel is intentionally simple. I would use it to identify where acquisition quality changes materially and then cut by market, campaign family, lead source or customer segment.
Why deduplication matters
| Opportunity | High-intent touches | Naive attributed pipeline | Deduplicated value |
|---|
ExampleOPP-052 has four high-intent touches. Repeating a DKK 160k opportunity amount on every touch produces DKK 640k of “pipeline”. The reporting model should preserve all four influences but only DKK 160k of commercial value.