【BigQuery】効率的なデータ操作のためのUNNESTとユーザー定義関数の活用法

Google Analytics 4（GA4）データをBigQueryに取り込む際、REPEATEDモードでデータが格納されることがあります。このようなデータ構造は、複数の値を1つのセルに格納するためのものであり、効率的なデータ操作が必要です。

REPEATEDモードとは

REPEATEDモードは、1つのセルに複数の値を含めるためのデータモードです。GA4のデータでは、イベントパラメータやユーザープロパティなどがこの形式で格納されることがあります。しかし、このままではデータの解析やクエリが困難です。

解決策:

UNNEST演算子を使用する

UNNEST演算子を使用することで、REPEATEDモードのデータを展開し、個々の要素を別々の行として取り出すことができます。これにより、データをより容易に操作することが可能となります。

ユーザー定義関数（UDF）の活用

さらに複雑なデータ操作が必要な場合、ユーザー定義関数（UDF）を活用することが有効です。JavaScriptやSQLを使用してUDFを定義し、独自の処理を適用することができます。これにより、より柔軟なデータ操作が可能となります。

/* UNNEST用のユーザー定義関数 */
    CREATE TEMP FUNCTION StringFromEventParams(event_params ANY TYPE, key_name STRING)
    RETURNS STRING
    AS((
        SELECT
            value.string_value
        FROM
            UNNEST(event_params)
        WHERE
            key = key_name
    ));

    CREATE TEMP FUNCTION NumberFromEventParams(event_params ANY TYPE, key_name STRING)
  -- RETURNS の型は省略できる
  AS((
      SELECT
        COALESCE(value.int_value, value.float_value, value.double_value)
        /* COALESCE は配列内の最初の非 null 値を取得するもの。int, float, doubleは共通のスーパータイプに変換可能なのでできる（stringは含められない）
        すべてfloat型として返すので注意すること*/
      FROM
        UNNEST(event_params)
      WHERE
        key = key_name
  ));


/* 使用例 */
SELECT
    event_timestamp
    ,date(timestamp_micros(event_timestamp),'Asia/Tokyo') AS date -- イベントの発生日付を選択
    ,event_name
    --event_paramsのUNNEST
    ,StringFromEventParams(event_params, 'page_location') AS page_location
    ,StringFromEventParams(event_params, 'page_referrer') AS page_referrer
    ,StringFromEventParams(event_params, 'link_url') AS link_url --クリックまたはファイルダウンロード先URL
    ,StringFromEventParams(event_params, 'link_domain') AS link_domain --クリック先ドメイン
    ,StringFromEventParams(event_params, 'link_text') AS link_text --ファイルダウンロードしたリンク名
    ,StringFromEventParams(event_params, 'page_title') AS page_title
    ,CAST(NumberFromEventParams(event_params, 'ga_session_id') AS INT64) AS ga_session_id --後続でPARTITIONを行うためINT型にする
    ,NumberFromEventParams(event_params, 'ga_session_number') AS ga_session_number
    ,StringFromEventParams(event_params, 'source') AS source
    ,StringFromEventParams(event_params, 'term') AS term
    ,StringFromEventParams(event_params, 'medium') AS medium
    ,StringFromEventParams(event_params, 'campaign') AS campaign
    ,StringFromEventParams(event_params, 'user_property') AS user_property
    ,FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC
        (timestamp_micros(event_timestamp), SECOND), 'Asia/Tokyo') AS time --秒表示時間
    --event_paramsのUNNEST(追加分)
    ,StringFromEventParams(event_params, 'currency') AS currency
    ,NumberFromEventParams(event_params, 'value') AS value
    ,StringFromEventParams(event_params, 'session_engaged') AS session_engaged --エンゲージメントの判定
    ,NumberFromEventParams(event_params, 'engagement_time_msec') AS engagement_time_msec --エンゲージメント時間
    ,StringFromEventParams(event_params, 'search_term') AS search_term --サイト内検索キーワード
    ,NumberFromEventParams(event_params, 'percent_scrolled') AS percent_scrolled --スクロール率

FROM
    `project.data_set.events_*`

まとめ

BigQueryを使用してGA4のデータを効率的に操作するためには、UNNEST演算子とユーザー定義関数（UDF）の活用が重要です。これらの手法を組み合わせることで、REPEATEDモードのデータを解除し、必要な情報を取り出すことができます。