aw_event.sql

SELECT
  -- event_date (dimension | the date on which the event was logged)
  PARSE_DATE('%Y%m%d',
    event_date) AS event_date,
  -- event_timestamp (dimension | the time (in microseconds, UTC) at which the event was logged on the client)
  TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
  -- event_name (dimension | the name of the event)
  event_name,
  -- event_key (dimension | the event parameter's key)
  (
  SELECT
    key
  FROM
    UNNEST(event_params)
  WHERE
    -- Change key to select another parameter
    key = 'page_location') AS event_key,
  -- event_string_value (dimension | the string value of the event parameter)
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    -- Change key to select another parameter
    key = 'page_location') AS event_string_value,
  -- event_int_value (metric | the integer value of the event parameter)
  (
  SELECT
    value.int_value
  FROM
    UNNEST(event_params)
  WHERE
    -- Change key to select another parameter
    key = 'page_location') AS event_int_value,
  -- event_float_value (metric | the float value of the event parameter)
  (
  SELECT
    value.float_value
  FROM
    UNNEST(event_params)
  WHERE
    -- Change key to select another parameter
    key = 'page_location') AS event_float_value,
  -- event_double_value (metric | the double value of the event parameter)
  (
  SELECT
    value.double_value
  FROM
    UNNEST(event_params)
  WHERE
    -- Change key to select another parameter
    key = 'page_location') AS event_double_value,
  -- event_previous_timestamp (dimension | the time (in microseconds, UTC) at which the event was previously logged on the client)
  TIMESTAMP_MICROS(event_previous_timestamp) AS event_previous_timestamp,
  -- event_value_in_usd (metric | the currency-converted value (in USD) of the event's "value" parameter)
  event_value_in_usd,
  -- event_bundle_sequence_id (dimension | The sequential ID of the bundle in which these events were uploaded)
  event_bundle_sequence_id,
  -- event_server_timestamp_offset (dimension | timestamp offset between collection time and upload time in micros)
  event_server_timestamp_offset,
  -- stream_id (dimension | stream ID)
  stream_id,
  -- platform (dimension | Platform, i.e. 'WEB' or 'ANDROID')
  platform,
  -- event_dimensions.hostname (dimension | hostname)
  event_dimensions.hostname
FROM
  -- Change this to your Google Analytics 4 export location in BigQuery
  `ga4.analytics_1234567890.events_*`
WHERE
  -- Define static and/or dynamic start and end date
  _table_suffix BETWEEN '20200220'
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  -- Change event_name to select another event
  AND event_name = 'page_view'
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14,
  15
LIMIT
  10