attribution_reporting

  • Updated on Sep 17, 2026
  • Published on Sep 17, 2026
  • 3 minute(s) read
Prev Next

Attribution Reporting Table

The Attribution Reporting table stores campaign-level attribution credit for leads and opportunities at each RevSure funnel stage. Each row is one campaign’s contribution to one entity entering one funnel stage, under one association type.

Use this table to measure which campaigns, channels, and source systems influenced funnel progression (for example MQL, Pipeline, Closed Won) under standard attribution models.

BigQuery: reporting_<tenant>.attribution_reporting

Granularity: one row per
(lead_id or opportunity_id) + campaign_id + revsure_funnel_stage_name + campaign_member_association_type

Important

  • revsure_funnel_stage_name is the display name of the tenant funnel stage (for example MQL, Pipeline, Closed Won), not an internal stage1/stage2 id.

  • campaign_id is the source-system campaign ID (Salesforce Campaign ID when available).

  • Always filter campaign_member_association_type. Mixing association types double-counts the same opportunity.

  • first_touch, last_touch, linear_shaped, u_shaped, j_shaped, inverse_j_shaped, and w_shaped are shares that sum to 1 per entity + stage + association type.

  • any_touch is 1.0 for every influencing campaign (influenced / campaign-member style). It does not sum to 1. Use it for influenced counts, not for allocating a single dollar or opportunity across campaigns.

  • Some tenants add extra columns (AI attribution, HMM score, extra campaign dimensions). Those are not part of the common schema.

For how each model assigns credit, see Attribution Methods in RevSure.

Name

Label

Type

Description

lead_id

Lead ID

STRING

Original lead ID from the source system (for example Salesforce Lead ID). Populated on Lead rows; may be null on Opportunity rows.

opportunity_id

Opportunity ID

STRING

Original opportunity ID from the source system (for example Salesforce Opportunity ID). Populated on Opportunity rows; may be null on Lead rows.

account_id

Account ID

STRING

Original account ID from the source system (for example Salesforce Account ID).

entity_type

Entity Type

STRING

Type of record being attributed. Values: Lead, Opportunity.

campaign_id

Campaign ID

STRING

Source-system campaign ID associated with the touch (Salesforce Campaign ID when available).

campaign_name

Campaign Name

STRING

Name of the campaign.

campaign_type

Campaign Type

STRING

Type of the campaign (for example organic post, sponsored update).

campaign_channel

Campaign Channel

STRING

Channel of the campaign (for example Paid Search, Organic Social, Direct Marketing).

campaign_source_system

Campaign Source System

STRING

Source system of the campaign (for example Salesforce, LinkedIn, Marketo).

revsure_funnel_stage_name

RevSure Funnel Stage Name

STRING

Funnel stage this attribution row is measured against (for example MQL, Pipeline, Closed Won). Display name from the tenant funnel config.

revsure_funnel_stage_timestamp

RevSure Funnel Stage Timestamp

TIMESTAMP

UTC timestamp when the lead/opportunity entered this funnel stage.

campaign_member_association_type

Campaign Member Association Type

STRING

How the campaign is associated to the entity. Typical values: lead, opportunity_account, opportunity_primary_contact, opportunity_all_contacts. For opportunities, this indicates whether attribution is based on the account, the primary contact, or all contacts.

first_touch

First Touch

FLOAT64

Contribution (out of 1) under the First Touch model. 100% credit to the first campaign before this stage.

last_touch

Last Touch

FLOAT64

Contribution (out of 1) under the Last Touch model. 100% credit to the last campaign before this stage.

any_touch

Any Touch

FLOAT64

Influenced credit. Each campaign that touched the entity before this stage receives 1.0. Not a share of 1.

w_shaped

W Shaped

FLOAT64

Contribution (out of 1) under the W-shaped model (emphasis on first, middle, and last touches).

linear_shaped

Linear Shaped

FLOAT64

Contribution (out of 1) under the Linear model. Equal credit across all campaigns before this stage.

u_shaped

U Shaped

FLOAT64

Contribution (out of 1) under the U-shaped model (emphasis on first and last touches).

j_shaped

J Shaped

FLOAT64

Contribution (out of 1) under the J-shaped model (emphasis on last touch).

inverse_j_shaped

Inverse J Shaped

FLOAT64

Contribution (out of 1) under the Inverse J-shaped model (emphasis on first touch).

Sample queries (common schema)

Replace reporting_<tenant> with the tenant dataset, for example reporting_serval or reporting_gainsight. Replace stage names with the tenant’s funnel display names.

Example 1 — Total MQL contribution by campaign channel

SELECT  campaign_channel,  SUM(linear_shaped) AS linear_contribution,  SUM(first_touch) AS first_touch_contribution,  SUM(last_touch) AS last_touch_contribution,  SUM(any_touch) AS any_touch_touchesFROM `reporting_<tenant>.attribution_reporting`WHERE revsure_funnel_stage_name = 'MQL'  AND campaign_member_association_type = 'lead'GROUP BY campaign_channelORDER BY linear_contribution DESC

Example 2 — Closed Won contribution for a given opportunity (standard models)

SELECT  opportunity_id,  campaign_name,  campaign_channel,  SUM(linear_shaped) AS attributed_share_linear,  SUM(first_touch) AS attributed_share_first_touch,  SUM(last_touch) AS attributed_share_last_touch,  SUM(u_shaped) AS attributed_share_u_shaped,  SUM(w_shaped) AS attributed_share_w_shapedFROM `reporting_<tenant>.attribution_reporting`WHERE opportunity_id = '<source_opportunity_id>'  AND revsure_funnel_stage_name = 'Closed Won'  AND campaign_member_association_type = 'opportunity_account'GROUP BY opportunity_id, campaign_name, campaign_channelORDER BY attributed_share_linear DESC

Example 3 — Influenced Closed Won opportunities by channel (Any Touch)

any_touch is 1.0 per influencing campaign, so count distinct opportunities rather than summing any_touch if the goal is an influenced opportunity count.

SELECT  campaign_channel,  COUNT(DISTINCT opportunity_id) AS influenced_closed_won_opportunitiesFROM `reporting_<tenant>.attribution_reporting`WHERE revsure_funnel_stage_name = 'Closed Won'  AND entity_type = 'Opportunity'  AND campaign_member_association_type = 'opportunity_account'  AND any_touch > 0GROUP BY campaign_channelORDER BY influenced_closed_won_opportunities DESC

Example 4 — Channel mix for a funnel stage (Linear)

SELECT  campaign_channel,  campaign_source_system,  COUNT(DISTINCT COALESCE(opportunity_id, lead_id)) AS entities,  SUM(linear_shaped) AS linear_contributionFROM `reporting_<tenant>.attribution_reporting`WHERE revsure_funnel_stage_name = 'Pipeline'  AND campaign_member_association_type IN ('opportunity_account', 'lead')GROUP BY campaign_channel, campaign_source_systemORDER BY linear_contribution DESC

Example 5 — Sanity check: Linear shares should sum to 1

This should return no rows (or only floating-point noise). Do not expect the same for any_touch.

SELECT  COALESCE(opportunity_id, lead_id) AS source_id,  revsure_funnel_stage_name,  campaign_member_association_type,  SUM(linear_shaped) AS linear_sum,  SUM(first_touch) AS first_touch_sum,  SUM(any_touch) AS any_touch_sum,  COUNT(*) AS campaign_rowsFROM `reporting_<tenant>.attribution_reporting`GROUP BY 1, 2, 3HAVING ABS(SUM(linear_shaped) - 1.0) > 0.01LIMIT 20