GA4用のBigQuery クエリ集

本ページはSQLを覚えることを目的にしたページではなく、SQLを使ってGA4のBigQueryからよく使いそうなSQLをまとめたものになります。対象は今までSQLを使ってデータをだした事が無い方、昔一度試して挫折あるいはどうすればよいか分からなくなってしまった方が対象です。そのため「とりあえず使ってみる」事を意識しております。必要な限りコメントを入れて、わかりやすさ優先の記述となっています。そのため必ずしも効率的書き方では無いことをご了承下さい。また本SQLの内容等に関してはサポート対象外となりますので、自己責任でご利用ください。

作成ご協力:@macchaice@kirin0890・他皆様(感謝!)

利用前の確認事項

自分のデータセットを指定する

例えば以下のようにデータが格納されている場合

fromの部分は以下の通りとなります。保存データごとに変わるので、必ず自社のプロジェクトIDにとプロパティIDを設定してください。

`ha-ga4.analytics_227084301.events_20220207` 

ha-ga4 = プロジェクトID
analytics_27084301 = プロパティID

events_以降は日付になります。全期間選びたい場合はevents_* と指定してください

月間1TBまで無償

月間1TBまで無償でクエリが処理されます。クエリを実施する前に処理量が表示されるので、参考にしましょう。極端に大きなデータ量にならないように、項目や日付を絞ってBigQueryは利用しましょう。

クエリ一覧

Google Docs版はこちら

1:特定のイベントをカウント

select
    count(event_name) as sessions -- 後ほど指定するイベント名の列の見出しを「sessions」にする
from
    `ha-ga4.analytics_227084301.events_20220207`    -- データの選択範囲。ここでは2022年2月7日のみを指定
where
    event_name = 'session_start'  -- イベント名がsession_startに合致するものだけを抽出
実行結果

2:日付範囲を指定し、日ごとの数値を出す

select
   date(timestamp_micros(event_timestamp),"Asia/Tokyo") as event_date, -- イベントの発生日付を選択
    count(event_name) as sessions -- 後ほど指定するイベント名の列の見出しを「sessions」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    event_name = 'session_start'  -- イベント名がsession_startに合致するものだけを抽出
    and _table_suffix between '20220201' and '20220207' -- データの取得期間を指定
group by
    event_date  -- 日付ごとに集計する
order by
    event_date  -- 昇順で並び替える。降順で並び替えたい場合は event_date desc と記載する
実行結果

3:ユーザーごとのセッション数やPVを降順で並べる

select
 user_pseudo_id, -- ユーザーのCookie IDを指定する
    countif(event_name = 'session_start') as number_of_sessions, --セッション数を取得
    countif(event_name = 'page_view') as page_voew --PV数を取得
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220207' -- データの取得期間を指定
group by
   user_pseudo_id -- ユーザーのCookie IDごとに集計する
order by
    number_of_sessions desc -- セッション降順で並べる
実行結果

4:日ごとのユーザー数をカウント

select
   date(timestamp_micros(event_timestamp),"Asia/Tokyo") as event_date, -- イベントの発生日付を選択
    count(distinct user_pseudo_id) as users  -- CookieIDのユニークな数をカウントする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by
    event_date
実行結果

5:ページごとのPV数を取得

select
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title, --ページタイトルをイベントパラメータから取得
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location, --ページURLをイベントパラメータから取得
    count(event_name) as pageviews --イベント数をカウントする。対象イベントはwhere内で指定
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
    and  event_name = 'page_view'  -- イベント名を指定
 
group by 
    page_title, -- ページタイトルでグルーピング
    page_location -- ページURLでグルーピング
 
order by 
    pageviews desc -- ページビュー数降順で並び替え
実行結果

6:相対的な日付指定を行う

select
   date(timestamp_micros(event_timestamp),"Asia/Tokyo") as event_date, -- イベントの発生日付を選択
    count(event_name) as pageview -- 後ほど指定するイベント名の列の見出しを「pageview」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    event_name = 'page_view'  -- イベント名がpage_voewに合致するものだけを抽出
    and _table_suffix between format_date('%Y%m%d', date_sub(current_date(), interval 365 day)) and format_date('%Y%m%d', date_sub(current_date(), interval 1 day)) -- データの取得期間を指定。本日の365日前から本日の1日前までが対象
