「GA4 × BigQuery」の実際の活用例を紹介

こんにちは、木田和廣です。

Googleアナリティクス4連載の第3回となる本記事では、Googleアナリティクス史上はじめて、無料のスタンダード版でも可能となった「BigQuery」（ビッグクエリ）へのデータエクスポートについて、「BigQueryにエクスポートされたデータならではの可視化」の例を交えながら紹介します。

最初に断っておくと、本記事はGA4のレポートでは飽き足らない、中上級者の方向けの内容となっています。レポートを作成するにあたっては、2つのカスタムSQLを記述しています。SQLの全文は本記事末尾に掲載しました。

BigQueryには強力な関数群が用意されているので、「単なるクラウド上のデータベース」と考えると、本質を見誤ってしまいます。本記事を通じて、GA4のBigQueryへのデータエクスポートの本質的な価値の1つが、BigQueryの強力な関数群を利用して柔軟に分析やデータの整形ができるところにあることを、理解していただければ幸いです。

私の最新の著書『できる逆引き Googleアナリティクス4 成果を生み出す分析・改善ワザ 192』では、9つある章のうち1章を割いて、BigQueryエクスポートについて触れています。初心者の方はもちろん、BigQueryにエクスポートされたデータを利用して分析したい中上級者の方のニーズにも応える意図を持って、BigQueryエクスポートの章を設けました。よろしければお手にとっていただければ幸いです。

『できる逆引き Googleアナリティクス4 成果を生み出す分析・改善ワザ 192』は、「やりたいことから探せる」のが特徴の書籍です。書影をクリックするとAmazonの商品ページに移動します。

なお、データの可視化基盤としては、読者のみなさんがいちばん利用しやすいはずの「Looker Studio」（旧：Googleデータポータル）を利用します。BigQueryは強力な関数群で分析をサポートしてくれますが、結果はいつも「表形式」ですので、分析の結果を素早く、的確に受け手に伝えるのは得意ではありません。そこで別途、Looker Studioや「Tableau」などのBIツールと組み合わせて利用するのが一般的です。

では、そろそろ本題に入りましょう。

ページ評価をBigQuery上のGA4データで行う

みなさんは、Webサイト上の「ページ」を評価したいというニーズを共通してお持ちではないかと思います。例えば「コンテンツマーケティングの一貫として費用をかけてアップしているページはどう貢献しているのか？」「お客様との交渉の末、やっと掲載した事例のページはどのようなパフォーマンスを出しているのか？」などです。

しかし、ページの評価にあたっては、以下の2つの難しい点があります。

• 評価の指標が多数ある
• 期間を揃える必要がある

なぜ、それらがページの評価を難しくするのかを次に説明します。

難点①：評価の指標が多数ある

ページのパフォーマンスを示す指標の1つとして、「どのくらい見られているのか？」を示すページビューがあります。最も基本的な指標ですが、ページビューだけではページは評価できないでしょう。

そこで、本連載の第1回・第2回でも登場した私のオウンドメディア「kazkida.com」を例にとり、ページビューに加えて以下の5つ、合計6個の指標でページを評価します。

1. エントランス数：何回、サイト外からのトラフィックの起点となったか？
2. スクロール率：90%スクロールの回数の、ページビュー数に対する比率
3. 平均エンゲージメント時間：ユーザーエンゲージメント÷ページビュー数で算出する、ユーザーがページをフォアグラウンドで見ていた時間
4. 平均コンバージョンジャンプ数：コンバージョン設定しているudmey.comやamazon.co.jpにジャンプした回数
5. 平均コンバージョンジャンプ率：コンバージョン設定しているudemy.comやamazon.co.jpにジャンプした回数の、ページビューに対する比率

6個もの指標を利用すれば、ある程度「総合的に」ページを評価できます。一方、指標が6個もあるので、分かりやすい可視化を行うには何かしらの工夫が必要になります。

難点②：期間を揃える必要がある

もう1つの難しさは、期間を揃える必要があるということです。

例えば、2023年1月〜3月の期間でページを評価しようとしたときに、2022年10月から存在していたページAと、2023年3月1日にはじめてアップロードされたページBを同一基準で評価できるでしょうか？

ユーザーの目に触れる機会があった期間や、検索エンジンにインデックスされていた期間が異なるので、横並びで評価することはできません。

完成したLooker Studioレポート

上記の2つの「難点」をクリアして完成したLooker Studioのレポートは、次の通りです。もちろん、この可視化だけが唯一の正解ではありませんが、BigQueryの柔軟性を利用して「難点」を回避していることを説明します。

BigQueryを利用することで、2つの「難点」を回避して完成したLooker Studioのレポートです。

工夫①：6つの指標の可視化

6つの指標を可視化するために、レポートの左下にある「レーダーチャート」を利用しています。これにより、視覚的かつ直感的に選択したページの「強み」「弱み」を理解できます。

選択したページは、ページビューやコンバージョンとなるサイト外ジャンプは強みとして持っていますが、スクロールされておらず、ページにユーザーが滞在した時間が短いという弱みがあることが一目瞭然です。

