Google Adwords Account Performance Daily Dump

google_adwords_to_td.js

CONSTANTS = {
  tdAPIKey: 'YOUR_TD_API_KEY_HERE',
  databaseName: 'adwords_reports',
  tableName: 'sample_report',
  timeColName: "Date",
  timeLowerBound: Date.parse(new Date())/1000 - 7*86400,
  timeUpperBound: Date.parse(new Date())/1000 + 3*86400
}

function validateDateColumn(o) {
  var t = o[CONSTANTS.timeColName];
  if (!((typeof t) === 'string' && /^\d\d\d\d-\d\d-\d\d$/.test(t))) {
    throw Error('bad input with ' + t.toString());
  }
  var parsed_t = Date.parse(Date.apply(this, t.split('-')))/1000;
  if (parsed_t < CONSTANTS.timeLowerBound || parsed_t > CONSTANTS.timeUpperBound) {
      throw new RangeError("time field = " + parsed_t.toString() + " is out of range");
  }
  o['time'] = parsed_t;
  delete o[CONSTANTS.timeColName];
  return o;
}

function postTreasureData(events, database, table, apikey) {
  var data = {};
  data[database+"."+table] = events;
  var payload = JSON.stringify(data);
  Logger.log(payload);
  var options = {
    "method": "POST",
    "contentType" : "application/json",
    "headers" : {
      "X-TD-Write-Key": apikey,
      "X-TD-Data-Type": "k"
    },
    "payload": payload
  };
  var response = UrlFetchApp.fetch("http://in.treasuredata.com/js/v3/event/", options);
  Logger.log(response);
}

function row2hash(row) {
  // AdWords report row returns some functions with it. This turns into a normal array.
  var h = {};
  for (var k in row) {
    if (typeof row[k] === 'function') { continue; }
    h[k] = row[k];
  }
  return h;
}

function main() {
  // Dump Account Report
  var report = AdWordsApp.report('SELECT Clicks, Cost, Ctr, Date, Conversions FROM ACCOUNT_PERFORMANCE_REPORT DURING YESTERDAY');
  var rows = report.rows();
  var events = [];
  while (rows.hasNext()) {
    var rowHash = row2hash(rows.next());
    rowHash = validateDateColumn(rowHash);
    events.push(rowHash);
  }
  postTreasureData(events, CONSTANTS.databaseName, CONSTANTS.tableName, CONSTANTS.tdAPIKey);
}