Skip to content

Instantly share code, notes, and snippets.

View krisjan-oldekamp's full-sized avatar

Krisjan O. krisjan-oldekamp

View GitHub Profile
@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
View google_analytics_bigquery_channel_grouping_function_advanced_ga4.sql
-- 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 January 9, 2024 21:43
Backup BigQuery Views and Scheduled Queries to a Git repository using Python. Full article on stacktonic.com
View google_bigquery_backup_views_scheduled_queries_git.py
############################################################
# 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.
View google_bigquery_merchant_center_export_feed_enrichment.sql
-- 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
View google_dv360_api_activate_pause_lineitems.py
###################################################
# 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