group by
   event_date -- 日付でグルーピングする
order by
    event_date -- 日付順に並べる
実行結果

7:時間単位のデータを出す

select
   date(timestamp_micros(event_timestamp),"Asia/Tokyo") as event_date, -- イベントの発生日付を選択
    count(event_name) as pageview -- 後ほど指定するイベント名の列の見出しを「pageview」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
select
    extract(hour from timestamp_micros(event_timestamp)at time zone "Asia/Tokyo") as hour, -- 「時」を抽出する
    count(event_name) as pageview -- 後ほど指定するイベント名の列の見出しを「pageview」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    event_name = 'page_view'  -- イベント名がpage_voewに合致するものだけを抽出
    and _table_suffix between '20220201' and format_date('%Y%m%d', date_sub(current_date(), interval 1 day)) -- データの取得期間を指定。固定日から本日の1日前までが対象
group by
   hour -- 時でグルーピングする
order by
    hour -- 時間順に並べる
実行結果

「hour」と指定している部分を全て以下に変えることで、違うグルーピングが出来ます

名称意味
date日付 yyyy-mm-dd形式
minute分(1~60の値)
year
quarter四半期
month
dayofyear1月1日を「1」とした時に何日目か
day日のみ(1~31の値)
week週(1~53の値)
second

複数の列を指定すれば掛け合わせが可能です。

select
    extract(hour from timestamp_micros(event_timestamp)at time zone "Asia/Tokyo") as hour, -- 「時」を抽出する
    extract(minute from timestamp_micros(event_timestamp)at time zone "Asia/Tokyo") as minute, -- 「分」を抽出する
    count(event_name) as pageview -- 後ほど指定するイベント名の列の見出しを「pageview」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    event_name = 'page_view'  -- イベント名がpage_voewに合致するものだけを抽出
    and _table_suffix between '20220201' and format_date('%Y%m%d', date_sub(current_date(), interval 1 day)) -- データの取得期間を指定。固定日から本日の1日前までが対象
group by
   hour, -- 時でグルーピングする
   minute -- 分でグルーピングする
order by
    hour, -- 時で並べる
    minute -- 分で並べる
実行結果

8:初回訪問日ごとのユーザー数を出す

select
    extract(date from timestamp_micros(user_first_touch_timestamp)at time zone "Asia/Tokyo") as day_first_touch,  -- user_first_touch_timestampという初回訪問時間が記録されている列から日付を抽出する
    count(distinct user_pseudo_id) as users  -- 該当日付のユニークなユーザー数をカウント
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by
    day_first_touch  -- 日付でグルーピングする
having 
    day_first_touch is not null   -- タイムスタンプがnullではないという条件を満たす事で空白を除外
order by
    day_first_touch -- 日付で並び替え
実行結果

9:流入元ごとのユーザー数を出す

(2022/10/06補足追記)下記の例に関しては、初回流入の流入元ごと(traffic_sourceは初回流入元のフィールド名)のユーザー数になります。セッションの参照元を取得したい場合は24番目の例をご覧ください。

select
    traffic_source.source,-- 参照元を抽出
    traffic_source.medium,-- メディアを抽出
    traffic_source.name as campaign, -- キャンペーン名を抽出しcampignと名付ける
    count(distinct user_pseudo_id) as users -- ユニークなユーザー数
 
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by
    source,  -- 参照元でグルーピング
    medium,  -- メディアでグルーピング
    campaign  -- キャンペーンでグルーピング
order by
    users desc  -- ユーザー降順で並び替え
実行結果

10:参照元 / メディアのように項目を繋げる

select
    concat(traffic_source.source, " / ", traffic_source.medium) as source_medium, -- concat関数で2つのsourceとmediumを「/」でつなぐ
    count(event_name) as sessions -- 後ほど指定するイベント名の列の見出しを「sessions」にする
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
    and event_name = 'session_start'  -- イベント名がsession_startに合致するものだけを抽出
group by
   source_medium  -- source_mediumでグルーピング
order by
   sessions desc  -- ユーザー降順で並び替え
実行結果

11:都道府県別のユーザー数を出す

