Skip to content

Instantly share code, notes, and snippets.

View krisjan-oldekamp's full-sized avatar

Krisjan O. krisjan-oldekamp

View GitHub Profile
-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/enrich-a-single-customer-view-with-google-analytics-4-big-query-data
declare lookback_window int64 default 365; -- how many days to lookback into the ga4 dataset to calculate profiles
-- udf: channel grouping (you could put this in a permanent function)
-- also see https://stacktonic.com/article/google-analytics-4-and-big-query-create-custom-channel-groupings-in-a-reusable-sql-function
create temporary function channel_grouping(tsource string, medium string, campaign string) as (
case
when (tsource = '(direct)' or tsource is null)
@krisjan-oldekamp
krisjan-oldekamp / google_analytics_bigquery_user_mapping_table_customer.sql
Last active June 12, 2024 13:12
How to create a user mapping table (or Identity Graph) based on all the available user identifiers in the Google Analytics 4 BigQuery exports (like device-IDs or customer-IDs). Full article on stacktonic.com
-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/create-a-user-mapping-table-based-on-the-google-analytics-4-big-query-dataset
declare lookback_window int64 default 90; -- how many days to lookback into the dataset to search for ids (compared to today)
-- udf: deduplicate array of struct
create temp function dedup(arr any type) as ((
select
array_agg(t)
from (
@krisjan-oldekamp
krisjan-oldekamp / google_analytics_bigquery_channel_attribution_build_models.py
Last active October 3, 2024 15:58
Get actionable insights on your channel performance by building custom attribution models using Google Analytics 4 data in BigQuery. Full article on stacktonic.com
###################################################
# Author Krisjan Oldekamp / Stacktonic.com
# Email krisjan@stacktonic.com
# Article https://stacktonic.com/article/build-a-data-driven-attribution-model-using-google-analytics-4-big-query-and-python
####################################################
#pip install marketing_attribution_models
#pip install --upgrade 'google-cloud-bigquery[bqstorage,pandas]'
#pip install pyarrow -> newest version!
@krisjan-oldekamp
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
-- 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.*)$')
@krisjan-oldekamp
krisjan-oldekamp / google_bigquery_backup_views_scheduled_queries_git.py
Last active May 8, 2024 10:54
Backup BigQuery Views and Scheduled Queries to a Git repository using Python. Full article on stacktonic.com
############################################################
# Author Krisjan Oldekamp / Stacktonic.com
# Email krisjan@stacktonic.com
# Article https://stacktonic.com/article/backup-your-valuable-big-query-views-and-scheduled-queries-using-python
############################################################
import os
import git
import google.oauth2.service_account
from google.cloud import bigquery
@krisjan-oldekamp
krisjan-oldekamp / google_bigquery_merchant_center_export_feed_enrichment.sql
Last active February 21, 2024 09:56
Enrich your product feed with Google Shopping Insights, like price competitiveness or demand, using Merchant Center BigQuery exports. Full article on stacktonic.com.
-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/create-advanced-google-shopping-insights-using-merchant-center-big-query-exports
-- set variable to change the fetch date easily
declare gmc_fetch_date date default date('2021-09-05');
with
-- get productfeed uploaded in gmc for specific date
gmc_products as (
select
@krisjan-oldekamp
krisjan-oldekamp / google_dv360_api_activate_pause_lineitems.py
Last active November 16, 2022 05:03
Activate and pause Google Display & Video 360 (DV360) line items, using the DV360 API and Python
###################################################
# Author Krisjan Oldekamp / Stacktonic.com
# Email krisjan@stacktonic.com
# Article https://stacktonic.com/article/how-to-activate-and-pause-line-items-in-google-dv-360-using-python
####################################################
import os
from urllib.error import HTTPError
from googleapiclient import discovery
from oauth2client.service_account import ServiceAccountCredentials