2024年2月16日

Looker Studio + BigQueryのクエリ費用をモニタリングする方法

執筆者吉本光一朗・解析コンサルタント

エンジニアリング

Looker StudioとBigQueryを利用してダッシュボードを構築する際、気になるのはBigQueryに課金される費用ではないでしょうか。

本記事では、BigQuery native integration in Looker Studioを利用して、Looker Studioダッシュボードによって発生するBigQuery費用をモニタリングする方法をご紹介します。

Looker Studio + BigQueryの費用計算方法

Looker Studioは基本無料のBIツール

まず、Looker Studioの利用料金は基本無料です。GA4の標準コネクタや、Googleスプレッドシートの標準コネクタを利用したダッシュボードであれば、閲覧や操作など運用によって発生する費用はありません。

Looker Studio Proという、有償版のLooker Studioもあり、ガバナンス機能やレポート配信の高度なスケジューリング機能が追加されています。しかし、特別な理由がない限りは無料版で十分なはずです。

今回はBigQueryをデータソースとした場合の費用、つまりLooker Studioを操作することにより発生するクエリの費用を確認する方法を解説します。

BigQuery料金体系

BigQueryの料金は、「コンピューティング料金」と「ストレージ料金」2つの要素で構成されています。

コンピューティング料金

SQLクエリ、ユーザー定義関数、スクリプト、特定のデータ操作言語（DML）とデータ定義言語（DDL）ステートメントなどのクエリの処理にかかる費用です。たとえば、BigQueryをデータソースとしたLooker Studioダッシュボードを操作する際に発生するクエリ費用は、こちらのコンピューティング料金に該当します。

ストレージ料金

BigQueryに読み込むデータの保存にかかる費用です。たとえば、GA4データをBigQueryに自動エクスポートしている場合、そのGA4テーブルを保存しておくための料金はこちらのストレージ料金に該当します。

なお、コンピューティング料金には、「オンデマンド」と「BigQueryエディション」の2種類のモデルがありますが、本記事はオンデマンドモデルを前提とします。

Looker Studio + BigQueryにより発生する費用の内訳

前項のBigQuery料金体系を踏まえ、Looker Studio + BigQueryのダッシュボード運用により発生する費用は次の4つに分類できます。

1. 元データをBigQueryテーブルに保存しておくためのストレージ費用
2. Looker Studioから接続するBigQueryのデータマート（中間テーブル）を保存しておくためのストレージ費用
3. BigQueryのデータマートを更新するためのクエリ費用
4. Looker Studioでレポートを操作した際に発生するクエリ費用

本記事は、これまで算出が難しかった「4. Looker Studioでレポートを操作した際に発生するクエリ費用」の費用をモニタリングする方法のご紹介です。

なお、1〜3の費用については、以下の方法で概算値を計算できます。

1. 元データをBigQueryテーブルに保存しておくためのストレージ費用

各テーブルの、「アクティブな論理バイト数、長期の論理バイト数、アクティブな物理バイト数、長期の物理バイト」の4つの容量を確認し、GiBあたりの料金と掛け算して算出する。

上記の項目は、BigQueryコンソール画面でテーブルをクリックし、「詳細」タブに移動すると確認できます。

※Google Cloud公式ドキュメント参照（2024/02/05）
※各容量に対し、毎月10GiBの無料枠が設けられています。

2. Looker Studioから接続するBigQueryのデータマート（中間テーブル）を保存しておくためのストレージ費用

1と同様の方法でデータマートの各容量を確認・算出する。

3. BigQueryのデータマートを更新するためのクエリ費用

データマートの更新1回あたりに消費されるクエリ容量を確認します。コンソールエディタへSQLを記述した際、画面右上に表示される数値が該当します。

こちらの数値を、TiBあたりの料金と掛け算して算出します。

※Google Cloud公式ドキュメント参照（2024/02/05）

Looker Studio + BigQueryのクエリ費用モニタリング方法の解説

さて前置きが長くなりましたが、本題であるLooker Studio + BigQueryのクエリ費用モニタリング方法、すなわちLooker Studioでレポートを操作した際に発生するクエリ費用の、確認方法と手順をご紹介します。

手順は以下のとおりです。

① BigQuery native integration in Looker Studioの登録
② Looker Studioダッシュボード一覧の作成（スプレッドシート）
③ 外部テーブルの作成
④ クエリ容量・料金を集計するSQLクエリの作成
⑤ テーブル作成とクエリのスケジューリング
⑥ Looker Studioでモニタリングレポート作成

手順① BigQuery native integration in Looker Studioの登録

