オウンドメディア担当者が知っておきたい GA4 × BigQuery 後編（寄稿：小川卓）

今回は、全2回で Google アナリティクス4（以下「GA4」) × BigQuery に関して紹介をしていきます。

後編となる第2回は、BigQueryとGA４の連携方法や、GA4のデータをより活用するための方法についてご紹介していきます。GA4 × BigQuery を活用することで、オウンドメディアの分析や効果の可視化に大きく役立つと思いますので、ぜひ参考にしてみてください。

なお、BigQuery内でGA4データがどのように保存されているかなど、データ構造に関しては本記事で詳しく触れませんのでご理解いただければ幸いです。

BigQueryとGA4の連携方法
- BigQueryのセットアップ
BigQueryでSQLクエリを書いてデータを取得する方法
BigQueryと他ツールの連携
- Googleスプレッドシートとの連携
- Looker Studioとの連携
まとめ

BigQueryとGA4の連携方法

前回の記事では、BigQueryとGA4を連携することの意味やどのように役立つかを紹介いたしました。前回の記事を読んで使ってみたいと思われた方も多いのではないでしょうか。利用するためには、まずBigQueryとGA4を連携する必要があります。「BigQueryのセットアップ」と「GA4からBigQueryにデータを送る」という２つのステップに分けて具体的に手順を紹介いたします。2023年8月時点での方法・画面となります。

BigQueryのセットアップ

まず会社で既にGoogle Cloud Platformを契約しているかを確認してください。既に契約している場合は、最初のステップ「Google Cloudのアカウントを作成」はスキップして「BigQueryのプロジェクト作成」から開始してください。

既にアカウントがある場合、Google Cloud Platformをメンテナンスしている方が社内にいるかと思いますので、その方に相談してセットアップを進めてもらうと良いでしょう。勝手に自分で作ることにより社内に複数のGoogle Cloud Platformが存在し管理ができなくなる、既に利用している他の機能に影響が出てしまう可能性があります。

また既にGA4とBigQueryが連携済みの可能性もあります、こちらも管理者がいたら事前に確認をするか、このあと紹介するGA4との連携方法で、既に連携が完了しているかをGA4の管理画面で確認しましょう。

Google Cloudのアカウントを作成

【1】https://cloud.google.com/にアクセスし右上にある「無料を開始」を押します。この際に利用するGoogle アカウントに事前にログインしておいてください。

【2】アカウント情報を入力し、続行を押します。

【3】SMSを受け取れる電話番号を入力し、コードを送信します。

【4】メッセージが届きますのでその番号を入力して、次のステップに進んでください。

組織名、お支払い方法、名前や住所などの情報を入力して「無料トライアルを開始」を押すとGoogle Cloud Platformのアカウント作成が完了します。

※無料トライアルに関する詳細は以下ページをご覧下さい
https://cloud.google.com/free

BigQueryのプロジェクトを作成する

Google Cloud Platformのアカウントが完了したら、今回利用するBigQueryのプロジェクト（データを集計するための箱）を作成します。

【1】左側のメニューからBig Queryを選択

【2】上部にある「My First Project」を選択し、その後に表示されるポップアップ画面で「新しいプロジェクト」を選択

【3】英文字でプロジェクト名を入力し、プルダウンから組織と場所を選びます。

最後に作成を押して完了です。プロジェクト名はGA4と連携する際に選択する必要があるので名前を覚えておきましょう。

GA4からBigQueryにデータを送る

GA4が導入されているか、そしてBigQueryが連携されていないかを確認しましょう。GA4の管理メニュー内にある「BigQueryのリンク」でプロジェクトが表示されない場合は連携されていない状態となりますので、連携を進めていきましょう。

【1】以下の画面から「リンク」を選択する
この際にGoogle Cloud Platformの権限を持ったアカウントでGA4にログインする必要がありますので、出来ていない場合はそれぞれの権限を付与しておきましょう。

【2】次の画面で「BigQueryプロジェクトを選択する」内にある「BigQuery プロジェクトを選択（青い文字）」を押してください。

【3】権限があるプロジェクトの一覧が表示されますので、GA4用に作成したプロジェクトを選んで右上の「確認」ボタンを押してください。

【4】データのローケーションを選んでください。日本のサイトの場合は「東京」あるいは「大阪」を選ぶと良いでしょう。選んだら「次へ」を押してください。

【5】構成の設定を入力していきます。

今回エクスポートするサイトの１日のイベント数が表示されます（無償版は１日100万イベントが上限となっています）。データストリームとイベントの設定で、除外したいイベントなどを指定することが可能です。

そして大切なのがエクスポートタイプになります。毎日とストリーミングがあります。毎日を選ぶと１日が終わったあとにBigQueryのその日のデータセットが作成されます。そのため前日までのデータを元にクエリを書く必要があります。ストリーミングがすぐにデータが送信され「随時更新される「今日」のデータセット」が作成され１日が終わると、その日のデータセットが作られます。

