社内の業務効率化を目指して開発した、各種広告効果計測ツールのデータを社内のデータ基盤システムに自動でアップロードするシステムを紹介します。
はじめに
こんにちは、エンジニアの@hey_cubeです。 2020 年の 2 月頃から表題のシステムを開発しているのですが、最近ようやく形になってきました。 そこで今回は、今日に至るまでの開発の経緯について記します。
開発の経緯
企画（2020 年 2 月）
デジタル広告の運用では、広告媒体（以下「媒体」と称する）と広告効果計測ツール（以下「計測ツール」と称する）が利用されます。 媒体には各広告の予算などの情報が、計測ツールには各広告の CV 数などの情報が含まれています。 弊社は Performance Platform(PP) というデータ基盤システムを社内に構築しており、そこに媒体と計測ツールのデータが集約されています。
各種広告媒体データと広告効果計測ツールデータを蓄積し、各種社内プロダクトにデータを返却する API を提供しています。主要な広告媒体データは媒体 API を用いて毎日自動で取得しており、工数削減に役立っています。また、広告媒体データと広告効果計測ツールデータの紐付けや着地見込の計算などを基盤システムで一元的に行うことにより、各プロダクトはその他の機能の開発に注力することができます。
引用の通り、媒体のデータは PP が毎日自動で取得しているのですが、計測ツールのデータは全て社員が手動でアップロードしていました。 手動というのはつまり、計測ツールの管理画面にログインして、データを CSV 形式でダウンロードして、Excel で開いて、整形して、XLSX 形式で保存して、社内ツールの管理画面にログインして、アップロードする...ということです。 「これを自動化することで工数を削減しよう」という提案が社内で勃興し、開発者として白羽の矢が立ったのが自分でした。 本当に実現できるか不明だったので、試作開発は自分一人で行うことになりました*1*2。 現場の運用などがどうなっているか全く知らない状態からのスタートだったため、まずは PP とその周辺システムの仕様理解や現場のオペレーションの詳細を知るところから始めました。
試作開発（2020 年 3 月 ~ 2020 年 11 月）
Google Analytics が最も使われているようだったため、ひとまず Google Analytics の CV データを自動でアップロードすることを目標にしました。 Reporting API v4 があるから楽勝だろうと高を括っていましたが、現実はそんなに甘くありませんでした。
認証をどうするか
Google Analytics のデータを取得するには、当然データの閲覧権限が必要です。 API 経由で取得する場合、権限の獲得には以下の 2 通りから選択することになります。
- Google Analytics の権限を付与したサービスアカウントを使う
- Google Analytics の権限を持つ人間に OAuth 2.0 認可 をしてもらい、そこで手に入るトークンを使う
Cloud Functions が毎朝 API を叩くという構成で考えていたため、やりやすいのは前者です。 が、計測ツールは基本的に顧客（広告主）の所有物であるため、広告代理店が計測ツールにユーザーを追加することはできない（= サービスアカウントに権限を付与できない）という話になりました。 仕方がないので、本システムでは後者の方式を採用することにしました。 また、毎朝実行する度に認可をもらうわけにはいかないので、リフレッシュトークンを使うことにしました。
試作したシステムにリフレッシュトークンを渡す流れは以下の通りです。
- 対象の Google Analytics に対して閲覧権限を持っている社員が認可コードを発行する*3
- 社員が認可コードを僕に渡す
- 僕が Google の API を叩いて認可コードをリフレッシュトークンに変換する
- 僕がリフレッシュトークンを Secret Manager 内に保存する
ただし、このやり方にもいくつかデメリットがあります。
- 作業の度に僕の手が必要になる
- 認可コードには有効期限があるため、作業に時間制限がある
- Google アカウントの認証は色々と制約がある*4ため、RPA などで自動化はできない
- 認可コードを発行した Google アカウントの状態が変わるとシステムが止まる可能性がある
- Google アカウントから Google Analytics の権限が剥奪された時
- 退職などで Google アカウントが削除された時
試作なので最初の 1 つはまあ良いとして、問題は 2 つ目と 3 つ目です。 これらはどうしようもないので、そういう問題があることをステークホルダーに説明した上で、仕様として許容することになりました。
サンプリングをどう回避するか
Google Analytics では、セッション数が多いアドホックレポートは自動的にセッションがサンプリングされます。
データのサンプリングについてより引用:
セグメントやフィルタ、セカンダリ ディメンションを適用してデフォルトのレポートを変更した場合や、デフォルトのレポートにはないディメンションや指標を組み合わせてカスタム レポートを作成した場合は、アナリティクス データに対するアドホック クエリが生成されます。
アナリティクスでは最初に集計データの表が参照され、アドホック クエリでリクエストされたデータが揃っているかどうかが確認されます。必要な情報がない場合は、リクエストに対応するため、フィルタされていない完全なデータセットが照会されます。
アドホック クエリでは、選択した期間内にセッション数が対象タイプのプロパティのしきい値を超えた場合、サンプリングが行われます。
PP にアップロードするデータのほとんどはカスタムレポートを使って作成されたものであり、今回使用する Reporting API v4 もカスタムレポートと同等であるため、サンプリングされる可能性があります。 しかし、正確な数字でないと顧客に提出したり社内のコンサルタントが参照したりする時に困るため、サンプリングされたレポートはアップロードできません。 そのため本システムでは、2 日以上の期間のレポートのアップロードが指示されたとしても、内部の API 呼び出しは必ず 1 日分ずつに分けて実行するよう設計しました。 また、API のレスポンスがサンプリングされているようだったらエラーを吐いて処理を中断するようにもしました。
広告階層の名称をどう変換するか
デジタル広告は、主に媒体・キャンペーン・広告グループという 3 階層のグルーピングが施されます。 それぞれは入れ子になっており、各階層には適当な名称が与えられます。 デジタル広告で利用される各種媒体・計測ツールはその概念に相当する機能を有していることが多く、PP もアップロードされるデータがそれらの情報を含んでいることを前提に設計されています。 より正確には、媒体のデータと計測ツールのデータを紐付けて分析等に活用するために、PP にデータをアップロードする際には「媒体側の各階層と計測ツール側の各階層の名称が一致している」必要があります*5。 しかし、社内の運用を見ると媒体と計測ツールそれぞれに登録している名称は一致していないケースがほとんどであり、計測ツールのデータをアップロードする時はまず手元で媒体側と名称が揃うよう書き換えるというオペレーションが発生していました。 さらにその書き換えのルールは顧客ごとにバラバラであったため、共通化も難しいという状態でした。
そこで本システムでは、Google スプレッドシートに「どんな広告をどんな名称に変換するか*6」を全て列挙してもらい、それと計測ツールのデータを BigQuery 上で JOIN することで解決しました。
BigQuery はスプレッドシートをデータソースとしたテーブルを簡単に作ることができるため、手軽に実現することができました。 ただ、このやり方だと広告の数だけスプレッドシートに記入する必要があり、多い顧客だと数万行にもなるため、管理上の問題があります。 今の現場の運用をそのまま踏襲しようとすると原理的にどうしてもこうなってしまうため、現場の運用ルールから変える必要があると考えています。
試作稼働（2020 年 11 月 ~ 2021 年 6 月）
最低限の要求を満たした試作が完成したので、それを一部の現場に導入することで、実際にどれだけ工数が削減されるのかを確認しました。 結果、2021 年 6 月末時点で累計約 340 万円の削減に成功しました。
ただ、導入を検討した現場に対して実際に導入できた割合は約 35% とかなり低い結果になりました。 現場の作業内容や計測ツールの使い方は顧客ごとにバラバラであり、それらをカバーした仕様を練り上げることの難しさに起因していると考えています。 実際に導入することで判明した現場の実態は色々あるのですが、例えば広告階層を UTM タグではなくカスタムディメンションで管理しているケースは想定していませんでした。 システムを改修してより幅広いケースに対応しつつ、現場の運用もシステマチックに扱いやすい形に統一するといった、開発と現場双方からのアプローチを進める必要があります。
余談ですが、本システムの開発が評価され、2020 年下期納会で準 MVP 賞をいただきました。
本開発（2021 年 1 月 ~ 2021 年 7 月）
2020 年 12 月末時点でこの調子なら黒字になるだろうと判断されたため、2021 年 1 月からは試作の稼働と並行して本開発も行うことになりました。 このタイミングで既存の別プロダクトチームに合流する形で開発メンバーが増え、自分含め 6 人で開発を進めることになりました。 試作の段階では作っていませんでしたが、全社へ導入するとなると諸々を操作できる UI が当然必要になるため、Web の画面とそれ用の API サーバーを作成しました。 また試作は長期間使用されることを想定して設計していなかったため、計測ツールからデータを取得し PP にアップロードする Cloud Functions 群も新しく作り直しました。 最終的な構成は以下の通りです。
フロントは Firebase、API サーバーは App Engine、データベースは Cloud SQL を使っています。 インフラは Pulumi で管理しています。
計測ツールのデータをアップロードする処理は Cloud Functions と Cloud Pub/Sub で構成されており、それのトリガーには App Engine の cron 機能を使っています。 App Engine が Cloud SQL から必要な情報を全部取得してからそれをメッセージとして Cloud Pub/Sub に渡す形にすることで、Cloud Functions と Cloud SQL を切り離し、Cloud Functions のリトライや試験稼働を実施しやすくしました。
PP は AWS 上に構築されているのですが、S3 バケットに CSV ファイルを配置すれば後は勝手に取り込んでくれるので、本システムは S3 バケットへのファイル配置までやれば大丈夫です。
本稼働（2021 年 7 月 ~）
これでシステムはおおよそ完成したので、これからいよいよ本稼働を開始します。*7。 まずは試作を導入した現場を本システムに移管し、それが完了したら他の現場にも拡大していく予定です。
最後に
今回は、表題のシステムの今日までの道のりを紹介しました。 ステークホルダーとの調整をもっと丁寧にやれば試作の開発や導入はもっと素早くできたはずだという反省はありつつ、それでも準 MVP 賞をいただけたということはそれなりに意義のある仕事ができたのかなと思っています。
Opt Technologies ではエンジニアを募集中です。カジュアル面談も可能ですので、下記リンク先よりお気軽にご応募ください。
*1:プロジェクトマネージャーやステークホルダーはいました。
*2:AD EBiS のデータを自動でアップロードするシステムも試作したのですが、その部分は他のエンジニアに作ってもらいました。
*3:本システムは Web アプリではないため、OAuth クライアント ID は「テレビと入力が限られたデバイス」で作成しました。
*4:新しい端末からのログインでは自動的に端末認証が課せられるなど。
*5:ほとんどの広告はこの条件を満たしている必要がありますが、全てではありません。
*6:広告によっては媒体に入稿した URL も記入してもらっています。
*7:この記事は 2021 年 7 月 21 日に執筆しています。
