読者です 読者をやめる 読者になる 読者になる

sekaie engineers' blog

セカイエ株式会社が主催するエンジニア勉強会について

爽快!分析基盤の紹介 #1 : リスティング広告分析編

おはようございます。

大阪生活4ヶ月目を迎えて体重 10kg 増量しました。佐々木です。

セカイエのマーケティングチームに所属していて分析基盤の導入をすすめています。

分析基盤の機能やらノウハウやらを分析基盤紹介と題してブログ化していこうと思います。

よろしくお願いします。

はじめに

リスティング広告分析を紹介する前にある程度前提部分のお話をします。

まず、セカイエの分析基盤では Google BigQuery を DWH として利用しています。

今となっては BigQuery の説明は不要なくらい有名ですし利用されている企業も増えてますね!

DWH を BigQuery にして、フロントエンド周りを自作する方向で進めています。

リスティング広告分析について

本題ですが、セカイエのリスティング広告分析について紹介していきます。

セカイエのリスティング広告は主に Google Adwords, Yahoo の二種類の媒体に出しています。

リスティング広告分析機能ではこれら2つの媒体のレポートをWebページで見られるようにしました。

レポートページ

各媒体の集計したレポートデータはこのページから参照できます。

フォーム画面はこんな感じ f:id:sekaie:20151013165032p:plain

レポート画面はこんなかんじ f:id:sekaie:20151013165418p:plain

Web 画面でレポートが見れるようになるまでの全体の流れ

では、レポート画面でデータが見られるようになるまでの流れを図にしてみました

f:id:sekaie:20151013162308p:plain

① Report CSV (Yahoo) を Web Page にアップロードする

このバッチ全体の流れのトリガーとなるのが Yahoo Report CSV をアップロードしたタイミングです。

ここでいう CSV は Yahoo のレポートページからダウンロードしてきたそのままのCSVファイルです。

YahooのAPIを使用するにはそれなりに制限があって自分たちでは使えませんでした。

しょうがなく、ここの部分だけ手動にしています(非常に悔しい)。

② ①でアップロードされた CSVGoogle Cloud Storage にアップロードする

この段階ではまだリスティング広告分析用の Batch Server は起動していません。

WebServer と BatchServer の共有ディレクトリ置き場(バックアップ用も兼ねる)として Google Cloud Storage を利用していて

そこに WebServer からアップロードされたファイルをアップしています。

③ Batch 用 EC2 Instance を起動する

CSVを無事にアップロードできたならば WebServer が AWS API 経由でリスティング分析用 Batch Server を起動します。

④, ⑤ Google Adwords のレポートを取得する / アップロードされたCSVを取得する

Batch Server が起動すると集計用の複数のバッチスクリプトが動きます。

それぞれのスクリプトがBigQueryで読み込み可能な形にしてファイルが出力されていきます。

⑥ 整形したレポートデータを Google Cloud Storage へアップロードする

それぞれのスクリプトが終了したあとは Google Cloud Storage へファイルをアップロードします。

⑦ ⑥でアップロードしたファイルを BigQuery へロードする

Storage へアップロードが完了するとあとは BigQuery で Storage をロードすると BigQuery で閲覧できる状態になります。

バッチが一通り終了すると、リスティング広告専用バッチは自動にシャットダウンされます

コンバージョン対応

ここまでで一応 CSV をアップロードすると自動で集計されて Web Page で集計結果を閲覧することが可能になっています。

しかし広告のレポート用の対応としてコンバージョンの対応をして欲しいとのことでした。

というのも、広告を見たのは昨日でもその後のコンバージョンに達したのは二日後、一週間後みたいなのはよくあるらしく、取得した日によってコンバージョン数に差分が生じています。

レポートを取得するときは過去30日分まで遡り取得しています。ので今度はこれの対応を行います。

対応策① SQL で頑張る

導入当初はノウハウも少なく運用方法を変えるのは辛かったのでできるだけ簡単に済ませる方法を考えました。

カラムに import した time を追加しレポートページで発行された SQL で time を見て最新のデータを取得してくるみたいな方法を考えていました。

SELECT
time, date,
account_name,
....
FROM report AS origin
INNER JOIN (
  SELECT
    MAX(time) AS time
    date,
    account_name,
    ....
  FROM report
  GROUP BY date, account_name.....
) max
ON origin.time = max.time
AND origin.date = max.date
....

ただ、これは実装はすごく簡単なのですが中長期的に見るとデータ量が莫大に膨れ上がります。

前述した通り、30日分遡って import するので単純計算データが30倍になります。

BigQueryはデータ参照量に対して従量課金される仕組みなのでクエリが投げられるたびに30倍分の課金が生じてしまいます。

この方法は断念し、次の方法で対応しました。

対応策② Batch の最後に最新版に Update する

f:id:sekaie:20151013175413p:plain

まず、集計を行う前に

  • 前日分の backup
  • 集計用テーブル(tmp)

をレポートテーブルからコピーします。

続いて、各レポートのCSVを整形したデータは集計用テーブル(tmp) にロードしていきます。

この段階の tmp table は今までの対策①のレポートテーブルと同じ状態です。

更に今回の対策では tmp table に対して対策①で作成していたような SQL を実行し、再度レポートテーブルとして必要な行だけを抽出して report table へ上書きします。

これで report table は最新のレポートデータだけが入ったテーブルになります。

おわりに

今回は導入した分析基盤の機能の一つであるリスティング広告分析機能についての紹介と、導入の際に悩んだ問題の共有をしました。

次回は他の分析基盤の機能について紹介できればなと思います!

ほな!