trocco®️を使ってわずか30分でBigQueryのクエリコストを1/600に削減した話

本投稿はtrocco®️のQiitaのAdvent Calender2023に12月7日の投稿としてはてなブログ経由で投稿されています。 qiita.com

ポイント！どうも、キュービックのテックリードの尾﨑です。
本日は育休で時短勤務中にtroccoを使用してわずか30分でBigQueryに蓄積した
GA4データを参照する際のクエリコストを1/600に削減した事例を紹介します。

概要
きっかけ
- しかし・・・
やったこと
今後の課題
まとめ

概要

BigQueryの特定のデータセット配下に毎日同期されるメディアごとのGA4データをtroccoのデータマート機能を使用して整形し、異なるデータセット配下の整形テーブルに書き出しTableauで可視化を30分で実行します。 ※trocco,tableauがある程度触れる前提です。作業時間には個人差があります。

きっかけ

2023年6月末でUAが廃止になり、GA4に運用移行が起きました。 50近いアクティブメディアを運営しているキュービックも例に漏れず新しいメトリクスでの分析が求められるようになりました。そういった中で広告とSEOのデータの分析担当者からBigQueryにGA4のデータを同期することはできるようになったが、クエリのコストが懸念でSQLでの整形処理をフォローして欲しいという相談が頻繁に来るようになり、短期的にコスト削減施策を講じることにしました。

しかし・・・

8月は産後パパ育休という制度を利用して時短勤務を行なっていたため、稼働時間はわずか3時間でした。定例や朝会などの主要なMTGを除くと1日あたりの実装に避ける時間は30分程度となりました。

これは困りました。コスト削減はしたいけど、時間もない。どうすれば良いのか・・・

そこで、troccoを使ってデータ連携とデータ整形を行うことで、短時間でコスト削減をすることにしました。

やったこと

データ連携パターンの洗い出し

まず、現在の利用技術でクエリのコストを削減するにはどのような方法があるか？をパターンで洗い出しました。troccoではGA4のコネクタが用意されていたのでBigQueryにそもそもGA4データを同期する必要はないのでは？という考えが一瞬過りました。

しかし、1時間あたりの上限や取得レコードの制限などもあることなどから現時点では適切ではないと判断しました。

※パターンの洗い出しは作業時間には含んでおりません

連携パターンの選定

色々方法論はあるもののBigQueryの知見はそれほどなく、RDBを建てるとなると予算承認など検証に至るまでのリードタイムが手間であると思いました。そこでBigQueryのベストプラクティスに則ってBigQuery内で整形データを生成してローデートと別のテーブルに保管し、そちらを参照することにしました。

troccoの設定

設定手順

1.troccoの接続設定でBigQueryを追加
2.データマートシンクジョブの設定
3.ワークフロー定義

1.troccoの接続設定

接続設定でBigQueryを追加します。GoogleアカウントでOAuth認証が提供されているので、Redshiftと比較するとかなり容易でした。

2.データマート定義の作成

シンクジョブを作成してBigQueryに対してSQLで整形を実施して加工データを異なるデータセット配下のテーブルに書き込みます。

今回ボトルネックになっていたのが、特定のデータセット配下にメディア毎にGA4のデータが毎日同期され、そちらを参照することでクエリのコストがかかっていました。

そこで、troccoのデータマート定義を使用して、整形データを異なるデータセット配下にテーブルとして整形データを蓄積しようとしたという寸法です

データマート定義の設定方法

左メニューのデータマート>データマート定義を謳歌して、画面右上の新規データマート作成を押下します。ワークフロー定義の作成画面に遷移したら必要項目を順番に埋めていきます。

1.troccoの接続設定で設定した接続情報を選択します

対象のデータウェアハウスとしてBigQueryを選択します

カスタム変数を設定します

データ転送モードを選択し、BigQueryのテーブルから加工したい情報をSQLで記載します。(ストアドプロシージャの関数をCALLする形でも構いません)

ワンポイントテクニック1 カスタム変数でデータセットテーブル名event_YYYYMMDDの部分を指定しワークフロー定義やスケジュール実行時に動的な値を設定できるようにすべし

  FROM
    `$tableName$_$date$`

ワンポイントテクニック2 GA4のデータがBigQueryに同期されるまでは48時間程度かかるため日付のキューイングは2日前にしておくべし

最後に出力先データセットと出力先テーブルを設定します。カスタム変数化しておくと後々便利です

3.ワークフロー定義の設定

ワークフロー定義で2で作成したシンクジョブを使用してカスタム変数でテーブル名をリストで登録します。

これによりループ実行が可能になります。

最後にスケジュール設定で毎朝指定時刻にワークフローを実行するように設定すれば自動化が完了です。

tableauに可視化

trocco®️でBigQueryに整形したデータを格納することができたので、お次は整形したデータをBIツールのTableauで参照できるように設定を行います。

1.tableauのワークブックを新規作成してコネクタでBigQueryを選択

2.データソースを選択してカスタムSQLでtroccoのワークフローで生成したテーブルのデータを参照

キュービックではカスタムSQLを使用して、データソースのデータをSQLで整形し、viewで参照するというtabelauをETLとしても扱う少々トリッキーな使い方をしています。

ワンポイントテクニックパラメータを使用しよう！！

tableauではパラメータというSQLに埋め込める動的な変数が用意されています

こちらのように任意のカスタムパラメータをSQLに埋め込むことで

日付の期間指定等のSQL内でのフィルタしたい項目を動的に設定することが可能です。

結果

1/600に縮小！！ troccoのワークフローで毎日整形したデータを蓄積し、参照する形式に更新したことでデータ量を1/600に削減することができました

今後の課題

今回は突貫ということもあり、データを加工して縮小するにとどまりました。分析したいイベントやメディアが増えた際や分析する期間が年単位などになった際は現状のアーキテクチャではコストメリットを受けられない可能性が高いです。BigQueryのプランをBigQuery Editionsに変更も視野に入れる必要があります。