BigQuery×GA4の連携とSQL活用
GA4無償版では、ユーザー単位およびイベント単位データの保存期間が14ヵ月までとされている。また、Google BigQuery(以下、BigQuery)にRAWデータを無料で蓄積できることもあり、GA4とともにBigQueryも導入するというのが昨今の流れだ。 BigQueryに蓄積したGA4データを有効活用するには、SQLというデータベース言語を使う必要がある。しかし、多くのユーザーはSQLの利用経験がなく、蓄積したデータを活用しきれていないのが実情である。
2023年1月25日、BigQueryを活用しようと考えている方へ向けて、BigQueryの活用方法やSQLを利用したGA4データの活用について解説するセミナーを開催した。講師を務めたのはマーケティングデータアナリストの八木。今回は、その内容をセミナーレポートとして紹介する。
※セミナー内容がより詳細にわかる※
セミナー資料をダウンロード
※関連ナレッジ資料※
これだけは押さえたい!Googleアナリティクス4(GA4)データ計測設定マニュアル をダウンロード
BigQueryとは?
BigQueryはGoogleが提供しているGoogle Cloud製品の1つで、クラウド型データウェアハウスである。データインポートやデータを格納するストレージとしての機能だけでなく、クエリを実行して分析を行う機能を備えている。「簡単にいうと『データ保管庫のサービス』」と八木は説明した。また、大規模データに対して高速でクエリを実行できるほか、低コストで利用できるなど様々な特徴がある。
BigQueryとGA4を連携するメリット

図1:GA4とBigQueryのデータ連携イメージ
BigQueryとGA4を連携する一番のメリットは、「BigQueryへRAWデータをエクスポートすることにより、データ活用の幅が広がる」=「マーケティングの仮説検証がより詳細にできる」ということだ。
GA4をBigQuery以外のデータウェアハウスと接続する場合、ETLツールなどのコネクタが別途必要になる。一方、GA4とBigQueryはコネクタなしで直接接続できるという連携面での強みもある。
GA4データをBigQueryにエクスポートすることで、以下のようなメリットがある。
・14ヶ月より過去のログデータも取り扱えるようになる
・GA4の探索レポートでは実現できない分析ができる
・CRM、SFA、MAのデータなど、他データと結合した分析ができる
・GCPの機械学習機能を使った分析にGA4データを利用できる
・Looker StudioやTableauなどの外部ツールと連携して分析ができる
GA4とBigQueryを連携することで、様々な切り口からデータ分析できるようになる。また、MAやSFA/CRMなどのデータもあわせてBigQueryに連携すると、オンラインオフラインを跨いだ顧客行動を分析できるようになる。
BigQueryの料金体系
BigQueryの利用料はStrage(データ保持)とCompute(データ操作)の2種類があり、合計して算出する。
ただ、両者には無料枠が設けられている。storageは月10GB、computeは月1TBまで無償で利用できることも押さえておくと良いだろう。
料金が気になる方は、Google Cloud Platformに備わっている「予算とアラート」機能を活用するのがおすすめだ。
詳しくはこちら ⇀
BigQueryの構造とクエリについて
BigQueryは大分すると「プロジェクト」「データセット」「テーブル」で構成されている。
プロジェクト
BigQueryの抽象概念におけるトップコンテナ。プロジェクトを作成しなければ、データセットやテーブルは作成できない仕組みになっている。また、データセットとテーブルはプロジェクトに紐づく形になる。
データセット
テーブルの集合を所有する、フォルダのようなもの。
テーブル
データを格納した行と列の値が入っている場所。実際のデータそのもの。
次に、クエリについて解説する。 クエリとは、システムへの問い合わせや処理要求を行う命令のこと。SQLを用いてデータベースに対する処理要求をおこなう。クエリを実行することでデータを入れたり、処理したりすることができる。
具体的な例を取り上げた説明あり
セミナー資料をダウンロード
GA4とBigQueryを連携する際に考えておくべきこと
これまでの内容や昨今のGA4の状況を踏まえ、GA4とBigQueryを連携する際に検討すべきこと、備えておくべきことについて4つ紹介する。
①SQL学習に興味のある社内メンバーがいるかどうか
勉強すれば理解できるくらいの難易度ではあるが、苦手意識を持ってしまうと習得しづらい。しかし、BigQueryにデータをためた後はSQLを書いて分析をすることが求められる。社内にSQLへの理解がある人がいるとBigQuery活用を進めやすい。学習意欲の高いメンバーを見つけておくと良いだろう。
②14か月間を超えるデータを扱う予定があるか
分析によっては14か月を超えるRAWデータが必要ないこともある。GA4への移行を機に、自社にとって本当に必要なデータは何か、見直すことも重要だ。
③予算
BigQueryはStrage(データ保持)とCompute(データ操作)で課金される。初期はそこまで大きな費用にならないことが多いが、データが増えるにつれBigQueryの利用料は上昇していく。 長期的にBigQueryを運用していくことを見据えて、予算を検討する必要がある。
④Looker StudioのGA4-API割り当て制限解消にBigQueryを利用するかどうか
Looker StudioのGA4コネクタを利用する場合、割り当て制限が発生してLooker Studioのデータコネクト画面にエラーが出ている方もいるだろう。その場合、GA4データを一旦BigQueryに蓄積し、Looker Studioから参照することも検討する必要がある。
BigQueryに蓄積したGA4データの特徴
BigQueryに蓄積したGA4データを分析する方法として、今回はBigQuery上でSQLを書いてクエリを実行する方法について紹介する。
【特徴①日付別テーブル構造】