レーダーチャートをうまく利用するには、6つの指標の値の範囲を揃える必要があります。例えば、ページビューが1000で、スクロール率が0.6といった範囲の揃っていない値はうまく可視化できないのです。

そこで、Looker StudioからBigQueryにはカスタムSQLで接続し、BigQueryに用意されている関数を利用することでデータの前処理をしています。具体的には「NTILE」（エヌタイル）という関数を利用し、各指標について、すべてのページを1から5に分けるフラグをつけています。上位20%に入っていれば5、次の20%に入っていれば4、最下位の20%に入っていれば1という具合です。

それにより、どの指標も1から5の範囲に揃うため、レーダーチャートを利用できるのです。該当部分のSQLは次の通りです。全体の中では42行目から49行目にあたります。

工夫②：期間を揃える

期間を揃えるには、BigQuery上の関数で以下を実現しています。なお、Googleタグマネージャー上でタグのカスタマイズをして、ページごとに「公開日」を取得しています（GTMのカスタマイズについては『できる逆引きGA4』の2章でたっぷり解説しています）。

BigQueryで適用したロジックは次の通りです。

1. ページの公開日（データ取得期間よりもページの公開日が古ければ、データ取得期間の最も古い日付）とデータ取得期間の中の最新の日付を差し引いて、ページが公開されていた「日数」を計算する
2. 指標（例えばページビュー）を「日数」で割り、日次の平均を取り出す
3. 30を掛けて、1カ月あたりのページビュー数に引き直す

このロジックにより、2023年1月～3月の期間でページを評価しようとしたときに、2022年10月から存在していたページAには1月1日から3月31日までの「日数」である90が紐付き、2023年3月1日にはじめてアップロードされたページBには、3月1日から31日までの「日数」である31が紐付きます。

ページA、ページBともに、ページビュー数を「日数」で割って30を掛ければ、平均で1カ月（30日で近似）あたりどのくらいのページビューを記録したかについて、期間を揃えることができます。

該当するSQL文は以下です。特に肝要な「日数」は、27行目のDATE_DIFF関数で計算しています。30日間の平均的な値に修正しているのは33行目から38行目あたりです（率の指標については30日間の値に転換していません）。

工夫③：横持ちを縦持ちに転換する

レポートの右下では、25%、50%、75%、90%それぞれのスクロール深度に到達したパーセンテージ（分母はページビュー）を棒グラフで表しています。GA4のデフォルトではスクロール深度90%しか取得できませんが、タグのカスタマイズを行い、25、50、75の各スクロール深度を追加で取得しています（『できる逆引きGA4』のワザ047で実際の操作方法を紹介しています）。

このグラフにより、90%スクロール（＝ページを最後までスクロールしたことの近似値）があまり起きていない場合、どこで離脱してしまっているのかが分かります。

この棒グラフは、ディメンションに「スクロール深度」、指標に「（ページビュー数）に対するヒットの割合」を利用しています。上段および下段左側のチャートとは、別のカスタムSQLを記述しています。

実現するにあたり、Looker Stuido側では特段の工夫はしておらず、工夫はすべてBigQuery側で行っています。

中でも最大の工夫は「横持ち」を「縦持ち」に転換するところです。横持ちとは以下の画面のように、1つの指標が1つの列を構成しているデータの持ち方です。「（ページビュー数）に対するそれぞれのスクロール深度のヒットの割合」を計算するには、この持ち方が適してます。

横持ちの表（テーブル）の例です。

一方、横持ちだとLooker Studioの棒グラフが描けません。そこで、縦持ちに変換する必要がありました。縦持ちとは以下の画面のように、1種類の指標（ここではhit_rate）を1列にまとめるデータの持ち方です。

こちらは縦持ちの表（テーブル）の例です。

この処理もBigQuery上のSQLで実現しています。横持ちのデータを縦持ちに変換することを「ピボット」とも呼びます。SQLでのピボットは以下の通り、「UNION ALL」というクエリ構文を利用して実現します。

まとめ

いかがでしたでしょうか？

NTILE、DATE_DIFF、UNION ALLなど、BigQueryが持つ関数やクエリ構文を駆使し、Looker Studio側の機能をフルに利用したり（レーダーチャートでのグラフ描画）、逆にLooker Studio側の機能に過度に依存しないようにデータを整形し、自分が実現したいグラフ表現でGA4のデータの可視化できる（スクロール深度の棒グラフの場合）、ということをご理解いただけたのではないかと思います。

Looker Studioのレポートを実現するのに記述したSQL文

Looker Studioでのレポート作成を行うにあたって記述した2つのSQL文全文を、以下の通りに掲載します。必ずしもパフォーマンス上、最適化されたSQL文ではない可能性がありますのでご了承ください。

もし、この機会にSQLについて学びたいという場合には、拙著『集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析』がお役に立てるかと思います。本記事で紹介した関数やクエリ構文はもれなく網羅しています。

