TOP
google big query
GoogleBigQueryを活用したGA4データ分析,SQL活用術応用編

GoogleBigQueryを活用したGA4データ分析,SQL活用術応用編

2021/12/17

目的

BigQueryに保存されたAnalyticsデータを柔軟に抽出して必要なデータを取得する方法として、
レコード内にネストされた情報を分解する方法を説明します。

ネストしたデータから必要なデータを抽出することで、より詳細な情報を得られるようになり、
独自のレポートを作成する際に非常に効果的になります。

課題

前回は1つのSQLでBigQueryに保存されたデータから必要な情報を抽出する方法を説明しました。
今回は複数のSQLを組み合わせて実行した結果を用いてより詳細なデータを収集する方法を中心に説明します。

若干複雑な内容になりますが、この方法を理解する事によって、
GAの探索機能やレポートでは再現の難しい、様々なニーズに合わせたデータ分析へと近づいていきます。

詳細

基本 : 複数SQLの実行結果の集約について

BigQueryのテーブルは下記のようにレコード内にネストされた情報が多く登録されています。
前回UNNEST関数を利用してネストされたデータを取得する方法を説明しましたが、それだけでは複数のデータをネストから分解して一覧に並べることはできません。

この問題を解決するためにWITH句というSQL構文を利用して、
副問合せ(サブクエリ)を作り、名前を付与して一時テーブルとして扱うことができます。

主にGA4データのBigQuery分析においては下記の2点のために利用します。

抽出項目ごとにWITH句作成し最後にまとめて出力する
複雑な検索結果ごとにWITH句作成し最後にまとめて出力する

まずは基本的なWith句の使い方を説明します。
BigQueryでは1レコードにpage_titleとpage_locationがまとまっていて、
それぞれの組み合わせで何軒ずつのレコードが登録されているかがわかりにくい状態です。

BigQueryのテーブル例

下記のSQLを実行することでpage_titleとpage_locationの
組み合わせのレコード件数を集計することが可能になります。

SQL

