本ページは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版はこちら
１：特定のイベントをカウント
select
count(event_name) as sessions -- 後ほど指定するイベント名の列の見出しを「sessions」にする
from
`ha-ga4.analytics_227084301.events_20220207` -- データの選択範囲。ここでは2022年2月7日のみを指定
where
event_name = 'session_start' -- イベント名がsession_startに合致するものだけを抽出実行結果
２：日付範囲を指定し、日ごとの数値を出す
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 と記載する
実行結果
３：ユーザーごとのセッション数や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 -- セッション降順で並べる実行結果
４：日ごとのユーザー数をカウント
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
実行結果
５：ページごとの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 -- ページビュー数降順で並び替え
実行結果
６：相対的な日付指定を行う
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 -- 日付順に並べる
実行結果
７：時間単位のデータを出す
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 | 月 |
| dayofyear | 1月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 -- 分で並べる実行結果
８：初回訪問日ごとのユーザー数を出す
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 -- 日付で並び替え
実行結果
９：流入元ごとのユーザー数を出す
(2022/10/06補足追記）下記の例に関しては、初回流入の流入元ごと(traffic_sourceは初回流入元のフィールド名)のユーザー数になります。セッションの参照元を取得したい場合は２４番目の例をご覧ください。
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 -- ユーザー降順で並び替え実行結果
１０：参照元 / メディアのように項目を繋げる
select
concat(traffic_source.source, " / ", traffic_source.medium) as source_medium, -- concat関数で２つの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 -- ユーザー降順で並び替え実行結果
１１：都道府県別のユーザー数を出す
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 -- ユーザー降順で並び替え
実行結果
１２：デバイスやブラウザ別の情報を取得
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 -- ユーザー降順で並び替え実行結果
１３：パラメータの値のユニーク数を取得
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 --日付昇順で並び替え
実行結果（２つの値が違う事が分かります。後者の方がより厳密なセッション数です）
１４：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 --日付昇順で並び替え実行結果
１５：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実行結果
１６：ページごとのスクロール率の集計
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 --ダウンロード回数降順で並び替え
実行結果
２１：セッションごとのエンゲージを確認
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 --セッションエンゲージのパラメータを取得（１か０が含まれている）
from
`ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by
user_pseudo_id, --ユーザーのCookieIDでグルーピング
session_id --セッションIDでグルーピング
実行結果
２２：エンゲージしたセッション数を取得
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 --セッションエンゲージのパラメータを取得（１か０が含まれている）
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のクエリ結果から取得
実行結果
２３：セッションごとの滞在時間を取得
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でグルーピング
実行結果
２４：流入元ごとのセッション数を取得
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 --セッション数、降順で並び替え
実行結果
２５：エンゲージしたユーザー数を取得
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のクエリ結果から取得
実行結果
２６：ページ別の訪問回数を取得
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--上記４2つのデータをpredataのクエリ結果から取得
group by
page_title, --ページタイトルでグルーピング
page --ページURLでグルーピング
order by
unique_pageviews 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を取得
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 --流入回数の降順に並び替え
実行結果
２８：ページごとの流入回数、離脱数、直帰数を取得
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のタイムスタンプの場合は離脱ページとして１を割り当てる
case
when count(1) over(partition by session_id) = 1 then 1 else 0
end as bounce --該当セッションIDでpage_viewイベントが１つしか存在しない場合は直帰として、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 --流入回数降順で並び替え実行結果
※注：ここで取得している直帰率は１ページだけ見て離脱したセッション数を表示しています。GA4内での直帰の定義は非エンゲージメントとなるため、クエリ結果とは数値があいません。
２９：閲覧ページの１つ前と１つ後をまとめて取得
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の場合は離脱
３０：指定ページの１つ次に見たページを取得
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 --カウントの降順で並び替え実行結果
３２：アイテムごとの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の降順に並び替え実行結果
３３：新規とリピートユーザーの取得
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のクエリ結果から取得実行結果
エンゲージメント時間を条件として使わずに新規・リピートユーザーを出したい場合は、タイムスタンプ部分及びengagemnt_time_msecの条件を外してください
３４：コンバージョン率や購入率を取得
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ではない行のみを取得
３７：取得イベント名とパラメータ名の一覧表示
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
実行結果
３８：１ページだけ閲覧したセッション数や率
直帰「数」を出したい場合は、セッション数との割り算を行わない
SELECT
count(distinct case when page_views <= 1 then ga_session_id end) -- ページビューが1以下を抽出（ページビューが発生しなかったセッションも抽出するため1以下を指定）
/count(distinct concat(user_pseudo_id, ga_session_id)) as session_bounce_rate -- セッション数を出し割り算する
from(
SELECT
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id,
count(distinct case when event_name = "page_view" then concat(user_pseudo_id,event_timestamp) end) as page_views
FROM `ha-ga4.analytics_227084301.events_*`-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by user_pseudo_id,ga_session_id)
実行結果
３９：セッションの平均エンゲージメント時間とセッションの平均時間
セッションの平均エンゲージメント時間：画面の前面にページが表示されていた時間を元にセッションの平均滞在時間を算出
セッションの平均時間：セッションの最初と最後のイベント発生時間の差分で平均滞在時間を算出
SELECT
sum(engagement_time_msec)/1000 --エンゲージメント時間はミリ秒単位なので変換
/count(distinct concat(user_pseudo_id,ga_session_id)) as engaged_session_duration, -- セッション平均エンゲージメント時間を計算
sum(end_time-start_time)/1000000 --タイムスタンプはマイクロ秒単位なので変換
/count(distinct concat(user_pseudo_id,ga_session_id)) as session_duration, -- セッション平均時間を計算
from(
SELECT
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id,
max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec, --エンゲージメント時間を算出
min(event_timestamp) as start_time, -- 最初のイベント発生時間を算出
max(event_timestamp) as end_time -- 最後のイベント発生時間を算出
FROM `ha-ga4.analytics_227084301.events_*`-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by user_pseudo_id,ga_session_id)
実行結果
４０：デフォルトチャネルグループごとの流入数（セッション）
流入元をデフォルトチャネルグループで分類。データはセッション単位となります。
with prep as (
select
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
array_agg((select value.string_value from unnest(event_params) where key = 'source') ignore nulls order by event_timestamp)[safe_offset(0)] as source,-- 参照元の取得
array_agg((select value.string_value from unnest(event_params) where key = 'medium') ignore nulls order by event_timestamp)[safe_offset(0)] as medium,-- メディアの取得
array_agg((select value.string_value from unnest(event_params) where key = 'campaign') ignore nulls order by event_timestamp)[safe_offset(0)] as campaign-- キャンペーンの取得
from`ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by
user_pseudo_id,
session_id)
select
case -- 条件に応じて分類。分類ルールはhttps://support.google.com/analytics/answer/9756891 に順次、最新版とは限らない
when source = '(direct)' and (medium in ('(not set)','(none)')) then 'Direct'
when regexp_contains(campaign, 'cross-network') then 'Cross-network'
when (regexp_contains(source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
or regexp_contains(campaign, '^(.*(([^a-df-z]|^)shop|shopping).*)$'))
and regexp_contains(medium, '^(.*cp.*|ppc|paid.*)$') then 'Paid Shopping'
when regexp_contains(source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Search'
when regexp_contains(source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Social'
when regexp_contains(source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
and regexp_contains(medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Video'
when medium in ('display', 'banner', 'expandable', 'interstitial', 'cpm') then 'Display'
when regexp_contains(source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
or regexp_contains(campaign, '^(.*(([^a-df-z]|^)shop|shopping).*)$') then 'Organic Shopping'
when regexp_contains(source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
or medium in ('social','social-network','social-media','sm','social network','social media') then 'Organic Social'
when regexp_contains(source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
or regexp_contains(medium,'^(.*video.*)$') then 'Organic Video'
when regexp_contains(source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
or medium = 'organic' then 'Organic Search'
when regexp_contains(source,'email|e-mail|e_mail|e mail')
or regexp_contains(medium,'email|e-mail|e_mail|e mail') then 'Email'
when medium = 'affiliate' then 'Affiliates'
when medium = 'referral' then 'Referral'
when medium = 'audio' then 'Audio'
when medium = 'sms' then 'SMS'
when medium like '%push'
or regexp_contains(medium,'mobile|notification') then 'Mobile Push Notifications'
else 'Unassigned' end as channel_grouping_session,
count(distinct concat(user_pseudo_id,session_id)) as sessions
from
prep
group by
channel_grouping_session
order by
sessions desc実行結果
４１：デフォルトチャネルグループごとの流入数（ユーザー）
流入元をデフォルトチャネルグループで分類。データはユーザー単位となります。
select
case -- 条件に応じて分類。分類ルールはhttps://support.google.com/analytics/answer/9756891 に順次、最新版とは限らない
when traffic_source.source = '(direct)' and (traffic_source.medium in ('(not set)','(none)')) then 'Direct'
when regexp_contains(traffic_source.name, 'cross-network') then 'Cross-network'
when (regexp_contains(traffic_source.source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
or regexp_contains(traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$'))
and regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$') then 'Paid Shopping'
when regexp_contains(traffic_source.source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Search'
when regexp_contains(traffic_source.source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Social'
when regexp_contains(traffic_source.source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
and regexp_contains(traffic_source.medium,'^(.*cp.*|ppc|paid.*)$') then 'Paid Video'
when traffic_source.medium in ('display', 'banner', 'expandable', 'interstitial', 'cpm') then 'Display'
when regexp_contains(traffic_source.source,'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
or regexp_contains(traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$') then 'Organic Shopping'
when regexp_contains(traffic_source.source,'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
or traffic_source.medium in ('social','social-network','social-media','sm','social network','social media') then 'Organic Social'
when regexp_contains(traffic_source.source,'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube')
or regexp_contains(traffic_source.medium,'^(.*video.*)$') then 'Organic Video'
when regexp_contains(traffic_source.source,'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
or traffic_source.medium = 'organic' then 'Organic Search'
when regexp_contains(traffic_source.source,'email|e-mail|e_mail|e mail')
or regexp_contains(traffic_source.medium,'email|e-mail|e_mail|e mail') then 'Email'
when traffic_source.medium = 'affiliate' then 'Affiliates'
when traffic_source.medium = 'referral' then 'Referral'
when traffic_source.medium = 'audio' then 'Audio'
when traffic_source.medium = 'sms' then 'SMS'
when traffic_source.medium like '%push'
or regexp_contains(traffic_source.medium,'mobile|notification') then 'Mobile Push Notifications'
else 'Unassigned' end as channel_grouping_user,
count(distinct user_pseudo_id) as users
from
`ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by
channel_grouping_user
order by
users desc実行結果
４２：URL階層ごとのページビュー数
第1階層～第3階層までに対応。第4階層以降も取得したい場合は、safe_ordinalの値を1を増やす形で設定する。
select
case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(4)],'?')[safe_ordinal(1)]) end as pagepath_level_1,
-- URLの４つ目の「/」から次の「/」までの文字列を取得し第1階層とする
case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(5)],'?')[safe_ordinal(1)]) end as pagepath_level_2,
-- URLの５つ目の「/」から次の「/」までの文字列を取得し第2階層とする
case when split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)] = '' then null else concat('/',split(split((select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location'),'/')[safe_ordinal(6)],'?')[safe_ordinal(1)]) end as pagepath_level_3,
-- URLの６つ目の「/」から次の「/」までの文字列を取得し第3階層とする
countif(event_name = 'page_view') as page_views -- ページビューのイベント数をカウント
from
`ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
group by
pagepath_level_1,
pagepath_level_2,
pagepath_level_3
order by
page_views desc
実行結果
