AdWordsスクリプトからBigQueryにデータをアップロードする。

非エンジニアによるエンジニアリング

なんでやったのか

日頃利用しているGoogle広告の配信実績をBigQueryに食わせたいなと思い実装しました。

やりたいことは、各広告媒体からの数値を一つのデータベースにまとめて統合管理する。レポーティングなどについても全て自動化。うちにはこういうツールがあるんですが、これを自分の力で作りたいと思ったのがきっかけ。(世にはDatoramaというツールがあってコネクタ繋げるだけでできるのがありますが、これは一旦置いておく)

Datorama|デートラマでPDCAと意思決定を高速化

図にするとこんな感じ。

イメージフロー

ただ、問題なのは自分は非エンジニアでAPIとか蕁麻疹でそうになる。いずれはやりたいけども手始めに。ということでスクリプトで動かせるGoogle広告からはじめました。

本題

広告運用者前提なので、AdWordsスクリプトは知っている前提でお話します。かつBigQuery(GCP)についても、記事が一杯あるので調べてください!

BigQuery - Analytics Data Warehouse | BigQuery | Google Cloud

早速本題。これを参考に作ってます (9割これっす)

Export AdWords Reports into BigQuery - Single Account | AdWords scripts | Google Developers

異なるところは下記のみ。

• 取得する項目が、リスティングではなく、バナー広告。
• コンバージョン数値を取れるようにしている。
• 実数以外(CTRやCVRなど)は、SSやDataPortal上で算出するので項目削除。
• メールが送られてくる機能は削除。
• 取得日を指定する。※指定日～前日までとか。※オリジナルは下記のみ TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH

では実際のコード

/*
- GCPは導入し、BigQueryのAPIは有効にしておく。
- データセットの箱を作る。
- プロジェクトID、データセットIDをコピーしておく。
////////////////////////////////////////////////////////////////////
   ______                       ____  _                
  / ____/___  ____  _______  __/ / /_(_)___  ____ _    
 / /   / __ \/ __ \/ ___/ / / / / __/ / __ \/ __ `/    
/ /___/ /_/ / / / (__  ) /_/ / / /_/ / / / / /_/ /     
\____/\____/_/ /_/____/\__,_/_/\__/_/_/ /_/\__, /      
                                          /____/       
   __                     
  / /____  ____ _____ ___ 
 / __/ _ \/ __ `/ __ `__ \
/ /_/  __/ /_/ / / / / / /
\__/\___/\__,_/_/ /_/ /_/ 
                          
         ______                   __           __          
  ____ _/ __/ /____  _____   ____/ /___ ______/ /__        
 / __ `/ /_/ __/ _ \/ ___/  / __  / __ `/ ___/ //_/        
/ /_/ / __/ /_/  __/ /     / /_/ / /_/ / /  / ,<     _ _ _ 
\__,_/_/  \__/\___/_/      \__,_/\__,_/_/  /_/|_|   (_|_|_)                                                                                                                               
////////////////////////////////////////////////////////////////////*/

// Date
var yesterday = new Date();
var formatDate = function (date) {return '' + date.getFullYear() + ('00' + (date.getMonth() + 1)).slice(-2) + ('00' + date.getDate()).slice(-2);}

// GCPのプロジェクト、BIGQueryのデータ・セットを指定 
var CONFIG = {
  BIGQUERY_PROJECT_ID: 'XXXXXXXX',
  BIGQUERY_DATASET_ID: 'XXXXXXXX',

  
// データセット・テーブルの更新方法。 上書きor追加
  TRUNCATE_EXISTING_DATASET: false,   
  TRUNCATE_EXISTING_TABLES: true,

// ドライブにバックアップを取るかどうか
  WRITE_DATA_TO_DRIVE: false,
  // フォルダ名指定
  DRIVE_FOLDER: 'INSERT_FOLDER_NAME',
     
// レポート内容    
  REPORTS: [{NAME: 'ACCOUNT_PERFORMANCE_REPORT',
     CONDITIONS: '',
     FIELDS: {'Date' : 'STRING',
              'Cost' : 'FLOAT',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Conversions' : 'FLOAT',
             }
    },  {NAME: 'AD_PERFORMANCE_REPORT',
     CONDITIONS: '',
     FIELDS: {'Date' : 'STRING',
              'AdGroupName' : 'STRING',
              'Cost' : 'FLOAT',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Conversions' : 'FLOAT',
             }
    }],
};

// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
var MAX_INSERT_SIZE = 10 * 1024 * 1024 - 512 * 1024;




