概要

Google広告は広告を配信するターゲットや一日あたりの予算、形式などを自由に設定できるカスタマイズ性、少額からでもスタートできる料金体系、なによりGoogle検索という多くの人の目に触れる場に広告を出稿できる点が特徴の広告サービスです。このような広告サービスを利用した広告戦略では取得されたデータを活用してクリック数やインプレッション数(閲覧数)を分析し、PDCAを回していくのが理想的です。しかしGoogle広告標準のレポート機能を超えた高度な分析には、それに先立って膨大な量の広告データをDWH(データウェアハウス)へと統合し、連携したBIツールでデータの可視化を行うというデータ分析基盤の構築が必要になります。

(DWHについてはこちらをご覧ください。)

そこで今回はtrocco®というデータ分析基盤構築支援サービスを利用し、Google広告のデータをDWH(Amazon Redshift)に統合し、LookerというBIツールを用いてデータを可視化するまでの方法をご紹介します。

なおtrocco®はGoogle広告のほか、様々な広告サービス、データベースと連携しており、様々な組み合わせでのデータ統合が可能です。

Google広告のデータをAmazon Redshiftへ転送

ゴール

Google広告から取得された下のようなレポートデータを

貼り付けた画像_2020_11_02_16_36.png

このようにLooker上で可視化するまでを30分程度でやっていきます(作成後は最新のデータが自動で同期されるようにします。)

貼り付けた画像_2020_11_02_16_31.png

こんな人におすすめ

  • Google広告のデータを分析基盤・DWHに取り込む予定の方
  • 広告レポートをcsvファイルにエクスポートして分析基盤に取り込むというルーチンワークを効率化、自動化してしまいたいと考えている方
  • データ分析の前準備に疲れてしまった方

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

1-1. DWHの選定

まずはデータをどこに集約するか、DWH(データウェアハウス)を選定します。

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

trocco®はいずれのDWHへもデータの統合が可能ですが、今回はAmazon Redshiftを利用することにします。

1-2.転送手段の選定

Amazon Redshiftにデータを集約することが決まったところで、次は転送するための手段を選びます。
1. パフォーマンスレポートをcsvデータ形式でエクスポートし、手動でAmazon Redshiftにアップロードする。
2. Google広告とAmazon Redshiftの各APIを、自前でプログラムを書いて連携させる。
3. Embulkを利用し、自前で環境を構築する
4. trocco®を利用し、画面上で設定する。
1. は単発の実行ならともかく、広告データに更新が発生するたびに手動でデータをアップロードするというのはタイムロスが多く、非効率な作業になってしまいそうです。
2. はAPIのキャッチアップ工数+プログラムを書く工数+環境構築工数が発生する他、エラー対応などの運用工数も継続的に発生します。
3. も2と同じくEmbulkはある程度の専門的なデータエンジニアリングの知識が必要になり、自前で環境構築・運用を行う手間が発生します。エラーの内容が専門的で、詰まると大幅に時間を浪費してしまいます。
そこで、Embulkの課題も解決してくれて、プログラムを書かずに画面上の設定で作業が完結する、4.trocco®というSaaSを利用します。

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

2-0. 事前準備

trocco®のアカウントおよびGoogle広告のアカウントが必要です。

https://trocco.io/lp/index.html

無料トライアルも実施しているので、事前に申し込みをしておきましょう。
(申し込みの際にこちらの記事を見たという旨を記載して頂ければスムーズにご案内できます)

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

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

image.png

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

image.png

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

2-2. Google広告との連携設定

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

image.png

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

貼り付けた画像_2020_11_02_9_51.png

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

image.png

認証が完了しました。「Googleアカウント」の欄が埋まったので、適当な名前を入力して設定を完了します。

貼り付けた画像_2020_11_06_9_56.png

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

image.png

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

これでGoogle広告との連携は完了です。続いてGoogle広告の取得データを設定します。

まずは必須項目となる「お客様ID」「リソースタイプ」をセレクトボックスの中から選択します。

貼り付けた画像_2020_11_06_13_03.png

