Skip to content

Instantly share code, notes, and snippets.

View ZordnajelA's full-sized avatar

Alejandro Zielinsky ZordnajelA

5 followers · 1 following
View GitHub Profile
@ZordnajelA
ZordnajelA / ga4_traffic_source_session_level.sql
Last active February 19, 2024 03:30
Using SQL FIRST_VALUE() window function to replicate GA4 traffic source attribution behavior
FIRST_VALUE(
(SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
key = 'name_of_traffic_source_parameter'
--source, medium, campaign, content, term, campaign_id
)
@ZordnajelA
ZordnajelA / ga4_session_scope_custom_dimension.sql
Last active February 19, 2024 06:24
Using SQL window functions to replicate Google Analytics custom dimensions behavior
LAST_VALUE(
(SELECT
value.string_value --value.int_value, value.float_value, value.double_value
FROM
UNNEST(event_params)
WHERE
key = 'name_of_event_parameter_to_use_as_dimension'
)
IGNORE nulls
) OVER (
@ZordnajelA
ZordnajelA / ga4_parameter_value_function.sql
Last active July 26, 2023 16:12
Bigquery SQL Function to obtain the value of a given parameter (as a string) from a Google Analytics 4 event and its original value type in case it's needed
CREATE OR REPLACE FUNCTION `YOUR_PROJECT.YOUR_DATASET.GA4_parameter_value_and_type`(
parameter_key_to_be_queried STRING,
event_params ARRAY<STRUCT<key STRING,
value STRUCT<
string_value STRING,
int_value INT64,
float_value FLOAT64,
double_value FLOAT64
>
>