ROI

This SQL example helps you find Total Cost, Attributed Value, Attributed Count, ROI and CPA by Channel and AdSource for Stage = NewBiz & Attribution Model = Data-Driven (paid_performance formulas: ROI = attributed value / cost × 100, CPA = cost / attributed count). Analytics Hub uses spend-linked touchpoints (attribution_with_spend); this example joins the attribution table to spend by channel/source as an approximation.

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,
    SUM(attr.weight) AS attributable_prospects,
    attr.model AS attribution_model
  FROM
    `table.attribution` AS p,
    UNNEST(attribution) AS attr
  WHERE
    p.stage.name = 'NewBiz'
    AND p.stage.timestamp >= '2025-01-01'
    AND p.stage.timestamp < '2025-05-20'
    AND attr.model = 'Data-Driven'
    AND p.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.attributable_prospects,
  100 * SAFE_DIVIDE(performance.total_value, cost.total_cost) AS ROI,
  SAFE_DIVIDE(cost.total_cost, performance.attributable_prospects) AS CPA,
  attribution_model
FROM
  cost
LEFT JOIN
  performance
ON
  cost.AdSource = performance.AdSource
  AND cost.channel = performance.channel