--- title: "RevSure Reporting Tables - Sample Queries" slug: "revsure-reporting-tables-sample-queries" updated: 2026-09-17T06:32:20Z published: 2026-09-17T06:33:10Z canonical: "documents.revsure.ai/revsure-reporting-tables-sample-queries" --- > ## Documentation Index > Fetch the complete documentation index at: https://documents.revsure.ai/llms.txt > Use this file to discover all available pages before exploring further. # RevSure Reporting Tables - Sample Queries ### ### 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* ```plaintext 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__ext.attribution_reporting` ar      INNER JOIN (        SELECT          OpportunitySourceId as OpportunityID,          OpportunityType,          OpportunityValue,          OpportunityStage        FROM          `reporting__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__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 ```plaintext 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__ext.attribution_reporting` ar      INNER JOIN (        SELECT          OpportunitySourceId as OpportunityID,          OpportunityType,          OpportunityValue,          OpportunityStage        FROM          `reporting__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.* ```plaintext select  count(distinct OpportunityID),  sum(OpportunityValue) from  `reporting__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.* ```plaintext select * from `reporting__ext.engagements` where source_object_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.* ```plaintext select * from `reporting__ext.engagements` where source_object_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.* ```plaintext select * from `reporting__ext.engagements` where source_object_id = '' and engagement = 'Funnel Movement' ```