BigQueryでセッション・エンゲージメントを分析するクエリ集

投稿日 2026年2月12日

本ページのSQLを利用する前に、利用前の確認事項を必ずご確認ください。自社のプロジェクトIDとプロパティIDをfrom句に設定してからクエリを実行してください。

このページでは、BigQueryを使ってGA4のセッション・エンゲージメントデータを分析するクエリを紹介します。エンゲージメントセッション数、滞在時間、流入元別セッション、アクティブユーザー、ページ別訪問回数、ランディングページ、離脱・直帰分析など、セッション品質の評価に使えるSQL集です。

２１：セッションごとのエンゲージを確認
２２：エンゲージしたセッション数を取得
２３：セッションごとの滞在時間を取得
２４：流入元ごとのセッション数を取得
２５：エンゲージしたユーザー数を取得
２６：ページ別の訪問回数を取得
２７：ランディングページごとの流入回数を取得
２８：ページごとの流入回数、離脱数、直帰数を取得

２１：セッションごとのエンゲージを確認

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 --流入回数降順で並び替え

実行結果