Skip to content

Instantly share code, notes, and snippets.

View brunomertins's full-sized avatar

Bruno Mertins brunomertins

View GitHub Profile
@brunomertins
brunomertins / DynamicDateRange.sql
Created March 20, 2023 14:12
Dynamic date range - BigQuery
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
brunomertins / StaticDateRange.sql
Last active March 20, 2023 14:11
Static date range - BigQuery
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_table_suffix BETWEEN '20160801'
AND '20170801'
Field name Data type Description
App
app_info RECORD A record of information on the app.
app_info.id STRING The package name or bundle ID of the app.
app_info.firebase_app_id STRING The Firebase App ID associated with the app
app_info.install_source STRING The store that installed the app.
app_info.version STRING The app's versionName (Android) or short bundle version.
Device
device RECORD A record of device information.
device.category STRING The device category (mobile, tablet, desktop).
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
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)
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)
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_*`
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)
(
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,
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