これはmediba Adventカレンダーの10日目の記事です。5日目に引き続き、ビジネス5Gの苅部が担当します。

今年は特にGCP関連の業務が多かったため、GCPの費用に関する話を共有したいと思います。

背景

Google Analytics 4の普及に伴い、BigQueryの活用が増えています。しかし、データの抽出に伴うコストを意識するのは、なかなか難しいと思います。SQLを使って必要なデータを取得することはできても、その継続的な運用コストについて考慮することは、多くの場合、見落とされがちです。

円安や値上げの影響でBigQueryの基本コストは上昇しており、予期せぬ高負荷・高頻度のクエリによってコストが急激に増加するリスクがあります。

私自身、今年は複数のプロジェクトでコスト問題に取り組むことになりました。そこでこの記事では、実際に行ったコスト削減策を備忘録として残しておきます。

特に大規模なGA4プロパティ（数十万から数百万MAU）を運用している方の一助となれば幸いです。

状況把握

まずは、問題となっているコストの詳細を把握します。

費用の調査

支払いレポートの画面から、費用の推移の詳細を確認します。

GCPではCloud Consoleの[お支払い]→[レポート]から、サービスごとの費用内訳を確認できます。デフォルトの表示ではサービスごとにグルーピングされて表示されますが、「SKU」をグループ条件に選択することで、より詳細な費用を確認できます。たとえば、BigQueryではクエリ料金やストレージ料金などに分けて表示されます。

サービスごとの表示

SKUごとの表示

このレポートを使って、「いつから」「どの費用が」増加しているかを明確にします。私は各GCPプロジェクトごとにこのレポートを毎朝確認するようにしています。

クエリの調査

INFORMATION_SCHEMA.JOBSビューを用いて、過去のクエリの状況を確認します。

以下のクエリでは「実行されたクエリ」、「クエリ発行者」、「発行された量」を確認できます。

SELECT
  job_id,
  query,
  user_email,
  total_bytes_processed
FROM
  `projectName`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- USリージョンの場合
WHERE
  EXTRACT(DATE
  FROM
    creation_time) = CURRENT_DATE()
ORDER BY
  total_bytes_processed DESC

この結果をもとに、コストに大きな影響を与えるクエリを特定します。負荷の高いクエリから優先的に対処します。クエリの発行元（オンデマンドクエリ、スケジュールクエリ、カスタムクエリ、Connected Sheet、IAMユーザー）を軸として調査するのも良いと思います。

実際のSQLのベストプラクティスについては以下のドキュメントが参考になります。

次に、実際にどのようにしてコスト問題を解決していくかを考えてみます。

対策1： BigQuery Editionsの導入

BigQuery Editionsに切り替え、スロット数の上限を設定します。

BigQueryのオンデマンドクエリは最大2,000スロットを利用してクエリを処理しますが、Editionsを使用してスロット数を制限することで、費用の削減ができるかもしれません。

例えば、あるプロジェクトでは「オンデマンドで1回の実行で2桁TB」のクエリにより、3万円近くの費用が発生していました。しかし、Editionsに切り替えて200スロットに制限することで、費用を2千円以下に抑えることができました。

最終的に、複数のプロジェクトでEditionsに切り替えたことで、年間で約200〜300万円の費用削減に繋がりました。急激な費用増加のリスクも低減するので安心感もあります。

200スロット程度の上限設定により、多くの高コストクエリを防ぐことができました。

※100〜200スロットの上限設定では、クエリの返却が遅くなりましたが、ほとんどがバッチ処理のため、大きな問題はありませんでした。

※Editionsの導入には、その理解とスロット管理の考慮が必要です。導入の注意点については後ほど詳述します。

対策2：中間テーブルの改善

バッチ処理（スケジュールクエリ等）で中間テーブルを作成する場合、そのプロセスの中で改善できる点を検討します。

冗長なクエリの確認

「日次で過去N日分を集計する（例：MAUの集計）」などのクエリはよくありますが、別の方法で解決できるかを検討しましょう。

スケジュールクエリの実行回数の削減

土日に稼働する必要がないクエリがあれば、それらを停止することで、約3割のコスト削減が可能です。

対策3：ダッシュボードの改善（Looker Studioの例）

Looker Studioでのカスタムクエリが原因で、コストが大幅に増加するケースがあります。利用時には注意が必要です。

カスタムクエリの見直し（キャッシュの活用）

Looker Studioでは、カスタムクエリを使用してBQテーブルに直接クエリを発行することができますが、クエリ結果が大きいとキャッシュが有効にならず、ダッシュボードを開くたびにテーブルを参照します。

たとえば以下のクエリでは、返却されるレコードが多いため、Looker Studioの内部キャッシュは通常有効になりません。

SELECT
  event_date,
  user_pseudo_id
FROM
  `projectId.analytics_propertyId.events_*`

キャッシュが無効であれば、閲覧者がレポートを開く度にクエリが発行されます。

そのため、Looker Studioのカスタムクエリを使用する場合は、返却されるデータを軽量化することが重要です。

最低限の対策としては、日付の範囲を指定しましょう。Looker Studioには期間パラメータがあるので、これを利用して日付範囲を狭めます。

SELECT
  event_date,
  user_pseudo_id
FROM
  `projectId.analytics_propertyId.events_*`
