Yahoo!広告のデータをAmazon Redshiftに自動同期し、Lookerで可視化する方法

概要

Yahoo!広告はGoogle広告と同様ユーザーからの検索に対して広告を表示できるサービスです。Yahoo!は30代以降のビジネスパーソンからの支持がいまなお根強いサービスです。したがってYahoo!広告も10代〜20代の若年層向けのtoC広告ではなくtoB向けの広告に向いています。

このような広告サービスの最適化にはYahoo!広告のデータを分析することが重要です。またYahoo!広告だけでなくGoogle広告やTwitter広告といった他の広告サービスのデータと組み合わせた分析や、データの可視化に特化したBIツールでのデータ分析はさらに有効ですが、このようなデータ分析は少なからずデータエンジニアリングの知識を必要とするデータ分析基盤の構築が必須です。

そこで今回は、trocco®というデータ分析基盤構築支援サービスを使ってYahoo!広告のデータをAmazon RedshiftというDWH（データウェアハウス）へ統合し、LookerというBIツールでデータの可視化を行います。

ゴール

このようなYahoo!広告のレポートデータから

このようなダッシュボードの完成を目指します（作成後は自動でデータが最新値に更新されるようにします）

こんな人におすすめ

• Yahoo!広告のレポートデータを分析基盤に取り込みたい方
• レポートデータをcsvファイルにエクスポートして分析基盤に取り込むという作業が面倒な方
• データ取得の作業に疲れている方

1. DWHと同期する手段の選定

1-1. DWHの選定

まずはデータを統合するDWHを選定します。

• Amazon Redshift
• Google BigQuery
• Snowflake
• MySQL
• PostgreSQL など

trocco®はいずれのサービスとも連携が可能ですが、今回はAmazon Redshiftへのデータ統合を行います。

1-2.転送手段の選定

データの集約先がAmazon Redshiftに決まったので、次は転送するための手段を以下の4つから選びます。
1. レポートデータをcsvエクスポートし、手動でAmazon Redshiftにアップロードする。
2. Yahoo!広告とAmazon Redshiftの各APIを用いて、自分でプログラムを書いて連携させる。
3. Embulkを利用し、自分で環境を構築する。
4. trocco®を利用し、画面上で設定する。

1 は単発の実行なら特に問題はありませんが、定期的に取り込む必要がある場合は非効率な作業になります。
2はAPIのキャッチアップ工数・プログラムを書く工数・環境構築工数が発生する他、エラー対応などの運用工数も継続的に発生します。
3も2と同じく、Embulkはある程度の専門知識が必要になり、自分で環境構築・運用を行う手間が発生します。さらに、エラーの内容が専門的で、詰まると大幅に時間を浪費してしまいます。

そこで、今回はEmbulkの課題も解決してくれて、プログラムを書かずに画面上の設定のみで作業が完結する、4のtrocco®というSaaSを利用します。

2. trocco®でYahoo!広告 → Amazon Redshiftの転送自動化

2-0. 事前準備

ここからの作業には、trocco®のアカウントとYahoo!広告を運用しているYahoo!アカウントが必要になります。
trocco®は無料トライアルも実施しているので、申し込み・登録をしておきましょう。

https://trocco.io/lp/index.html
（申し込みの際にこちらの記事を見たという旨を記載して頂ければスムーズにご案内することができます）

2-1. 転送元・転送先を決定

trocco®にアクセスし、「ダッシュボード」にある「転送設定を作成」ボタンを押します。

転送元にYahoo!広告、転送先にAmazon Redshiftを選択し、「この内容で作成」ボタンを押します。

転送設定画面になるので、転送に必要な情報を入力していきます。

2-2. Yahoo!広告との連携設定

trocco®には社内のユーザー間でチームを作成し、チームで転送設定を共有するチーム機能があります。チームの他メンバーにも設定がわかりやすく伝わるよう、転送設定の名前と設定内容等のメモを入力します。

転送設定の名前を決めたら、「転送元の設定」内の「接続情報を追加」ボタンを押し、Yahoo!広告の接続情報の設定を行います。

「Yahooアカウント認証」のボタンを押して、データを取得したいYahoo!広告のアカウントで認証を行います。

認証が完了すると「アクセストークンの発行日時」の欄が自動で埋まります。管理しやすいようにこの接続に名前を入力して接続設定を保存します。

再度転送設定画面に戻り、「接続情報を読み込む」ボタンを押すと、作成した接続情報が選択できるようになります。

これでYahoo!広告との連携は完了です。

2-3. Yahoo!広告からのデータ抽出設定

Yahoo!広告からどのようなデータを取得するか設定していきます。

