BigQuery Abfrage für Google Analytics 4 (GA4): Datum und Zeit: Dimensionen und Metriken

Berechnete Datums- und Zeit-Dimensionen

date
year
iso year
month of year
month of the year
week of year
week of the year
iso week of the year
iso week of iso year
day of the month
day of week
day of week name
hour
minute
hour of day
date hour and minute

Beispiel-Abfrage

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)),format("%02d",extract(minute from timestamp_micros(event_timestamp)))) as date_hour_and_minute
from
    -- change this to your google analytics 4 export location in bigquery
    `privat-327611.analytics_266663932.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20211224' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))