Google Analytics

Google Analytics 4 + BigQueryでよく使う基本的なSQL例

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

Google Analytics 4 (以下GA4)がリリースされましたね。
というわけでGA4のBigQueryエクスポート機能を使って早速データを抽出してみましょう!

以前、GA360+BigQueryで私がよく使う基本的なSQL例を紹介しましたが、今回はそれのGA4バージョンです。
 

1. ページビュー数

GA4の日別ページビューを出してみます。

タイムゾーンを任意で変更したいので、event_dateカラムではなくevent_timestampを「Asia/Tokyo」にして使ってます。
それがメンドクサイならevent_dateでもOK。
event_name = ‘page_view’ に絞るとページビューになる。
※アプリのスクリーンビューは’screen_view’を使う

SELECT
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
  COUNT(1) AS pageviews
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001'
  AND '20201031'
  AND event_name = 'page_view'
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別ページビュー数

 
なお、ページURL別にページビューを出すならこうです。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(1) AS pageviews
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'page_view'
GROUP BY 1
ORDER BY 2 DESC

ページURLは、event_params.key が「page_location」になっている行のstring_value列に入ってます。
event_paramsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにしてあげる必要があります。
以前書いた、「GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する」の記事を参考。

結果ははこうなります。

ページ別PV数

 

2. セッション数

user_pseudo_id と event_params.key=ga_session_idのint_valueを文字列にしたものをCONCATして、ユニーク数をカウントしたのがセッション数。
これも日別で出します。

WITH
  t1 AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001'
    AND '20201031'
    AND event_name = 'session_start' )
SELECT
  hitdate,
  COUNT(DISTINCT sid) AS sessions
FROM t1
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別セッション数


 
なお、Webとアプリ両方でUserIDを使ってる場合は user_pseudo_id の代わりに user_id を使えばクロスデバイスで同じユーザーとして扱えます。

3. ユーザー数

user_pseudo_id のユニーク数をカウントするとユーザー数。
下記はhitdateでGROUP BYしてるので、日別のユーザー数を出してます。

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001'
  AND '20201031'
  AND event_name = 'session_start'
GROUP BY 1
ORDER BY 1

結果はこうなります。

日別ユーザー数

 

4. ページ別ランディング数・離脱数・直帰数

先程出したセッションIDを使います。
これをevent_timestampを昇順ソートして、一番目のページがランディングページ。
逆にevent_timestampを降順ソートしたら、一番目のページが離脱ページ。
そしてセッション中に見たページの数が「1」なら直帰。
event_name = page_viewで絞ってます。

WITH
  t1 AS (
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid
  FROM                     
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'page_view'
  )
  ,t2 AS (
  SELECT
    page_location,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp) = 1 THEN 1
      ELSE 0
    END AS is_entry,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS is_exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
      ELSE 0
    END AS is_single
  FROM
    t1
  )
SELECT
  t2.page_location AS page_location,
  SUM(t2.is_entry) AS entry,
  SUM(t2.is_exit) AS exit,
  SUM(t2.is_single) AS singlevisit
FROM t2
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。

ランディング数・離脱数・直帰数

ただし、この書き方だと集計期間を跨いでる訪問の場合には集計期間中の最初に見たページがランディングページになるけどキニシナイ
 

5. ページ別合計滞在時間・平均滞在時間

これもセッションIDを使う。
ページ別に次ページとのタイムスタンプの差分を合算した秒数をページ別トータル滞在時間として抽出。
次ページとのタイムスタンプ差が1800秒以上あるものはセッションが切れてるものとして除外する。
滞在時間の合計を、ページのセッション数で割れば平均滞在時間になる。
event_name = page_viewに絞ってないので、スクロールイベントや離脱クリックなどのエンゲージイベントも滞在時間に含まれます。

