Content

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

SELECT
  event. url_clean,
  COUNT(event_name) AS views
FROM
  `table.events`
WHERE
  dd_tracking_type NOT IN ('exposure')
  AND event. url_clean IS NOT NULL
  AND dd_is_primary_event IS TRUE
  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(event_name) AS views,
  COUNT(distinct dd_session_id) as sessions,
  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
  dd_tracking_type NOT IN ('exposure')
  AND event. url_clean IS NOT NULL
  AND dd_is_primary_event IS TRUE
  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
    urlClean AS url,
    COUNT(DISTINCT dd_session_id) AS sessions
  FROM
    `table.events`
  WHERE
    urlClean IS NOT NULL
    AND timestamp >= '2025-05-01'
    AND timestamp < '2025-06-01'
  GROUP BY
    url
),
influenced_data_raw AS (
  SELECT
    e.urlClean AS url,
    e.email AS email,
    st.dealId AS deal_id,
    st.value AS revenue_value,
    ROW_NUMBER() OVER (PARTITION BY e.urlClean, st.dealId) AS rn
  FROM
    `table.events` AS e,
    UNNEST(e.stages) AS st
  WHERE
    st.revenueModel = 'MQL'
    AND e.timestamp >= '2025-05-01'
    AND e.timestamp < '2025-06-01'
    AND e.urlClean IS NOT NULL
),
influenced_data_deduped AS (
  SELECT
    url,
    email,
    revenue_value
  FROM
    influenced_data_raw
  WHERE
    rn = 1
),
aggregated_influence AS (
  SELECT
    url,
    COUNT(DISTINCT email) AS influenced_prospects,
    SUM(revenue_value) AS influenced_value
  FROM
    influenced_data_deduped
  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