Prompt
cross-channel attribution report with CRM revenue
Latest observation
Oct 30, 2026
OpenAI APIWeb search: off
Sure — a cross-channel attribution report with CRM revenue typically combines marketing touchpoint data (ads, email, organic, etc.) with CRM/opportunity data (revenue, pipeline, close date) to show which channels influenced booked revenue.
What this report should answer
- Which channels contributed to closed-won revenue
- Which channels assisted vs. directly converted
- How revenue is distributed across first touch, last touch, linear, or multi-touch models
- How channel performance differs by stage, deal size, or lead source
- Whether marketing spend is generating measurable CRM revenue
Core data needed
1) Marketing / channel touch data
Examples:
- Paid search
- Paid social
- Organic search
- Direct
- Referral
- Events
- Partner
- Webinars
Typical fields:
contact_id/lead_idaccount_idtouch_datechannelcampaignsourcemediumsession_idorutm_*
2) CRM revenue data
From Salesforce, HubSpot CRM, Dynamics, etc.
Typical fields:
opportunity_idaccount_idcontact_idor linked contactsstageamountclose_dateis_closed_wonownercreated_date
Common attribution models
First-touch
Gives all credit to the first channel that created the lead.
Last-touch
Gives all credit to the last channel before conversion.
Linear multi-touch
Splits credit evenly across all touches.
Time-decay
More credit to touches closer to conversion.
Position-based
Weights first and last touches more heavily than middle touches.
Recommended report layout
Summary table
| Channel | Attributed Revenue | # Deals | Avg Deal Size | CAC / Spend | ROAS |
|---|---|---|---|---|---|
| Paid Search | $250,000 | 40 | $6,250 | $50,000 | 5.0x |
| $120,000 | 22 | $5,455 | $8,000 | 15.0x | |
| Organic Search | $180,000 | 30 | $6,000 | $12,000 | 15.0x |
Secondary cuts
- By attribution model
- By funnel stage
- By campaign
- By region
- By segment / ICP vs non-ICP
- By sales rep or team
- By deal type
Example KPI formulas
- Attributed Revenue = sum of CRM revenue allocated by the attribution model
- ROAS = attributed revenue / marketing spend
- Cost per attributed opportunity = spend / attributed opportunities
- Pipeline influenced = sum of open pipeline tied to touchpoints
Important implementation notes
- Deduplicate contacts and opportunities before attribution
- Define a consistent lookback window (e.g. 90/180/365 days)
- Decide whether to attribute at the contact, lead, or account level
- Handle multi-contact opportunities carefully
- Exclude internal/test traffic and invalid CRM records
- Ensure UTM/source normalization across channels
Suggested dashboard visuals
- Revenue by channel
- Attribution model comparison
- Funnel conversion by source
- Touchpoint path analysis
- Spend vs attributed revenue scatter plot
- Closed-won revenue trend over time
If you want to build this in SQL
I can help you create:
- a data model
- a SQL query
- a dbt model
- a Looker/Tableau dashboard spec
- a Salesforce/HubSpot-compatible report
If you want, I can draft a sample SQL query for cross-channel attribution using CRM revenue next.