aw_user.sql

SELECT
  -- user_id (dimension | User ID)
  user_id,
  -- user_pseudo_id (dimension | the pseudonymous id (e.g., app instance ID) for the user)
  user_pseudo_id,
  -- user_first_touch_timestamp (dimension | the time (in microseconds) at which the user first opened the app/website)
  TIMESTAMP_MICROS(user_first_touch_timestamp) AS user_first_touch_timestamp,
  -- user_string_value (dimension | the string value of the user property)
  (
  SELECT
    value.string_value
  FROM
    UNNEST(user_properties)
  WHERE
    -- Insert a parameter key
    key = '<insert key>') AS user_string_value,
  -- user_int_value (metric | the integer value of the user property)
  (
  SELECT
    value.int_value
  FROM
    UNNEST(user_properties)
  WHERE
    -- Insert a parameter key
    key = '<insert key>') AS user_int_value,
  -- user_float_value (metric | the float value of the user property)
  (
  SELECT
    value.float_value
  FROM
    UNNEST(user_properties)
  WHERE
    -- Insert a parameter key
    key = '<insert key>') AS user_float_value,
  -- user_double_value (metric | the double value of the user property)
  (
  SELECT
    value.double_value
  FROM
    UNNEST(user_properties)
  WHERE
    -- Insert a parameter key
    key = '<insert key>') AS user_double_value,
  -- user_set_timestamp_micros (dimension | the time (in microseconds) at which the user property was last set)
  TIMESTAMP_MICROS((
    SELECT
      value.set_timestamp_micros
    FROM
      UNNEST(user_properties)
    WHERE
      -- Insert a parameter key
      key = '<insert key>')) AS user_set_timestamp_micros,
  -- user_ltv_revenue (metric | the Lifetime Value (revenue) of the user)
  user_ltv.revenue AS user_ltv_revenue,
  -- user_ltv.currency (dimension | the Lifetime Value (currency) of the user)
  user_ltv.currency
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))
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10
LIMIT
  10