ga4_purchase_data_gbq.sql

select
  timestamp_micros(event_timestamp) as timestamp_utc, -- converts the timestamp into UTC
  format_datetime("%Y-%d-%m %H-%M-%S %Z", (timestamp_micros(event_timestamp)), 'America/New_York') as timestamp_newyork, --timestamp in NY
  event_name,
  (select value.string_value from unnest(event_params) where key = 'transaction_id') as transaction_id, -- gets the string value
  (select value.int_value from unnest(event_params) where key = 'value') as transaction_value, --gets the integer value
  (select value.string_value from unnest(event_params) where key = 'currency') as currency,
  (select value.string_value from unnest (event_params) where key = 'page_title') as page_title,
  (select value.string_value from unnest (event_params) where key = 'page_location') as url
  
from
  `[projectId].[dataset].events_*` --wildcard to query both events_ & events_intraday_ tables
  
where
  regexp_extract(_table_suffix, r'[0-9]+') between format_date('%Y%m%d',date_sub(current_date(), interval 30 day)) --get the data for last 30 days from the partitioned table
  and format_date('%Y%m%d', current_date()) --includes today
  and event_name = 'purchase' --filtering only the purchase event
  
order by
  timestamp_utc desc