/*↓↓↓Main method↓↓↓*/
function main() {
  createDataset();
  for (var i = 0; i < CONFIG.REPORTS.length; i++) {
    var reportConfig = CONFIG.REPORTS[i];
    createTable(reportConfig);
  }

  var jobIds = processReports();
  waitTillJobsComplete(jobIds);
}


/* Creates a new dataset*/
function createDataset() {
   if (datasetExists()) {
    if (CONFIG.TRUNCATE_EXISTING_DATASET) {
      BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
        CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
      Logger.log('Truncated dataset.');
    } else {
      Logger.log('Dataset %s already exists.  Will not recreate.',
       CONFIG.BIGQUERY_DATASET_ID);
      return;
    }
  }

  // Create new dataset.
  var dataSet = BigQuery.newDataset();
  dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
  dataSet.datasetReference = BigQuery.newDatasetReference();
  dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
  dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;

  dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
  Logger.log('Created dataset with id %s.', dataSet.id);
}

/* Checks if dataset already exists in project*/
function datasetExists() {
  // Get a list of all datasets in project.
  var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
  var datasetExists = false;
  // Iterate through each dataset and check for an id match.
  if (datasets.datasets != null) {
    for (var i = 0; i < datasets.datasets.length; i++) {
      var dataset = datasets.datasets[i];
      if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
        datasetExists = true;
        break;
      }
    }
  }
  return datasetExists;
}

/* Creates a new table*/
function createTable(reportConfig) {
  if (tableExists(reportConfig.NAME)) {
    if (CONFIG.TRUNCATE_EXISTING_TABLES) {
      BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
          CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
      Logger.log('Truncated table %s.', reportConfig.NAME);
    } else {
      Logger.log('Table %s already exists.  Will not recreate.',
          reportConfig.NAME);
      return;
    }
  }

  // Create new table.
  var table = BigQuery.newTable();
  var schema = BigQuery.newTableSchema();
  var bigQueryFields = [];

  // Add each field to table schema.
  var fieldNames = Object.keys(reportConfig.FIELDS);
  for (var i = 0; i < fieldNames.length; i++) {
    var fieldName = fieldNames[i];
    var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
    bigQueryFieldSchema.description = fieldName;
    bigQueryFieldSchema.name = fieldName;
    bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName];

    bigQueryFields.push(bigQueryFieldSchema);
  }

  schema.fields = bigQueryFields;
  table.schema = schema;
  table.friendlyName = reportConfig.NAME;

  table.tableReference = BigQuery.newTableReference();
  table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
  table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
  table.tableReference.tableId = reportConfig.NAME;

  table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
      CONFIG.BIGQUERY_DATASET_ID);

  Logger.log('Created table with id %s.', table.id);
}

/*Checks if table already exists in dataset.*/
function tableExists(tableId) {
  // Get a list of all tables in the dataset.
  var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
      CONFIG.BIGQUERY_DATASET_ID);
  var tableExists = false;
  // Iterate through each table and check for an id match.
  if (tables.tables != null) {
    for (var i = 0; i < tables.tables.length; i++) {
      var table = tables.tables[i];
      if (table.tableReference.tableId == tableId) {
        tableExists = true;
        break;
      }
    }
  }
  return tableExists;
}

/* Process all configured reports*/
function processReports() {
  var jobIds = [];

  // Iterate over each report type.
  for (var i = 0; i < CONFIG.REPORTS.length; i++) {
    var reportConfig = CONFIG.REPORTS[i];
    Logger.log('Running report %s', reportConfig.NAME);
    // Get data as an array of CSV chunks.
    var csvData = retrieveAdwordsReport(reportConfig);

    // If configured, back up data.
    if (CONFIG.WRITE_DATA_TO_DRIVE) {
      var folder = getDriveFolder();
      for (var r = 0; r < csvData.length; r++) {
        var fileName = reportConfig.NAME + '_' + (r + 1);
        saveCompressedCsvFile(folder, fileName, csvData[r]);
      }
      Logger.log('Exported data to Drive folder %s for report %s.',
             CONFIG.DRIVE_FOLDER, reportConfig.NAME);
    }

    for (var j = 0; j < csvData.length; j++) {
      // Convert to Blob format.
      var blobData = Utilities.newBlob(csvData[j], 'application/octet-stream');
      // Load data
      var jobId = loadDataToBigquery(reportConfig, blobData, !j ? 1 : 0);
      jobIds.push(jobId);
    }
  }
  return jobIds;
}

