G-gen の杉村です。記事タイトル通り「Connected Sheets と BigQuery で技術ブログの GA4 アクセス解析をしている話」について、技術的な話題を中心にご紹介したいと思います。

BigQuery + Connected Sheets を使った GA4 の分析

はじめに

技術ブログと GA4

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

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

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

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

データ保持期間と BigQuery

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

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

参考 : データの保持

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

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

参考 : [GA4] BigQuery Export

Connected Sheets の活用

このようにして BigQuery に集積された GA4 のデータを、Connected Sheets 機能を使って分析します。

blog.g-gen.co.jp

G-gen 社は全従業員が Google Workspace を使って仕事をしており、Google Sheets からエクセルライクな操作感でデータ分析が可能な Connected Sheets は、手軽に分析をスタートするのに最適でした。

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

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

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

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

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

構成

構成図

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

ビューの活用

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

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

しかし、先述のネストされたデータの特性から、ある程度は見たい形に合わせて元データを加工することが必要です。

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

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

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

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

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

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

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

現在ではワイルドカードテーブルの利用ではなく パーティションテーブル の使用が推奨されていますが、GA4 のエクスポートの仕組みでは依然、ワイルドカードテーブルが使われています。よって、今回は通常のビューを利用します。

BigQuery へのアクセス権限

Connected Sheets では BigQuery のテーブルに接続し、データを利用したり、Google Sheets の関数操作で BigQuery にクエリを実行することもできます。しかし当然ながらシート自体へのアクセス権限に加えて、ソースデータ (BigQuery) へのアクセス権限も考慮が必要です。

Google Sheets から BigQuery へのアクセス権限は、以下のいずれかから選択します。

Connected Sheets を作成/クエリを実行する人のアカウントの権限
アクセス権の委任機能を利用して特定アカウントに委任

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

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

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

Connected Sheets

やろうとしたこと

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

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

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 数を可視化したいと考えました。

Connected Sheets 接続

まずは Connected Sheets の接続を作成します。先ほど作成したビュー monthly_page_view に対して、シートから Connected Sheets で接続します。

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

VLOOKUP / SUMIF の注意点

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

そこで Connected Sheets で接続したデータに対して以下のように SUMIF() 関数を使用しようとしました。

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

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

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

データの抽出

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

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

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

自動更新

Connected Sheets では自動更新を設定することができます。

ストリーミングエクスポートを有効化していないため、GA4 から BigQuery へのエクスポートは1日1回です。Connected Sheets の更新も、1日1回としました。

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

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

BigQuery の料金

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

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

参考 : BigQuery pricing
参考 : BigQueryの料金体系(BigQuery Editions)を徹底解説

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

記事のジャンル

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

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

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

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