TOP
google big query
GA4のデータをBigQueryから参照する方法

GA4のデータをBigQueryから参照する方法

2021/12/18

概要

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

データベースについて

データベースとテーブル

データベースは検索や集計が容易できるよう整理された情報集合体です。

GA4のデータが出力されたBigQueryがデータベースになります
DBと省略する事があります。

BigQueryの他にもMySQLやPostgre SQL※など様々なデータベースがあります。

実際にデータが保存される場所はテーブルです。
DBとテーブルの関係性はSpreadSheetのファイルとタブの関係性に似ています。

※ 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の説明をする際に
その他の関数の使い方について説明をしていきます。