Krisjan O. krisjan-oldekamp

## google_analytics_bigquery_customer_view_360.sql
-- 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)

## google_analytics_bigquery_user_mapping_table_customer.sql
-- 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 (

## google_analytics_bigquery_channel_attribution_build_models.py
###################################################
# 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!

## 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.*)$')

## 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

## 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

## 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
	-- 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)
	-- 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 (
	###################################################
	# 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!
	-- 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.*)$')
	-- 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