『集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析』は、データ分析のためのSQLを最速で身に付けたい方のための入門書です。書影をクリックするとAmazonの商品ページに移動します。

SQL文①：上段、及び、下段左側のグラフを描画するのに利用

    with raw_data as
    (SELECT event_date, event_name
    ,(select value.string_value from unnest(event_params) where key="page_title") as page_title
    ,(select value.string_value from unnest(event_params) where key="date_published") as date_published
    ,(select value.int_value from unnest(event_params) where key="entrances") as entrances
    ,(select value.int_value from unnest(event_params) where key="engagement_time_msec") as engagement_time_msec
    ,(select value.int_value from unnest(event_params) where key="percent_scrolled") as percent_scrolled
    FROM `bigquerytableauoct.analytics_323400862.events_*`
    where _table_suffix between '20230501' and '20230531'
    ), master as(
    select page_title
    , if(event_name = "page_view",1,0) as pageviews
    , if(event_name ="scroll" and percent_scrolled = 90, 1,0) as scroll_90s
    , if(event_name = 'purchase',1,0) as conversions
    , date(safe_cast(substr(date_published,1,4) as int64), safe_cast(substr(date_published, 6,2) as int64), safe_cast(substr(date_published, 9,2) as int64)) as date_published
    , date(cast(substr(event_date,1,4) as int64),cast(substr(event_date,5,2) as int64),cast(substr(event_date,7,2) as int64)) as event_date
    , entrances
    , engagement_time_msec
    from raw_data
    ), aggregated as (
    select page_title
    ,sum(pageviews) as pageviews
    ,sum(scroll_90s) as scrolls
    ,sum(conversions) as conversions
    ,sum(entrances) as entrances
    ,sum(engagement_time_msec)/1000 as engagement_time_sec
    ,date_diff(max(event_date), if(min(date_published)<=min(event_date), min(event_date),min(date_published)), day)+1 as days
    from master
    group by page_title
    having days is not null
    ), cleansed as (
    select page_title
    , pageviews/days*30 as avg_pageviews
    , scrolls/pageviews as avg_scroll_rate
    , conversions/pageviews as avg_cv_jump_rate
    , conversions/days*30 as avg_cv_jump
    , entrances/days*30 avg_entrances
    , engagement_time_sec/pageviews as avg_engagement_time_sec
    from aggregated
    where pageviews > 0
    ), finished as (
    select *
    , ntile(5) over (order by avg_pageviews desc) as pv_rank
    , ntile(5) over (order by avg_scroll_rate desc) as scroll_rate_rank
    , ntile(5) over (order by avg_cv_jump_rate desc) as cv_jump_rate_rank
    , ntile(5) over (order by avg_cv_jump desc) as cv_jump_rank
    , ntile(5) over (order by avg_entrances desc) as entrance_rank
    , ntile(5) over (order by avg_engagement_time_sec desc) as engagement_time_rank
    from cleansed  
    )

    select * from finished
    

SQL文②：下段右側のグラフを描画するのに利用

    with raw_data as
    (SELECT
    (select value.string_value from unnest(event_params) where key="page_title") as page_title
    ,(select value.int_value from unnest(event_params) where key="percent_scrolled") as percent_scrolled
    FROM `bigquerytableauoct.analytics_323400862.events_*`
    where _table_suffix between '20230501' and '20230531' and event_name="scroll"
    ), master_pv as(
    select
    (select value.string_value from unnest(event_params) where key="page_title") as page_title
    ,count(*) as pageviews
    FROM `bigquerytableauoct.analytics_323400862.events_*`
    where _table_suffix between '20230501' and '20230531' and event_name="page_view"
    group by page_title
    ), master_scroll as (
    select page_title
    ,if(percent_scrolled = 25,1,0) as scroll_25
    ,if(percent_scrolled = 50,1,0) as scroll_50
    ,if(percent_scrolled = 75,1,0) as scroll_75
    ,if(percent_scrolled = 90,1,0) as scroll_90
    from raw_data
    ), aggregated as (
    select page_title
    , sum(scroll_25) as scroll_25
    , sum(scroll_50) as scroll_50
    , sum(scroll_75) as scroll_75
    , sum(scroll_90) as scroll_90
    from master_scroll
    group by page_title
    ), join_pv as(
    select * from aggregated
    left join
    master_pv using(page_title)
    ), scroll_rate as (
    select
    page_title
    ,pageviews
    ,scroll_25/pageviews as scroll_25_pct
    ,scroll_50/pageviews as scroll_50_pct
    ,scroll_75/pageviews as scroll_75_pct
    ,scroll_90/pageviews as scroll_90_pct
    from join_pv
    )


    select page_title, "25%scroll" as percent_scrolled, scroll_25_pct as hit_rate from scroll_rate
    union all
    select page_title, "50%scroll", scroll_50_pct from scroll_rate
    union all
    select page_title, "75%scroll", scroll_75_pct from scroll_rate
    union all
    select page_title, "90%scroll", scroll_90_pct from scroll_rate