Google 広告スクリプト→BigQuery→Tableauで広告レポート分析環境を構築する

Google 広告スクリプトでBigQueryにデータを連携し、Tableauで可視化。そんなレポート分析環境を構築してみましょう。

データのリポジトリとしてGoogleスプレッドシートはとても使いやすく、柔軟性も高いですし、Google 広告スクリプトとの親和性も当然高いので、使い勝手はいいのですが、TableauのようなBIツールでそのデータを使いたい、となるとやや不向きだったりします（いずれTableauでも連携対応するような気もしますが、今はまだ未対応です）。

ということで、Googe広告のレポートデータを最終的にTableauで分析できるようにするために、中間的にデータの置き場所をGoogle BigQueryにする、という環境を作りました。Tableauの標準機能でBigQueryのデータは選べるようになっていますので、BigQueryにさえデータがあればすぐに分析ができます。

連携イメージ

こんなイメージです。

Google 広告スクリプトのサンプルコード

早速いきましょう！Google 広告スクリプトのサンプルコードはこちらです。サーチクエリーレポートを取り込みました。指定さえすれば基本的にはどんなレポートでも取得できます（レポートの指定は64-67行目）。

// グローバル変数部分
var projectId = 'ここにBigQueryのprojectIdを入れてください';
var datasetId = 'ここにBigQueryのdatasetIdを入れてください';
var tableId = 'ここにBigQueryのtableIdを入れてください';


// 実行用main関数。テーブル作成が必要でない場合はcreateTableをコメントアウト
function main() {
createTable()
getreportjsoninsert()
}


// テーブル自動作成用関数
function createTable() {
  var table = BigQuery.newTable();
  var schema = BigQuery.newTableSchema();

  // BQスキーマの定義。レポートのフィールドをここで作ってください
  var queryFieldSchema = BigQuery.newTableFieldSchema();
  queryFieldSchema.description = 'クエリー';
  queryFieldSchema.name = 'query';
  queryFieldSchema.type = 'STRING';

  var keywordFieldSchema = BigQuery.newTableFieldSchema();
  keywordFieldSchema.description = 'キーワード';
  keywordFieldSchema.name = 'keyword';
  keywordFieldSchema.type = 'STRING';  

  var matchtypeFieldSchema = BigQuery.newTableFieldSchema();
  matchtypeFieldSchema.description = 'マッチタイプ';
  matchtypeFieldSchema.name = 'matchtype';
  matchtypeFieldSchema.type = 'STRING'; 

  var impressionsFieldSchema = BigQuery.newTableFieldSchema();
  impressionsFieldSchema.description = 'インプレッション';
  impressionsFieldSchema.name = 'impressions';
  impressionsFieldSchema.type = 'INTEGER';
  
  var clicksFieldSchema = BigQuery.newTableFieldSchema();
  clicksFieldSchema.description = 'クリック';
  clicksFieldSchema.name = 'clicks';
  clicksFieldSchema.type = 'INTEGER';
  
  var costFieldSchema = BigQuery.newTableFieldSchema();
  costFieldSchema.description = 'コスト';
  costFieldSchema.name = 'cost';
  costFieldSchema.type = 'FLOAT';

  schema.fields = [
queryFieldSchema, keywordFieldSchema, matchtypeFieldSchema, impressionsFieldSchema, clicksFieldSchema, costFieldSchema
  ];

  table.schema = schema;
  table.id = tableId;
  table.friendlyName = 'searchqueryreport';

  table.tableReference = BigQuery.newTableReference();
  table.tableReference.datasetId = datasetId;
  table.tableReference.projectId = projectId;
  table.tableReference.tableId = tableId;

  //テーブルのインサート  
  table = BigQuery.Tables.insert(table, projectId, datasetId);

  Logger.log('Data table with ID = %s, Name = %s created.',
      table.id, table.friendlyName);
}


// レポート取得・JSON変換・BQインサート用関数
function getreportjsoninsert() {
  // レポート取得
  var report = AdWordsApp.report(
    'SELECT Query, KeywordTextMatchingQuery, MatchType, Impressions, Clicks, Cost ' +
    'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'DURING LAST_MONTH');
  
  var rows = report.rows();
  var insertAllRequest = BigQuery.newTableDataInsertAllRequest();
  insertAllRequest.rows = [];
  insertid_index = 0;

  // JSON変換
  while (rows.hasNext()) {
  var row = rows.next();
  insertid_index++;
  var bqrow = BigQuery.newTableDataInsertAllRequestRows();
  bqrow.insertId = insertid_index;
  bqrow.json = {
    'query': row['Query'],
    'keyword': row['KeywordTextMatchingQuery'],
    'matchtype': row['MatchType'],
    'impressions': row['Impressions'],
    'clicks': row['Clicks'],
    'cost': row['Cost'],
  };
  insertAllRequest.rows.push(bqrow);
}
  Logger.log(insertAllRequest);   

  // データのストリーミングインサート  
  BigQuery.Tabledata.insertAll(insertAllRequest, projectId, datasetId, tableId);  
}

処理の内容

1. createTable()関数が自動でテーブルを作成
2. getreportjsoninsert()関数は、まずAdWordsレポートを取得
3. その後、データをJSON形式に変換
4. できあがったJSONデータをBigQueryにストリーミングインサート

準備と注意点

1. 予めGoogle BigQueryのプロジェクトとデータセットは作成してください。1,2行目はそのIDを記入します
2. テーブルは関数を使って毎回作っても（この場合は3行目に任意のテーブル名を入れてください）、予めあるものにデータを流し込んでもいいです。後者の場合はcreateTable()関数はコメントアウトしておいてください
3. Google 広告スクリプトで取得できるレポートデータは10000行までの制限があります。本スクリプトでは制限チェックなどの迂回作は含んでいませんので、このまま使う場合は10000行に収まる量のレポートを指定してください。
4. 非常に見落としがちな点です。Google 広告スクリプトのスクリプトの中から、必ずBigQuery APIを「オン」にすることをお忘れなく！標準ではオフなのでよくある失敗です！【やり方】「API（上級）」をクリック→BigQueryにチェック→下にあるGoogle Developer Consoleのリンクをクリック→BigQuery APIをクリック→「APIを有効にする」をクリック

では実行してみましょう！

BigQueryにサーチクエリーレポートのデータが入りました！

ここまでくればあとは簡単です。Tableauを開いて、接続→その他サーバー→Google BigQueryを選択し、Googleアカウントでログインしてください。

ログインすると、Google BigQueryに入っているプロジェクト、データセット、テーブル（Tableauでは表）をプルダウンで選ぶことができます。

あとはTableauの得意なさまざまな集計やビジュアリゼーションにかけるだけです。ここでは簡単ですが、クエリー、キーワードのセットでクリックの多さをツリーマップで一目で見れるようにしました。

いかがでしたでしょうか？一旦BigQueryにデータを流し込んでしまえばとても簡単にTableauでGoogle 広告のレポートデータを活用できることがご理解いただけたかと思います。Google 広告スクリプト側で対応すれば、どのレポートでも、また、タイマーを設定すれば、例えば毎日自動的にBigQueryに流し込んでおくこともでき、好きなときにTableauで分析ができます。ぜひGoogle 広告スクリプトデータを詳細に分析するBI環境を構築してみてはいかがでしょうか？

Special thanks：スクリプト作成の上でアタラ開発部の緒方さんにご指南をいただきました（特にJSON変換の部分）。緒方さん、ありがとうございました！

記事関連キーワード
• ♯API
• ♯広告レポート