Prompt
How do I build a multi-touch attribution dashboard from CRM data?
Latest observation
To build a multi-touch attribution (MTA) dashboard from CRM data, you need to turn raw customer and sales activity into a unified timeline that shows which marketing and sales touches contributed to conversions or revenue.
Here’s a practical approach.
1) Define the business question first
Before building anything, decide what you want to attribute:
- Lead conversion: Which touches influenced a lead becoming an opportunity?
- Opportunity creation: Which channels drove pipeline generation?
- Closed-won revenue: Which touches influenced deals that were won?
- Pipeline velocity: Which touches shorten sales cycles?
Also decide:
- Attribution unit: lead, contact, account, opportunity, or deal
- Lookback window: e.g. 90 days before conversion
- Channels to include: paid search, organic, email, events, SDR calls, webinars, etc.
2) Identify the data sources in your CRM
Typical CRM data tables/objects:
- Contacts / Leads
- Accounts / Companies
- Opportunities / Deals
- Campaigns
- Campaign members / engagement logs
- Tasks / calls / emails / meetings
- Custom activity objects if your CRM has them
- Revenue / line items if you need order-level attribution
If you have marketing automation or ad platforms, you may also want:
- Web visits
- Email opens/clicks
- Ad clicks/impressions
- Form fills
- Event attendance
3) Build a unified touchpoint table
This is the foundation of the dashboard.
Create a table where each row is a touchpoint:
| person_id | account_id | touch_timestamp | channel | source | campaign | touch_type | opportunity_id |
|---|---|---|---|---|---|---|---|
| 123 | 456 | 2026-01-03 10:20 | HubSpot | Q1 Nurture | click | 789 | |
| 123 | 456 | 2026-01-05 14:00 | SDR call | CRM | Outbound | call | 789 |
| 123 | 456 | 2026-01-10 09:30 | Paid Search | Google Ads | Brand Search | click | 789 |
Important transformations
- Standardize timestamps to one timezone
- Deduplicate repeated events
- Normalize channel names
- Map campaigns to a channel taxonomy
- Link anonymous touches to known people where possible
- Associate touches to the right account/opportunity
4) Decide on attribution logic
There are several common models. Most dashboards show multiple models side by side.
Common attribution models
- First-touch: all credit to the first touch
- Last-touch: all credit to the final touch before conversion
- Linear: equal credit to all touches
- Time decay: more credit to touches closer to conversion
- U-shaped / position-based: first and last touches get more credit, middle touches share the rest
- W-shaped: first touch, lead creation, and opportunity creation get higher weights
Example: linear attribution
If a deal had 4 touches and closed for $100k, each touch gets $25k of attributed revenue.
Example: time-decay
Touches closer to close date get more credit, useful when you believe late-stage touches matter more.
5) Create an attribution fact table
Build a derived table that assigns credit per touch per conversion.
Example structure:
| opportunity_id | touch_id | touch_timestamp | channel | model | attribution_weight | attributed_revenue |
|---|---|---|---|---|---|---|
| 789 | 1 | 2026-01-03 | linear | 0.25 | 25000 | |
| 789 | 2 | 2026-01-05 | SDR call | linear | 0.25 | 25000 |
| 789 | 3 | 2026-01-10 | Paid Search | linear | 0.25 | 25000 |
| 789 | 4 | 2026-01-15 | Webinar | linear | 0.25 | 25000 |
This makes dashboarding much easier.
6) Choose the dashboard metrics
Useful KPIs:
Conversion metrics
- Leads influenced
- Opportunities influenced
- Closed-won deals influenced
- Conversion rate by channel
Revenue metrics
- Attributed pipeline
- Attributed revenue
- Average deal size by channel mix
- CAC or cost per attributed opportunity if cost data exists
Efficiency metrics
- Time to conversion
- Touches per conversion
- Sales cycle length by touch pattern
Channel performance
- Top channels by first touch
- Top channels by last touch
- Top channels by attributed revenue under each model
7) Design the dashboard views
A good MTA dashboard usually has these sections:
A. Executive summary
- Total attributed pipeline
- Total attributed revenue
- Top channels by model
- Conversion trend over time
B. Channel performance
Bar chart:
- Channel vs attributed revenue
- Channel vs opportunities influenced
C. Attribution model comparison
Show the same channels under:
- First-touch
- Last-touch
- Linear
- Time-decay
This helps prevent overreliance on one model.
D. Journey analysis
- Most common touch sequences
- Average number of touches before conversion
- Path analysis by persona or segment
E. Drill-down table
Opportunity-level or account-level table:
- Deal name
- Stage
- Touch count
- First touch channel
- Last touch channel
- Attributed revenue by model
8) Handle CRM data issues carefully
CRM data is messy. Watch for:
- Missing campaign IDs
- Duplicate contacts
- Multiple contacts per opportunity
- Inconsistent lead/contact/account relationships
- Offline touches not logged
- Self-reported or hidden sources
- Stale ownership or stage dates
- Closed-won opportunities without proper contact association
You’ll need data quality rules such as:
- Exclude touches after conversion
- Only count touches within the lookback window
- Require a valid person/account match
- Deduplicate bot/spam traffic
- Define how to handle multi-contact deals
9) Pick the right tooling
You can build this with different stacks:
Simple stack
- CRM export to CSV
- SQL database or warehouse
- BI tool like Tableau, Power BI, Looker, or Metabase
Scalable stack
- ETL/ELT: Fivetran, Airbyte, Stitch
- Warehouse: Snowflake, BigQuery, Redshift, Postgres
- Transformation: dbt or SQL scripts
- BI: Looker, Power BI, Tableau, Mode, Metabase
More advanced
- Python/R for custom attribution modeling
- Identity resolution and graph-based customer journey analysis
10) Recommended data model
A clean warehouse setup often looks like this:
Dimension tables
dim_contactdim_accountdim_campaigndim_channeldim_opportunitydim_date
Fact tables
fact_touchpointsfact_opportunityfact_attributionfact_revenue
This makes it easy to aggregate by channel, campaign, segment, or time.
11) Example SQL logic for linear attribution
A simplified approach:
WITH touches AS (
SELECT
opportunity_id,
touch_id,
touch_timestamp,
channel,
ROW_NUMBER() OVER (PARTITION BY opportunity_id ORDER BY touch_timestamp) AS touch_num,
COUNT(*) OVER (PARTITION BY opportunity_id) AS total_touches
FROM fact_touchpoints
WHERE touch_timestamp <= close_date
AND touch_timestamp >= close_date - INTERVAL '90 days'
),
attribution AS (
SELECT
opportunity_id,
touch_id,
channel,
1.0 / total_touches AS attribution_weight
FROM touches
)
SELECT
a.opportunity_id,
a.channel,
SUM(a.attribution_weight * o.amount) AS attributed_revenue
FROM attribution a
JOIN fact_opportunity o
ON a.opportunity_id = o.opportunity_id
GROUP BY 1, 2;
12) Validate the model before launching
Check that the dashboard outputs make sense:
- Do attributed totals roughly match actual closed revenue?
- Are high-value deals being assigned correctly?
- Are “unknown” or “other” channels dominating?
- Do results change dramatically with small data fixes?
- Are touches being double-counted?
Good validation tests:
- Totals reconcile to known revenue
- Touch counts are within expected range
- Attribution weights sum to 1 per conversion
- Model outputs are stable over time
13) Start simple, then iterate
A common mistake is trying to do “perfect attribution” too early.
Best practice:
- Start with first-touch, last-touch, and linear
- Add time decay or position-based
- Improve identity resolution
- Add offline sales touches
- Add cost data and ROI
- Test more advanced models like Markov chains or algorithmic attribution
14) Useful dashboard formulas
A few metrics you may want:
-
Attributed revenue by channel [ \sum (\text{deal amount} \times \text{attribution weight}) ]
-
Channel influence rate [ \frac{\text{deals with channel touch}}{\text{total deals}} ]
-
Average touches per opportunity [ \frac{\text{total touches}}{\text{opportunities}} ]
-
Attributed ROI [ \frac{\text{attributed revenue} - \text{channel cost}}{\text{channel cost}} ]
15) What to show stakeholders
Different audiences need different views:
- Executives: revenue, pipeline, ROI, top channels
- Marketing: campaign performance, channel mix, conversion paths
- Sales: influence of SDR/outbound touches, stage acceleration
- Ops / RevOps: data quality, attribution rules, source integrity
A practical build plan
If you want a fast implementation:
- Export CRM opportunity, contact, campaign, and activity data
- Build a clean touchpoint table in SQL
- Create linear and first/last-touch attribution tables
- Build a BI dashboard with:
- attributed revenue by channel
- model comparison
- opportunity drill-down
- Validate against known deals
- Add more sophisticated modeling later
If you want, I can also give you:
- a sample data schema
- a dbt model design
- SQL for first-touch, last-touch, and time-decay
- or a Power BI / Tableau dashboard layout for this.