SQL

GA4とBigQueryの連携方法と基本的なunnestなどのクエリを学ぼう!

GA×BigQuery
記事内に商品プロモーションを含む場合があります
ウマたん
ウマたん
当サイト【スタビジ】の本記事では、GA4とBigQueryの連携方法を見ていった後にBigQueryに連携することで出来ることを解説していきます。また、GA4のデータをBigQueryで集計・分析する際に利用する基本的なunnestなどのクエリやセッションの集計方法も同時に解説していきますよ!

こんにちは!データサイエンティストのウマたん(@statistics1012)です!

Webサービスの分析で欠かせないのがGA4!

そしてGA4だけでも十分問題ないのですが、GA4とあわせて使えると便利なのがBigQuery

BigQuery」とは、Google Cloud が提供する大規模なデータを管理・分析するためのデータウェアハウスです。

BigQueryにGA4のデータを連携してSQLで集計・分析することでGA4だけでは出来ないより細かい分析が可能です。

実はGA4はBigQueryと簡単に連携できるんです!

ぜひこの記事を読んでGA4とBigQueryの連携方法、そして簡単なクエリについて理解していきましょう!

GA4のデータは特殊なネスト構造になっていて最初は戸惑いますが順を追って見ていけば問題ありません。

BigQueryに関しては以下の記事で詳しく解説していますのであわせてチェックしてみてください!

bigquery
BigQueryとは?サービス内容やできること4つを紹介!当サイト【スタビジ】の本記事では、BigQueryついて紹介します。サービス内容やBigQueryで出来ること、サービスを利用する際の注意点まで徹底的に解説していきます!本記事を読んでBigQueryによるデータ分析を始めていきましょう!...

ちなみにUdemyにてGA4のデータをBigQueryで集計してPythonで分析しLookerStudioで可視化する応用コースを出しているので興味がある人はチェックしてみてください!

GA4のログデータをBigQueryで集計してPythonで分析&LookerStudioで可視化してみよう!

Udemy_BQGA4
【時間】4時間
【レベル】初級

GA4のデータをBigQueryで集計してPythonで分析してLookerStudioで可視化するという一連の流れを学びたいのであればこれ!

\30日以内なら返金無料/このコースを見てみる

GA4とBigQueryの連携方法

まず最初にGA4とBigQueryの連携方法について見ていきたいと思います。

Step1:GCPのアカウントを作成しBigQueryを使えるようにする

まずは、BigQueryのトップページに入り、”BigQueryの無料トライアル“をクリックします。

bigquery

下記項目に適切なものを選択し、”続行”をクリックします。

  • :住んでいる国を選択
  • お客様の組織またはニーズの説明として最も適切なものはどれですか?:GCPの使用用途を選択
  • 利用規約:GCPの利用規約への同意
  • 最新情報に関する通知メール:GCP関連ニュースの通知の是非
bigquery3

次に”お支払いプロファイル”で支払い情報を入力して「無料トライアルを開始」をクリックしてGoogle Cloudにログインできます。

支払情報は本人確認のため行われるもので、何かリソースを立ちあげないかぎりは自動で課金が発生することはないです。

ウマたん
ウマたん
BigQueryを利用するには、まずはGCPのアカウントを作ろう!

Step2:BigQuery APIを有効化する

続いてBigQuery APIを有効化する必要があります。

Google Cloudにログインが出来たら検索ボックスにBigQuery APIと入力して選択しましょう!

そしてBigQuery APIを有効化しましょう。既にAPIが有効になっている場合はそのままで問題ございません。

Step3:GA4の画面からBigQuery連携の設定を行う

続いてGA4のアカウントに移りましょう!

GA4の画面からBigQuery連携の設定をおこなっていきます。

左下の管理ボタンを押し、いくつかある項目の中から「サービス間のリンク設定」を押しましょう!

そしてその中からBigQueryのリンクを押しましょう!

続いてリンクというボタンを押下してください。

BigQuery連携設定画面に移ります。連携したいBigQueryプロジェクトを選択してください。

イベントデータのエクスポートタイプはリアルタイムで連携するか1日に1回連携するか選ぶことが可能です。

設定を完了して送信ボタンを押せば連携完了!めちゃくちゃ簡単ですね。

