【GA4】Looker StudioとBigQueryを使いこなす｜基本指標 ※無料テンプレートあり

はじめに

前回の記事は、GA4の基本的な指標をLooker Studioで可視化するための具体的な方法をご紹介しました。

今回は、全く同じ指標をBigQueryで加工してLooker Studioで可視化するための具体的な方法を紹介します。（可視化にあたってはGCP課金プロジェクトが必要となります）
「具体的な設定方法を読みながら、弊社テンプレートと同様のレポートを作成することができる」
という状態を目指して説明します。

あわせて、弊社のブログ編集部で作成した無料テンプレートを配布しています。どうぞ最後までお読みください。

Looker Studioを一度も使ったことがない方向けに、Looker Studioの概要や基本操作をまとめた記事もあります。初めての方はまずこちらの記事を読んでみてください。

Looker Studio

【Looker Studio】どこよりもやさしい基本の使い方解説

BigQueryを使用するメリット

GA4のデータをSQLで加工・集計して必要なデータを揃える作業を実施します。

「Looker StudioとGA4を直接つないでしまえばいいのに」

と思われかもしれません。それはごもっともな意見です。Looker StudioではGA4データを取得する際、面倒な集計を完了させたうえでレポート作成のための材料を用意してくれています。実際にBigQueryは使わずとも、分析意図に沿う凝ったレポートを作成することは可能です。それでもBigQueryを使用するのは、いくつかのメリットがあるからです。

①閾値の適用に強い
実際のデータとLooker Studio上で計測するデータに揺らぎが発生することがあります。これは”閾（しきい）値の適用”といって、取得したデータ量が少ない場合、Googleが個人特定を防ぐためにデータを一部除外するためです。BIgQueryを使用する場合、GA4で取得した全てのデータを格納できるため、精緻な値を取得することができます。

②定義変動に強い
イベント、コンバージョンを新設したり定義を変更することがあると思います。本来なら新しいコンバージョンを設置しても過去に対して実績を取得することができません。その点、BigQueryを噛ませることで過去データに対しても新しい定義を当てはめた上でデータを再集計することが可能です。

③データ連携に強い
SQLを使用することで様々なデータとの連携が可能です。本来であればバラバラにしか可視化できなかった広告データや顧客データと連携することで、ユーザーの一環した行動としてリッチな分析をすることもできます。

また今回作成するレポートはGA4の標準レポートに倣い、そのほとんどを『アクティブ ユーザー』の実績として作成しますが、BIgQueryでの加工の仕方によっては『新規ユーザー』に絞った実績を出すことも可能です。

ただ、いずれの実績においても、限りなく近い数値は出すことができますが、GA4で確認できる数値とは異なる場合があります。様々な要因はありますが、その一つにGoogleが計算ロジックを明かしていないために、「おそらくこうである」というレベルでしか集計ができない点が挙げられます。

GA4で見るのか？
GA4→Looker Studioで見るのか？
GA4→BigQuery→Looker Studioで見るのか？
それぞれのデータの持ち方に特徴があるので、見たいデータや分析意図に合わせて正しくツールを選択してください。

レポート概要

弊社ブログ編集部で作成したレポートです。指標・グラフはすべてGA4のホーム・標準レポートに記載されているものを参照しています。加えていくつかのボタンとフィルタを設置しました。

※記事の後半ではこちらのテンプレートを無料配布しています。

フィルタ・ボタン
①レポートのダウンロード
レポートをPDF形式でダウンロードできます。日次で資料化をして報告をする必要がある場合、こちらを使用することで簡単にダウンロードができます。
※PDF化したデータではフィルタが使えません

②期間フィルタ
閲覧したいデータ期間を指定することができます。プリセットでは過去7日間になるように設定をしています。

グラフ
A 総合サマリ
サイトパフォーマンスを知るための基本的な指標です。
GA4のホーム画面上グラフと同様の内容です。