WHERE
  _table_suffix BETWEEEN @DS_START_DATE AND @DS_END_DATE

次に、レポートの必要に応じて返却するデータを最小限にします。

例えば、前述のSQLを以下のように変更することで、レコード数を大幅に削減できます

SELECT
  event_date,
  count(user_pseudo_id) as uu
FROM
  `projectId.analytics_propertyId.events_*`
WHERE
  _table_suffix BETWEEEN @DS_START_DATE AND @DS_END_DATE
GROUP BY
  event_date

「1年間毎日1万人が訪れる」と仮定すると、前述のSQL（グルーピングなし）では1年間で2列365万行になりますが、後述のSQL（グルーピングあり）では2列365行になります。

このようにグルーピングを行うことで、データサイズを大幅に削減できます。これは、Looker Studioのキャッシュ活用とクエリの抑制において重要なポイントです。

例えば、以下のような流れで問題が発生することがあります。

Aさんがレポートを閲覧
GAテーブルに SELECT *
Bさんがレポートを閲覧
2.のキャッシュがないため再度クエリ実行
GAテーブルに SELECT *

閲覧者が多いほど、クエリの発行回数が増え、課金が発生します。クエリ量が小さくても、閲覧者が多いと大きな問題になります。

レポートの高速化とコスト削減のためには、Looker Studioのキャッシュが有効に機能するようなクエリ設計が必要です。

「データの抽出」機能の活用

Looker Studioには「データの抽出」という機能があります。これにより、直接BQテーブルにクエリを投げるのではなく、中間テーブルをスケジュール実行で更新し、BQテーブルへの参照を減らすことができます。

※ヘルプに記載されている通り、データ量の上限（テーブルのサイズ）は100MBです。

ダッシュボードの利用状況を計測する

Looker Studioでは、GA4の測定IDを設定できます。

不要なLooker Studioのページを後で精査する可能性もあるため、事前にGA4プロパティを作成し、レポートの利用状況を収集することを推奨します。

予防策1：クエリの上限設定

BigQueryでは、発行されるクエリの上限を設定できます。これにより、高負荷なクエリの事前防止が可能です。

[IAMと管理] → [割り当て] → [BigQueryAPI] → [Query usage per day]で任意の値をセットできます。

一定のクエリ発行量に達すると自動的に停止するので、サーキットブレーカーのような役割を果たします。

予防策2：費用データのBQエクスポートと可視化

日々のクエリ発行に伴い、請求額も日々チェックすることが重要です。

[お支払い] → [課金データのエクスポート]から、BigQueryへの費用データエクスポートを設定できます。

BigQueryテーブルに費用データをエクスポートすることで、Looker Studioなどのダッシュボードから確認できます。

GCP内で連携することにより、課金自体の自動停止もできるようです。

予防策3：予算アラートの設定

[お支払い] → [予算とアラート] から、請求額の目標値（上限）を設定し、進捗に応じたアラートを設定できます。

予防策4: 権限管理

「利用状況が不明なバッチクエリ」の増加を防ぐため、権限の付与やバッチ実行は慎重に行うことが重要です。野良クエリ（スケジュールクエリ、スプレッドシートのConnected Sheet、Looker Studioでのカスタムクエリ）や野良テーブルが乱立し、誰も対処できない状態になることを避けましょう。自社で管理できる範囲内での「クエリ実行権限の付与」に留め、テーブル参照権限の付与に限定しましょう。

備考：BigQuery Editionsについて

BigQuery Editionsの設定は比較的容易ですが、いくつか考慮すべき点があります。

Editionsの種類

EditionsにはStandard、Enterprise、Enterprise Plusがあり、プロジェクトの状況に応じた適切なプランを選択する必要があります。例えば、VPCで保護された領域との連携が必要な場合や、BigQuery MLを利用する場合にはEnterprise以上が必要です。

導入検討の際には費用変動をシミュレーションしてみましょう。

ベースライン（下限）スロット数

ベースライン（下限）スロット数が0より大きい場合、使用していなくても固定費が発生します。そのため、基本的には0に設定しておくと良いと思います。

クライアント側のタイムアウト

通常のオンデマンドクエリでは最大2,000スロットを利用しますが、Editionsでスロット数を減らすと、クエリの実行時間が長くなり、同時処理能力も低下します。並列処理できないクエリはキューに入り、順番待ちとなりますが、これはクライアント側でタイムアウトとなる可能性があります。

例えば、Looker Studioのタイムアウトは3〜5分ですので、その時間内にクエリが返却されない場合、Looker Studioはクエリをキャンセルします。

注: Looker Studio のクエリは 3～5 分後にタイムアウトすることがあります。カスタムクエリがタイムアウトした場合は、次の方法で問題を解決してみてください。

実行速度を上げるために、クエリを簡素化します。

データベースでクエリを実行し、結果を別のテーブルに保存してからデータソース内のそのテーブルに接続します。

https://support.google.com/looker-studio/answer/6370296?hl=ja#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9

他のBIツールでも同様のタイムアウトが発生する可能性があり、エラー監視やスロット量の最適化が必要です。

おわりに

GA4をBigQueryに接続すること自体は簡単ですが、導入後の管理とパフォーマンスの最適化は難易度が高いかもしれません。

BigQueryが従量課金制であることを念頭に置き、想定外の請求を避けるためにしっかりと事前準備をしましょう。