概要
昨今データの利活用はますます盛んになり、活用事例は枚挙に暇がありません。
例えばですが、
・顧客の属性を詳細に分析することで、確度の高い見込み顧客に営業リソースを集中させることができるようになった
・複数の広告サービスのデータを比較分析することで最も効果の高いサービスに広告コストを集中することができるようになったといった
一方で、データ分析基盤の構築には、データエンジニアリングの知識が必要不可欠であるほか、基盤を構築できたとしても、データに更新が発生する度に人の手で分析基盤を最新の状態に維持していくという体制はとてもコストがかかります。
今回はデータエンジニアリングの知識に乏しい企業や部署がデータ分析基盤を構築するケースを想定し、データ分析基盤構築サービスtrocco®を使い、広告データの分析基盤を構築してみます。
なおETLツールという名称は
E：Extract(抽出)
T：Transform(変換)
L：Load(格納）
の頭文字に由来し、自社に散逸する多種多様なデータを抽出、加工し、DWH（データウェアハウス）へ統合することでデータ分析基盤の構築をサポートするツールです。
今回はFacebook広告、Google広告、Yahoo広告の３つの広告データの分析を行うケースを考えます。
通常このようにいくつかの広告サービスのデータを分析する際は
- 各広告ツールの管理画面に入りレポートをダウンロード（またはコピー）
- エクセル（またはGoogle Spreadsheetsなど）に貼り付け
- ツールのレポート機能でデータを可視化（グラフ化）する
- この作業を、利用している広告サービスの数だけ行い、日次や週次で定期的に更新
といった手順になるでしょうか。
この方法では広告側のデータが更新が発生するたびに人手でレポートをアップデートする必要があります。利用している広告サービスが増えれば増えるほど、単純作業の工数も増えます。
また、それぞれの広告データは、それぞれのレポート機能によって整理されるため、広告効果を比較するのも困難です。
今回利用するtrocco®は、ほとんどコーディング作業をすることなく各データをDWHへ統合することができるほか、定期的なデータの転送を自動で行う「スケジュール機能」や、いくつかの転送をひとまとめに管理できる「ワークフロー機能」などデータ分析基盤の構築だけでなく運用をトータルにサポートする機能を多数備えています。
それらの機能を利用しながら、Google広告、Facebook広告、Yahoo広告の3つの広告データをGoogle BigQueryというDWHへと統合し、以後は自動的にデータの更新が行われるというデータ分析基盤を構築します。
最後にGoogle BigQueryへ統合したそれらのデータを元にして、TableauというBIツールで広告データの可視化（グラフ化）を行い、データをひと目で理解できるダッシュボードを作成します。
なお、trocco®は、これらの広告サービスのほか、様々CRM・DBなどのデータソースの転送に対応しています。
大まかな流れ
具体的な転送の流れは
- Facebook広告のデータを転送
- Google広告のデータを転送
- Yahoo広告のデータを転送
- trocco®のデータマート機能を利用して、各テーブルに格納されたデータを整形、抽出
- Tableauに移動し、データマート機能で抽出したGoogle BigQueryのテーブルと接続
- Tableau上で広告レポートを作成
- trocco®とTableauでレポートの自動更新を設定
このようになります。
以下順番に行っていきます。またデータ転送の手順は割愛していますが、別途その工程を取り上げた記事があるのでそちらを参考にしてください。
事前準備
はじめに各サービスのデータ、trocco®のアカウントが必要になります。
trocco®は無料トライアルを実施しているので、事前に申し込みをしておきましょう。
https://trocco.io/lp/index.html
trocco®での転送設定（Facebook広告)
※Facebook広告の転送手順についてはこちらをご参考ください。
転送元と転送先を選択し、画面の表示に従って設定を行っていきます。
設定にしたがい、プレビューが作成されました。
取得された情報の中には今回の分析には必要ない情報も含まれていますが、後のデータマート定義の工程で必要な情報のみを抽出するので、ひとまずこのまま設定を保存して転送を実行します。
trocco®での転送設定（Google広告)
※Google広告の転送手順についてはこちらをご参考ください。
Facebook広告と同様に設定を進め、プレビューが取得できました。
こちらも必要な情報は後のデータマート定義の工程で抽出するためこのまま設定を保存します。
trocco®での転送設定（Yahoo!広告)
※Yahoo!広告の転送手順についてついてはこちらをご参考ください。
Facebook広告、Google広告と同様に設定を進め、プレビューが取得できました。
こちらも必要な情報は後のデータマート定義の工程で抽出するためこのまま設定を保存します。
Google BigQueryの設定
ここでこれまで作成した3つの転送設定について、取得できる分のデータをすべて転送します。
広告データの取得を完全に自動化するにはレポートデータの取得期間についても自動で最新のデータを追従して取得するよう設定するべきですが、それは後述のカスタム変数機能を用いて行うため、一旦そのままで次に進んで構いません。
またtrocco®できちんと設定ができていればGoogle BigQuery側でユーザーが何か操作をする必要はありません。ためしにFacebook広告のデータを転送したテーブルのプレビュー画面を確認するとtrocco®のプレビュー通りにデータが転送されていることが確認できます。
先述のとおり、これらのデータには広告効果の分析に不要なデータがいくつか含まれています。
trocco®は取得したデータのうち、分析に必要なデータだけを指定して転送するよう設定することも可能ですが、一部のデータだけがDWHへ統合されず社内に散逸したままとなり、部署あるいは個人単位でのデータの属人化が解消されません。
逆に不要なデータを抱えたままデータ分析を行うと各ツールのパフォーマンスに影響します。
そこで、trocco®のデータマート定義機能を利用し、このローデータ（分析のための加工・変換を行う前のデータのこと）はそのままに、分析に必要なデータだけを抽出して別のテーブルへ転送、分析用のデータマートを作成します。
データマート定義の設定
少し話が逸れますが、既に何度か登場しているDWHという言葉がデータのWareHouse = 倉庫を意味するのに対し、特定の目的のために大元のデータから必要なデータだけを抽出・加工したものをデータマート（Mart = 小売店）といいます。
あえてデータマートを用意する利点は、「データの利用者が目的に合わせて使いやすい形にデータを加工しても元のデータに影響がない」「必要なデータにのみクエリ（データ検索）を走らせることで運用コストを下げパフォーマンスを向上させる」といったものが挙げられます。
今回の例でいえば、分析に不要なデータを除けるほか、複数のサービスを横断的に分析するにあたって必要となる“カラム名の統一”に元のデータが影響されないという点がメリットです。
（例えばFacebook広告では一日あたりのコストが「spend」というカラム名なのに対し、Google広告とYahoo広告では「cost」というカラム名となっているため、分析に先立ってどちらかのカラム名に統一する必要があります。）
それでは再びtrocco®のトップ画面に戻り、「データマート定義の新規作成」をクリックします。
使用するDWHサービスを選択します。
今回は大元となるローデータをGoogle BigQueryに転送したため、データマートもGoogle BigQuery上に作成します。
画面の表示に従ってGoogle BigQueryへの接続設定や、クエリを記述します。
クエリの記述はデータエンジニアリングの知見に乏しい人には少し抵抗を覚えるかもしれませんが、trocco®にはクエリの実行に「データ転送モード」と「自由記述モード」の２種類のモードを選択でき、「データ転送モード」はデータの加工はできないものの、このようにごく簡単なクエリのみでデータの抽出が行なえます。
またプレビュー機能も備わっているため、クエリに不備があればその場で確認・修正が可能です。
既にデータを扱った経験があり、クエリの記述にも抵抗がなければ、「自由記述モード」でデータの抽出に合わせてデータの加工など自由に行えます。
今回は「自由記述モード」を利用してデータの抽出と同時にカラム名を統一して並び替えたほか、Google広告のデータおいて広告を表示したデバイスを区切るカラムを除去して一日ごとの広告効果を統一するなどの加工を行いました。
クエリの記述やデータの転送先テーブルの指定などができたら設定を保存し、データマート定義の詳細画面に移動します。
そのまま右上の「シンク」を押し、転送を実行します。
実行が終了しました。データマートの作成先としたGoogle BigQueryのテーブルを見てみるとクエリ通りにデータの抽出と加工が行われ、広告サービスが混在しながらも共通のカラムにデータが格納されています。
Tableauでの可視化
続いてTableuを利用して可視化を行います。まずはトップ画面右上の「新規」から新しいワークブックを作成します。
自動的にデータの接続先を選ぶ画面になるので、コネクタからGoogle BigQueryを選択します。
（ここでGoogleアカウント認証を用いた連携設定を行います。）
画面左側でデータを転送したプロジェクト、データセットを選び、読み込まれたテーブルをワークブックに追加します。
新しいシートの左側には先程作成したデータマートからから読み込まれたカラムの名前がフィールドとして表示されています。これらを行と列に配置し、右上の表示設定から任意のグラフを選択することで簡単にグラフを作成することが可能です。
このような数年に跨るマクロなレポートもいいですが、今回は「過去4〜12月までの広告効果を概観できるレポート」を作成してみます。
まずは毎日確認するものではないですが直近一年分の推移を確認するレポートを作成してみました。
この分析に使用しているのはあくまでサンプルのデータなので現実的ではありませんが、Facebook広告はimpressions数に対してconversions数があまりにも少ないです。Google広告はここ数ヶ月で急にcostが伸びていますがconversions数はそれに比例して伸びているようではないのでcpaは悪化しているようです。
次に直近30日分のデータを確認するためのレポートを作成しました。（破線はTableauの機能を利用した予測線）
↑の２つのレポートはTableauのフィルター機能を利用し、現在の日付を基準にした相対日付でフィルターをかけることで表示する期間をいちいち変更することなく最新のレポートが取得できます。
またここでレポートの自動更新設定を行っておきます。最後にTableauでの分析に至るまでのデータ転送、データマートの作成を自動化します。
レポートの自動化
trocco®のワークフロー機能、カスタム変数機能を用いてこの一連の流れをすべて自動化します。
trocco®のトップページに戻り、「ワークフロー定義」をクリックします。
作成したワークフローが一覧で表示されますので、右上の「新規ワークフロー作成」をクリックします。
ワークフロー機能は複数の転送ジョブをひとまとめにして管理・実行する機能です。
後の工程で定める流れに沿ってワークフロージョブが実行されますが、途中のジョブで何かしらのエラーが起きてしまったケースなどを考え設定を行います。
設定が済んだらフロー編集画面に進みましょう。
フロー編集画面ではこの緑枠に囲まれた各項目をフローチャートに配置することでワークフローを組み立てることができます。
これまでの流れに沿って、まずは「trocco転送ジョブ」から３つの広告データの転送をフローチャートに配置します。
フローチャートに３つの転送ジョブが配置されました。ワークフローは左端の「START」からスタートし、曲線で繋がれた順番で各転送ジョブを行っていきます。まずはシンプルにジョブを順番につないでみましょう。
このようにドラッグアンドドロップで簡単に３つのジョブを順番に実行するフローが作成できました。
またジョブを並列につなげることで複数のジョブを同時並行で実行するというフローも作成できます。
続いてデータマート定義のジョブを配置します。
今回はこれで終わりですが、必要に応じてその他の機能を配置します。それぞれの機能の紹介を簡単に書いておきます。
- Tableauデータ抽出
- Tableauの機能を利用してデータをエクスポートします。
- BigQueryデータチェック
- 事前に設定したアラート基準に従ってデータを監視し、その条件を満たしたときに転送ジョブをエラーとして終了させます。
- Slack通知
- エラーの発生を特定のSlackチャンネルに通知します。
フローの編集を保存し、ワークフローの詳細画面からスケジュール設定を行います。
これで毎日０時に広告データの転送が行われ、各広告データのうち分析に必要なデータだけが抽出・加工され、Tableauと接続した分析用のテーブルに格納されるという流れを作ることができました。
ただしこのままでは各転送ジョブでデータの取得期間が固定されているため、何度実行しても同じ期間のデータのみが取得されます。最新のデータだけを取得するために、毎日データの取得期間の設定を一日ずつ人の手でずらし続けるというのでは自動化が達成されないため、trocco®のカスタム変数機能を利用します。
既に作成した広告データの転送ジョブのひとつを編集し、カスタム変数の設定を行います。
trocco®には転送ジョブの実行時点を基準に日時のカスタム変数を作成する機能があります。これを利用してこのように「一日前」と「今日」を返すカスタム変数を作成します。
最後に作成したカスタム変数をデータの取得期間の項目に埋め込みます。
このように日時に関するカスタム変数を埋め込むことで、先程のワークフロー上でこのジョブが実行された時点を基準に一日前のデータを自動的に取得するという設定ができました。
同様に残りの広告データの転送ジョブ、データマート定義にもカスタム変数の設定を行い、一日前のデータのみを取得、抽出することができるようになります。
まとめ
いかがでしたでしょうか。今回は複数の広告データをGoogle BigQueryへ統合、分析に必要なデータだけをデータマート機能を利用して抽出、Tableauで可視化という流れをほぼコーディングなし作ってみました。
このようにtrocco®を使うことで、複数サービスに跨り、かつ膨大なデータでも自動でDWHへと統合することができるほか、100万規模の大きなデータからごく小さなデータまで複雑なコーディングをせずtrocco®の画面上の設定のみでデータの転送が可能です。
実際に弊社サービスのtrocco®においても、マーケティングKPI等をこのような流れで収集・分析しています。
データ分析の際にはぜひご活用ください。
実際にデータ統合を試してみたい場合は、クレジットカード不要のフリープランや無料の資料請求を実施しているので、この機会にぜひご検討ください。
