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

目的

GA4からBigQueryに保存されたAnalyticsデータを利用しSQLで分析をする方法を説明をします。
BigQueryを利用する事で、必要に応じたデータを自由に取得することができます。

GA4の標準レポートや探索レポート（カスタムレポート）では出ないレポートが必要な際に役立ちます。

課題

日々のアクセスデータをBigQueryに保存し続けていても、
どのように活用していくのかが難しいと思います。

Google Analyticsの画面から参照できるデータと同じような結果を
BigQueryから取得する方法を説明します。


※ 逆にいうと、GA4の管理画面、探索機能やレポートで
再現できないデータ分析がBigQueryでは可能となります。

理由としては、最小単位でのローデータをBigQueryで持ってるためです。

詳細説明

基本 : Google Analyticsからデータを参照する

GoogleAnalyticsを利用して下記のようなデータを画面がら簡単に参照することができます。

Google Analyticsの画面

Big Queryからデータを参照する

Big Queryを利用して下記のようなデータを画面から簡単に参照することができます。

Big Queryから取得したデータ

データを取得するためのSQL

SELECT
  geo.country,
  geo.region,
  geo.city,
  count(*) AS access_count
FROM 
  `just-genius-216220.analytics_264011635.events_202107*`
GROUP BY
  geo.country, geo.region, geo.city

応用

SQLによってデータの抽出・集計をする事ができるので
数種類のデータ抽出のアイディアを説明します。

都道府県単位のアクセス数を多い順番で取得する

上述のSQLを下記のように修正します。

• geo.cityを削除して、区市町村を取得対象外にします。
• ORDER BYを追加して、並び替え順序を降順に指定します。

ORDER BYの役割

並べ替え対象にしたいカラムに下記の引数を指定して利用します。

• ASC : 昇順(初期値、無指定の場合は昇順が採用される)
• DESC : 降順

データを取得するためのSQL

SELECT
  geo.country,
  geo.region,
  count(*) AS access_count
FROM 
  `just-genius-216220.analytics_264011635.events_202107*`
GROUP BY 
  geo.country, geo.region
ORDER BY 
  access_count DESC

Big Queryから取得したデータ

このように都道府県毎のアクセス数を一覧化して降順で並び替る事ができます。

流入経路毎のアクセス数を取得する場合

サイトの流入経路毎のアクセス数を取得する場合は少し工夫が必要になります。
流入経路は下記のようにネストされた項目に保存されいてるので、
ネストを分解してデータを取得する必要があります。

データを取得するためのSQL

SELECT
  param.value.string_value AS page_referrer,
  count(*) AS access_count
FROM 
  `just-genius-216220.analytics_264011635.events_202107*`,
  UNNEST(event_params) as param
WHERE
  param.key = "page_referrer"
GROUP BY 
  param.value.string_value
ORDER BY 
  access_count DESC

ネストされた項目の値を取得する際にはUNNEST関数を利用して
ネストされている項目名を指定することによって分解された結果を取得する事ができます。

下記のようにネストされたカラムのトップにあたるevent_paramをUNNEST関数に指定します。

UNNEST(event_params) as param

これによりparamという名称でデータを参照することが可能になります。

Big Queryから取得したデータ

日単位のページビューの一覧化

特定月のデータを抽出するだけではなく、開始終了日を指定したデータ抽出をする事ができます。
日単位のページビューを一覧化するSQLを参考にすると下記のようになります。

データを取得するためのSQL

SELECT
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS target_date,
  COUNT(1) AS page_views
FROM
　　`just-genius-216220.analytics_264011635.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210715' AND '20210815'  AND 
  event_name = 'page_view'
GROUP BY 1
ORDER BY 1

ポイント

_TABLE_SUFFIXを利用して対象テーブル指定することができます。
FROM句のテーブル名末尾の日付部分を*(アスタリスク)にすることによって、
WHERE句でBETWEEN句を利用して抽出することが可能です。

GROUP BY, ORDER BY による集計、並び替えカラムは明示的にカラム名を指定する以外に、SELECT句の表示順を指定することも可能です。

上記の例では1行目に指定したtarget_dateをキーに指定しています。

Big Queryから取得したデータ

まとめ

今回説明したようなシンプルなSQLによってBig Queryからデータを抽出することができます。

応用的な使い方として用途に合わせた関数を利用して
下記のような抽出をするSQLを説明していく予定です。

• 抽出した値をもとに計算する
• 条件によって値を分岐させる
• 複数のSQL結果を組み合わせた結果を抽出する
• テーブルを集約した結果を抽出する

基本的なSQLの使い方を覚えながら、
用途に応じたSQLを組み立てていき必要に応じて関数を利用するような流れで
少しずつSQLに触れる機会を設けていくことが
要求に基づいた効果的なデータ解析を実現していくための道筋になると思います。