【GA4】Looker StudioとBigQueryを使いこなす｜チャネル別分析 ※無料テンプレートあり

はじめに

前回の記事は、GA4のチャネル別分析をLooker Studioで行う方法をご紹介しました。

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

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

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

GA4

【GA4】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で見るのか？
それぞれのデータの持ち方に特徴があるので、見たいデータや分析意図に合わせて正しくツールを選択してください。

レポート概要

弊社ブログ編集部で作成したレポートです。指標・グラフは以前の記事で紹介をした標準レポートと探索レポートを参考に作成しました。加えていくつかのボタンとフィルタを設置しました。

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

フィルタ・ボタン

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

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

③④ディメンション切り替え
こちらのディメンションを切り替えることで、表・グラフのディメンションが変わります。
初期状態のディメンションはそれぞれ、
④…medium
⑤…medium
となっています。

※BigQueryにエクスポートされるデータには”デフォルト チャネル グループ”がありません。GA4→Looker Studioでレポート作成をする際と異なるポイントです。

グラフ

A 新規ユーザー数の割合
新規ユーザー数をチャネル別の割合で示したものです。
前回の記事の探索レポートで作成した表を参照しました。
わかりやすいようにドーナツの中央に実数値を設置しています。これは探索レポートではできません。

B 新規ユーザー数の推移
チャネル別の新規ユーザー数を時系列で示したものです。
GA4の標準レポートと同様の内容です。

C 新規ユーザー数
チャネル別の新規ユーザー数を棒グラフで示したものです。
GA4の標準レポートと同様の内容です。

D チャネル別　各種実績
チャネル別の各種実績を示したものです。
GA4の標準レポートと同様の内容です。

E アクティブ ユーザー数の割合
アクティブ ユーザー数をチャネル別の割合で示したものです。
前回の記事の探索レポートで作成した表を参照しました。
わかりやすいようにドーナツの中央に実数値を設置しています。これは探索レポートではできません。

F アクティブ ユーザー数の推移
チャネル別のアクティブ ユーザー数を時系列で示したものです。
GA4の標準レポートと同様の内容です。

G アクティブ ユーザー数
チャネル別のアクティブ ユーザー数を棒グラフで示したものです。
GA4の標準レポートと同様の内容です。

