Content

This SQL example helps you find the number of views per URL

SELECT
  event.url_clean,
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND event.url_clean IS NOT NULL THEN dd_event_activity_id END) AS views
FROM
  `table.events`
WHERE
  dd_tracking_type NOT IN ('exposure')
  AND event.url_clean IS NOT NULL
  AND event_name = 'page_view'
  AND timestamp >= '2025-05-01'
  AND timestamp < '2025-06-01'
GROUP BY
  event.url_clean
ORDER BY
  views DESC

This SQL example helps you find the number of views, sessions, visitors, contacts and companies per URL

SELECT
  event.url_clean,
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND event.url_clean IS NOT NULL THEN dd_event_activity_id END) AS views,
  COUNT(distinct dd_session_activity_id) as sessions,
  COUNT(distinct CASE WHEN dd_tracking_type <> 'exposure' THEN dd_visitor_id END) as visitors,
  COUNT(distinct dd_contact_id) as contacts,
  COUNT(distinct dd_company_id) as companies
FROM
   `table.events`
WHERE
  dd_tracking_type NOT IN ('exposure')
  AND event.url_clean IS NOT NULL
  AND event_name = 'page_view'
  AND timestamp >= '2025-05-01'
  AND timestamp < '2025-06-01'
GROUP BY
  event.url_clean
ORDER BY
  views DESC

This SQL example helps you measure URL impact with Influenced Value and Prospects

WITH sessions_per_url AS (
  SELECT
    event.url_clean AS url,
    COUNT(DISTINCT dd_session_activity_id) AS sessions
  FROM
    `table.events`
  WHERE
    event.url_clean IS NOT NULL
    AND timestamp >= '2025-05-01'
    AND timestamp < '2025-06-01'
  GROUP BY
    url
),
influenced_data_raw AS (
  SELECT
    e.event.url_clean AS url,
    st.dd_stage_id,
    MIN(s.value) AS revenue_value
  FROM
    `table.events` AS e,
    UNNEST(e.stages) AS st
  INNER JOIN
    `table.stages` AS s ON st.dd_stage_id = s.dd_stage_id
  WHERE
    st.name = 'MQL'
    AND e.timestamp <= s.timestamp
    AND e.timestamp >= '2025-05-01'
    AND e.timestamp < '2025-06-01'
    AND e.event.url_clean IS NOT NULL
  GROUP BY
    url,
    st.dd_stage_id
),
aggregated_influence AS (
  SELECT
    url,
    COUNT(DISTINCT dd_stage_id) AS influenced_prospects,
    SUM(revenue_value) AS influenced_value
  FROM
    influenced_data_raw
  GROUP BY
    url
)
SELECT
  s.url,
  s.sessions,
  COALESCE(i.influenced_prospects, 0) AS influenced_prospects,
  COALESCE(i.influenced_value, 0) AS influenced_value
FROM
  sessions_per_url s
LEFT JOIN
  aggregated_influence i
ON
  s.url = i.url
ORDER BY
  influenced_value DESC