まずは必須項目の「アカウントID」「レポート種別」をセレクトボックスの中から選択します。

次に「データ取得期間」の設定です。
取得期間を転送のたびに設定し直すのは手間がかかるので、ここではカスタム変数を使います。
このように変数を設定することで、「ジョブ実行時から１日前のデータを取得する」というような設定が可能です。

次に、転送したいパラメータを「カラム」に設定します。
データ型をどれに設定するか分からない場合は、すぐ下の「ドキュメント」のリンクから確認することができます。

以上でYahoo!広告側の設定は完了です。
次は転送先のAmazon Redshiftの設定を行っていきましょう。

2-4. 転送先Amazon Redshiftへの転送設定

基本的には転送元と同じ要領です。
「接続情報を追加」ボタンからAmazon Redshiftの接続設定を行い、データベース名、スキーマ、テーブルを入力します。

S3パケット・S3キープレフィックス・転送モードも必須項目なので、これらも入力します。

最後に、「この内容で接続を確認」ボタンを押して、接続確認が問題なく通るか確認します。

これで入力は完了です。「保存して自動データ設定・プレビューへ」ボタンを押して、確認作業に進みましょう。

2-5. データのプレビュー

Yahoo!広告からどのようにデータが取得されるかプレビューで確認することが出来ます。取得したいデータが表示されていなければ再度設定画面に戻って転送設定をやり直します。

2-6. スケジュール設定

「スケジュール・トリガー設定」タブを開きます。

以下のように実行スケジュールを設定することで、転送を自動化することができます。

2-7. 通知設定

必須の設定ではないですが、通知タイプ・通知条件・通知先を選択し設定を保存することで、EmailやSlackに通知を行うことができます。

画像のように、通常は通知をしないで転送が失敗した際にのみ通知をするなどの設定が可能です。

また必要な時のみ実行したい場合は、手動で転送を実行することも出来ます。
今回は手動で実行し、転送設定詳細の「実行」ボタンを押して進めていきます。

転送が成功しました。

3. 転送先Amazon Redshiftの設定

Amazon Redshift側で特に設定することはありませんが、Amazon Redshiftのコンソール画面からプレビューを表示してみるとたしかにデータが転送されていることが確認できます。

4. Lookerで可視化

4-1. Amazon RedshiftとLookerの接続設定

ここからAmazon Redshiftに集約されたデータを可視化していきます。
Lookerの画面から、「管理 > Database > Connections」を開きます。

すると接続しているデータベース一覧が表示されています。
この画面で「Add Connection > Database Connection」を開き、「Connection Setting」に接続するデータベース情報を入力します。

これで、Amazon RedshiftとLookerの接続設定が完了です。

4-2. LookMLプロジェクトの作成

次はデータを可視化するのに必要なLookMLプロジェクトを作成していきます。
「開発 > LookML プロジェクトの管理」を開きます。

「New LookML Project」を押して、新しいLookMLプロジェクトを作成します。

これで、LookMLプロジェクトを作成できました。

4-3. モデルとビューの定義

表示されたエディタで、モデルとビューを定義します。
後々必要になるので、モデルにデータベース接続とその接続を使用するExploreを定義します。
(書き方はLookerの公式ドキュメントを参考にしてください)

これでグラフを作成する準備ができました。

4-4. グラフの作成

トップページに戻り、「New > Dashboard」を押して、ダッシュボードを作成していきます。

このように白紙のダッシュボードが作成されました。
試しに、日毎のクリック数を可視化してみます。
「Dashboardの編集」を押し、「タイルの追加」を押し、先ほどのモデルで定義したExploreを選択します。

「DIMENSIONS」に横軸にしたいデータ、「MEASURES」に縦軸にしたいデータを選択し、「実行」を押すことで、グラフを作成することができます。今回であれば、横軸に日付、縦軸にクリック数を選択することで、日毎のクリック数のグラフを作成し、可視化することができました。

これで一つグラフが完成しました。このような感じで他のグラフも作成すると、今回のゴールであるYahoo!広告のレポートデータダッシュボードが完成します。

まとめ

いかがでしたでしょうか。trocco®を使うと管理画面を触ることなく、簡単にYahoo!広告のレポートデータを取得し、DWH（今回はAmazon Redshift）に統合し、連携したLookerでレポートを作成することが出来ました。

trocco®は、ETL/データ転送・データマート生成・ジョブ管理・データガバナンスなどのデータエンジニアリング領域をカバーした、分析基盤構築・運用の支援SaaSです。trocco®について詳しく知りたいという方は、以下より資料をご請求ください。