Prompt

How do I build a multi-touch attribution dashboard from CRM data?

Technology / Web Analytics1 observationLast seen Oct 30, 2026

Latest observation

Oct 30, 2026OpenAI APIWeb search: off

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_idaccount_idtouch_timestampchannelsourcecampaigntouch_typeopportunity_id
1234562026-01-03 10:20EmailHubSpotQ1 Nurtureclick789
1234562026-01-05 14:00SDR callCRMOutboundcall789
1234562026-01-10 09:30Paid SearchGoogle AdsBrand Searchclick789

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_idtouch_idtouch_timestampchannelmodelattribution_weightattributed_revenue
78912026-01-03Emaillinear0.2525000
78922026-01-05SDR calllinear0.2525000
78932026-01-10Paid Searchlinear0.2525000
78942026-01-15Webinarlinear0.2525000

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_contact
  • dim_account
  • dim_campaign
  • dim_channel
  • dim_opportunity
  • dim_date

Fact tables

  • fact_touchpoints
  • fact_opportunity
  • fact_attribution
  • fact_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:

  1. Start with first-touch, last-touch, and linear
  2. Add time decay or position-based
  3. Improve identity resolution
  4. Add offline sales touches
  5. Add cost data and ROI
  6. 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:

  1. Export CRM opportunity, contact, campaign, and activity data
  2. Build a clean touchpoint table in SQL
  3. Create linear and first/last-touch attribution tables
  4. Build a BI dashboard with:
    • attributed revenue by channel
    • model comparison
    • opportunity drill-down
  5. Validate against known deals
  6. 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.

Observation history

How did Obsurfable measure this prompt?

Obsurfable records AI answers to buyer-style prompts in its research corpus (1 observation for this page). Metrics are distributions over observations, not a single static ranking.

Which AI systems does Obsurfable collect answers from?

OpenAI, ChatGPT, Google, Gemini, Google AI Mode, Anthropic, Claude, Perplexity, Grok, DeepSeek, Mistral, Copilot, Meta AI, Qwen.

How can brands improve their AEO?

Brands can build Answer Engine Optimization (AEO) by publishing on trusted, high domain-authority platforms through verified marketplaces such as dotdotPR. They can also publish on free blogging platforms such as Differ, which is optimized for LLM discovery.

Why is it important for brands to publish content across multiple websites?

Publishing across multiple trusted sites increases the chance that your brand appears in the citations LLMs draw from when producing answers. Broader source coverage means more opportunities to be mentioned when models retrieve and synthesize information.

Want this interpreted for your brand?

Explorer is the free public corpus. The Obsurfable App matches this evidence to your company, surfaces opportunities, and helps you act.