Salesforce multi-touch attribution: Campaign Influence vs. a warehouse model

Salesforce reports campaign revenue through Customizable Campaign Influence. That feature only sees campaign members and contact roles on opportunities, so it misses website visits and ad clicks entirely. The native models are useful for a first answer. A warehouse model is how you get a multi-touch answer you can audit.

How Salesforce Campaign Influence works

Customizable Campaign Influence identifies revenue share using standard and custom attribution models. It is available in Salesforce Professional, Enterprise, Performance, Unlimited, and Developer editions, and it adds the CampaignInfluence and CampaignInfluenceModel objects.

Influence models scan active campaigns for campaign members who are also assigned a contact role on an open opportunity. When they find one, they create a campaign influence record from that relationship. Every campaign member counts, regardless of member status. Once an opportunity is closed, won or lost, new influence records are no longer created.

The default model is Primary Campaign Source. It gives 100% of the influence to the campaign in the opportunity's Primary Campaign Source field. Salesforce creates these records through auto-association, so users can't add them manually.

Auto-association can be limited by a time frame and by campaign types, both set in Setup. That setting applies only to the standard models.

The influence models available

Where native Campaign Influence falls short

• Data hygiene decides the result. If the opportunity has no contact role for a person, or the person is not a campaign member, that campaign gets no influence. Missing records fail silently.
• Leads that never convert don't link to opportunities. When a lead converts, the rep creates a Contact, and optionally an Account and an Opportunity. Anything that happened on the lead before that point depends on the conversion carrying it forward.
• Campaign Influence only knows campaigns. Website sessions, ad clicks, and organic visits do not appear unless someone turns them into campaign members. Most paid and organic traffic never gets that step.
• Ad spend lives outside Salesforce. Influence can tell you which campaign was touched. It cannot put that campaign next to what it cost in Google Ads or Meta without a second system.

When to build multi-touch attribution in a warehouse

• You need first-touch, last-touch, or even-distribution credit without an Account Engagement license.
• Website sessions and paid ad clicks should count alongside campaigns, not only the campaigns a rep entered.
• Finance or leadership needs a model whose rules someone can read, question, and change.
• Spend and won revenue need to sit in the same report with the same definitions.

Building Salesforce attribution in BigQuery

The pipeline has five steps: sync the Salesforce objects, keep the membership data complete, apply the attribution logic to won opportunities, add the web and ad touches, and check the output against native reporting.

1. Sync Salesforce into BigQuery

Load these objects into BigQuery on a schedule: Opportunity, OpportunityContactRole, CampaignMember, Campaign, Lead, and Contact. Maven or any other connector can do this. The table names in this guide are illustrative snake_case names such as salesforce_opportunity, so match them to whatever your sync creates.

2. Keep campaign membership and contact roles complete

The model is only as complete as the membership data it reads. Every form, list import, and event that creates a lead should also add that person as a campaign member, and every opportunity should have at least one contact role. Those rules belong in the process, not in a cleanup project each quarter.

UTMs and click IDs are the other gap. Capture them when the lead is created so the web touch can be matched later. The offline conversions guide covers how to capture them at that point.

3. Apply attribution models to won opportunities

The query below walks from each won opportunity to its contact roles, then to those contacts' campaign memberships created on or before the close date. It orders each opportunity's touches by campaign member created date, the same ordering the native First-Touch model uses. It then computes three models from that order: first touch, last touch, and even distribution.

WITH touches AS (
  SELECT
    o.id AS opportunity_id,
    o.amount,
    cm.campaign_id,
    ROW_NUMBER() OVER (PARTITION BY o.id ORDER BY cm.created_date) AS touch_n,
    COUNT(*)     OVER (PARTITION BY o.id)                          AS touches
  FROM salesforce_opportunity AS o
  JOIN salesforce_opportunity_contact_role AS ocr
    ON ocr.opportunity_id = o.id
  JOIN salesforce_campaign_member AS cm
    ON cm.contact_id = ocr.contact_id
   AND DATE(cm.created_date) <= o.close_date
  WHERE o.is_won
)
SELECT
  c.name AS campaign_name,
  c.type AS campaign_type,
  SUM(IF(t.touch_n = 1, t.amount, 0))         AS first_touch_revenue,
  SUM(IF(t.touch_n = t.touches, t.amount, 0)) AS last_touch_revenue,
  SUM(t.amount / t.touches)                   AS even_distribution_revenue
FROM touches AS t
JOIN salesforce_campaign AS c
  ON c.id = t.campaign_id
GROUP BY campaign_name, campaign_type
ORDER BY even_distribution_revenue DESC

Each model column sums to the won revenue that has at least one matching campaign touch. The inner joins drop won opportunities with no touches, so that revenue shows up as a gap, not as a zero row. Report the gap instead of hiding it. Unlike native influence, this table is computed for won deals after the fact, and every rule is visible in the query: the contact-role join, the close-date cutoff, and the ordering.

4. Add web and ad touches

To include web and ad activity, union GA4 sessions or ad clicks into the same touch list. Join them to the opportunity through the UTMs and click IDs stored on the lead or contact, the same way campaign members are joined. Each touch should carry a campaign name, a source, and a timestamp so the same ordering rules apply. The SQL above is the place to add that union; the attribution logic does not need to change.

5. Report and sanity-check

Compare the warehouse output with native Campaign Influence reports for the same period. Differences are expected, and they should be explained, not smoothed over. Common causes are contacts missing from the opportunity, campaign members created after close, web touches that do not match a contact, and time zone differences between Salesforce and the warehouse. Write each cause down next to the report.

Before you trust the numbers

1. 1Every lead-creating form, list import, and event adds the person as a campaign member.
2. 2Every opportunity has at least one contact role, and won and closed flags are current.
3. 3Duplicate leads and contacts are merged, so one person does not count under two records.
4. 4Won revenue with at least one touch plus won revenue with none equals total won revenue, and the gap is documented.
5. 5The warehouse output is compared with native Campaign Influence for the same period, and every difference has a stated cause.

For the wider model, read the GA4 multi-touch attribution guide for BigQuery, then the explanation of why Salesforce and GA4 numbers differ. For the underlying models and their trade-offs, the multi-touch attribution technical guide covers them in more depth. If you want the Salesforce model built against your objects, book a tracking audit and bring the report you are trying to reconcile.