GA4でよく使う基本的な SQL集

GA４でよく使うSQLの備忘録です。
GA4のBigQueryエクスポート機能を使ってデータを抽出します。
今回のSQL文は難しくない基礎的な内容となっています。
つらつら書いていくので気になったら参考にしていただければ幸いです。

　　
　　

１：特定のイベントをカウント

特定のイベントのみを抽出しています。
where句でイベント名を指定する点がポイントでしょうか。

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

　　
　　

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

table_suffix between ... and ... で日付指定をしています。
group byで日付ごとに数値をまとめることも忘れずに。

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

　　
　　
　　

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

count if でevent_nameを指定し、セッション、PVを抽出しています。
user_pseudo_idをグループ化して、ユーザーごとの表記になります。

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
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`    　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20201101' and '20210201'　-- データの取得期間を指定
group by
  　user_pseudo_id -- ユーザーのCookie IDごとに集計する
order by
    number_of_sessions desc -- セッション降順で並べる

　　
　　
　　

４：日ごとのユーザー数をカウント

count(distinct user_pseudo_id) でユニークなユーザーを抽出しています。
日付を指定すれば、日ごとのユニークなユーザーを抽出することができます。

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

　　
　　
　　

５：ページごとのPV数を取得

このデータの抽出は、unnest 関数で入れ子のデータをほどいていますね。
このunnest関数については次回またどこかでブログに書きたいと思います。
unnest関数でpage_titleとpage_locationを抽出し、where句でpageviewを指定しています。
　　

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
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`    　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between  '20201101' and '20210201' 　-- 日付の指定
    and  event_name = 'page_view' 　-- イベント名を指定
 
group by 
    page_title,　-- ページタイトルでグルーピング
    page_location -- ページURLでグルーピング
 
order by 
    pageviews desc -- ページビュー数降順で並び替え

まとめ

今回は、５つのSQLの構文を書かさせて頂きました。
SQLで析ログを分析すると、GA4で集めたデータを自由にドリルダウン出来るのがメリットです。

GA4には標準でBigQueryエクスポート機能が備わっています。
BigQueryを使った自由で柔軟なログ抽出を始めてみましょう！