続いて「フィールド」の設定です。レポートに含める項目(カラムといいます)を設定していきます。
クリック数・インプレッション数など、分析に必要な項目をピックアップします。

次に「データ取得期間」の設定です。
取得期間を転送のたびに設定し直すのは手間がかかるので、ここではカスタム変数を使います。

このように変数を設定することで、「1ヶ月前〜現在のデータを取得する」というような設定が可能です。

image.png

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

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

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

貼り付けた画像_2020_10_23_15_15.png

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

貼り付けた画像_2020_10_23_15_19.png

最後に、接続確認が問題なく通るか確認します。

image.png

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

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

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

貼り付けた画像_2020_11_02_11_23.png

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

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

貼り付けた画像_2020_11_06_9_56.png

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

貼り付けた画像_2020_11_02_11_26.png

2-7. 通知設定

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

貼り付けた画像_2020_11_02_11_27.png

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

貼り付けた画像_2020_11_02_16_41.png

転送が完了しました。

image.png

3. Amazon Redshiftの設定

Amazon Redshift側で特に操作することはありません。念の為Amazon Redshiftのコンソール画面からプレビューを確認してみるとたしかにデータが転送されていることが確認出来ます。

貼り付けた画像_2020_11_02_16_01.png

広告データがAmazon Redshiftに同期されていることが確認できました。

4. Lookerで可視化

それでは、これらのデータをLookerで分析してみます。

まずはAmazon RedshiftとLookerを接続します。
管理タブを開いて「Database」の「Connections」を開きます。

貼り付けた画像_2020_11_02_16_02.png

接続しているデータベース一覧が表示されています。ここで「Add Connection」→「Database Connection」から接続するデータベース情報を入力します。

貼り付けた画像_2020_11_02_16_06.png
貼り付けた画像_2020_11_02_16_06.png

Amazon Redshiftのデータベースに接続できたら、次はデータを可視化するために必要なLookMLプロジェクトを作成します。
開発タブを開いて「LookMLプロジェクトの管理」に移動します。

貼り付けた画像_2020_11_02_16_09.png

「New LookML Project」からLookMLプロジェクトを作成します。

貼り付けた画像_2020_11_02_15_51.png
image.png

「Create Project」を押したら、エディタでmodelとviewを定義します。
後々必要になるので、modelの中ではexploreを設定しておきましょう。
(書き方が分からない場合はLookerの公式ドキュメントを参照してください)

image.png
貼り付けた画像_2020_11_02_16_16.png

これで下準備が整いました。
トップページに戻って「New」からDashboardを作成します。

貼り付けた画像_2020_11_02_16_18.png

白紙のダッシュボードができました。ここに各種グラフを追加していきます。「新しいTILE」を押します。

貼り付けた画像_2020_11_02_16_24.png

先ほどのmodel内で定義したExploreを選択します。

貼り付けた画像_2020_11_02_16_25.png

DIMENSIONSとMEASURESを設定し、Tileに表示したいデータをプロットします。ここではキャンペーンごとのクリック数を分析しています。

貼り付けた画像_2020_11_02_16_27.png

これで一つTileが完成しました。この調子で他のTileも作成すると、今回のゴールであるGoogle広告の広告データダッシュボードが出来上がります。

貼り付けた画像_2020_11_02_16_29.png
貼り付けた画像_2020_11_02_16_31.png

まとめ

いかがでしたでしょうか。trocco®を利用することでGoogle広告の管理画面を触ることなく、簡単にデータを取得してDWH(今回はAmazon Redshift)に統合することが出来ました。
Amazon Redshiftにデータを貯めると、Amazon Redshiftの特徴であるデータの高速処理を活かして大きなデータもLooker上で可視化することが出来ます。

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


hirokazu.kobayashi

慶應義塾大学卒業後、2014年より株式会社リブセンスへ入社。データエンジニアとして同社分析基盤立ち上げをリードする。2017年より現職primeNumberに入社。自社プロダクト「systemN」におけるSpark/Redshift活用等のデータエンジニアリング業務を行うかたわら、データ統合業務における工数削減が課題だと感じ、データ統合を自動化するサービス「trocco®」を立ち上げる。