Segmentation

Legacy example only. Uses the pre–V2 revenue table and does not match current Analytics Hub metrics. For V2 attribution and stage metrics, see attribution.md and acquisition.md.

This SQL query provides a breakdown of revenue-driving activities by contact, combining engagement metrics with pipeline contribution and time-to-close analysis

-- Based on Dreamdata Legacy Schema

WITH raw_data AS (
  SELECT
    r.*,
    e.* EXCEPT (companyId)
  FROM
    `table.revenue` AS r
  LEFT JOIN
    `table.events` AS e
  ON
    r.companyId = e.companyId
    AND e.timestamp < r.revenueTimestamp
  WHERE
    r.revenueModel = 'Closed-Won'
    AND r.revenueTimestamp >= '2025-07-01'
    AND r.revenueTimestamp < '2025-08-01'
    AND e.event NOT LIKE '%impression%'
), metrics AS (
  SELECT
    rd.email AS contact,
    COUNT(DISTINCT rd.channel) AS total_channels,
    COUNT(DISTINCT rd.dd_session_id) AS total_sessions
  FROM raw_data AS rd
  GROUP BY rd.email
), revenue AS (
  SELECT
    rd.dealId,
    MAX(revenue) AS total_value,
    TIMESTAMP_DIFF(MAX(rd.revenueTimestamp), MIN(rd.timestamp), SECOND) / 86400 AS days_to_revenue
  FROM raw_data rd
  GROUP BY rd.dealId
), total AS (
  SELECT
  SUM(total_value) as total_value,
  AVG(days_to_revenue) AS avg_days_to_revenue
  from revenue
) SELECT
  COUNT(DISTINCT rd.email) AS total_contacts,
  COUNT(DISTINCT rd.companyId) AS total_companies,
  COUNT(DISTINCT rd.dealId) AS total_deals,
  AVG(m.total_channels) AS avg_channels,
  AVG(m.total_sessions) AS avg_sessions,
  t.total_value,
  t.avg_days_to_revenue
FROM raw_data rd
LEFT JOIN metrics m
ON rd.email = m.contact
LEFT JOIN total t
ON TRUE
GROUP BY total_value,avg_days_to_revenue