データが連携され始めるまでには数日かかることがあるので注意しましょう。

数日経ったらBigQueryを見てみるとデータが貯まっていることを確認できるはずです。

GA4とBigQueryとの連携でできること

それではBigQueryへの連携が完了したところで、連携するとどんなことができるのか?どんないいことがあるのか?見ていきましょう!

SQLを使ってGA4のデータを集計・分析することができる

BigQueryに貯まったデータはSQLを使って集計・分析することが可能です。

GA4の管理画面上からだけではなかなかできないような細かい集計が可能なのです!

例えば、「ユーザーがどのタッチポイントでどの広告に触れて最終的に購買に至っているか」などのアトリビューション分析。

また、購買データも同様にBigQueryに保有しているのであれば、GA4のログデータと購買データを組み合わせてよく購入してくれているロイヤルユーザーの動きを追うことも可能です。

このようにGA4をBigQueryに連携するだけで集計・分析の幅が広がります。

BigQuery内のGA4データにアクセスしてPythonで分析ができる

また、GA4のログデータをBigQueryに連携して保持しておくことで簡単にPythonからBigQueryのデータにアクセスして分析することが可能です。

Python上では以下のように記述するだけで簡単にBigQueryのデータにアクセスすることが可能なんです!

from google.cloud import bigquery

project_id = '自分の対象プロジェクトID'

# クライアント認証のためのコード
client = bigquery.Client(project=project_id)

# BigQueryにアクセスして実行するクエリ
query =  """
                SELECT  
                *
                FROM 
                `自分のプロジェクトID.dataset.table` 
                ;
         """
# 以下で、dfに上で指定したデータを格納できます!
df = client.query(query).to_dataframe()

 

PythonでBigQueryのデータにアクセスして分析する方法について詳しく知りたい方は以下のUdemyコースで解説していますのでチェックしてみてください!

【初学者向け】BigQueryを使ってSQLで集計しPythonで加工・可視化・機械学習モデル構築まで一気通貫で行おう!

Udemy_BQ
【時間】3.5時間
【レベル】初級

BigQueryの使い方を理解してSQLからPythonまでの一気通貫の処理を理解したいのであればこれ!

\30日以内なら返金無料/このコースを見てみる

またスタビジアカデミーの「SQL実践コース」でも同様にBigQueryのデータをPythonで分析する方法について取り上げています。

細かい集計した結果をLookerStudioに連携して可視化できる

さらにBigQueryで集計した結果をもとにLookerStudioというGoogleが用意しているBIツールで可視化することが可能です。

GA4から直接LookerStudioに連携して可視化することもできるのですが、それだと可視化できる内容が限られてしまいます。

SQLを使って集計した結果をLookerStudioに連携することで多様な可視化ダッシュボードを作ることができるのです。

このLookerStudioに関しては以下の記事で解説していますので興味のある方はチェックしてみてください!

looker_studio
【入門】Looker Studioの使い方を徹底解説!データを集めて分析してみよう!当サイト【スタビジ】の本記事では、Looker Studioの使い方を解説していきます!Looker Studioは様々なツールで収集したデータを可視化・分析できるBIツール!実務でもよく利用されるので使い方をマスターしましょう!...

BigQueryでのGA4の基本的なクエリ

それではBigQueryでGA4のデータを集計する際にどんなクエリを書いたらよいか簡単に見ていきましょう!

ここでは、重要なポイントを2つ取り上げます。

ネスト構造を分解するunnestの使い方

GA4のデータは以下のような構造になっています。

GA4のレコード

event単位ごとに各レコードが存在し、eventにはsession_startやpage_viewなどが存在します。

従来のGAがpage_view単位だったのに対して、GA4ではpage_viewも含めた様々なevent単位でレコードが存在しているのが特徴です。

そして、以下のようにeventレコードに対してga_session_numberやpage_referrerなどの複数のkey・valueが紐づいているのがわかります。

GA4のレコード

このような構造をネスト構造と言い、ネスト構造を解消するのには以下のようにUNNESTという構文を使います。

select
  event_timestamp,
  event_name,
  params
from
  `プロジェクトID.データセット名.テーブル名`, unnest(event_params) params

 

このunnestを使ってネスト構造を分解してフラット化することで通常のテーブルと同様に扱いやすくなります。