select
    geo.country,  -- 国を指定
    nullif(geo.region,'') as region,  -- 地域(都道府県)を指定。空白の場合はnullに置き換える
    nullif(geo.city,'') as city,   -- 都市を指定。空白の場合はnullに置き換える
    count(distinct user_pseudo_id) as users
 
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
    and geo.country = 'Japan'  -- 国が日本のみを指定
group by
    country,  -- 国でグルーピング
    region,  -- 都道府県でグルーピング
    city  -- 市でグルーピング
order by
    users desc  -- ユーザー降順で並び替え
実行結果

12:デバイスやブラウザ別の情報を取得

select
    device.category,
    device.operating_system,
    device.operating_system_version,
    device.language,
    device.web_info.browser,
    device.web_info.browser_version,
    device.web_info.hostname,
    count(distinct user_pseudo_id) as users
 
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by 
    category,
    operating_system,
    operating_system_version,
    language,
    browser,
    browser_version,
    hostname
order by
    users desc  -- ユーザー降順で並び替え
実行結果

13:パラメータの値のユニーク数を取得

select
    count(distinct (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id_count -- ga_session_idパラメータのユニーク数(distinct)をカウントする(count)。unnestでパラメータ値を展開する必要があります
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
 
where
    _table_suffix between '20220201' and '20220205' -- 日付の指定

ここではga_session_id(セッションに割り当てられるパラメータ値)をカウントしています。

実行結果

参考:session_idは訪問した時間を元に生成されています。そのため同時にアクセスがあった場合、同じsession_idの値が2人のユーザーに紐づいてしまいます。そこで厳密にセッションを出す場合は、user_pseudo_idとsession_idを組み合わせた上でのユニークな件数を見る必要があります。そのための記述と実行結果は以下の通りとなります。

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 --日付昇順で並び替え
実行結果(2つの値が違う事が分かります。後者の方がより厳密なセッション数です)

14: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 --日付昇順で並び替え
実行結果

15:eコマースの商品ごとの購入数と売上

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
実行結果

16:ページごとのスクロール率の集計

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 --発生回数の降順に並び替え
実行結果

17:外部リンクのクリック回数を取得

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 --クリック数降順で並び替え
実行結果

18:サイト内検索キーワードを取得

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 --検索回数の降順でに並び替え
実行結果

19:動画エンゲージメントの情報を取得

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  --動画開始回数降順に並び替え
実行結果

20:ファイルダウンロードの情報を取得

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 --ダウンロード回数降順で並び替え
実行結果

21:セッション後のエンゲージを確認

select
    user_pseudo_id, --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, --ユーザーのセッションIDを取得
    max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged --セッションエンゲージのパラメータを取得(1か0が含まれている)
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
 
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
 
group by 
    user_pseudo_id, --ユーザーのCookieIDでグルーピング
    session_id --セッションIDでグルーピング

実行結果

22:エンゲージしたセッション数を取得

with predata as ( --14行目までのselect文に名称をつける。ここではpredata。「(」を忘れないように
select
    user_pseudo_id, --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, --ユーザーのセッションIDを取得
    max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged --セッションエンゲージのパラメータを取得(1か0が含まれている)
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する

where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by 
    user_pseudo_id, --ユーザーのCookieIDでグルーピング
    session_id) --セッションIDでグルーピング。クエリの最後でpredataを閉じるため「)」を忘れないように
 
select 
    count(distinct concat(user_pseudo_id,"-",session_id)) as sessions, --CookieIDとセッションIDをつなげユニーク数をカウントし、厳密なセッション数を取得
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,"-",session_id) else null end) as engaged_sessions --session_engagedの値が1の時に、CookieIDとセッションIDをつなげたユニーク数をカウントし、エンゲージメントセッション数を算出。1以外の場合はnullに変換
from 
    predata --上記の2つのcountをpredataのクエリ結果から取得
実行結果

23:セッションごとの滞在時間を取得

