BigQueryでページ遷移・コンバージョンを分析するクエリ集

with predata as (
select
    user_pseudo_id,  --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id,--ユーザーのセッションIDを取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page,--ページのURLを取得
    event_timestamp　--イベントの発生時間を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and  event_name = 'page_view') 　-- イベント名を指定
 
select 
    user_pseudo_id, --ユーザーのCookieIDを取得
    session_id, --ユーザーのセッションIDを取得
    lag(page,1) over (partition by user_pseudo_id,session_id order by event_timestamp) as previous_page,　--イベント時間昇順に並びかえた後に、lagを使ってpage列の1つ前の値を取得してprevious_pageに格納
    page, --ページのURLを取得
    lead(page,1) over (partition by user_pseudo_id,session_id order by event_timestamp) as next_page,　--イベント時間昇順に並びかえた後に、leadを使ってpage列の1つ先の値を取得してprevious_pageに格納
 
from 
    predata--上記のデータをpredataのクエリ結果から取得

with predata as (
select
    user_pseudo_id,  --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id,--ユーザーのセッションIDを取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page,--ページのURLを取得
    event_timestamp　--イベントの発生時間を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and  event_name = 'page_view'),　-- イベント名を指定
 
prep_nextpage as (
select 
    user_pseudo_id, --ユーザーのCookieIDを取得
    session_id, --ユーザーのセッションIDを取得
    page, --ページのURLを取得
    lead(page,1) over (partition by user_pseudo_id,session_id order by event_timestamp) as next_page,　--イベント時間昇順に並びかえた後に、leadを使ってpage列の1つ先の値を取得してnext_pageに格納
    event_timestamp --イベントの発生時間を取得
from 
    predata--上記のデータをpredataのクエリ結果から取得
)
select 
    page,　--URLを取得
    ifnull(next_page,'(exit)') as next_page, --次のページを取得
    count(distinct concat(user_pseudo_id,"-", session_id)) as count　--CookieIDとセッションIDをつなげユニーク数をカウントする
from
    prep_nextpage　--prep_nextpageのクエリ結果から取得
where
    page='https://happyanalytics.co.jp/'　--起点となるURLを指定
group by
    page,next_page　--ページと次ページでグルーピング
order by
    count desc　--カウントの降順で並び替え

with predata as (
select
    user_pseudo_id,  --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id,--ユーザーのセッションIDを取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page,--ページのURLを取得
    event_timestamp　--イベントの発生時間を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and  event_name = 'page_view'),　-- イベント名を指定
 
prep_previouspage as (
select 
    user_pseudo_id, --ユーザーのCookieIDを取得
    session_id, --ユーザーのセッションIDを取得
    page, --ページのURLを取得
    lag(page,1) over (partition by user_pseudo_id,session_id order by event_timestamp asc) as previous_page,　--イベント時間昇順に並びかえた後に、leadを使ってpage列の1つ前の値を取得してprevious_pageに格納
    event_timestamp --イベントの発生時間を取得
from 
    predata--上記のデータをpredataのクエリ結果から取得
)
select 
    ifnull(previous_page,'(entrance)') as previous_page, --前のページを取得
    page,　--URLを取得
    count(distinct concat(user_pseudo_id,"-", session_id)) as count　--CookieIDとセッションIDをつなげユニーク数をカウントする
from
    prep_previouspage　--prep_previouspageのクエリ結果から取得
where
    page='https://happyanalytics.co.jp/'　--起点となるURLを指定
group by
   page,previous_page　--ページと前ページでグルーピング
order by
    count desc　--カウントの降順で並び替え

with prepdata as (
select 
    event_name,　-- イベント名を選択
    items.item_name, --アイテム名を選択
    count(items.item_id) as items --発生回数を集計
from
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
    unnest(items) as items --アイテムのパラメータをアンネストする
 
where
    _table_suffix between '20210101' and '20220131' 　-- 日付の指定
group by
    event_name,　--イベント名でグルーピング
    item_name)　--アイテム名でグルーピング
 
