Funktionen sind in vielen Programmiersprachen wie JavaScript, C oder Python fundamentale Bestandteile, um Code wiederzuverwenden. So vermeidet man es denselben Code mehrfach zuschreiben und muss bei Anpassungen nur eine zentrale Stelle bearbeiten.
Auch in BigQuery SQL gibt es die Möglichkeit Funktionen zuschreiben, und dadurch zum Beispiel komplexes „case when“ statements zu verwenden. So ist ein Praxisbeispiel die Umwandlung von source/medium Kombinationen in den GA4 Rohdaten, um das Channelgrouping aus dem GA4 Interface nachzustellen.
BigQuery UDFs können entweder in SQL, aber auch in JavaScript geschrieben werden. Die Nutzung von JavaScript ermöglicht die Nutzung von nativen JavaScript-Funktionen, die in vielen Fällen mehr Flexibilität liefert als SQL.
So können zum Beispiel Bestandteile einer HTML kodierte URL sehr einfach mit einer JavaScript UDF dekodiert werden. So können z.B. Vorkommnisse wie „email%20boilerplate“ zu „email boilerplate“ transformiert, da z.B. „%20“ einem Whitespace in ASCII entspricht.
JavaScript UDF
In GA4 kann eine Session mehrere Kombinationen aus source / medium enthalten. Das Erhalten der Reihenfolge der Kombinationen ist durchaus wichtig, da Google Sessions bei fehlenden source / medium Parametern die letzte Kombination der vorangegangen Session nutzt.
Mit folgendem SQL Code können wir die source / medium Kombinationen pro Session aggregieren. Dabei haben wir aber auch viele Doppelungen, die wir nicht benötigen.
SELECT
MIN(DATE(timestamp_micros(event_timestamp))) as session_date,
session_id,
array_agg(if(source is not null,struct(event_timestamp as touch_timestamp,source as source,medium as medium,campaign as campaign,page_location),null)ignore nulls) as session_touchpoints,
current_datetime() as created_at
from(
select
event_timestamp,
farm_fingerprint(concat(user_pseudo_id,(select value.int_value from unnest(event_params)where key ="ga_session_id"))) as session_id,
(select value.string_value from unnest(event_params) where key ="source") as source,
(select value.string_value from unnest(event_params) where key ="medium") as medium,
(select value.string_value from unnest(event_params) where key ="campaign") as campaign,
(select value.string_value from unnest(event_params)where key ="page_location") as page_location,
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
where
(select value.int_value from unnest(event_params)where key ="ga_session_id") is not null
and event_name not in ('session_start', 'first_visit')
)
group by 2Wenn wir diese Query ausführen erhalten wir Doppelungen, welche keine echten Mehrwert bieten. Die einzigartige Reihenfolge reicht vollkommen aus, um eine Attribution anzuwenden.
Um das Array zu reduzieren und von Doppelungen zu befreien, ohne die Reihenfolge der unterschiedlichen Kombinationen zu verändern, können wir folgende JavaScript UDF nutzen. Dabei übergeben wir das ungefilterte Array und entfernen alle Doppelungen, ohne die Reihenfolge abzuändern.
CREATE or replace FUNCTION ${self()}(originalArray ARRAY<STRUCT<touch_timestamp INT64, source STRING, medium STRING, campaign STRING, page_location STRING>>)
RETURNS ARRAY<STRUCT<touch_timestamp INT64, source STRING, medium STRING, campaign STRING, page_location STRING>>
LANGUAGE js
AS r"""
if(originalArray==null) return;
let newArray = [];
for (let index = 0; index < originalArray.length; index++) {
if(index == 0){
newArray.push(originalArray[index]);
}
else if(originalArray[index-1].source != originalArray[index].source || originalArray[index-1].medium != originalArray[index].medium || originalArray[index-1].campaign != originalArray[index].campaign){
newArray.push(originalArray[index]);
}
}
return newArray;
"""
;Dabei spielt uns JavaScript in die Karten, denn die Modifikation des Arrays mit SQL wäre hier wesentlich zeitaufwendiger. Wir übergeben an die Funktion ein Array, welches aus Objekten besteht und die source, medium und die page_location enthält. Wir pushen nur Objekte in das neue Array, wenn die Kombination aus source/medium nicht dem letzten Objekt im Array entspricht, um Doppelungen zu vermeiden.
Die Ergebnisse können wir dann nutzen, um die Attribution aus dem GA4 Interface nachzustellen, oder unsere eigene Attributionslogik anzuwenden. Wir erhalten so einzigartige Kombinationen aus source/medium für jede Session in GA4 und sortieren das Ganze nach der Reihenfolge des Auftretens.
Die UDF können wir über die Persistent Function ID aufrufen, die sich aus GCP project id, dataset id und name der Routine zusammensetzt. So können wir z.B. bei unserem SQL Code einen Verweis auf die Funktion nutzen und das aggregierte Array direkt bereinigen. Hierfür übergeben wir den Output der array_agg Funktion an unsere JavaScript UDF:
`MY_PROJECT.MY_DATASET.UDF_NAME`(array_agg(if(source is not null,struct(event_timestamp as touch_timestamp,source as source,medium as medium,campaign as campaign,page_location),null)ignore nulls)) as session_touchpoints,
SQL UDF
Ein Anwendungsfall für die Nutzung von SQL UDFs in Verbindung mit den GA4 Rohdaten ist die Erstellung eines Channelgroupings. Hierfür werden unterschiedliche Parameter wie die „source“, „medium“, „page_location“ und „page_referrer“ an die Funktion übergeben und in einem großen „case when“ statement verarbeitet.
create or replace function ${self()}(tsource string, medium string, campaign string, page_location string, page_referrer string) as (
case
-- we deal with redacted data in the ga4 demo dataset
when (medium = '(data deleted)')
then 'data_deleted'
when (regexp_contains(campaign, r'^(.*shop.*)$')
and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$'))
or regexp_contains(page_location, r'gclid')
then 'shopping_paid'
when regexp_contains(tsource, r'^(google|bing)$')
and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$')
then 'search_paid'
...Dem aufmerksamen Beobachter wird aufgefallen sein, dass wir die „page_location“ nach Vorkommen des gclid URL Parameter durchsuchen. Das ist leider aktuell, Stand August 2023, noch notwendig, da Google fälschlicherweise source/medium Kombination, die eigentlich google/cpc wären, google/organic zuordnet. Dieser Fehler wird im Webinterface bereits korrigiert, aber nicht in den Rohdaten. Wir dürfen uns bedauerlicherweise selber darum kümmern. Wie war das noch? „Lange lebe die clientseitige Verarbeitung?!“
Wie man in dem oberen Ausschnitt erkennen kann, geben wir keine Variable zurück, sondern ein komplettes „case when“ SQL statement, welches im Code, wo wir die Funktion aufrufen, eingefügt wird.
Das Aufrufen der Funktion funktioniert auf die gleiche Weise wie auch bei der JavaScript Variante. Die Persistent Function ID mit den zu übergebenen Parametern in Klammern.
BigQuery UDFs in Dataform
UDFs können auch in Dataform gemanagt werden. Um diese anzulegen, benötigen wir eine .sqlx Datei und einen config Block, der wie folgt konfiguriert ist:
config {
type: "operations",
hasOutput: true,
description: "Defines channels with campaign, medium and source"
}Danach kann dann wie auch im SQL Workspace der Code zum Erstellen der UDF hinterlegt werden.
Zusammenfassung
User Defined Functions eigenen sich vor allem für Anwendungen, wenn der gleiche Code an unterschiedlichen Stellen wiederverwendet werden soll. Änderungen können einfach eingepflegt werden, ohne den aufrufenden Code bearbeiten zu müssen. Die Nutzung in Dataform ermöglicht die Nutzung in SQL Pipelines. Anders als native JavaScript Funktionen in Dataform sind UDFs auch außerhalb von Dataform nutzbar.