with prepdata as ( -- select文に名称をつける
select
    user_pseudo_id,--ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,--ユーザーのセッションIDを取得
    event_timestamp --イベントのタイムスタンプを取得
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220207') -- データの取得期間を指定
 
select
    user_pseudo_id,--ユーザーのCookieIDを取得
    session_id,--ユーザーのセッションIDを取得
    timestamp_seconds(session_id) as session_start_time,--セッション開始時ののタイムスタンプを取得
    (max(event_timestamp)-min(event_timestamp))/1000000 as session_length_seconds --セッションごとの最大と最小のタイムスタンプを引き算し、100万で割ることで秒数に変換
from
    prepdata --perpdataから取得
group by 
    user_pseudo_id,--ユーザーのCookieIDでグルーピング
    session_id--ユーザーのセッションIDでグルーピング
実行結果

24:流入元ごとのセッション数を取得

with predata as ( --select文に名称をつける。ここではpredata。「(」を忘れないように
select
    user_pseudo_id, --ユーザーのCookieIDを取得
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, --ユーザーのセッションIDを取得
    max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged, --セッションエンゲージのパラメータ取得
    max((select value.string_value from unnest(event_params) where key = 'medium')) as medium, --イベントパラメータからメディアを取得。session_startのイベントには流入元は記録されていないため、該当セッションID内に含まれているメディアを取得してくる
    max((select value.string_value from unnest(event_params) where key = 'source')) as source --イベントパラメータから参照元を取得。session_startのイベントには流入元は記録されていないため、該当セッションID内に含まれているメディアを取得してくる
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
group by 
    user_pseudo_id, --ユーザーのCookieIDでグルーピング
    session_id) --セッションIDでグルーピング。クエリの最後でpredataを閉じるため「)」を忘れないように
 
select 
    concat(ifnull(source,'(direct)'),' / ',ifnull(medium,'(none)')) as session_source_medium, --セッションとメディアを「/」でつなげる。参照元に値が入っていない場合は参照元を(direct)に変換、メディアに値が入っていない場合はメディアを(none)に変換
    count(distinct concat(user_pseudo_id,"-",session_id)) as sessions, --CookieIDとセッションIDをつなげユニーク数をカウントし、厳密なセッション数を取得
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,"-",session_id) else null end) as engaged_sessions --session_engagedの値が1の時に、CookieIDとセッションIDをつなげたユニーク数をカウントし、エンゲージメントセッション数を算出。1以外の場合はnullに変換
 
from 
    predata --上記の2つのcountをpredataのクエリ結果から取得
group by
    session_source_medium  --参照元メディアでグルーピング
order by
    sessions desc  --セッション数、降順で並び替え
実行結果

25:エンゲージしたユーザー数を取得

