BigQueryでeコマース・イベントを分析するクエリ集

select
  　date(timestamp_micros(event_timestamp),"Asia/Tokyo") as date, -- イベントの発生日付を選択
    count(distinct ecommerce.transaction_id) as transactions,　--トランスアクション数をカウント
    sum(ecommerce.purchase_revenue) as purchase_revenue --売上を集計
from
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20210101' and '20220131' 　-- 日付の指定
group by 
    date --日付グルーピング
order by
    date --日付昇順で並び替え

select
    items.item_name as item_name,
    sum(items.quantity) as items,
    sum(items.item_revenue) as item_revenue
from
    `ha-ga4.analytics_227084301.events_*`,   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
    unnest(items) as items
where
    _table_suffix between '20220101' and '20220207' 　-- 日付の指定
    and event_name = 'purchase'

group by
    item_name

select
    case 
        when (select value.int_value from unnest(event_params) where event_name = 'scroll' and key = 'percent_scrolled') = 90 --スクロールのパラメータ値に90が入っているという条件。カスタムでそれ以外のスクロール率も取得している場合は、ここで指定が可能
        then (select value.string_value from unnest(event_params) where event_name = 'scroll' and key = 'page_location') else null end as page,　--上の条件を満たしたURLを取得
    countif(event_name = 'scroll') as scrolls --イベント名がスクロールの回数をカウント
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by 
    page --ページ単位でグルーピング
order by 
    scrolls desc　--発生回数の降順に並び替え

select
    (select value.string_value from unnest(event_params) where event_name = 'click' and key = 'page_location') as page, --クリックイベント発生時のページURLを取得
    (select value.string_value from unnest(event_params) where event_name = 'click' and key = 'link_url') as link_url, --クリックイベント発生時のリンク先URLを取得
    countif(event_name = 'click' and (select value.string_value from unnest(event_params) where event_name = 'click' and key = 'outbound') = 'true') as clicks --イベント名がclickかつイベントパラメータのoutboundがtrueの時にカウントを行う
 
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by 
    page, --ページURLでグルーピング
    link_url --リンク先URLでグルーピング
order by 
    clicks desc --クリック数降順で並び替え

select
    (select value.string_value from unnest(event_params) where event_name = 'view_search_results' and key = 'search_term') as search_term, --イベントパラメータsearch_termを取得する
    countif(event_name = 'view_search_results') as searches --検索結果の回数を取得する
 
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220101' and '20220205' 　-- 日付の指定
group by 
    search_term --検索キーワードでグルーピングする
order by 
    searches desc --検索回数の降順でに並び替え

select
    (select value.string_value from unnest(event_params) where event_name like 'video%' and key = 'video_title') as video_title, --動画のタイトルを取得
    (select value.string_value from unnest(event_params) where event_name like 'video%' and key = 'video_url') as video_url, --動画のURLを取得
    (select value.int_value from unnest(event_params) where event_name like 'video%' and key = 'video_duration') as video_duration, --動画の長さ（秒数を取得
    countif(event_name = 'video_start') as video_start,　 --動画の開始回数をカウント
    countif(event_name = 'video_progress' and (select value.int_value from unnest(event_params) where event_name = 'video_progress' and key = 'video_percent') = 25) as video_progress_25_percent,　--25%までの再生をカウント
    countif(event_name = 'video_progress' and (select value.int_value from unnest(event_params) where event_name = 'video_progress' and key = 'video_percent') = 50) as video_progress_50_percent,　--50%までの再生をカウント
    countif(event_name = 'video_progress' and (select value.int_value from unnest(event_params) where event_name = 'video_progress' and key = 'video_percent') = 75) as video_progress_75_percent,　--75%までの再生をカウント
    countif(event_name = 'video_complete') as video_complete　--動画再生完了をカウント
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20210901' and '20220205' 　-- 日付の指定
group by 
    video_title, --動画タイトルでグルーピング
    video_url, --動画URLでグルーピング
    video_duration  --動画の長さでグルーピング
order by 
    video_start desc　 --動画開始回数降順に並び替え

select
    (select value.string_value from unnest(event_params) where event_name = 'file_download' and key = 'file_extension') as file_type,　--ファイルタイプを取得
    (select value.string_value from unnest(event_params) where event_name = 'file_download' and key = 'file_name') as file_name,　--ファイル名を取得
    (select value.string_value from unnest(event_params) where event_name = 'file_download' and key = 'link_text') as link_text,　--リンクテキストを取得
    (select value.string_value from unnest(event_params) where event_name = 'file_download' and key = 'link_url') as link_url,　--リンクURLを取得
    countif(event_name = 'file_download') as downloads --ファイルダンロードのイベントが発生した回数を取得
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20210901' and '20220205' 　-- 日付の指定
group by
    file_type, --ファイルタイプでグルーピング
    file_name, --ファイル名でグルーピング
    link_text, --リンクテキストでグルーピング
    link_url --リンクURLでグルーピング
order by
    downloads desc --ダウンロード回数降順で並び替え