/* Writes a CSV file to Drive, compressing as a zip file*/
function saveCompressedCsvFile(folder, fileName, csvData) {
  var compressed = Utilities.zip([Utilities.newBlob(csvData)]);
  compressed.setName(fileName);
  folder.createFile(compressed);
}

/* Retrieves AdWords data as csv and formats any fields*/
function retrieveAdwordsReport(reportConfig) {
  var fieldNames = Object.keys(reportConfig.FIELDS);
  var report = AdWordsApp.report(
    'SELECT ' + fieldNames.join(',') +
    ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
    ' DURING ' + formatDate(new Date('2018-11-01')) + ',' + formatDate(new Date(yesterday)));    
  
  var rows = report.rows();
  var chunks = [];
  var chunkLen = 0;
  var csvRows = [];
  var totalRows = 0;
  // Header row
  var header = fieldNames.join(',');
  csvRows.push(header);
  chunkLen += Utilities.newBlob(header).getBytes().length + 1;

  // Iterate over each row.
  while (rows.hasNext()) {
    var row = rows.next();

    if (chunkLen > MAX_INSERT_SIZE) {
      chunks.push(csvRows.join('\n'));
      totalRows += csvRows.length;
      chunkLen = 0;
      csvRows = [];
    }
    var csvRow = [];
    for (var i = 0; i < fieldNames.length; i++) {
      var fieldName = fieldNames[i];
      var fieldValue = row[fieldName].toString();
      var fieldType = reportConfig.FIELDS[fieldName];
      // Strip off % and perform any other formatting here.
      if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
        if (fieldValue.charAt(fieldValue.length - 1) == '%') {
          fieldValue = fieldValue.substring(0, fieldValue.length - 1);
        }
        fieldValue = fieldValue.replace(/,/g,'');
      }
      // Add double quotes to any string values.
      if (fieldType == 'STRING') {
        fieldValue = fieldValue.replace(/"/g, '""');
        fieldValue = '"' + fieldValue + '"';
      }
      csvRow.push(fieldValue);
    }
    var rowString = csvRow.join(',');
    csvRows.push(rowString);
    chunkLen += Utilities.newBlob(rowString).getBytes().length + 1;
  }
  if (csvRows) {
    totalRows += csvRows.length;
    chunks.push(csvRows.join('\n'));
  }
  Logger.log('Downloaded ' + reportConfig.NAME + ' with ' + totalRows +
      ' rows, in ' + chunks.length + ' chunks.');
  return chunks;
}


/* Creates a new Google Drive folder*/
function getDriveFolder() {
  var folders = DriveApp.getFoldersByName(CONFIG.DRIVE_FOLDER);
  // Assume first folder is the correct one.
  if (folders.hasNext()) {
   Logger.log('Folder name found.  Using existing folder.');
   return folders.next();
  }
  return DriveApp.createFolder(CONFIG.DRIVE_FOLDER);
}

/* Creates a BigQuery insertJob to load csv data. */
function loadDataToBigquery(reportConfig, data, skipLeadingRows) {
  // Create the data upload job.
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: CONFIG.BIGQUERY_PROJECT_ID,
          datasetId: CONFIG.BIGQUERY_DATASET_ID,
          tableId: reportConfig.NAME
        },
        skipLeadingRows: skipLeadingRows ? skipLeadingRows : 0,
        nullMarker: '--'
      }
    }
  };

  var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
  Logger.log('Load job started for %s. Check on the status of it here: ' +
      'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
       CONFIG.BIGQUERY_PROJECT_ID);
  return insertJob.jobReference.jobId;
}


function waitTillJobsComplete(jobIds) {
  var complete = false;
  var remainingJobs = jobIds;
  while (!complete) {
    if (AdWordsApp.getExecutionInfo().getRemainingTime() < 5){
      Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') +
        ' are still incomplete.');
    }
    remainingJobs = getIncompleteJobs(remainingJobs);
    if (remainingJobs.length == 0) {
      complete = true;
    }
    if (!complete) {
      Logger.log(remainingJobs.length + ' jobs still being processed.');
      // Wait 5 seconds before checking status again.
      Utilities.sleep(5000);
    }
  }
  Logger.log('All jobs processed.');
}

function getIncompleteJobs(jobIds) {
  var remainingJobIds = [];
  for (var i = 0; i < jobIds.length; i++) {
    var jobId = jobIds[i];
    var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
    if (getJob.status.state != 'DONE') {
      remainingJobIds.push(jobId);
    }
  }
  return remainingJobIds;
}```

以上です。