dataUnNest
krisjan-oldekamp
/ google_analytics_bigquery_channel_grouping_function_advanced_ga4.sql
Last active
November 15, 2023 18:13
Define custom Channel Groupings in a reusable "User Defined Function"(UDF) to make your life easier when working with Google Analytics 4 data in BigQuery. Full article on stacktonic.com
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Author: Krisjan Oldekamp | |
| -- https://stacktonic.com/article/google-analytics-4-and-big-query-create-custom-channel-groupings-in-a-reusable-sql-function | |
| create or replace function `<your-project>.<your-dataset>.channel_grouping`(tsource string, medium string, campaign string) as ( | |
| case | |
| when (tsource = 'direct' or tsource is null) | |
| and (regexp_contains(medium, r'^(\(not set\)|\(none\))$') or medium is null) | |
| then 'direct' | |
| when regexp_contains(campaign, r'^(.*shop.*)$') | |
| and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') |