B エンゲージメント実績
ユーザーのエンゲージメント（興味・関心度）を測るグラフです。
GA4の標準レポート、エンゲージメント『概要』と同様の内容です。

C PV・イベント実績
ユーザーの行動量を測るグラフです。
GA4の標準レポート、エンゲージメント『概要』と同様の内容です。

D 収益実績
サイトの収益を測るためのグラフです。
GA4の標準レポート、収益化『概要』と同様の内容です。

E 購入者数実績
サイトの購入者数を測るためのグラフです。
GA4の標準レポート、収益化『概要』と同様の内容です。

レポートの作り方

それではレポートの作り方を解説します。テンプレートはコピーしてお使いいただけるのですが、Looker Studioをご自身のスキルとして身につけたい方は、是非これから解説する作り方をご覧いただきながら、家具でも組み立てるような感覚で作成してみてください。

1.データソースを追加する

レポートを新規作成するとデータソースの選択を求められます。
『BigQuery』を選択します。

『カスタムクエリ』を選択。課金対象として設定したいプロジェクトIDを指定します。

ここまでの設定が完了後、下記でご紹介するクエリを実行すると、設定した課金PJを通じてGoogleのデモデータを加工集計します。
※BigQueryに作成した集計済データを接続する方法が一般的ですが、今回はLooker Studioのツール内で作業完結できるカスタムクエリを採用しています。

基本指標可視化のためのSQLクエリをそのまま入力します。

