G-gen の杉村です。コネクテッドシートと BigQuery を使い、技術ブログの GA4 アクセス解析をしている事例について、技術的な観点でご紹介します。

はじめに

技術ブログと GA4

G-gen のエンジニアは、G-gen Tech Blog を通して積極的に技術情報を発信することが奨励されています。

せっかく発信活動をするのであれば、より多くの人に読まれる記事を書きたいものです。どのような記事がより読まれるのか、傾向を掴むためにアクセス解析を試みています。

G-gen Tech Blog には Google Analytics 4（以下、GA4）のタグが埋め込まれており、アクセス情報が収集されています。このデータを分析に活用します。

当記事では主に上記分析のためのデータ準備に関する技術的な話題を中心にご紹介しますが、最後に少しだけ、当ブログのよく読まれる記事の傾向などについても簡単にご紹介します。

データ保持期間と BigQuery

GA4 のデータはデフォルトでは2ヶ月で削除されます。設定により14ヶ月まで保持することが可能です。

これを超えてデータを保持することはできません。

参考 : データの保持

GA4 ではデータが失われることを防ぐため、データを BigQuery にエクスポートすることが可能です。GA4 の前身である Universal Analytics（UA）では、BigQuery のエクスポート機能の使用には有償版であるアナリティクス 360 が必要でしたが、GA4 では無償版で利用できます。

エクスポートを設定すると、1日に1回（ストリーミングエクスポートを有効化した場合は1日を通して継続的に）データが自動的にエクスポートされ、BigQuery に集積されます。

参考 : [GA4] BigQuery Export

コネクテッドシートの利用

このようにして BigQuery に集積された GA4 のデータを、コネクテッドシート機能を使って分析します。

blog.g-gen.co.jp

G-gen では Google Workspace を使っていることから、 Google スプレッドシートからエクセルライクな操作感でデータ分析が可能なコネクテッドシートは、手軽に分析をスタートするのに最適でした。

エクスポートされた GA4 データの特徴

BigQuery にエクスポートされた GA4 データの一筋縄ではいかない点として RECORD 型を持っていることです。RECORD 型はネストされた型であり、1行の中に複数の値を持つ型です。つまり RECORD 型は第1正規形になっていないテーブルを実現しており、分析用データベースである BigQuery ならではです。

参考 : [GA4] BigQuery Export スキーマ

このデータをそのままコネクテッドシートでスプレッドシートへ読み込むと、以下のスクリーンショットのようになります。

まさに第1正規形になっていないがために、例えば event_name 列の値が page_view であるデータに対して VLOOKUP を使い、page_location や page_referrer を取得する、といった操作が不可能であることが分かります。

構成

構成図

以下のような構成で、GA4 データを解析します。当記事ではこれから、この構成について解説していきます。

ビューの活用

ネストを解き集計するためのビュー利用

設計時、No ETL/ELT で実現することを目指しました。専用のワークフロー管理ツールを使うこと無くデータパイプラインを実装し、可能な限り実装・運用コストを下げたい意図です。

しかし、先述のネストされたデータの特性から、ある程度は元データを加工する必要があります。

これを実現するため、元テーブルをクエリし「ネストを解く」「必要な event_name のみ抽出」「一定期間で PV 数を集計」する等の加工を行うビューを BigQuery で作成しました。

参考 : 論理ビューの概要

ビューは仮想的なテーブルであり実体を持ちませんが、クエリを保存する意味合いがあり、ELT の代わりとして使用しました。コネクテッドシートからはビューを参照することが可能であり、これでスプレッドシートからネストが解かれた状態のデータを扱うことができます。

マテリアライズド・ビューは断念

ビューへの問い合わせを行うと、全履歴を保持しサイズの大きい元テーブルへのクエリが発生してしまいます。もしコネクテッドシートから頻繁にビューへクエリが発せられると、体験の低下とスキャン料金の上昇に繋がってしまいます。

これを解消するため、まずマテリアライズドビューの使用を検討しました。マテリアライズド・ビューであればデータを実体として保有し、定期的なリフレッシュも可能なので、料金節約に最適です。

参考 : マテリアライズドビューの概要

しかし、マテリアライズドビューにはワイルドカードテーブルを参照できないという制約があり、断念しました。

GA4 から BigQuery へエクスポートされたデータは、ワイルドカードテーブルという形式になる仕様です。ワイルドカードテーブルは events_20230507 events_20230508 のように「接頭辞 + 接尾辞」形式のテーブル名で定義されたテーブル群です。このテーブル群は BigQuery からはまとまったテーブルと認識され、SQL の FROM 句で events_* や events_2023* のようにまとめてクエリできます。

参考 : ワイルドカードテーブルを使用した複数テーブルに対するクエリ

現在、BigQuery では、ワイルドカードテーブルではなくパーティションテーブルの使用が推奨されていますが、GA4 のエクスポートの仕組みでは依然、ワイルドカードテーブルが使われています。

参考 : BigQueryのパーティションとクラスタリングについての解説 - G-gen Tech Blog

これらの事情から、今回は通常のビュー（論理ビュー）を使用することとしました。

BigQuery へのアクセス権限

コネクテッドシートでは BigQuery のテーブルに接続し、データを利用したり、スプレッドシート上の関数操作で BigQuery にクエリを実行することもできます。そのため、シート自体へのアクセス権限に加えて、データソース（BigQuery）へのアクセス権限も考慮が必要です。

Google スプレッドシートから BigQuery へのアクセス権限は、以下のいずれかから選択します。

コネクテッドシートを作成/クエリを実行する人のアカウントの権限
アクセス権の委任機能を利用して特定アカウントに委任

