ブログ

問題の解決のためにはクラウド環境でのフルマネージドシステムでのシステム設計が必要となり、
Google社のクラウドサービスプラットフォームであるGCP(Google Cloud Platform)に含まれる
BigQueryを中心としたシステム構成にすることによって、
超高速なデータ分析環境の構築が実現できました。

大量データの登録処理についての説明は割愛しますが、
GA4のデータ取り込みの際に利用したFIirebaseのような
簡単に大量のデータをBigQueryに連携できる仕組みが
GCPに含まれているサービスを活用することによって実現可能になります。

最近のクラウドサービスの円熟によって、データ分析のみならずAIや機械学習、
さらには3D CADなどのデザイン分野で高性能な処理をする事が可能となり、
AWS、GCPどちらでも高性能なシステムが運用されていて、様々なビジネスや社会基盤を支えています。

もちろんMicrosoft社のAzureも様々なケースで利用されています。

それ以外でもGCPやAWS上で動作できるデータウェアハウスの
Snowflakeや、
GUI上でのノンコーディングによるデータサイエンスプロセスの自動化が可能であるAlteryxなど
高性能かつ効果的なデータマネジメントサービスが利用されています。

個人的に興味あるのはAWSが提供しているAWS Ground Stationというサービスで、
衛星通信のコントロールやデータ処理を可能としているサービスです。

宇宙開発へどのように活用されていくかが非常に興味深いですね。

概要

GA4のデータを日々BigQueryに蓄積するための設定方法を前回解説しました。
今回は蓄積されたデータから必要なものを集計、抽出するための方法を解説します。

データベースについて

データベースとテーブル

※ MySQLやPostgre SQLはRDBというデータベースの種類です。
RDBはRelational Databaseの略称になり、関係型データベースという意味を示します。

特徴として複数の表でデータを管理して定義の異なる表を関係付けする事ができ、
データ処理が一貫して行うことが可能となります。

企業の社内システムやオンラインストアの会員情報管理など、
身近なところでのデータ管理に活用されています。

その一方で拡張性や処理速度に問題があり、
データ構造の変更や高速化を求めた改造などは専門的な知識を要します。

そして膨大なデータ量を扱う場合には処理速度が落ちるため、
データ分析や機械学習など大量のデータを扱う目的には向いていません。

SQLについて

データの操作や定義を行うために
データベースに問い合わせをするための言語です。

SQLを利用することによって、
例えば特定のデバイスからのアクセスがあったページの抽出や
特定の地域からのアクセスを抽出する事が可能になります。

その他にもSQLではデータ構造の定義変更や
データの新規登録、更新、削除をする事が可能になります。

BigQueryにおけるSQLの特徴

BigQueyでは日単位で別のテーブルにデータが出力されるため、
複数のテーブルの情報を統合して結果を抽出したい場合にもSQLを使うことで実現できます。

なお、BigQueryではweb UIに直接SQL記載して実行する事ができます。


下記の画面にて、[クエリの新規追加]をクリックすると
クエリエディタが表示されてSQLを直接実行することが可能になります。

他のDBでは専用ツールの導入やPCからDBに接続するという前提が必要になりますが、
BigQueryではそのような事前準備が一切不要になります。

GA4からエクスポートされたBigQueryのデータの解析には、
データ参照命令文であるselect文のみ使用します。

それ以外の構文は使用しないのでこちらでは説明を省略します。

参照命令文を覚えるだけで十分な解析ができるため、
コツさえ理解できれば簡単に使えるようになります。

Select文の基本構造

データを参照するためにSQLであるselect文は下記の構造になっています。
参照したいデータに応じて各パラメータの値を変更することで、
様々なデータの抽出ができます。

BigQueryで利用するSQLの注意点

BigQueryは実行したSQLの結果サイズによって課金が発生します。
広範囲のデータを参照するSQLを実行すると大量課金に繋がるため、
参照カラムの指定、検索条件の指定を工夫する必要があります。

下記のようなSQLではデータの絞り込みがされずに
大量のデータが取得されるため避けるようにしましょう。

• select *による全カラム指定でデータを取得する
• where句を省略して取得条件を指定しないデータ取得をする

クエリエディタにSQLを入力すると、
下記の図のように処理されるデータサイズが表示されます。

こちらを参考にSQLを組むこともコスト削減に有効です。

参考資料
BigQuery で費用を抑える

より詳細な情報を抽出する方法

基本的なSQLに関数やパラメータを追加することで、
より詳細な情報を取得する事ができます。

下記のテーブルから抽出するケースを参考に、
簡単でよく使われる事例を方法を説明します。

sample_table

データ件数を集計する

COUNT関数
取得するテーブルの行数を取得することができます。

SQL

SELECT 
  COUNT(*)
FROM
  sample_table
 WHERE
   event_name = 'first_visit'

結果
下記のようにevent_nameが first_visit のデータ集計数が取得できます。

取得する検索結果の上限数を指定する

LIMIT
取得するデータの上限数を指定することができます。

SQL

SELECT 
   *
 FROM
   sample_table
 LIMIT 3

結果
下記のように3行分のデータのみ取得できます。

データの表示順を指定する(ソート)

ORDER BY

取得するデータの中でソートするカラムを指定することができます。

ORDER BYの引数によって昇順、降順を指定できます。
なお、引数を無指定の場合にはデフォルトで昇順が指定されます。

• ASC : 昇順
• DESC : 降順

SQL

SELECT 
   *
 FROM
   sample_table
 ORDER BY 
   event_date DESC

結果
下記のようにevent_dateが降順にソートされます。

対象となるデータのグループ化

GROUP BY

取得するデータのカラムをグループ化することができます。
COUNT関数と組み合わせてグループ毎の集計数を取得する事ができます。

SQL

SELECT 
   browser, 
   COUNT(*)
 FROM
   sample_table
  GROUP BY browser

結果

下記のようにbrowserカラムに出現するパターンが抽出されます。
COUNT関数を指定追加して、browerカラムの修正数を合わせて取得しています。

まとめ

今回はBigQueryを中心としたデータベースについての基礎的な内容を説明しました。

BigQueryはWeb UI上で一連の作業が可能であり、視覚的にも非常に使いやすいです。

また、データ抽出のために別のアプリケーションのインストールや環境設定が不要です。

そしてBigQueryは大量のデータ分析をする上で最適なデータウェアハウスになりますので、 GA4との連携に非常に効果的なWebアクセス解析の実現が可能になります。

GA4のデータ分析で使用するSQLはデータ参照系の処理だけなので、
比較的簡単に覚えることができます。

事例にて説明したCOUNT関数以外にもデータ分析に役立つ関数がありますので
今後、具体的なデータ分析をするためのSQLの説明をする際に
その他の関数の使い方について説明をしていきます。

目的

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を生成することが、さらなるデータ活用やニーズに応じたレポートを柔軟に生成するために重要になります。