with predata as ( --14行目までのselect文に名称をつける。ここではpredata。「(」を忘れないように
select
    user_pseudo_id, --ユーザーのCookieIDを取得
    sum((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でグルーピング。クエリの最後でpredataを閉じるため「)」を忘れないように
 
select 
    count(distinct user_pseudo_id) as users, --ユーザーのCookieIDユニーク数をユーザー数として取得
    count(distinct case when engagement_time_msec > 0 then user_pseudo_id else null end) as active_users  --エンゲージメント時間が0ミリ秒より大きい場合はアクティブユーザーとする。0の場合はnullとし、アクティブユーザーのユーザーCookieiDのユニーク数を取得
    
from 
    predata --上記の2つのcountをpredataのクエリ結果から取得
実行結果

26:ページ別の訪問回数を取得

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 event_name = 'page_view' and key = 'page_title') as page_title,--ページタイトルを取得
    (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as page--ページURLを取得
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205')  -- 日付の指定
 
select 
    page_title, --ページタイトルを取得
    page, --ページURLを取得
    count(*) as total_pageviews, --行数をページビューとして取得
    count(distinct concat(user_pseudo_id,"-",session_id)) as unique_pageviews --CookieIDとセッションIDをつなげユニーク数をカウントし、ページ別訪問回数を取得
 
from 
    predata--上記42つのデータをpredataのクエリ結果から取得
group by 
    page_title, --ページタイトルでグルーピング
    page --ページURLでグルーピング
order by
    unique_pageviews desc --ページ別訪問回数の降順で並び替え
実行結果

27:ランディングページごとの流入回数を取得

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を取得
case
when (select value.int_value from unnest(event_params) where key = 'entrances') = 1
then (select value.string_value from unnest(event_params) where key = 'page_location')
end as landing_page -- entrancesのパラメータに「1が入ってる場合」page_locationの値をlanding_pageとして扱う
from
    `ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' -- 日付の指定
    and event_name = 'page_view'-- イベント名を指定
) 
 
select
    landing_page,
    count(distinct concat(user_pseudo_id, "-", session_id)) as entrances --CookieIDとセッションIDをつなげユニーク数をカウントし、流入回数とする
from
    predata--上記2つのデータをpredataのクエリ結果から取得
where
    landing_page is not null --nullが入っていない行のみを表示
group by
    landing_page --ランディングページでグルーピングする
order by
    entrances desc --流入回数の降順に並び替え

実行結果

28:ページごとの流入回数、離脱数、直帰数を取得

with predata as (
select
    event_timestamp,
    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_location --ページURLを取得 
 
from
    `ha-ga4.analytics_227084301.events_*`    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
    and  event_name = 'page_view'),  -- イベント名を指定
 
predata2 as (
  SELECT
    page_location, -- ページURLを取得
    case
    when row_number() over(partition by session_id order by event_timestamp) = 1 then 1 else 0 end as entrance,
    case
      when row_number() over(partition by user_pseudo_id, session_id order by  event_timestamp desc) = 1 then 1 else 0
    end as exit, --該当セッションIDで一番最後のpage_viewのタイムスタンプの場合は離脱ページとして1を割り当てる
    case
      when count(1) over(partition by session_id) = 1 then 1 else 0
    end as bounce --該当セッションIDでpage_viewイベントが1つしか存在しない場合は直帰として、1を割り当てる。GA4の直帰(非エンゲージメント)とは違うでの注意
from predata)
 
select 
    page_location as page_location, --ページURLを取得
    sum (entrance) as landing, --ランディングページを合計
    sum (exit) as exit, --離脱ページを合計
    sum(bounce) as bounce  --直帰ページを合計

from
    predata2 --predata2からデータを取得
group by
    page_location --URLでグルーピング
order by
    anding desc --流入回数降順で並び替え
実行結果

※注:ここで取得している直帰率は1ページだけ見て離脱したセッション数を表示しています。GA4内での直帰の定義は非エンゲージメントとなるため、クエリ結果とは数値があいません。

29:閲覧ページの1つ前と1つ後をまとめて取得

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のクエリ結果から取得
実行結果

previous_pageがnullの場合はランディングページ、next_pageがnullの場合は離脱

30:指定ページの1つ次に見たページを取得

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 --カウントの降順で並び替え
実行結果

31:指定ページの1つ前に見たページを取得

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 --カウントの降順で並び替え
実行結果

32:アイテムごとのeコマースイベント発生回数とCVR

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の降順に並び替え
実行結果

33:新規とリピートユーザーの取得

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, --セッション番号が1 かつエンゲージメント時間が存在する場合、新規ユーザー
    count(distinct case when session_number > 1 and engagement_time_msec > 0 then user_pseudo_id else null end) as returning_users --セッション番号が1より大きい かつエンゲージメント時間が存在する場合、リピートユーザー
from 
    prepdata --上記のデータをpredataのクエリ結果から取得
実行結果

エンゲージメント時間を条件として使わずに新規・リピートユーザーを出したい場合は、タイムスタンプ部分及びengagemnt_time_msecの条件を外してください

34:コンバージョン率や購入率を取得

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 --日付昇順で並び替え
実行結果

35:ユーザープロパティのデータを取得

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 --ユーザーのパラメータ値でグルーピング
実行結果

36:ユーザープロパティの値をユーザーごとにグルーピング

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ではない行のみを取得
実行結果

37:取得イベント名とパラメータ名の一覧表示

SELECT
    event_name, --イベント名を取得
    params.key as event_parameter_key, --イベントパラメータ名を取得
    case
      when params.value.string_value is not null then 'string' --文字列
      when params.value.int_value is not null then 'int' --整数
      when params.value.double_value is not null then 'double' --64ビット倍精度小数点
      when params.value.float_value is not null then 'float' --32ビット倍精度小数点
end
     as event_parameter_value --イベントパラメータ値を分類
 
from
    `ha-ga4.analytics_227084301.events_*`,    -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
    unnest(event_params) as params
where
    _table_suffix between '20220201' and '20220205'  -- 日付の指定
 
group by
    event_name,
    event_parameter_key,
    event_parameter_value
実行結果

参考になりましたか?

Google Analytics 4のセミナー講座販売中
活用・実装・改善・LookerStudioなど3時間半で学べる動画+資料を買い切り販売中です。一度購入いただくと、随時アップデートも行われます。