AdWords Script→BigQuery→Tableauでレポート分析環境を構築する

こんにちは。週末プログラマーの杉原です。AdWords ScriptとGoogleスプレッドシートを連携させたスクリプトはこれまでも何度か取り上げてきました。

 

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

 

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

 

こんなイメージです。

ss55

 

早速いきましょう!AdWords Scriptのサンプルコードはこちらです。サーチクエリーレポートを取り込みました。指定さえすれば基本的にはどんなレポートでも取得できます(レポートの指定は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. AdWords Scriptで取得できるレポートデータは10000行までの制限があります。本スクリプトでは制限チェックなどの迂回作は含んでいませんので、このまま使う場合は10000行に収まる量のレポートを指定してください。
  4. 非常に見落としがちな点です。AdWords Scriptのスクリプトの中から、必ずBigQuery APIを「オン」にすることをお忘れなく!標準ではオフなのでよくある失敗です!【やり方】「API(上級)」をクリック→BigQueryにチェック→下にあるGoogle Developer Consoleのリンクをクリック→BigQuery APIをクリック→「APIを有効にする」をクリック

 

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

 

ss56

 

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

 

ss58

 

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

 

screenshot37

 

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

 

screenshot39

 

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

 

screenshot41

 

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

 

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

著者
Tags ,

Related posts

*

Top