【必ずやるべき！】BigQueryとLooker Studio連携でおすすめのカスタムクエリ設定をご紹介【実践動画付】

2023年12月20日2024年3月31日

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

BigQueryとLooker Studioを連携させることのメリットがいまいち分からないと感じている方

BigQueryとLooker Studioの接続や設定について動画を使うなどした分かりやすい記事を探している方

GA4→BigQuery→Looker Studioの連携の概要をふんわりとでも知りたい方

のびノーリ

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

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

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

統計検定2級

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

目次 [ close ]

BigQueryとLooker Studioを連携することの意義

この記事ではGA4とBigQueryを連携して取得したデータをLooker Studioにエクスポートするデモをご紹介していきます。

GA4とBigQueryの連携について

↓こちらの記事で紹介しておりますのでご覧ください。

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

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

Looker Studioとは

Googleが提供する無料のBI（ビジネスインテリジェンス）ツールです。

BIツールとはデータの分析を図表やグラフを使って、より直感的に把握できるように可視化するツールです。

分析レポートは簡単に共有でき、Excelなどを使っての集計時間の削減も実現する優れものです。

BigQueryとLooker Studioを連携する意義

GA4からLooker Studioへ直接連携することもできますが、下記のデメリットが生じます。

１日、１時間あたり、プロパティ、プロジェクトあたりに利用できる回数や人数に制限がある。

GA4にはある項目がLooker Studioにはないケースがある。

GA4の項目名とLooker Studioでの項目名が一致しないケースがある。

これらのデメリットを解決する手段としてもBigQueryとLooker Studioを連携する意義があります。

のびノーリ

BigQueryとLooker Studioを連携するメリットとしては、以下で解説します！

BigQueryとLooker Studioを連携するメリット

表形式のデータを図やグラフで可視化

BigQueryの分析データは表形式でしか見れませんが、Looker Studioにその分析データをエクスポートすることで、図やグラフに変換することができるので素早くデータや情報を把握できるようになります。

複数のデータソースをLooker Studioのレポート上に統合できる

BigQueryで個別に取得した複数のデータを、Looker Studioのレポート上にまとめてみることができます。

BigQueryでは一まとまりのクエリ毎に実行結果を見ていたものが、Looker Studioのレポートにその結果を並べて、一括でまとめてデータを見ることができます。

見たいデータの期間範囲をフレキシブルに指定できる

カスタムクエリの設定で、Looker Studioのレポート上で簡単に期間指定ができるため、期間毎の比較データなど素早く確認できます。

のびノーリ

こちらの設定は、以下の別途デモにて実践的に解説します。

GA4からBigQueryへエクスポートして取得するデータについて

この記事では、実際に筆者のブログのGA4データからエンゲージメント率を取得するデモをご紹介します。

エンゲージメントが導入された背景

今年の7月1日をもってUA（ユニバーサルアナリティクス）のサポートは終了し、GA4へ完全移行されていると思います。

UAでは１ページしか見なかったセッションは全て直帰として扱われていました。言い換えると、各ページがユーザーにとって有益な情報であったためしっかり読まれているのにも関わらず、それが分からない状態でした。
その結果、ユーザーの行動を正確に反映した分析ができているとはいえませんでした。

その他の改善点も踏まえ、GA4ではより分析の精度を上げるためにエンゲージメントという概念が導入されました。

エンゲージメントの定義について

以下のように、ユーザーが訪れたページに対しどれだけ興味を示したかを確認できるようになりました。これがエンゲージメントとなります。

ユーザーが10秒以上セッションを継続した

コンバージョンイベントの発生した

２ページ以上閲覧した

なお、詳細については下記のリンクにてご確認ください。

https://support.google.com/analytics/answer/11109416?hl=ja

ベージ毎のエンゲージメント率を取得するクエリ

今回のデモでは筆者の記事毎のエンゲージメント率を取得しますので、そのクエリを簡単な解説を入れて紹介します。

SQLクエリ

with prep as (       --　仮想テーブルを作成しています。
SELECT
(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 session_id,
max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged,　　　--エンゲージの有無を確認するためのクエリ
from `＊＊＊＊＊＊-＊＊＊-＊＊＊＊＊＊.analytics_＊＊＊＊＊＊＊＊.events_*`　　　--　＊はご自分のプロジェクトやIDです。　
where _table_suffix between '20231101'and '20231130'　　--11月で絞っています。
group by page_title, user_pseudo_id, session_id 
)
select
prep.page_title,
round(safe_divide(count(distinct case when prep.session_engaged = '1' then concat(prep.user_pseudo_id, prep.session_id) end),count(concat(prep.user_pseudo_id, prep.session_id))) * 100,1) as engagement_rate,　　-- エンゲージ数をセッション数で割っています。
from prep 
group by prep.page_title;