brunomertins

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

brunomertins

SELECT
  *
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20160801'
  AND '20170801'

brunomertins

brunomertins

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

brunomertins

SELECT
  -- geo.continent (dimension | the continent from which events were reported, based on IP address)
  geo.continent,
  -- geo.sub_continent (dimension | the subcontinent from which events were reported, based on IP address)
  geo.sub_continent,
  -- geo.country (dimension | the country from which events were reported, based on IP address)
  geo.country,
  -- geo.region (dimension | the region from which events were reported, based on IP address)
  geo.region,
  -- geo.city (dimension | the city from which events were reported, based on IP address)

brunomertins

SELECT
  -- app_info.id (dimension | the package name or bundle ID of the app)
  app_info.id,
  -- app_info.version (dimension | the app's versionName (Android) or short bundle version)
  app_info.version,
  -- app_info.install_store (dimension | the store that installed the app)
  app_info.install_store,
  -- app_info.firebase_app_id (dimension | the Firebase App ID associated with the app)
  app_info.firebase_app_id,
  -- app_info.install_source (dimension | the source that installed the app)

brunomertins

SELECT
  -- traffic_source.name (dimension | name of the marketing campaign that first acquired the user)
  traffic_source.name,
  -- traffic_source.medium (dimension | name of the medium (paid search, organic search, email, etc.) that first acquired the user)
  traffic_source.medium,
  -- traffic_source.source (dimension | name of the network that first acquired the user)
  traffic_source.source
FROM
  -- Change this to your Google Analytics 4 export location in BigQuery
  `ga4.analytics_1234567890.events_*`

brunomertins

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)
  (

brunomertins

WITH
  -- Subquery to define static and/or dynamic start and end date for the whole query
  period AS (
  SELECT
    '20200220' AS start_date,
    FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date),
  -- Subquery to prepare and calculate user data
  user AS (
  SELECT
    user_pseudo_id,

brunomertins

WITH
  -- Subquery to define static and/or dynamic start and end date for the whole query
  period AS (
  SELECT
    '20200220' AS start_date,
    FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date),
  -- Subquery to prepare and calculate engagement data
  engagement AS (
  SELECT
    COUNT(DISTINCT