Googleアナリティクス４（GA4）とBigQueryを連携して特定データの取得にチャレンジ　【実践例つき】

2023年11月29日2024年3月31日

こんな方におすすめの記事です！

GA4の設定と見方は分かってきたけど、もう少しピンポイントの情報だけが見れたら良いなと思い始めた方

BigQueryとGA4連携させることで何ができ、何が良いのかを知りたい方

BigQueryについて専門的な解説だけだと分かりづらいので、もう少し簡単な記事をお探しの方

のびノーリ

筆者もコロナ禍をきっかけに50歳からITを学び始めました。

その結果、以下の項目を達成しています！

ブログを立ち上げて、生成AIツールなども活用し収益化達成

統計検定2級

Python3エンジニア認定データ分析試験などの資格も取得

GA4とはGoogleが無料で提供しているあらゆるプラットフォームにおいて、サイトやアプリからのデータを収集し分析するためのアクセス解析ツールです。

BigQueryとは、Googleが提供するクラウドベースのデータウェアハウスで、大量のデータを高速かつ効率的に格納し、分析することができます。

↓BigQueryについて詳しい説明はこちらの記事でも紹介しているので参考にされて下さい。

【超入門編】スプレッドシート→BigQuery→LookerStudioとの連携をご紹介！【実践例付】

2023年11月19日 2024年3月31日

目次 [ close ]

GA4とBigQueryを連携することでできること

ここではGA４とBigQueryを連携させることをおすすめする理由をご紹介します。

GA４のデータ保持期限リスクの回避

GA4で取得されたデータは最大で14ヵ月保持され、14ヵ月を過ぎたデータは月単位で自動的に削除されることになります。

データを長いスパンで比較検討したりする場合や保存しておく必要がある場合など、BigQueryと連携させることでデータの長期保存が可能になります。

データの絞り込みなど、SQL分析が可能に

GA4でだけでも十分なデータが取得されますが、本当に必要なデータだけが欲しいといったケースもあるかと思います。

そういったニーズにBigQueryと連携させ、データベースからデータを取得するなどの指示を出す際に用いる言語：SQL（クエリ）を使って分析することで応えられます。

ちなみにSQL（クエリ）にもデーターベースの種類により多少方言みたいなものが存在します。

この記事ではクエリという表現に統一して書き進めていきます。

GA4のリアルタイムデータとオフラインデータとの統合

例えば実店舗を運営していて取得しているオフラインデータとネット販売（ECサイト）で得られるリアルタイムなデータとを、BigQueryと連携して統合させることでより深く顧客行動や傾向などを捉えることができるようになります。

オフラインデータはエクセルなどで管理しているかと思われますが、BigQueryとの連携も案外簡単にできます。

BigQueryに関する注意点

BigQueryは無料で使える枠があるものの、基本的に課金サービスですので念のためご注意下さい。

といっても保存データの無料枠は毎月10GBまででクエリについては毎月１TBまでは無料なので、よほど大規模なサイト運営をしていない限りはあまり神経質になる必要はないかと思います。

↓詳細は公式サイトにてご確認下さい。

https://cloud.google.com/bigquery/pricing?hl=ja

BigQueryにおけるGA４データ構造について

GA4とBigQueryを連携する具体的な手順を詳細に紹介しているサイトが沢山ありますので、連携自体から知りたい方はそれらの記事を参考にして連携から始めてください。

この記事では既に連携されていることを前提に進めていきます。

GA４のデータの保存について

GA4からのデータは下の画像のように日別に保存されていきます。

BigQueryが取り込んだGA4のデータ構造について

下の画像のようにevnet_nameがfirst_visitの項目にevent.prams.keyがga_session_numberからengaged_session_eventまで情報が紐づいていて、入れ子（ネスト）になった特殊な構造になります。

一般的なデータベースは１対１のデータ構造となっておりますが、この辺がBigQuery独特のクエリでデータを展開することになります。

BigQueryでGA4のデータ分析時によく使うクエリ

GA4のデータを分析するにあたり、よく使うことになるクエリを一部紹介します。

① 特定のページURLを取得するクエリ

GA4では発生したイベントを計測しますが、そのイベントの情報がevent_name（イベント名）で分類されており、かつ分類されたイベントの中でも属性（パラメータ：event_params）ごとに何の（key)、値（value)か更に分類された入れ子（ネスト）になった形で記録されています。

そのevent_paramsからページURLを取得するクエリが必ず使われます。

下のSELECT句は後述の実践クエリでは欲しい情報を入れ子のクエリ（サブクエリ）として使用しておりますのでご確認ください。

SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location

② セッション数を取得するクエリ

セッション数を求めるときは少々注意が必要で一発で取得できず、データを重複してカウントしてしまう可能性を取り除くため、user_pseudo_id（cookeiに保存されるデバイス固有のid)とga_session_id（ユーザーがサイトにアクセスした回数が分かるセッションID）を連結させてセッション数をカウントする時に使うクエリになります。（２行に分かれます）

また、後ほど紹介する筆者が書いたコードでは連続しておらず、下のSELECT句はサブクエリとして使用しておりますのでご注意ください。

SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id

COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_count

③ 日付の範囲指定をするクエリ

下のクエリではWhere句で昨日から遡って一週間を範囲指定する相対的に指定するクエリになっています.

例えば１ヶ月を指定したい場合はinterval 7 → 30 or 31に変更するといった具合に柔軟に対応できるクエリです。

WHERE _table_suffix between format_date('%Y%m%d', date_sub(current_date(), interval 7 day)) and format_date('%Y%m%d', date_sub 
   (current_date(), interval 1 day)

特定の日付で範囲指定する場合は下のクエリで大丈夫です。（例えば20231101〜20231128を指定）

WHERE _table_suffix between '20231101'and '20231128'

実践　BigQueryでターゲット指標の取得

実際に筆者のブログサイトのデータを使って分析してみましたので結果を公開します。

ベージ及びページタイトル毎のセッション数をランキング化

前章で解説しましたクエリを使って、ベージURLとタイトルごとに今月のセッション数をランキング化したクエリと結果が下の画像になります。

FROM句にある目隠し部分はBigQueryのプロジェクトI DとアナリティクスのデータセットIDになります。

筆者のブログサイトにおけるセッション数ベスト３が確認できました。

クエリの解説

①WITH句を使って事前準備

下のクエリの様にWITH句であらかじめ取得するデータが整理されたテーブルを準備するイメージです。

中間テーブル名をprepとして作成し、そのテーブルにはpage_location、page_title及びga_session_idをUNNEST関数を使って展開済の値にして取得しておきます。

WITH prep AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,　 ---　①で説明した良く使うクエリ
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, 
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id　　 ---　②で説明した良く使うクエリ（上段）
  FROM `＊＊＊＊＊-＊＊＊-＊＊＊＊＊＊.analytics_＊＊＊＊＊＊＊＊＊.events_*`
  where _table_suffix between '20231101'and '20231127'　　 ---　③で説明した良く使うクエリ
   )

②本命のデータを取得するクエリを続ける

SELECT
page_location,
page_title, 
COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_count,　　---　②で説明した良く使うクエリ（下段）
FROM prep
group by 1,2
order by 3 desc;

あらかじめ展開しておいたpage_location、page_titleは中間テーブル（prep)からそのまま呼び出し、ga_session_idは展開する必要がなかったuser_pseudo_idと下のクエリの通り連結してsession_countとしてページ毎に集計します。

上記の2ステップのクエリでターゲットとした、月あたりセッション数のランキングをピンポイントで調べることができました。