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 a.stage.timestamp >= '2025-05-01'
AND a.stage.timestamp < '2025-06-01'
AND a.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,
  SUM(attr.value) AS total_value,
  SUM(attr.weight) AS attributable_prospects
FROM
  `table.attribution` AS a,
  UNNEST(attribution) AS attr
WHERE
  a.stage.name = 'NewBiz'
  AND a.stage.timestamp >= '2025-05-01'
  AND a.stage.timestamp < '2025-06-01'
  AND attr.model = 'Data-Driven'
GROUP BY
  session.channel,
  session.source
ORDER BY
  channel,
  source