GA4×BigQueryで特定記事のページ滞在時間を計測するSQLクエリ

先日Googleの検索アルゴリズムに関する内部文書が流出して、ページ滞在時間がSEOに影響あるという一般論がこれによって明らかになりました。

しかし、GA4の管理画面では平均エンゲージメント時間しか確認できず、多くの場合この指標は意味ないです。（平均が2分だとしても、最頻値は10秒程度っていうのは往々にしてあるので）

そこで本記事では、BigQueryでGA4の生データを扱って、ユーザーごとに特定記事のページ滞在時間を計測するSQLクエリについて紹介していきます。

本記事の筆者

国立理系修士2年（23歳 | 専攻は機械学習）
SEOマーケティング会社で実務を1年ほど経験
ブログ運営3年目（月間10,000PVほど）
2025年4月からIT事業会社のデータサイエンティストとして働く予定で、現在はBigQueryを活用したGA4やGSCのデータ分析に注力

GA4×BigQueryにおける基本的なSQLの使い方は下記記事で紹介してますので、参考までにどうぞ<(_ _)>
» GA4とBigQueryを連携するメリット& 基本的なSQLの使い方を解説

また、SQL（BigQuery）スキルを習得するために以下のUdemy講座はおすすめです。
» BigQuery で学ぶ非エンジニアのための SQL データ分析入門

GA4×BigQueryで特定記事のページ滞在時間を計測するSQLクエリ

さっそく下記にSQLクエリを紹介していきます。

SQLクエリ

/*
特定の記事のページ滞在時間を集計
*/

with pageviews as (
  select
    user_pseudo_id,
    timestamp_micros(event_timestamp) as event_timestamp,
    lead(timestamp_micros(event_timestamp)) over (partition by user_pseudo_id order by event_timestamp) as next_event_timestamp,
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    event_name = 'page_view'
    and _table_suffix between '20240501' and '20240531'
)
select
  user_pseudo_id,
  page_location,
  timestamp_diff(next_event_timestamp, event_timestamp, second) as stay_duration
from
  pageviews
where
  page_location = 'https://sgs-prog.com/swell-cafe-homepage-develop1/'
  and next_event_timestamp is not null
  and timestamp_diff(next_event_timestamp, event_timestamp, second) <= 1800 -- 1800秒より上の値は極端なケースとして除外

当サイトの「SWELLでコーポレートサイトを作ってみた！【デザインカンプあり】」という記事のページ滞在時間を計測するクエリとなっています。