ROI

This SQL example helps you find Total Cost, Total Value and ROI by Channel and AdSource for Stage = NewBiz & Attribution Model = Data-Driven

WITH
  cost AS (
  SELECT
    channel,
    adNetwork AS AdSource,
    SUM(cost) AS total_cost
  FROM
    `table.spend` AS c
  WHERE
    timestamp BETWEEN '2025-01-01'AND '2025-05-20'
  GROUP BY
    AdSource,
    channel ),
    performance AS (
  SELECT
    session.channel,
    session.source AS AdSource,
    stage.name AS StageName,
    SUM(attr.value) AS total_value,
    attr.model AS attribution_model
  FROM
    `table.attribution` AS p,
    UNNEST(attribution) AS attr
  WHERE
    stage.name = 'NewBiz'
    AND timestamp > '2025-01-01'
    AND attr.model = 'Data-Driven'
    AND session.channel IN ('Paid',
      'Review Sites',
      'Paid Social',
      'Paid Search',
      'Paid Video',
      'Display Ads',
      'Paid Other')
  GROUP BY
    channel,
    AdSource,
    StageName,
    attribution_model
  ORDER BY
    channel,
    AdSource,
    StageName,
    attribution_model )
SELECT
  cost.*,
  performance.StageName,
  performance.total_value,
  performance.total_value / cost.total_cost AS ROI,
  attribution_model
FROM
  cost
LEFT JOIN
  performance
ON
  cost.AdSource = performance.AdSource
  AND cost.channel = performance.channel