--①セッション×ユーザーごとの各種実績（購買は除く）
WITH user_performance_by_session AS
(
SELECT
 event_date
 ,session_user_id
 ,user_pseudo_id
 ,MIN(ga_session_number) AS ga_session_number
 ,COUNT(event_name) AS cnt_event_name
 ,SUM(IF(event_name IN ('page_view','screen_view'),1,0)) AS sum_pv
 ,SUM(IF(event_name IN ('add_to_cart','begin_checkout','first_visit','Membership','predict_ltv_payer','predicted_top_spenders','purchase','qualified_visit','view_cart','view_item'),1,0)) AS sum_cv
 ,SUM(engagement_time_msec) AS engagement_time_msec
 ,MAX(session_engaged_flg) AS session_engaged_flg
 ,MAX(IF(event_name='first_visit',1,0)) AS first_visit_flg
FROM
 (
  SELECT
   CAST(FORMAT_TIMESTAMP('%Y-%m-%d',TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') as DATE) AS event_date
   ,CONCAT(user_pseudo_id,'-',CAST((SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='ga_session_id')AS STRING)) AS session_user_id
   ,user_pseudo_id
   ,(SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='ga_session_number') AS ga_session_number
   ,(SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='engagement_time_msec') AS engagement_time_msec
   ,event_name
   ,(SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key='session_engaged') AS session_engaged_flg
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX > '20210124'
 )
GROUP BY 1,2,3
)

--②ユーザー×セッションごとのユーザーの購買実績
,user_purchase_performance_by_session AS
(
SELECT
 CONCAT(user_pseudo_id,'-',CAST((SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='ga_session_id')AS STRING)) AS session_user_id
 ,user_pseudo_id
 ,SUM(items.item_revenue) AS sales_amount
,1 AS purchase_flg
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
 ,UNNEST(items) AS items
WHERE
 _TABLE_SUFFIX > '20210124'
  AND
 event_name ='purchase'
  AND
  item_revenue IS NOT NULL
GROUP BY 1,2
)

--③ユーザーの初回購買日
,user_first_purchase AS
(
SELECT
 user_pseudo_id
 ,MIN(CAST(FORMAT_TIMESTAMP('%Y-%m-%d',TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') as DATE)) AS first_purchase_date
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'purchase'
GROUP BY 1
)

--①②③の結合
SELECT
 t1.event_date
 ,t1.session_user_id
 ,t1.user_pseudo_id
 ,t1.ga_session_number
 ,t1.cnt_event_name
 ,t1.sum_pv
 ,t1.sum_cv
 ,IFNULL(t1.engagement_time_msec,0)/1000 AS engagement_time_msec
 ,IFNULL(t2.sales_amount,0) AS sales_amount
 ,t1.session_engaged_flg
 ,IFNULL(t2.purchase_flg,0) AS purchase_flg
 ,t1.first_visit_flg
 ,t3.first_purchase_date
FROM user_performance_by_session t1
 LEFT JOIN user_purchase_performance_by_session AS t2
  ON t1.session_user_id = t2.session_user_id
  AND t1.user_pseudo_id = t2.user_pseudo_id
 LEFT JOIN user_first_purchase t3
  ON t1.user_pseudo_id = t3.user_pseudo_id

2.レイアウトを変更する

レポートの背景
『テーマとレイアウト』から『カスタマイズ』を選択します。
背景色を16進数『#f8f9fa』に変更

キャンバスサイズ
『テーマとレイアウト』から『レイアウト』を選択します。

サイズを『幅1500×高さ900』に変更

3.ボタン・フィルタを作成する

①レポートのダウンロード
『コントロールの追加』から『ボタン』を選択します。
『レポートの操作』→『レポートをダウンロード』に設定

『新規ボタン』をクリックして、『レポートのダウンロード』に書き換えます。
『スタイル』→文字サイズを『14px』、背景を『白』に変更
『枠線に影を付ける』を選択

②期間フィルタ
『コントロールの追加』から『期間設定』を選択します。
今回のクエリで集計している期間は2021年1月25日~1月31日の一週間です。

4.グラフを作成する

A 総合サマリ

・アクティブユーザー
『グラフを追加』から『スコアカード』を選択します。

指標：IF(session_engaged_flg="1" OR first_visit_flg=1 OR engagement_time_msec>0,user_pseudo_id,NULL)

文字サイズ：『24px』
文字配置：すべて中央

・イベント数
指標：cnt_event_name
指標以外の設定はアクティブユーザー数と同じ

・コンバージョン
指標：sum_cv
指標以外の設定はアクティブユーザー数と同じ

・新規ユーザー数

指標：IF(ga_session_number=1,user_pseudo_id,NULL)

指標以外の設定はアクティブユーザー数と同じ

・時系列グラフ
グラフの種類：時系列グラフ
ディメンション：event_date
指標：下記を参照（上記で設定した指標を再度設定します）

スタイル：系列１（平均エンゲージメント時間）の軸を左
　　　　　系列２（エンゲージのあったセッション数）の軸を右
　　　　　系列３（セッションあたりのエンゲージメント時間）の軸を左

・背景
総合サマリと同じ

B エンゲージメント実績

・平均エンゲージメント時間

指標：SUM(engagement_time_msec)/COUNT_DISTINCT(IF(session_engaged_flg="1" OR first_visit_flg=1 OR engagement_time_msec>0,user_pseudo_id,NULL))

指標以外の設定はアクティブユーザー数と同じ

・エンゲージのあったセッション数　※１アクティブ ユーザーあたりの平均

指標：COUNT_DISTINCT(IF(session_engaged_flg="1",session_user_id,NULL))/COUNT_DISTINCT(IF(session_engaged_flg="1" OR first_visit_flg=1 OR engagement_time_msec>0,user_pseudo_id,NULL))

指標以外の設定はアクティブユーザー数と同じ

・セッションあたりのエンゲージメント時間

指標：SUM(engagement_time_msec)/COUNT_DISTINCT(session_user_id)

※データの種類を持続時間（秒）に変更
指標以外の設定はアクティブユーザー数と同じ

・時系列グラフ
グラフの種類：時系列グラフ
ディメンション：event_date
指標：下記を参照（上記で設定した指標を再度設定します）

スタイル：系列１（平均エンゲージメント時間）の軸を左
　　　　　系列２（エンゲージのあったセッション数）の軸を右
　　　　　系列３（セッションあたりのエンゲージメント時間）の軸を左

・背景
総合サマリと同じ

C PV・イベント実績

・表示回数
指標：sum_pv
指標以外の設定はアクティブユーザー数と同じ

・イベント数
指標：cnt_event_name
指標以外の設定はアクティブユーザー数と同じ

・時系列グラフ
グラフの種類：時系列グラフ
ディメンション：event_date
指標：下記を参照（上記で設定した指標を再度設定します）

スタイル：系列１（表示回数）の軸を左
　　　　　系列２（イベント数）の軸を左

・背景
総合サマリと同じ

D 収益実績

・合計収益
指標：sales_amount
指標以外の設定はアクティブユーザー数と同じ

・購入による収益
指標：sales_amount
指標以外の設定はアクティブユーザー数と同じ

・時系列グラフ
グラフの種類：時系列グラフ
ディメンション：event_date
指標：下記を参照（上記で設定した指標を再度設定します）

スタイル：系列１（合計収益）の軸を左
　　　　　系列２（購入による収益）の軸を左
　　　　　系列３（広告収入合計）の軸を左

・背景
総合サマリと同じ

E 購入者実績

・総購入者数

指標：IF(purchase_flg=1,user_pseudo_id,NULL)

指標以外の設定はアクティブユーザー数と同じ

・初回購入者数

指標：IF(event_date=first_purchase_date AND purchase_flg=1,user_pseudo_id,NULL)

指標以外の設定はアクティブユーザー数と同じ

・時系列グラフ
グラフの種類：時系列グラフ
ディメンション：event_date
指標：下記を参照（上記で設定した指標を再度設定します）

スタイル：系列１（総購入者数）の軸を右
　　　　　系列２（初回購入者数）の軸を右

・背景
総合サマリと同じ

レポートの無料テンプレート

上記で紹介したレポートを無料で配布しております。

無料配布しているレポートの一例です
クリック拡大 可

※ご自身のBigQueryに格納されているGA4データを閲覧するためには、クエリの書き換えが必要です。
※BigQueryの利用については課金発生する場合があります。

データ分析はDX-Acceleratorにお任せください

今回はECサイトの基本指標をBigQueryとLooker Studioを使用して確認するための設定方法をご紹介しました。

BigQueryを使用する分、設定が難しい場面があったと思います。SQLクエリを一つとっても、分析者の意図に沿ってクエリをカスタマイズ・メンテナンスしていく必要があります。適切なデータ人材の内製化ができていない場合、まずは人材探しから始まって分析までのリードタイムは開くばかりです。連動して施策実行も後ろ倒しになります。

また、これまでに数多くのお客さまとお取組みを実現してきましたが、よくある課題として、本来のデータ分析やデータ基盤の構築にチカラを入れたいデータアナリストとエンジニアが、分析データの作成と可視化に追われるがため、稼働がひっ迫してしまうケースがあります。

施策成果の最大化には、スピーディで正確なデータ分析環境が欠かせません。そのためには社内データを熟知しながら、関係者との関わり合いの中で柔軟なデータ分析環境を用意する人材が不可欠です。

適切なスキルを持ったデータ人材が御社メンバーの一人として寄り添い、データ活用を全力で支援します。

DX-Acceleratorのサービスに関する資料は下記よりダウンロードをお願いいたします。

この記事を書いた人

松原 健太
（まつばら けんた）

株式会社UNCOVER TRUTH
ビジネスデベロップメントグループ DX-Acceleratorチーム
2022年、UNCOVER TRUTHに中途入社。
大手GMS・外資系SPA・大手百貨店といった小売業界を経験後、一念発起してIT業界に転身。
広告代理店・マーケティング支援会社などのデータ業務を支援。よく使うツールはBigQueryとLooker Studio、ときどきTableau。