Events

'Events' report looks at all tracked events

This SQL example helps you find # of events, sessions, visitors, contacts and companies

SELECT
  COUNT(DISTINCT(dd_event_activity_id)) AS events,
  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
  EXTRACT(YEAR
  FROM
    timestamp) = EXTRACT(YEAR
  FROM
    CURRENT_DATE())
  AND EXTRACT(MONTH
  FROM
    timestamp) = EXTRACT(MONTH
  FROM
    CURRENT_DATE())

This SQL example helps you find event conversions, influenced prospects and influenced value

WITH
  conversions AS (
  SELECT
    COUNT(*) AS conversions
  FROM
    `table.events`
  WHERE
    event.is_conversion IS TRUE
    AND EXTRACT(YEAR
    FROM
      timestamp) = EXTRACT(YEAR
    FROM
      CURRENT_DATE())
    AND EXTRACT(MONTH
    FROM
      timestamp) = EXTRACT(MONTH
    FROM
      CURRENT_DATE()) ),
  influenced AS (
  SELECT
    COUNT(DISTINCT dd_stage_id) AS influenced_prospects,
    SUM(stage_value) AS influenced_value
  FROM (
    SELECT
      st.dd_stage_id,
      MIN(s.value) AS stage_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 EXTRACT(YEAR
      FROM
        e.timestamp) = EXTRACT(YEAR
      FROM
        CURRENT_DATE())
      AND EXTRACT(MONTH
      FROM
        e.timestamp) = EXTRACT(MONTH
      FROM
        CURRENT_DATE())
    GROUP BY
      st.dd_stage_id ) )
SELECT
  c.conversions,
  i.influenced_prospects,
  i.influenced_value
FROM
  conversions c
CROSS JOIN
  influenced i

This SQL example helps you find # of events grouped by event name

SELECT
  event_name,
  COUNT(DISTINCT(dd_event_activity_id)) AS events
FROM
  `table.events`
WHERE
  EXTRACT(YEAR
  FROM
    timestamp) = EXTRACT(YEAR
  FROM
    CURRENT_DATE())
  AND EXTRACT(MONTH
  FROM
    timestamp) = EXTRACT(MONTH
  FROM
    CURRENT_DATE())
GROUP BY
  event_name
ORDER BY
  events DESC

This SQL example helps you find ad impression volume from the events table (matches Analytics Hub Impressions metric: sum quantity on exposure rows)

SELECT
  SUM(quantity) AS impressions
FROM
  `table.events`
WHERE
  dd_tracking_type = 'exposure'
  AND EXTRACT(YEAR
  FROM
    timestamp) = EXTRACT(YEAR
  FROM
    CURRENT_DATE())
  AND EXTRACT(MONTH
  FROM
    timestamp) = EXTRACT(MONTH
  FROM
    CURRENT_DATE())