図2:日付別テーブル構造の解説
図2のように、GA4データはBigQuery内で日付ごとに作成されたテーブルで保存されている。図2の『events_20230123』というのは図2の画面を開いている日だ。『01-23▼』をクリックすると日付を選択でき、日付ごとのテーブルを確認できるようになっている。
【特徴②ネスト構造】
ネストとは、1つの要素に複数の要素が含まれる『入れ子構造』のこと。1つの大きな要素のなかに複数の要素が入っている構造のことを指す。
ネスト構造になっている場合、そのままの状態でデータを抽出することはできない。「unnest」という処理で入れ子構造を分解し、単体で抽出できるようにする必要がある。
ネスト構造をより詳細に説明
セミナー資料をダウンロード
SQLの活用例
ここからは、実際にSQLを活用してGA4データを集計する方法をパターンに分けて紹介する。
特定の期間のPV数を集計する

BigQueryでは基本的に、以下の形でSQLを書く。
・SELECT:どの項目のデータを持ってくるのか
・FROM:どのテーブルからデータを持ってくるのか
・WHERE:どの条件でデータを持ってくるのか
上記のSQLコードは、『event_date』というイベントを『PageViews』というフィールド名で集計しているもの。FROM部分の『events_*』はワイルドカードの形式で特定の日付を指定しないという形になっている。
また、過去7日間という条件の指定をしているが、『format_date("%Y%m%d", date_add(current_date(), INTERVAL -7 DAY))』部分を '20230101'のようにYYYYMMDDの形式に書き換えることで、特定の日付を指定することもできる。
最下部では、PV数を集計するためイベント名を『page_view』と指定している。
特定の期間のセッション数を集計する

GA4データをBigQueryへエクスポートした際、『ga_session_id』というパラメータ情報のなかに、セッションが開始されたタイムスタンプが入る。ただこれはユニークなIDではないため、『user_pseudo_id』というCookieベースのユーザーID情報を使い、文字列を結合する処理をしている。そうすることで、ユニークな集計をおこなえるようになる。
ページ別の閲覧数を集計する

ここでは『page_location』すなわちページのURLを出力するために、イベントパラメータの値をunnestし、イベントパラメータのキーの情報が『page_location』の時に値(文字列)を集計するという書き方をしている。
また、量的なデータは集計軸を指定しなければ集計できないため、『event_date』と『page_location』をグループの項目として指定している。
(参考)CVしたセッションでの閲覧ページを集計する
CVしたセッションでの閲覧ページを集計する方法については、以下のSQLコードを参考にしてほしい。

GA4支援サービスの紹介
パワー・インタラクティブはGA4支援サービスを提供している。大きく分けて、2つのサービスがある。
・GA4導入支援サービス
・GA4活用支援サービス
GA4導入支援サービスの紹介

GA4支援サービスの4つのプラン
GA4導入支援サービスでは、4つのプランを用意している。下記の基準を参考に、利用するプランを検討してほしい。
・簡単なログ分析のみを実施:ミニマムプラン
・ダッシュボードでKPIのモニタリングを効率化:スタンダードプラン
・GA4とBigQueryを連携して14ヶ月以上のデータ蓄積:アドバンスプラン
・MAやCRMなど他データも集約し、多様な分析ができる環境を整備:プロフェッショナルプラン
データ分析は事業推進のエンジンになる。BigQueryと連携したGA4を導入し、Looker Studioで可視化することで、データに基づいた合理的な判断ができる体制の整備をサポートしている。
GA4活用支援サービスの紹介
GA4を導入した後は、実際に活用していく必要がある。しかし、使い慣れたUAの計測が停止していない現状、GA4の活用に乗り出せている方は多くない。
弊社ではGA4の活用支援サービスとして、以下のようなサポートを提供している。
・探索レポートやLooker Studioを用いたレポート作成の指南
・GA4関連情報の解説
・GA4新規イベント設定の示唆、取得設定
・Q&A対応
GA4は実際に活用して初めて効果を発揮する。導入はしたものの、活用に至らず困っている方は弊社に相談してほしい。
※セミナー内容がより詳細にわかる※
セミナー資料をダウンロード

マーケティングデータアナリスト
八木 耕祐
Web行動履歴やアプリデータによる顧客行動分析
アナリストとして、50社のアクセスログ分析に携わる。現在は、データ設計、データマート構築などの基盤づくりから、ダッシュボード作成、分析まで、データ活用を極めている。セミナー登壇は50回以上、満足度90%以上のセミナーも多数。
リモートワークになり、海の近くでマリンスポーツをエンジョイ中。