BigQuery – User Defined Functions

Functions are a fundamental building block in many programming languages like JavaScript, C, or Python that let us reuse code. They keep us from writing the same code multiple times and let us update logic in one central place.

BigQuery SQL also lets us write functions, which is useful for things like complex “case when” statements. A practical example is converting source/medium combinations from the GA4 raw data to recreate the channel grouping from the GA4 interface.

BigQuery UDFs can be written in either SQL or JavaScript. Using JavaScript gives us access to native JavaScript functions, which is often more flexible than SQL.

For example, parts of an HTML-encoded URL can easily be decoded with a JavaScript UDF. Occurrences like email%20boilerplate can be transformed into email boilerplate, since %20 corresponds to a whitespace character in ASCII.

JavaScript UDF

In GA4 a session can contain multiple source / medium combinations. Preserving the order of those combinations matters, because Google sessions reuse the last combination of the previous session when source / medium parameters are missing.

With the following SQL we can aggregate the source / medium combinations per session. We end up with a lot of duplicates, though, that we don’t really need.

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 2

Running this query gives us duplicates that don’t add real value. The unique sequence is enough to apply attribution.

To shrink the array and get rid of duplicates without changing the order of the different combinations, we can use the following JavaScript UDF. We pass in the unfiltered array and remove all duplicates without changing the order.

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;
"""
;

JavaScript plays into our hands here, because modifying the array with SQL would be much more time-consuming. We pass an array of objects to the function — each object has source, medium, and page_location. We only push an object into the new array if its source/medium combination differs from the previous one in the array, which removes the duplicates.

We can then use the result to recreate the GA4 attribution from the interface, or to apply our own attribution logic. We end up with unique source/medium combinations per GA4 session, sorted by the order in which they occurred.

The UDF can be called via the persistent function ID, which is composed of GCP project id, dataset id, and the routine name. So we can reference the function from our SQL and clean the aggregated array directly. We pass the output of array_agg into our 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

A typical use case for SQL UDFs together with the GA4 raw data is building a channel grouping. Different parameters like source, medium, page_location, and page_referrer are passed to the function and processed in a large case when statement.

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'
...

The attentive reader will have spotted that we check the page_location for the presence of the gclid URL parameter. As of August 2023, this is unfortunately still necessary because Google incorrectly assigns source/medium combinations that should be google/cpc to google/organic. The fix has been applied in the web interface but not in the raw data, so we have to deal with it ourselves. How did the saying go again? “Long live client-side processing?!”

As you can see in the snippet above, we’re not returning a value but a complete case when SQL statement that gets inlined in the calling code.

Calling the function works the same way as with the JavaScript variant — the persistent function ID followed by the parameters in parentheses.

BigQuery UDFs in Dataform

UDFs can also be managed in Dataform. To create one, we need a .sqlx file with a config block configured like this:

config {
    type: "operations",
    hasOutput: true,
    description: "Defines channels with campaign, medium and source"
}

After that you can put the UDF creation code there, just like in the SQL workspace.

Summary

User defined functions are particularly useful when the same code needs to be reused in multiple places. Updates can be made centrally without touching the calling code. Using them in Dataform allows them to be reused inside SQL pipelines. Unlike native JavaScript functions in Dataform, UDFs can also be used outside of Dataform.