セッションの集計方法

また、GA4をBigQueryに連携するとuser_pseudo_idというカラムが確認できます。

これはGA4が付与するユーザー識別のためのIDでありUUを集計する際に利用できます。

user_idというものも存在するのですが、デフォルトではNullになっています。

そして、このuser_pseudo_idとga_session_idを文字列結合したものを重複除外し合計したものがセッション数になります。

そのためセッションを集計する際は以下のように記述します。

with session_id_tb as (
  select
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  from
    `プロジェクトID.データセット名.テーブル名`
)

select
  event_date,
  count(distinct concat(user_pseudo_id, ga_session_id)) session_num
from
  session_id_tb
group by event_date
order by event_date

 

このネスト構造の分解方法とセッションの集計方法さえ覚えておけばGA4の集計は基本つまづかないと思います!

もっとGA4のデータの扱い方やクエリに関して詳しく知りたい方はUdemyの「GA4のログデータをBigQueryで集計してPythonで分析&LookerStudioで可視化してみよう!」で詳しく説明しているのでチェックしてみてください。

GA4とBigQueryの連携方法 まとめ

ここまでで、GA4とBigQueryの連携方法について見てきました。

GA4を使っている人は非常に多いですが、BigQueryと連携してゴリゴリSQLで集計している人はまだまだ多くありません。

ぜひさらに踏み込んだGA4の分析ができるようになりましょう!市場価値バク上がりです!

冒頭でもお伝えしましたが、UdemyにてGA4のデータをBigQueryで集計してPythonで分析しLookerStudioで可視化する応用コースを出しているので興味がある人はチェックしてみてください!

GA4のログデータをBigQueryで集計してPythonで分析&LookerStudioで可視化してみよう!

Udemy_BQGA4
【時間】4時間
【レベル】初級

GA4のデータをBigQueryで集計してPythonで分析してLookerStudioで可視化するという一連の流れを学びたいのであればこれ!

\30日以内なら返金無料/このコースを見てみる

また、当メディアが運営するスタアカの以下のコースでもBigQuery×SQL×Pythonについて詳しく解説していますので是非チェックしてみてください!

スタアカは業界最安級のAIデータサイエンススクールです。

スタアカトップ
【価格】ライトプラン:1280円/月
プレミアムプラン:149,800円
【オススメ度】
【サポート体制】
【受講形式】オンライン形式
【学習範囲】データサイエンスを網羅的に学ぶ
実践的なビジネスフレームワークを学ぶ
SQLとPythonを組みあわせて実データを使った様々なワークを行う
マーケティングの実行プラン策定
マーケティングとデータ分析の掛け合わせで集客マネタイズ

・BigQuery上でSQL、Google Colab上でPythonを使い野球の投球分析
・世界最大手小売企業のウォルマートの実データを用いた需要予測
・ビジネス・マーケティングの基礎を学んで実際の企業を題材にしたマーケティングプランの策定
・Webサイト構築してデータ基盤構築してWebマーケ×データ分析実践して稼ぐ

AIデータサイエンスを学んで市場価値の高い人材になりましょう!

データサイエンスやAIの勉強方法は以下の記事でまとめています。

【5分で分かる】データサイエンティストに必要なスキルと独学勉強ロードマップ!当サイト【スタビジ】の本記事では、データサイエンティストに求められるスキルとそれを身に付けるための勉強法について徹底的にまとめていきます!入門者でも、しっかりデータサイエンティストについて理解しある程度独学で駆け出しの状態までいけることを目指します。...
AIのロードマップ
【これだけ!】AI(人工知能)の勉強ロードマップを徹底的に解説!当サイト【スタビジ】の本記事では、AIを勉強するロードマップについて徹底的に解説していきます。まずは、AIの概要について理解して統計学の基本知識・機械学習の基本知識・Pythonでの実装・インフラ周りの知識などを包括的に理解していきましょう!...
スタビジアカデミーでデータサイエンスをさらに深く学ぼう!

スタアカサービスバナースタビジのコンテンツをさらに深堀りしたコンテンツが動画と一緒に学べるスクールです。

プレミアムプランでは私がマンツーマンで伴走させていただきます!ご受講お待ちしております!

スタビジアカデミーはこちら