-- WITHを定義してt1という一時テーブルに情報を取集する
WITH t1 AS (
  SELECT
    -- event_paramsにネストされたデータからkeyがpage_titleの値をtitleという項目名で取得する
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_title") AS title,
    -- event_paramsにネストされたデータからkeyがpage_locationの値をlocationという項目名で取得する
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS location,
    event_name
   FROM
    `analytics_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211101'
    AND '20211110'
)
SELECT
  title, -- t1で取得したtitle
  location,  -- t1で取得したlocation
  COUNT(*) as count -- レコード件数
FROM
  t1
WHERE
  event_name = "first_visit" --　event_nameがfirst_visitの値を抽出
GROUP BY location, title -- SELECT句に列挙したカラム名をグループ化する

SQLの説明

WITH句の詳細
- t1 という名称の一時テーブルを生成して下記の値を取得する
  - ネストされた event_paramsを分解してkey が “page_title”のデータを抽出する
  - ネストされた event_paramsを分解してkey が”page_location”のデータを抽出する
  - 取得対象のレコードはeventカラムの値を対象とする
  - 取得対象のテーブルは2021/11/01から2021/11/10までのものを対象にする
WITH以下のSELECT句
- t1で収集したデータに対してSQLを実行する
- t1からSELECTするカラムはグループ化(GROUP BY)する

結果例

応用 : 日単位セッション数の取得

with句を日単位のセッション数を取得する方法を説明します。
データを取得する上で視認性を向上させるためにevent_timestampのフォーマットをYYYY-MM-DD型に変換します。
ユーザー(ブラウザ)特定IDであるuser_pseudo_idとevent_paramsに含まれているga_sessionを結合した値を1セッションとして扱います。

上記の情報から1日あたりのセッション数を集計することが可能になります。

SQL

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

SQLの説明

WITH句の詳細
- t1 という名称の一時テーブルを生成して下記の値を取得する
- UNIXTIME型のevent_timestampを日本時刻のYYYY-MM-DD型に変換する
- user_pseudo_idとネストされた event_paramsを分解してkey が “ga_session_id”のデータを結合して1セッションのデータとする
- 上記の組み合わせてセッションの発生した日付のレコードが一つ生成される
- 取得対象のレコードはevent_nameがsession_start(セッション開始)を対象にする
- 取得対象のテーブルは2021/12/01から2021/12/10までのものを対象する
WITH以下のSELECT句
- t1で収集したデータに対してSQLを実行する
- セッション数を集計したいので、t1で取得したセッションIDを集計(count)する
- t1からSELECTするカラムはグループ化(GROUP BY)する
  - 過去の記事で説明した通り、実際のカラム名以外でもSELECTの順番の数値を指定することも可能となる

結果例

応用 : ページ別ランディング数、離脱数、直帰数、離脱率の集計

with句を利用してページ別ランディング数・離脱数・直帰数を取得して、離脱率を計算した結果を一覧化する方法を説明します。

このケースではWITH句による一時テーブルを2つ生成します。

一つ目の一時テーブル(t1)ではevent_paramsにネストされたpage_locationのデータと、それに紐付くセッションIDを取得します。セッションIDの形式は前項で説明した内容と同じものになります。

二つ目の一時テーブル(t2)ではt1で取得したデータに対してSQLを実行してアクセスデータを収集します。

最終的にt2の結果に対してSQLを実行して一覧を出力します。

SQL

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_264011635.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20211001' and '20211031'
  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 entry,
    CASE
      WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY  event_timestamp DESC) = 1 THEN 1
      ELSE 0
    END AS exit,
    CASE
      WHEN COUNT(1) OVER(PARTITION BY sid) = 1 THEN 1
      ELSE 0
    END AS bounce
  FROM
    t1
  )
SELECT
  t2.page_location AS page_location,
  SUM(t2.entry) AS entry,
  SUM(t2.exit) AS exit,
  SUM(t2.bounce) AS bounce,
  IFNULL(SAFE_DIVIDE(CAST(SUM(t2.entry) AS numeric), CAST(SUM(t2.exit) AS numeric)), 0) AS exit_ratio
FROM t2
GROUP BY 1
ORDER BY 2 DESC

SQLの説明

WITH句(t1)の詳細
- t1 という名称の一時テーブルを生成して下記の値を取得する
- event_paramsにネストされたpage_locationのデータを取得する
- セッションIDを取得する
  - セッションIDの形式は前項で説明した内容と同じものになる
WITH句(t2)の詳細
- t2 という名称の一時テーブルを生成して下記の値を取得する
  - t2ではt1で取得したデータに対してSQLを実行する
- page_locationに対するセッションIDレコードの順番をもと3つの指標を取得する
  - 今回はCASE文を利用して3つのケースに分類している
  - event_timestamp(イベント発生日)をソートした際に最初に現れるページをランディング、最後に現れるページを離脱ページ、そしてセッション内にレコードが一件の場合は直帰として扱う
WITH以下のSELECT句の詳細
- t2で収集したデータに対してSQLを実行する
  - entry, exit, bounceをそれぞれSUM関数を使い集計します
  - [entryの集計結果 ÷ exitの集計結果]の結果を離脱率として取得する
    - SAFE_DIVIDE関数によって0を割算した際のエラーを回避して、0を設定するようにしている
    - IFNULL関数によってentryまたはexitに値が含まれない場合は計算をしないようにしている
- t2からSELECTするカラムはグループ化(GROUP BY)する

まとめ

複数のSQLを組み合わせてより詳細なレポートを出力する方法について説明しました。

取得したいデータを1回のSQLで取得できない場合はWITH句を利用して一時テーブルを作成することで、データを階層的に収集して使いやすい形にフィルタリングすることが有効になります。

特にBigQueryに登録されるデータの活用はネストされているデータをうまく取り扱うことがコツになります。

前回説明したUNNEST関数とWITHを組み合わせて用途に応じたSQLを生成することが、さらなるデータ活用やニーズに応じたレポートを柔軟に生成するために重要になります。

GoogleBigQueryを活用したGA4データ分析,SQL活用術応用編

目的

課題

詳細

基本 : 複数SQLの実行結果の集約について

BigQueryのテーブル例

SQL

SQLの説明

結果例

応用 : 日単位セッション数の取得

SQL

SQLの説明

結果例

応用 : ページ別ランディング数、離脱数、直帰数、離脱率の集計

SQL

SQLの説明

まとめ

関連記事

概要

お問い合わせ