リアルタイムで当日のデータを見たいという場合はストリーミングを選ぶことになりますが、こちらは有償となり、200MBあたり$0.010となっています（2023年7月時点）。最新の料金は以下ページをご覧ください。

料金 | BigQuery: クラウドデータウェアハウス | Google Cloud

ストリーミングを利用するためには赤文字にある通り、Google Cloud Platform側で課金が有効になっている必要があります。リアルタイム性を求めていない場合は毎日の方を選択しておきましょう。

【6】内容を確認したら「送信」を押して連携が完了します。データがたまるのを待ってください。

データが貯まると、BigQueryプロジェクト配下にデータがたまりはじめます。以下のような構成となっています。

プロジェクト名：ha-ga4
データセットID：analytics_XXXXXXXXXX
テーブル：
- events_　日ごとに作成されたテーブル
- events_intraday_ 集計途中の本日のデーブル（ストリーミング有効時）

BigQueryでSQLクエリを書いてデータを取得する方法

データが溜まり始めたら、いよいよデータを取得することが可能となります。データをBigQueryから取得するためにはSQLクエリを書く必要があります。これは保存されているデータに対して、どういった条件データを取得してくるか？というのを指示する文章になります。

それでは流れを見ていきましょう。まずはevents_を選択し、右側の画面にある「クエリ」ボタンを押しましょう。

クエリを作成する画面が表示されます。

クエリを入力します。例えば日ごとのユーザーであれば以下のようなSQLクエリを利用します。

select
　date(timestamp_micros(event_timestamp),"Asia/Tokyo") as event_date, -- イベントの発生日付を選択
count(distinct user_pseudo_id) as users 　-- CookieIDのユニークな数をカウントする
from
`ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by
event_date

右上に処理するデータ量が出てきますので確認の上「実行」を押してください。
※データ処理と料金の関係に関しては前回の記事で紹介しています。

実行結果が画面下部に表示されます。

これが大きな流れとなります。

ここでやはりポイントになってくるのはどういったSQLクエリを書けばどのようなデータが出てくるか？というところになります。ここでSQLについて説明をしてしまうと終わりきらないので、筆者が用意した「GA4用のBigQuery クエリ集」を案内しておきます。

GA4用のBigQuery クエリ集 – Google Analytics 4 ガイド

このページでは現在37個のクエリ例とその出力結果を紹介しています。自社プロジェクトIDに置き換えればそのままコピペして利用することが可能です。

より深くSQLクエリを学びたい場合は以下などを利用するとよいでしょう。

UdemyのGoogle BigQueryコース

https://www.udemy.com/topic/google-big-query/?persist_locale&locale=ja_JP

海外著名アナリストのSimo Ahavaのコース

https://www.teamsimmer.com/all-courses/query-ga4-data-in-google-bigquery/

BigQueryと他ツールの連携

BigQueryから直接データを取得する方法としてSQLクエリがありますが、それ以外にも他サービスからBigQueryのデータを呼び出す方法もあります。本記事ではGoogleの他プロダクトである「Google スプレッドシート」と「Looker Studio」の連携方法について紹介いたします。

なお連携してデータを取得する場合は前回も触れた分析用の無償枠1TB/月の対象となりますのでご注意いただければ。

Googleスプレッドシートとの連携

Google スプレッドシートでは「データコネクタ」という機能が用意されており、BigQueryに直接つなぐことが出来ます。

利用したいプロジェクトなどを選択して進めていくと

スプレッドシート上にデータが出力され、そこからグラフやピボットテーブルなどを利用してレポートを作成できます。

Looker Studioとの連携

ダッシュボード兼BIツールであるLooker StudioとBigQueryも連携が可能です。Looker StudioとGA4自体が連携可能ですが、SQL クエリの結果を直接Looker Studioと連携したりより細かいデータ取得を行いたい場合はBigQueryと連携するのが良いでしょう。

Looker Studioと連携するためには、データソースの追加を選択し

コネクタからBigQueryを選び、データが格納されているプロジェクト＞データセットと選んでいき追加を行います。

これによりLooker Studioのインターフェース内でBigQueryのデータを操作することが可能となります。

ちなみにBigQuery側で実行したSQL クエリの結果をスプレッドシートやLooker Studioに送ることも可能です。

まとめ

今回はGA4とBigQueryを連携して利用する方法を紹介してきました。これでもまだBigQueryの第一歩ではありますがイメージは掴めたのではないでしょうか？なおGoogleはGA4のデータが格納されたBigQueryのサンプルデータを用意してくれています。Google Cloud Platformのアカウントさえあれば、誰でも利用することが出来ますので、公式ヘルプを参考にまずはこのサンプルデータから使ってみても良いかもしれません。