【BigQuery】コンバージョンに至ったログの全てのURLをユーザーID単位で抽出するクエリ【sign関数】

Google BigQueryでユーザーID（user_pseudo_id）単位で、コンバージョンに至った（＝サンクスページに到達した）ログにおける全てのアクセスページURLを抽出するクエリの例をご紹介します。

ご紹介するクエリで以下が実現可能です。

ログ全体を抽出することで、コンバージョンに至った最初のランディングURL・日時がわかる
ユーザーID単位で取るため、セッションが切れている場合でも初回ランディングをトラッキング可能
コンバージョンしたログの中で、どのページを回遊していたか？を抽出できる

BigQueryに格納されるGA4のログデータはやや複雑で、パラメータをunnestして分析できる状態にする必要があります。

unnestしたあと、sign関数でログにフラグを立てていきます。

詳しくご説明していきます。

コンバージョンしたログ全体をユーザーID単位で抽出するクエリ
1. event_paramsをunnestし、left joinする
2. sign関数でコンバージョンしたログにフラグを立てる
まとめ

コンバージョンしたログ全体をユーザーID単位で抽出するクエリ

実際のクエリ例は以下のとおりです。

with ga4 as (
  select
    user_pseudo_id
    ,format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_trunc(timestamp_micros(e.event_timestamp), second), 'Asia/Tokyo') as datetime
    ,ep.value.string_value as page_location
  from
    `projects.analytics_xxxxxxxxx.events_*` as e
    left join unnest(e.event_params) as ep
  where
    e._table_suffix between '20231001' and '20241031'
    and e.event_name = 'page_view'
    and ep.key = 'page_location'
), log_w_conversion as (
  select
    user_pseudo_id
    ,datetime
    ,page_location
    ,sign(sum(case when regexp_contains(page_location, '/thanks') then 1 else 0 end)
          over (partition by user_pseudo_id order by datetime desc rows between unbounded preceding and current row))
    as has_cv
  from
    ga4
)

select
  *
from
  log_w_conversion
where
  has_cv = 1
order by
  1,2

ポイントをそれぞれ解説します。

event_paramsをunnestし、left joinする

with句でGA4のログデータから「user_pseudo_id」「datetime」「page_location」を取得していきますが、GA4のデータは以下のようにevent_paramsがネストされて格納されるため、これをunnestでほどきます。

unnest(e.event_params) としてunnestし、eventsデータにleft joinします。

with ga4 as (
  select
    user_pseudo_id
    ,format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_trunc(timestamp_micros(e.event_timestamp), second), 'Asia/Tokyo') as datetime
    ,ep.value.string_value as page_location
  from
    `projects.analytics_xxxxxxxxx.events_*` as e
    left join unnest(e.event_params) as ep
  where
    e._table_suffix between '20231001' and '20241031'
    and e.event_name = 'page_view'
    and ep.key = 'page_location'
)

where句で、event_nameは「page_view」、keyは「page_location」を指定します。

イベントデータの期間は、_table_suffixで指定できます。

GA4のログデータは重いので、クエリ容量を軽くする意味でも期間を指定することをおすすめします。

sign関数でコンバージョンしたログにフラグを立てる

sign関数とover句を使い、ユーザーIDごとにコンバージョンしたログ（以下の例では、/thanks のURLに到達したログ）に「1」、それ以外に「0」のフラグを立てます。

user_pseudo_idでパーティションすることで、セッションが切れているケースでもユーザーIDが同一であれば、ログ全体にフラグを立てることができます。

, log_w_conversion as (
  select
    user_pseudo_id
    ,datetime
    ,page_location
    ,sign(sum(case when regexp_contains(page_location, '/thanks') then 1 else 0 end)
          over (partition by user_pseudo_id order by datetime desc rows between unbounded preceding and current row))
    as has_cv
  from
    ga4
)

select
  *
from
  log_w_conversion
where
  has_cv = 1
order by
  1,2

「1」のフラグがついているものだけを抽出することで、コンバージョンしたログだけを返せます。