WITH t1 AS (
  SELECT
    timestamp_micros(event_timestamp) AS event_timestamp,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM                     
    `analytics_12345678.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
)
,t2 AS (
  SELECT
    event_timestamp,
    sid,
    page_location,
    CASE
      WHEN timestamp_diff(LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp), event_timestamp, second) > 1800 THEN NULL
      ELSE LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp) END AS next_hit
  FROM t1
)
SELECT
  t2.page_location,
  COUNT(DISTINCT t2.sid) AS sessions,
  SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)) AS total_time,
  ROUND(SAFE_DIVIDE(SUM(IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second), 0)), COUNT(DISTINCT t2.sid)), 2) AS avg_time
FROM t2
WHERE t2.page_location IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。
滞在時間の単位は「秒」です。

合計滞在時間・平均滞在時間


 

6. イベントパラメータの集計

サイト内検索ワードをイベントパラメータで計測してるという前提です。
view_search_resultsイベントが発生した時に、検索ワードがevent_params.key = search_term の string_value列に入るので、それを集計します。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term') AS int_search_term,
  COUNT(1) AS int_search
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'view_search_results'
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。

サイト内検索ワード

event_name と event_paramsのキー名はイベント内容に合わせて書き換えてください。
 

7. ユーザープロパティの集計

ユーザープロパティは event_params と同じく縦持ちのレコードなのでUNNEST関数でフラットにする必要があります。
ユーザープロパティの値別にユーザー数を出してみます。

SELECT
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'weather') AS weather,
  COUNT(distinct user_pseudo_id) AS users
FROM
  `analytics_12345678.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201001' and '20201031'
  AND event_name = 'user_engagement'
GROUP BY 1
ORDER BY 2 DESC

結果はこうなります。

ユーザープロパティ

event_name と user_propertiesのキー名はユーザープロパティをセットするイベント内容に合わせて書き換えてください。
 

まとめ

SQLでWeb解析ログを分析すると、GA4で集めたデータを自由にドリルダウン出来るのがメリットです。

GA4には標準でBigQueryエクスポート機能が備わっています。
みなさんもこの機会にぜひBigQueryを使った自由で柔軟なWeb解析を始めましょう。

弊社ではGoogleAnalytics/AdobeAnalyticsなどの各Martechツールの導入実装コンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ。

Google Analytics 360: BigQueryを使ってアトリビューション分析で91日以上のルックバック期間を実現前のページ

Mouseflow vs Hotjar:どちらを選ぶべき?次のページ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Google BigQuery

    Tableau : BigQueryでLOD計算が使えない場合の対処法

    こんにちは、エクスチュアの渡部です。Tableau×BigQu…

  2. Excel

    Google Analytics URLクエリパラメータ 簡単削除方法

    今回は前回紹介したクエリパラメータを削除する方法に続いて、Excelを…

  3. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う

    こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。…

  4. Google BigQuery

    BigQuery BI Engine解説

    こんにちは、エクスチュア渡部です。2019/4/9-4/11に行わ…

  5. Cortex

    BigQueryとSnowflakeのLLM関数を比較してみた

    はじめにこんにちは、石原です。生成 AI と大規模言…

  6. Google BigQuery

    OWOX BI: GoogleSheetsアドオンでBigQueryをお手軽ビジュアライズ

    こんにちは、エクスチュアの權泳東(コン・ヨンドン)です。今日は…

カテゴリ
最近の記事
  1. Manusを使ってみたうえでManusに感想ブログを書かせて…
  2. SquadbaseとStreamlitでお手軽アプリ開発
  3. [Snowflake Summit 2025] Snowfl…
  4. [Snowflake新機能]AI_AGGを試してみた
  5. Snowflake Summit 2025 参加レポート【D…
  1. Adobe Dynamic Tag Manager

    【Adobe Analytics,Launch】進化するDebugger
  2. Data Clean Room

    SnowflakeのData Clean Roomを基礎から一番詳しく解説(1回…
  3. Adobe Analytics

    Adobe AnalyticsからDWHレポートをGoogle Cloud St…
  4. Amazon Web Services

    IAM (Identity and Access Management) を理解…
  5. Adobe Analytics

    DataWarehouseについて
PAGE TOP