Attribution

This SQL query helps you understand how different channels contribute to new business deals and their associated value.

New Schema i.e Google BigQuery V2

SELECT
session.channel,
SUM(attr.weight) AS Prospects,
SUM(attr.value) AS Value
FROM `table.attribution` as a,
UNNEST (a.attribution) as attr
WHERE stage.timestamp >= '2025-05-01'
AND stage.timestamp < '2025-06-01'
AND stage.name = 'MQL'
AND attr.model = 'Data-Driven'
GROUP BY session.channel
ORDER  BY prospects DESC

Old Schema i.e Google BigQuery Legacy

SELECT
channel,
SUM(attributableDeal) AS Prospects,
SUM(attributableRevenue) AS Value
FROM `table.revenue_attribution`
WHERE revenueTimestamp >= '2025-05-01'
AND revenueTimestamp < '2025-06-01'
AND revenueModel = 'NewBiz'
AND attributionModel = 'Data-Driven'
GROUP BY channel
ORDER  BY prospects DESC

This SQL query provides a detailed breakdown of new business performance by both channel and source, using a data-driven attribution model

SELECT
  session.channel,
  session.source,
  COUNT(DISTINCT dd_stage_id) AS influenced_prospects,
  SUM(attr.value) AS total_value,
  SUM(attr.weight) AS attributable_prospects
FROM
  `table.attribution` AS a,
  UNNEST(attribution) AS attr
WHERE
  stage.name = 'NewBiz'
  AND timestamp >= '2025-05-01'
  AND timestamp < '2025-06-01'
  AND attr.model = 'Data-Driven'
GROUP BY
  channel,
  SOURCE
ORDER BY
  channel,
  source