RevSure Reporting Tables - Sample Queries

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

Campaign level ROI for Stage 0, Stage 1 and Closed Won stages based on Linear attribution for the period 1-Jan-2026 to 1-Jun-2026

Note: Includes custom filters on Opportunity Fleet, Campaign Group etc

SELECT 
  att.revsure_funnel_stage_name, 
  att.campaign_source_system, 
  att.campaign_name, 
  cs.campaign_spend, 
  round(
    sum(att.linear_shaped), 
    3
  ) linear_attributed_volume, 
  round(
    sum(
      att.OpportunityValue * att.linear_shaped
    ), 
    3
  ) as linear_attributed_value, 
  round(
    round(
      sum(
        att.OpportunityValue * att.hmm_score
      ), 
      3
    )/ cs.campaign_spend, 
    3
  ) as roi 
from 
  (
    select 
      *, 
      ROW_NUMBER() OVER (
        PARTITION BY ar.opportunity_id, revsure_funnel_stage_name
      ) as influence_rn 
    FROM 
      `reporting_<tenant>_ext.attribution_reporting` ar 
      INNER JOIN (
        SELECT 
          OpportunitySourceId as OpportunityID, 
          OpportunityType, 
          OpportunityValue, 
          OpportunityStage 
        FROM 
          `reporting_<tenant>_ext.lead_history` 
        WHERE 
          OpportunitySourceId is not null 
          and OpportunityStage in (
            'Stage 1', 'Stage 0', 'Closed Won' -- Change according to your funnel
          ) QUALIFY ROW_NUMBER() OVER (
            PARTITION BY OpportunityID, 
            OpportunityStage 
            ORDER BY 
              FunnelActivityTimestamp asc
          ) = 1
      ) ms ON ar.opportunity_id = ms.OpportunityID 
      and ms.OpportunityStage = ar.revsure_funnel_stage_name 
    WHERE 
      revsure_funnel_stage_timestamp >= '2026-01-01 08:00:00' 
      and revsure_funnel_stage_timestamp < '2026-06-02 06:59:59' 
      AND ar.campaign_member_association_type IN ('lead', 'opportunity_account') 
      AND ms.OpportunityType = 'NEW' -- Change according to your needs
  ) att 
  left join (
    select 
      source_campaign_id, 
      round(
        sum(spend), 
        3
      ) as campaign_spend 
    from 
      `reporting_<tenant>_ext.campaign_spend` 
    where 
      date between '2026-01-01' 
      and '2026-06-02' 
    group by 
      source_campaign_id
  ) cs on att.campaign_id = cs.source_campaign_id 
group by 
  att.revsure_funnel_stage_name, 
  att.campaign_source_system, 
  att.campaign_name, 
  cs.campaign_spend 
order by 
  att.revsure_funnel_stage_name, 
  round(
    round(
      sum(
        att.OpportunityValue * att.hmm_score
      ), 
      3
    )/ cs.campaign_spend, 
    3
  ) desc

Linear and Influenced Attribution Volume and $ Value for all Opportunity Stages

SELECT 
  revsure_funnel_stage_name, 
  sum(linear_shaped) linear_volume, 
  sum(OpportunityValue * linear_shaped) as linear_value, 
  sum(
    case when influence_rn = 1 then OpportunityValue else 0 end
  ) as influenced_amount, 
  sum(
    case when influence_rn = 1 then any_touch else 0 end
  ) as influenced_volume 
from 
  (
    select 
      *, 
      ROW_NUMBER() OVER (
        PARTITION BY ar.opportunity_id, revsure_funnel_stage_name
      ) as influence_rn 
    FROM 
      `reporting_<tenant>_ext.attribution_reporting` ar 
      INNER JOIN (
        SELECT 
          OpportunitySourceId as OpportunityID, 
          OpportunityType, 
          OpportunityValue, 
          OpportunityStage 
        FROM 
          `reporting_<tenant>_ext.lead_history` 
        WHERE 
          OpportunitySourceId is not null 
          and OpportunityStage in (
            'Stage 1', 'Stage 0', 'Closed Won' -- Change according to your funnel config
          ) QUALIFY ROW_NUMBER() OVER (
            PARTITION BY OpportunityID, 
            OpportunityStage 
            ORDER BY 
              FunnelActivityTimestamp asc
          ) = 1
      ) ms ON ar.opportunity_id = ms.OpportunityID 
      and ms.OpportunityStage = ar.revsure_funnel_stage_name 
    WHERE 
      revsure_funnel_stage_timestamp >= '2026-01-01 08:00:00' 
      and revsure_funnel_stage_timestamp < '2026-06-02 06:59:59' 
      AND ar.campaign_member_association_type IN ('lead', 'opportunity_account') 
      AND ms.OpportunityType = 'NEW' 
  ) 
group by 
  1

Closed Won count and $ Value YTD

Note: Same query can be used for other Opportunity stages by changing the OpportunityStage filter. 

select 
  count(distinct OpportunityID), 
  sum(OpportunityValue) 
from 
  `reporting_<tenant>_ext.lead_history` 
where 
  1 = 1 
  AND Date(StageCreatedDate) BETWEEN '2026-01-01'  AND '2026-06-01' 
    AND OpportunityType IN ('NEW') 
    and OpportunityStage = 'Closed Won' 
  )

Look at all Engagements for a Lead/Contact

Note:  This shows all engagements including activities, campaign touches and funnel movements for a given lead or contact id.

select *
from `reporting_<tenant>_ext.engagements`
where source_object_id = '<sfdc lead or contat id/hubspot contacts id>'

Look at all Attributable Touch points for a Lead/Contact

Note:  This shows all engagements including activities, campaign touches and funnel movements for a given lead or contact id.

select *
from `reporting_<tenant>_ext.engagements`
where source_object_id = '<sfdc lead or contat id/hubspot contacts id>'
and engagement = 'Campaign Touch'

Look at all Funnel Stage Progressions for a Lead/Contact

Note:  This shows all engagements including activities, campaign touches and funnel movements for a given lead or contact id.

select *
from `reporting_<tenant>_ext.engagements`
where source_object_id = '<sfdc lead or contat id/hubspot contacts id>'
and engagement = 'Funnel Movement'