まず、今回は現在プレビュー版としてリリースされている機能、BigQuery native integration in Looker Studio（BigQueryのネイティブインテグレーション）を使用します。

プレビュー機能のため、以下の特徴があります。

機能に対するサポートは限定的（申請・利用は無料）
使用するためには登録フォームから申請が必要

登録フォームでは、以下の項目を自社の環境に合わせて記入します。

Organization name：組織名（英語）
Google point of contact (email)：メールアドレス
GCP Projects：プロジェクトID（英語）
※複数プロジェクトを登録したい場合は、「コンマ+空白」で区切って記載します。
※例）project_a, project_b

記入が完了したら「送信」ボタンを押して登録完了です。申請が通ると、BigQuery native integration in Looker Studioの機能を利用できるようになります。弊社の場合は、翌日には申請が通っていました。

BigQuery native integration in Looker Studioの機能である、INFORMATION_SCHEMA.JOBS ビューを活用することで、Looker Studioによるクエリ結果を確認できるようになります。

手順②Looker Studioダッシュボード一覧の作成（スプレッドシート）

Looker Studioのレポート名を参照するための一覧を、Googleスプレッドシートで作成します。INFORMATION_SCHEMA.JOBSでは、レポートリンクを取得できますが、レポート名は取得できないため、レポートリンクとレポート名を紐付ける一覧表を作成します。

レポートリンクの末尾のディレクトリ（reporting/~）には、固有のレポートIDが入ります。レポートIDはLooker Studioダッシュボードを開き、URLリンクから確認できます。

また、左から3つ目のフィールドに、日本円と米国ドルの為替レートを格納しています。これは、BigQueryの料金体系が米国ドルで構成されているため、日本円に換算するためのフィールドです。スプレッドシート内で、GOOGLEFINANCE関数を用いて表示しています。

使用したGOOGLEFINANCE関数：=GOOGLEFINANCE("CURRENCY:USDJPY")

手順③ 外部テーブルの作成

②で作成したGoogleスプレッドシートを使用し、BigQueryで外部テーブルを作成します。手順は以下の通りです。

1. BigQueryのコンソール画面から、「追加」ボタンを押します。その他のソースから「Googleドライブ」を選択します。

2. テーブル作成の設定を入力します。入力が完了したら、画面最下部の「テーブルを作成」をクリックします。

3. 作成された外部テーブルを確認します。作成の際に指定したプロジェクト・データセットへテーブルが作成されていれば問題ありません。

手順④ クエリ容量・料金を集計するSQLクエリの作成

①のINFORMATION_SCHEMA.JOBSビューと、③で作成した外部テーブルをベースに、レポート別のクエリ容量（GB）と課金料金（円）を集計するSQLクエリを作成します。

CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING)
AS (
  (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key)
);

WITH base AS (
  SELECT
    DATE(creation_time, "Asia/Tokyo") AS job_date
    ,CONCAT("https://lookerstudio.google.com/reporting/", GetLabel(labels, "looker_studio_report_id")) AS report_link
    ,SUM(total_bytes_billed) AS total_bytes_billed
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS jobs
  WHERE
    DATE(creation_time, "Asia/Tokyo")
    BETWEEN DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 6 MONTH), MONTH) AND CURRENT_DATE('Asia/Tokyo') --6ヶ月前1日〜今日までのデータに絞り込み
  AND
    GetLabel(labels, 'requestor') = 'looker_studio'
  GROUP BY 1,2
),

prep AS (
  SELECT
    b.job_date
    ,s.report_name
    ,b.report_link
    ,SAFE_DIVIDE(b.total_bytes_billed, 1073741824) AS total_gb_billed 
    ,SAFE_DIVIDE(b.total_bytes_billed, 1099511627776) AS total_tib_billed 
    ,s.exchange_rate
  FROM
    base AS b
  LEFT JOIN 
    '③で作成した外部テーブルの宛先' AS s USING(report_link)
  WHERE 
    s.report_name IS NOT NULL --スプレッドシートに記入されているレポートのみに絞り込み
)

SELECT
  --ディメンション
  job_date
  ,report_name
  ,report_link
  --指標
  ,ROUND(total_gb_billed, 2) AS total_gb_billed
  ,ROUND(total_tib_billed * 6.25 * exchange_rate, 2) AS total_yen_billed --合計消費TiB×$6.25(オンデマンドコンピューティング料金)×為替レート(JPY/USD)
FROM
  prep
ORDER BY
  job_date ASC
  ,total_yen_billed DESC

※SQLクエリの一例です。貴社の環境に合わせて編集してください。