この記事は弊社（弊グループ）Wanoグループ Advent Calendar 2020の18日目の記事です。
私はWanoグループのAlpha Architect株式会社（以下AA）に所属する maretol です。
主に内部向けの管理画面、その中でもレポート周りで業務を行なっております。
はじめに
AAは業務として動画広告の配信サービスを行っております。そう、みんな大嫌い動画広告です。弊社は内容や配信時の負荷は爆発しないように意識しているつもりですがまあ嫌われ者です。
そんな嫌われ者の動画配信ですが、配信する側として重要なものの一つにログがあります。
つまり「どんな人が」「どの広告を」「どこまで見た」「そしてそのあとどうしたか」あたりです。物によってはもっとログを取ろうとしたり、あるいは取らなかったりします。
ですがいずれにせよ、広告の視聴が行われた段階で予算消化（広告提供側や広告掲示側などの間でお金の授受）が発生するので記録そのものは大変重要です。
そしてその上で広告が効果的であったかどうかを確認するため各種数値を評価する必要があります。
今回はその評価のためのデータに関する取り扱いでどのようなことをしているか紹介させていただきます。思い出話が混じるほか、技術的に特異なことをしているわけではありませんが、一つの問題解決としてエンジニアリングの中での根本でもあると思いますのでご容赦ください。
Web広告、と言うか昨今のWeb業界に詳しい方であればご存知の通りですが、そう言ったログなどのデータは徐々に取りにくくなっています。Cookieの制限だけでなく、ドメインの制限など様々な事情がありますし、いわゆる巨大IT企業と同じことは単純にインフラ面でのコストの都合もあり不可能です。
そのため多くのパターンで外部のサービスを使うことになるでしょう。
じゃあどう言ったサービスを使うの？と言うと弊社ではその中の一つにADEBIS社の機能を用いて効果計測を行っております（ターゲットとなるセグメントや配信方式次第でクライアントと相談して適宜決定しております）。
ADEBIS社の計測ツールは優秀ですが、問題点が一つありました。
それが__ログの管理とそのログデータからのレポートの生成です__。
レポートの取り込みシステムができるまで
今回の外部のログを取り込むと言う仕組みが生まれるまでどのような形であったかと言うと、
- ADEBISの管理画面からCSVをダウンロード
- 動画情報が入っているExcelファイルに取り込み
- ピボットテーブル・マクロ・計算式にて数字を抽出
- 動画情報を参照して手動で数字合わせ
と言った形を取っておりました。つまり手動です。人の手が入る以上どこかでミスが発生する可能性もありますし、何より手間がかかります。手動でCSVダウンロードからExcelに貼り付け、と言うのは焦ったくなかなか精神的な負荷が高い作業でしょう。おまけにこのレポート、クライアントへ提出するものですので間違えれば大変なことになります。
結果としてそのような作業が負担となっていた運用側から改善要望が入りました。
とは言うものの、外部の数字を持ってくるとなると向こう側、つまりデータ提供側であるADEBISさんにも協力が必要となります。なければスクレイピングなどを行う形になるでしょうが、そこはAPIがあると言うので調査しました。
結論から言うと、有料サービスではあるものの確かにそのAPIならデータが持ってこれるだろう、となりましたが、なんとなんとそのAPIが近々提供終了するとのことでした。完全に余談ですがこのAPIはSOAP規格で、全く知らなかったのでTwitterで経歴の長いエンジニアさんに教えてもらったりしました。
ですがAPIのサービス終了と同時に新サービスとして「データエクスポート」なる機能が追加されるらしく、それを使用してみないかと先方より提案がありそちらを利用することとなりました。
レポート取り込みシステムの作成
では現状の手動でのレポート作成を自動化します。ざっくり自動化する上で必要だったことを上げていきます。
データエクスポート機能への対応
まずは先方のデータを取り込むシステムを作ることと相成ります。提案されたデータエクスポート機能を使います。
ざっくり説明するとこの機能、「指定条件のスプレッドシートをGoogleスプレッドシートで出力する、またはCSVファイルをメールで送信する」と言うものでした。
APIと違い、こちらの任意で時間を指定できず、投げつけてもらうためにはメールで受け取るしかありません。専用アドレスを用意し、そこでのメール受信から起動するか、バッチ処理で定時にスプレッドシートが用意されていることを前提とした動きをするしかありません。
大変ですね。
今回はGoogleスプレッドシートの生成を前提としたバッチ処理で取り込むことにしました。ファイルの操作などを考えたとき、受け取ったCSVファイルの保管がスプレッドシートの場合Googleアカウントで保存されるので普通に楽だったこと、リアルタイム性はそこまで必要なかったことが理由です。
問い合わせたところAM09:00にはファイルが作られている想定でいいとのことだったため、バッチは9時過ぎぐらいに実行するようにして取り込むようにしました。
ちなみに最初は「今後のことも考えて可能な限り全てのデータを保管したい」と言われていたのですが、残念ながらGoogleスプレッドシートのセル数の上限に引っかかったりしました。Googleスプレッドシートってセル数の上限あるんですね。調べたところちょっとずつ上限セル数は増えているそうなので、いつか全部取れる日が来るのかもしれないですね。
データの取り込み
データはそのままではいけません。検索したり数えたり、既存のレポートではExcel上で数えたり計算したりしているわけで、同じことができなければなりません。スプレッドシートではデータが分散したまま（日毎で出力されるので）なので、一括で管理できるデータベースに入力する必要があります。
今回はRDSではなくより大量のデータを扱うのに向いたBigQueryを使うことにしました。選定の基準としてもともと弊社ではBigQueryを用いて管理しているデータがあったことと、データ量としてはビッグデータというほど多くはありませんが、しかしRDSでは処理に手間がかかることや金銭面での都合を考えそうすることにしました。
これは余談ですが、実際BigQueryの使用料が増えると思って課金額のアラートを新設し注視したのですが（これまた余談ですが今までアラートがなかったりしたのですが）、ほとんど使用料は増えませんでした。
データの取り込みはLambdaを用いて行います。本来GoogleスプレッドシートをBigQueryに取り込む上ではベストプラクティスではないとも思いましたが、現在稼働中の処理のいくつかLambdaで管理されていることや基盤としてAWSを利用しているところが多いためそれらの連携の都合です。
上述のバッチ処理としてLambdaの起動をCloudwatch Eventsで行い、Googleスプレッドシートを参照、SpreadsheetAPIで値を読み取り、BigQueryに流す梯子役を作成しました。
詳しくは後述しますが、この処理ではGoogleAPIへのAPIKeyとBigQueryへのサービスアカウントの情報をAWS Secrets Managerに保管したほか、読み込んだスプレッドシートのログをDynamoDBに保管するなど地味に情報の取り扱いに気を使ってます。そうすることで後々にメリットが生まれたのが理由でもあるのですが。
レポートの数字の導出
データは保存するだけではダメで、それを利用できなければいけません。
今回は運用サイド・営業サイドの使用方法を聞きながら実際にどの数字をどのように出しているか、実際に使用しているExcelファイルなどと睨めっこしながらそれを実装することになりました。
こちらも結論から言いますがこの作業が一番大変でした。なぜなら文章化されたマニュアルもなく、場合によって、案件によって違う計算がされているケースもあったからです。
つまり事実上仕様がなかったのです。しょうがないね。
ヒヤリングからの仕様作成という地味にエンジニアっぽくないようで最もエンジニアリングな体験ができたのはいい思い出です。何度か実際に出してみた数字が合致していないというケースもありました。
余談ですがこのときが3月ごろで、リモートでの作業が増えたことにより普段は忙しい運用サイドの拘束時間が結果的に取りやすくなったことは幸運でした。
閑話休題。
さて実際に話を聞いたところ、少し特殊な計算方法を行なっていることに行き着きました。単純に数を数えるだけではなく、特定の条件では多重でカウントしつつ、別の条件ではカウントしない、と言った感じです。
またカウントする際の情報はログデータのみではなく動画情報のデータも必要でした。
これらを一括のクエリで出すのは、不可能ではないもののクエリ自体が重くなり、金銭的にも処理時間的にもあまり優しくないなと考えました。また、カウントの処理は複雑でクエリで実装するのは現実的ではありませんでした。そのため、条件検索に使いやすい中間テーブルを用意し、検索を行うことと、検索した結果をカウントするという別処理を置くことにしました。
中間テーブルは（も）BigQuery上のテーブルを用意します。こちらもデータ量自体は多くないという点では同じですが、検索条件でBigQueryの配列型を使うことでより簡易に検索ができるようにしたかったための決定です。
テーブルの変換はBigQuery上のスケジュールクエリの機能を利用しました。この機能とクエリ上での関数を条件・変換に用いることでテーブルの変換を実装しました。
ちなみにスケジュールクエリはベータ版ですが、現状問題は特になく動いております。内向きの機能＋いざというときは手動で対応可能なので使用に踏み切りましたが、やはりGoogle大先生は優秀ですね。
そして変換したテーブルに対して、条件を指定して検索するクエリを投げる役割と、検索結果のカウント処理はLambdaで実装を行いました。これに関しては管理画面側から呼び出すという点で上述のLambdaと同様に管理したかったというのが理由です。管理画面上から直接呼び出すこともでき、カウント処理も管理画面の機能として実装することは……事実上不可能でした。後述します。
管理画面からの呼び出し
最終的にレポートとして管理画面から呼び出されるデータと、CSVから紆余曲折で出てくる数字をExcel上で合成していたことから、じゃあレポート画面から一括で出せれば楽だよね、という話になるわけでして、管理画面上からの呼び出しで最終的な実装となります。
このとき、管理画面上のデータや値をカウントに利用するため、いくつかの項目を新設。今までは運用側が（というかクライアント向けのExcelファイルを作っていた人が）分かっていればよかった各種設定値や名称を、システムリーダブルなもの（つまり同一の値）で登録してもらう必要がありました。こちらは相談の結果対応してもらいました（が、現状いくつかの場面ではヒューマンエラーが発生してしまうところもある）。
特にADEBISの管理画面で設定する物に関しては直接的にシステムが参照できないため手動でのコピペで対応してもらっているところが多くなっており、なかなか難しいところではあります（これも余談ですがADEBISは現在新APIを開発中だそうで、詳細は不明ですが登録情報を引っ張ってこれるといいな、と思ってます）
管理画面からの呼び出しは前述の通り、Lambdaをいったん経由することにしました。なぜなら現在絶賛刷新中ではあるのですが、弊社の社内向け管理画面はPerlによって記述されているからです。
Perlには、BigQueryへのライブラリなどありませぬ。まあ無理をすればbqコマンドで呼び出して標準出力で受け取る方法もあります。が、現在刷新中で、そっちではScalerを用いていることから、実装に二度手間をかけたくないため切り分けることにしました。
ただ結果的にBigQueryの処理をLambdaに移設することで、Lambdaで使うサービスアカウントの情報が流し込みの時に学んだことを直接生かすことができあっさりと実装できたのは行幸でした。
最終的に、管理画面からAPIGateway経由でLambdaに計測関連で必要な動画登録情報を投げ、LambdaはBigQueryにクエリを投げ、結果を受け取り、動画情報などを元に計測・計算処理を行い、管理画面に返す、という流れが完成しました。計算時間はかかるものの、APIGatewayの同期呼び出しでタイムアウトせずに返ってくるぐらいの速度で数字は出せています。
運用と課題
システムは完成して終了ではありません。その後の運用も大事です。
幸いなことに、運用側からはシステムは好評に受け入れられております。しかしいくつかの課題があることも事実でした。
その中には「本来結び付けられているはずのデータが結び付けられていない」というものもありました。これは、仕様が固まり切る前に順次実装していた故に関係性を実装しきれずリリースしてしまったものもあります。
また、同様の理由で無駄に冗長化したデータ構造になっているものもあり、そう言った課題は解決しきれていません。
そのほか、やはり手動で入力してもらう必要があるいくつかの項目にミスが発生しやすいという点もありました。実装時に相談した際は双方納得していたものの、実運用段階になるとどうしても回避しづらいものも出てきます。そう言った物に今後どうやって対応していくかは今後の課題となりました。
とはいえやはり今までExcelでやりくりしていたデータがほぼ一発で出てくるのはかなりのメリットがあったようで、レポートの作成時間も大幅に短縮が可能となったようです。また、仕様が確定して出せるようになったことで今まで数字を算出できなかった物に関してもアプローチを得ることができたと聞いています。
最後に
結局のところやっていることは
- 外部サービスがスプレッドシートを作る
- スプレッドシートを読み込みBigQueryに流す
- BigQueryが中間テーブルを作る
- それを検索する
- 使用時に検索結果をカウントする
というだけですが、そこに至るまでの流れと工数は私の未熟さと仕様の煩雑さが相まって結構な物になってしまいました。ですが、問題解決を行うという点では非常にエンジニア冥利に尽きる仕事でもありました。問題はまだあるので仕事が終わったわけではないですが。
以上長々と失礼いたしました。
現在Alpha Architectはエンジニアを募集中です。いつでもお待ちしております。こちらからどうぞ。