Segmentation

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