H セッション別　各種実績
チャネル別の各種実績を示したものです。
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
 ,MAX(IF(row_num=1,user_first_medium,NULL)) AS user_first_medium
 ,MAX(IF(row_num=1,user_first_source,NULL)) AS user_first_source
 ,MAX(IF(pave_view_row_num=1,medium,NULL)) AS medium
 ,MAX(IF(pave_view_row_num=1,source,NULL)) AS source
  FROM
 (
  SELECT
   CAST(FORMAT_TIMESTAMP('%Y-%m-%d',TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') as DATE) AS event_date
   ,CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') as DATETIME) AS event_time
   ,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
   ,traffic_source.medium AS user_first_medium
   ,traffic_source.source AS user_first_source
   ,(SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key='medium') AS medium
   ,(SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key='source') AS source
   ,ROW_NUMBER() OVER (
                       PARTITION BY 
                        CONCAT(user_pseudo_id, '-', CAST((SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='ga_session_id') AS STRING))
                       ORDER BY 
                        CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS DATETIME)
                      ) AS row_num
   ,IF(event_name='page_view',
          ROW_NUMBER() OVER (
                             PARTITION BY 
                              CONCAT(user_pseudo_id, '-', CAST((SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key='ga_session_id') AS STRING))
                             ORDER BY 
                              CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS DATETIME)
                            ),NULL) AS pave_view_row_num
  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
 ,IFNULL(t1.user_first_medium,'不明') AS user_first_medium
 ,IFNULL(t1.user_first_source,'不明') AS user_first_source
 ,IFNULL(t1.medium,'不明') AS medium
 ,IFNULL(t1.source,'不明') AS source
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』に変更

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

サイズを『幅1600×高さ1650』に変更

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

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

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

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

③④ディメンション切り替え
使用するデータソースの最下部にある『パラメータを追加』を選択します。

下記の通りテキストを入力、２つのパラメータを作成します。

⚫︎値の一覧
ユーザー獲得
-medium
-source
-source/medium

トラフィック獲得
-medium
-source
-source/medium

4.グラフを作成する

A 新規ユーザー数の割合

・ドーナツグラフ
『グラフを追加』から『ドーナツグラフ』を選択します。
ディメンション：『ディメンション』

CASE
WHEN ユーザー獲得='medium' THEN user_first_medium
WHEN ユーザー獲得='source' THEN user_first_source
WHEN ユーザー獲得='source/medium' THEN CONCAT(user_first_source,'/',user_first_medium)
ELSE NULL
END

指標：『新規ユーザー数』

IF(ga_session_number=1,user_pseudo_id,NULL)

並べ替え：『新規ユーザー数』降順
クロスフィルタリング：有効

・スコアカード
『グラフを追加』から『スコアカード』を選択します。
指標：『新規ユーザー数』

IF(ga_session_number=1,user_pseudo_id,NULL)

B 新規ユーザー数の推移

『グラフを追加』から『時系列グラフ』を選択します。
ディメンション：『日付』
指標：『新規ユーザー数』※Aと同じです
内訳ディメンション：『ディメンション』※Aと同じです
内訳ディメンションの並べ替え：『新規ユーザー数』降順
クロスフィルタリング：有効

C 新規ユーザー数

『グラフを追加』から『棒グラフ』を選択します。
ディメンション：『ディメンション』※Aと同じです
指標：『新規ユーザー数』※Aと同じです
並べ替え：『新規ユーザー数』降順
クロスフィルタリング：有効

D チャネル別　各種実績

『グラフを追加』から『表』を選択します。
ディメンション：『ディメンション』※Aと同じです
指標：『新規ユーザー数』※Aと同じです
　　：『エンゲージのあったセッション数』

IF(session_engaged_flg='1' , session_user_id,NULL)

　　：『エンゲージメント率』

COUNT_DISTINCT(IF(session_engaged_flg='1',session_user_id,NULL))/COUNT_DISTINCT(session_user_id)

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

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(IF(first_visit_flg=1,engagement_time_msec,NULL))/COUNT_DISTINCT(IF(first_visit_flg=1,user_pseudo_id,NULL))

※データの種類を持続時間（秒）に変更
　　：『cnt_event_name』→イベント数に名前変更
　　：『sum_cv』→コンバージョンに名前変更
　　：『sale_amount』→合計収益に名前変更
並べ替え：『新規ユーザー数』降順
クロスフィルタリング：有効

E アクティブ ユーザー数の割合

・ドーナツグラフ
『グラフを追加』『ドーナツグラフ』を選択します。
ディメンション：『ディメンション』

CASE
WHEN トラフィック獲得='medium' THEN medium
WHEN トラフィック獲得='source' THEN source
WHEN トラフィック獲得='source/medium' THEN CONCAT(source,'/',medium)
ELSE NULL
END

指標：『アクティブ ユーザー数』

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

並べ替え：『アクティブ ユーザー数』降順
クロスフィルタリング：有効

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

IF(ga_session_number=1,user_pseudo_id,NULL)

F アクティブ ユーザー数の推移

『グラフを追加』から『時系列グラフ』を選択します。
ディメンション：『日付』
指標：『アクティブ ユーザー数』※Eと同じです
内訳ディメンション：『ディメンション』※Eと同じです
内訳ディメンションの並べ替え：『アクティブ ユーザー数』 降順
クロスフィルタリング：有効

G アクティブ ユーザー数

『グラフを追加』から『棒グラフ』を選択します。
ディメンション：『ディメンション』※Eと同じです
指標：『アクティブ ユーザー数』※Eと同じです
並べ替え：『アクティブ ユーザー数』降順
クロスフィルタリング：有効

H チャネル別　各種実績

『グラフを追加』から『表』を選択します。
ディメンション：『ディメンション』※Eと同じです
指標：『アクティブ ユーザー数』※Eと同じです
　　：『session_user_id』→セッションに名前変更
　　：『エンゲージのあったセッション数』

IF(session_engaged_flg='1' , session_user_id,NULL)

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

SUM(engagement_time_msec)/COUNT_DISTINCT(session_user_id)

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

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(cnt_event_name)/COUNT(session_user_id)

　　：『エンゲージメント率』

COUNT_DISTINCT(IF(session_engaged_flg='1',session_user_id,NULL))/COUNT_DISTINCT(session_user_id)

：『cnt_event_name』→イベント数に名前変更
：『sum_cv』→コンバージョンに名前変更
：『sale_amount』→合計収益に名前変更
並べ替え：**『アクティブ ユーザー数』**降順
クロスフィルタリング：有効

背景
『ユーザー獲得』『トラフィック獲得』ごとのまとまりを見せるために白枠で囲みます。下記の図形を用意の上、まとめます。

『長方形』→背景『白』、角の丸みを『5』、枠線に影

図形を右クリック→順序を『最も奥に移動』に変更

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

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

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

※ご自身の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。