Aquisition

This SQL example helps you find total value, influenced prospects and total prospects

SELECT
  SUM(attr.value) AS total_value,
  COUNT(DISTINCT dd_stage_id) AS influenced_prospects,
  SUM(attr.weight) AS prospects
FROM
  `table.attribution` AS a,
  UNNEST(attribution) AS attr
WHERE
  attr.model = 'Data-Driven'
  AND a.stage.name = 'MQL'
  AND a.timestamp >= '2025-05-01'
  AND a.timestamp < '2025-06-01'

This SQL example helps you find total value and total prospects by Channel

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

This SQL example helps you find the overall Channel Performance

WITH
  unique_deal_stage_value_per_channel AS (
  SELECT
    a.session.channel AS Channel,
    a.dd_stage_id,
    MAX(a.stage.value) AS influenced_deal_value
  FROM
    `table.attribution` AS a
  WHERE
    a.stage.name = 'MQL'
    AND a.timestamp >= '2025-05-01'
    AND a.timestamp < '2025-06-01'
  GROUP BY
    a.session.channel,
    a.dd_stage_id ),
  channel_influenced_value AS (
  SELECT
    Channel,
    ROUND(SUM(influenced_deal_value), 0) AS Influenced_Value_Per_Channel
  FROM
    unique_deal_stage_value_per_channel
  GROUP BY
    Channel ),
  channel_attribution AS (
  SELECT
    session.channel AS Channel,
    SUM(attr.value) AS Value,
    COUNT(DISTINCT dd_stage_id) AS Influenced_Prospects,
    SUM(attr.weight) AS Prospects
  FROM
    `table.attribution` AS a,
    UNNEST(attribution) AS attr
  WHERE
    attr.model = 'Data-Driven'
    AND a.stage.name = 'MQL'
    AND a.timestamp >= '2025-05-01'
    AND a.timestamp < '2025-06-01'
  GROUP BY
    Channel ),
  channel_events AS (
  SELECT
    session.channel AS Event_Channel,
    COUNT(DISTINCT dd_visitor_id) AS Visitors,
    COUNT(DISTINCT dd_contact_id) AS Contacts,
    COUNT(DISTINCT dd_company_id) AS Companies
  FROM
    `table.events`
  WHERE
    timestamp >= '2025-05-01'
    AND timestamp < '2025-06-01'
  GROUP BY
    Event_Channel )
SELECT
  COALESCE(a.Channel, e.Event_Channel) AS Channel,
  e.Visitors,
  e.Contacts,
  e.Companies,
  a.Influenced_Prospects,
  a.Prospects,
  a.Value,
  civ.Influenced_Value_Per_Channel
FROM
  channel_attribution a
FULL OUTER JOIN
  channel_events e
ON
  a.Channel = e.Event_Channel
LEFT JOIN
  channel_influenced_value civ
ON
  COALESCE(a.Channel, e.Event_Channel) = civ.Channel
ORDER BY
  a.Value DESC