Google スプレッドシートではどのエディションでもコネクテッドシートを利用できますが、注意すべき点として、アクセス権の委任機能は Enterprise、Education Standard、Education Plus のいずれかのエディションでしか使えません。さらに、組織の管理コンソールで同機能が有効化されている必要があります。

参考 : コネクテッドシートでアクセス権の委任を使用する

今回のケースでは G-gen 従業員の全員が GA4 データの BigQuery データセットに対して閲覧権限を持っていることから、1. を選択しました。

実装

やろうとしたこと

実施しようとした分析の一部をご紹介します。

ある Google スプレッドシートのシートには、これまでリリースした記事の一覧があります。ここでは記事マスタと呼ぶことにします。なおこの一覧は、現在のところ手動でメンテナンスされています。

No	URL	タイトル	執筆者	ジャンル	リリース日
1	https://blog.g-gen.co.jp/entry/iam-explained	これで分かった！Google CloudのIAMの仕組みやAWSとの違い	杉村勇馬	徹底解説	2021-09-29
2	https://blog.g-gen.co.jp/entry/login-your-vm-with-iap	踏み台サーバはもういらない。IAP(Identity-Aware Proxy)の便利な使い方	杉村勇馬	機能解説	2021-10-05

変更されづらい URL 列をキーにして BigQuery 上の GA4 データと突合（JOIN）し、「記事ごとの PV数」「執筆者ごとの PV 数」「ジャンルごとの PV 数」など軸を変えて PV 数を可視化したいと考えました。

コネクテッドシート接続

まずはコネクテッドシートの接続を作成します。先ほど作成したビュー monthly_page_view に対して、シートからコネクテッドシートで接続します。

以下のように、対象がビューであってもデータをプレビューできています。

VLOOKUP / SUMIF の注意点

当初は記事マスタの URL をキーにしてコネクテッドシートで得た月間 PV 数等に対して VLOOKUP() や SUMIF() 関数を使って集計を行い、PV 数を表・グラフで可視化することを考えました。

始めは、コネクテッドシートで接続したデータに対して以下のように SUMIF() 関数を使用しようとしました。

=sumif(monthly_page_view!page_location,A3,monthly_page_view!count)

A3 は記事マスタの URL です。コネクテッドシートで得たビュー monthly_page_view から全 PV 数を合計しようとしました。ところが、以下のように表示されます。

上のスクリーンショットように適用ボタンが表示されています。これを押下すると BigQuery に SQL が発行されます。関数が1個実行されるたびに BigQuery にクエリが発行されるので、これでは1記事ごとにクエリが発行されてしまいます。現在のビュー設定だと、1回のクエリで BigQuery 上の GA4 エクスポートデータ全体にフルスキャンがかかってしまうため、記事数分のフルスキャンが発生することになり、膨大なスキャン量と時間がかかってしまいます。これは RDB における N+1 問題によく似た状態です。

データの抽出

これを解決するために、BigQuery 上のデータをスプレッドシート上に永続化することを考えました。ビューの結果を定期的にスプレッドシートに複製しておき、そこに対して SUMIF() や VLOOKUP() をかければ、BigQuery にクエリは発行されません。

これを実現するためにデータの抽出機能を使いました。

データの抽出機能は、最大で 500,000 行または 10 MB まで、BigQuery データを取り出してシートに残しておけます。また抽出したデータはコネクテッドシートのデータ自動更新で自動的にリフレッシュされるので No ELT に近づけます。

参考 : コネクテッドシートを使用して Google スプレッドシートで BigQuery データを分析、更新する - データを抽出する

自動更新

コネクテッドシートでは自動更新を設定することができます。

今回のケースではストリーミングエクスポートを有効化していないため、GA4 から BigQuery へのエクスポートは1日1回です。コネクテッドシートの更新も、1日1回としました。

この自動更新により「データの抽出」機能でシートに永続化したデータも更新されます。よって、そのシートを参照している SUMIF() や VLOOKUP() の結果も更新されます。

これをもって限りなく No ETL / No ELT で自動更新される分析シートが完成しました。

BigQuery の料金

BigQuery のコンピュート料金を見積もる方法もご紹介します。今回の場合はコネクテッドシートからビューに対してクエリを発行します。

更新時の処理量の見積もりは、更新オプションの設定画面の下部に表示されます。これを BigQuery の単価（オンデマンドもしくは Editions）に掛けることでコンピュート料金を見積もることができます。それに加え、BigQuery に保存されているデータ量に応じたストレージ料金も発生します。

参考 : BigQuery pricing
参考 : BigQueryの料金体系(BigQuery Editions)を徹底解説 - G-gen Tech Blog

G-gen Tech Blog のアクセス傾向

記事のジャンル

2021年9月に開設された G-gen Tech Blog は、開設以来、日本語情報で分かりやすく Google Cloud 情報を発信することをテーマに、記事を増やしてきました。

執筆しているのは多くの Google Cloud 関連プロジェクトで経験を積んだ現役のエンジニアであり、いわゆる「やってみたブログ」「いかがでしたかブログ」に留まらない、本当に身のある技術情報を発信することを心がけています。

当社記事は、内部的に以下のように分類されています。

ジャンル名	説明
資格試験	資格試験に関する事項を扱う記事
徹底解説	特定プロダクトの全体を網羅的に解説する記事
機能解説	プロダクトの一部機能を詳細に解説する記事
手順解説	作業手順やサンプルコードの解説記事
速報記事	イベントや発表に関する速報を扱う記事
トラシュ	エラー事象とトラブルシューティングを解説する記事
アーキ・事例	アーキテクチャ解説や事例紹介など抽象事項を扱う記事
動画解説	動画による手順等解説を主とする記事