aw_time.sql

SELECT
  -- Date (dimension)
  event_date AS date,
  -- Year (dimension)
  FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d",
      event_date)) AS year,
  -- ISO Year (dimension)
  FORMAT_DATE('%G', PARSE_DATE("%Y%m%d",
      event_date)) AS iso_year,
  -- Month of Year (dimension)
  FORMAT_DATE('%Y%m', PARSE_DATE("%Y%m%d",
      event_date)) AS month_of_year,
  -- Month of the Year (dimension)
  FORMAT_DATE('%m', PARSE_DATE("%Y%m%d",
      event_date)) AS month_of_the_year,
  -- Week of Year (dimension)
  FORMAT_DATE('%Y%U', PARSE_DATE("%Y%m%d",
      event_date)) AS week_of_year,
  -- Week of the Year (dimension)
  FORMAT_DATE('%U', PARSE_DATE("%Y%m%d",
      event_date)) AS week_of_the_year,
  -- ISO Week of the Year (dimension)
  FORMAT_DATE('%W', PARSE_DATE("%Y%m%d",
      event_date)) AS iso_week_of_the_year,
  -- ISO Week of ISO Year (dimension)
  FORMAT_DATE('%G%W', PARSE_DATE("%Y%m%d",
      event_date)) AS iso_week_of_iso_year,
  -- Day of the Month (dimension)
  FORMAT_DATE('%d', PARSE_DATE("%Y%m%d",
      event_date)) AS day_of_the_month,
  -- Day of Week (dimension)
  FORMAT_DATE('%w', PARSE_DATE("%Y%m%d",
      event_date)) AS day_of_week,
  -- Day of Week Name (dimension)
  FORMAT_DATE('%A', PARSE_DATE("%Y%m%d",
      event_date)) AS day_of_week_name,
  -- Hour of Day(dimension)
  CONCAT(event_date,CAST(FORMAT("%02d",EXTRACT (hour
        FROM
          TIMESTAMP_MICROS(event_timestamp))) AS String)) AS hour_of_day,
  -- Hour (dimension)
  FORMAT("%02d",EXTRACT (hour
    FROM
      TIMESTAMP_MICROS(event_timestamp))) AS hour,
  -- Minute (dimension)
  FORMAT("%02d",EXTRACT (Minute
    FROM
      TIMESTAMP_MICROS(event_timestamp))) AS minute,
  -- Date Hour and Minute (dimension)
  CONCAT(CONCAT(event_date,CAST(FORMAT("%02d",EXTRACT (hour
          FROM
            TIMESTAMP_MICROS(event_timestamp))) AS String)),EXTRACT (Minute
    FROM
      TIMESTAMP_MICROS(event_timestamp))) AS date_hour_and_minute
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))
LIMIT
  10