select 
    item_name,　--アイテム名を選択
    sum(case when event_name = 'view_item' then items else 0 end) as view_item,　--イベント名がview_itemが発生している場合にその回数を合計。発生していなければゼロ
    sum(case when event_name = 'add_to_cart' then items else 0 end) as add_to_cart,　--イベント名がadd_to_cartが発生している場合にその回数を合計。発生していなければゼロ
    sum(case when event_name = 'begin_checkout' then items else 0 end) as begin_checkout,　--イベント名がbegin_checkoutが発生している場合にその回数を合計。発生していなければゼロ
    sum(case when event_name = 'purchase' then items else 0 end) as purchase,　--イベント名がpurchaseが発生している場合にその回数を合計。発生していなければゼロ
    safe_divide(sum(case when event_name = 'purchase' then items else 0 end),sum(case when event_name = 'view_item' then items else 0 end)) as view_to_purchase_rate --purchase数÷view_item数を計算。safe_divideは分母がゼロの時にエラーが出ないようの記述形式
from 
    prepdata --prepdataのクエリからデータ取得
group by 
    item_name --アイテム名のグルーピング
order by 
    view_item desc --view_itemの降順に並び替え

with prepdata as (
select
    user_pseudo_id,　 --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,　--ユーザーのセッションIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_number') as session_number, --ユーザーのセッション番号を取得
    max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec --ユーザーのタイムスタンプを取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
 
group by
    user_pseudo_id,　--ユーザーのCookieIDでグルーピング
    session_id,　--セッションIDでグルーピング
    session_number)　--セッション番号でグルーピング
 
select
    count(distinct case when session_number = 1 and engagement_time_msec > 0 then user_pseudo_id else null end) as new_users, --セッション番号が１　かつエンゲージメント時間が存在する場合、新規ユーザー
    count(distinct case when session_number > 1 and engagement_time_msec > 0 then user_pseudo_id else null end) as returning_users --セッション番号が１より大きい　かつエンゲージメント時間が存在する場合、リピートユーザー
from 
    prepdata　--上記のデータをpredataのクエリ結果から取得

with prepdata as (
select
    user_pseudo_id, --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, --セッションIDを取得
    event_date, --イベントの発生日付を取得
    countif(event_name = 'file_download') as file_download,　--CVを見たいイベント名を指定。ここではfile_download
    ecommerce.transaction_id, --トランザクションIDを取得
    ecommerce.purchase_revenue --売上を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220101' and '20220207' 　-- 日付の指定
group by
    user_pseudo_id, --ユーザーのCookieIDでグルーピング
    session_id, --セッションIDでグルーピング
    event_date, --日付でグルーピング
    transaction_id, --トランザクションIDでグルーピング
    purchase_revenue)  --売上でグルーピング
 
select
    event_date as date, --日付を取得
    count(distinct concat(user_pseudo_id,"-",session_id)) as sessions, --セッション数を取得
    sum(file_download) as file_download, --CV数（今回はファイルダウンロード数）を取得
    sum(file_download)/count(distinct concat(user_pseudo_id,"-",session_id))  as file_download_CVR,　--ファイルダウンロード数÷セッション数でCVRを計算
    sum(purchase_revenue) as purchase_revenue, --売上を取得
    count(distinct transaction_id) as puchases, --トランザクション数を取得
    count(distinct transaction_id) / count(distinct concat(user_pseudo_id,"-",session_id)) as ecommerce_conversion_rate_all_sessions, --トランザクション数÷セッション数で購入率を計算
 
from
    prepdata　--prepdataからデータを取得
group by 
    date --日付でグルーピング
order by 
    date --日付昇順で並び替え

select
    user_pseudo_id, --ユーザーのCookieIDを取得
    key, --ユーザープロパティで計測しているパラメータ名を取得
    value.string_value --ユーザープロパティで計測しているパラメータの値を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
    cross join unnest(user_properties) 　-- ユーザープロパティのデータを利用するため配列を展開する必要あり
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and key='user_id' --user_idというユーザープロパティで絞り込み。これらユーザープロパティのパラメータ名や値を実装が前提になります
group by 
    user_pseudo_id, --ユーザーのCookieIDでグルーピング
    key, --ユーザープロパティのパラメータがグルーピング
    value.string_value --ユーザーのパラメータ値でグルーピング

select distinct
    user_pseudo_id, --ユーザーのCookieIDを取得
    (select value.string_value from unnest(user_properties) where key = 'user_id') as user_id --ユーザープロパティ内のプロパティ名user_idの値を取得。またmaxを利用する事で、同一user_psuedo_idで値がnullと何かしらのuser_idが紐づていている場合に後者のみを取得

from
    `ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する

cross join unnest(user_properties) -- ユーザープロパティのデータを利用するため配列を展開する必要あり

where
    _table_suffix between '20220201' and '20220205' -- 日付の指定
    and user_id is not null -- user_idがnullではない行のみを取得