将 AdWords 报告导出至 BigQuery - 单一帐号

This script is for a single account. For operating on multiple accounts in a Manager Account, use the Manager Account version of the script.

BigQuery 是由 Google 全面管理的、无需运维、费用低廉的分析数据库。将您的 AdWords 报告加载到 BigQuery,即可执行功能强大的大数据分析。

BigQuery 基本知识

  • 项目用于保存一组数据集。
  • 数据集是控制对零个或多个表的访问的分组机制。
  • 是标准的二维表格,由各个记录组成行,并对每一列指定数据类型(也称为字段)。
  • 作业用来启动所有可能长时间运行的操作,如查询、表导入和导出请求。
  • BigQuery 设置

    • 使用 Google 云端平台控制台注册 BigQuery。如果您已经有现成的项目,请在 API 管理器中启用 BiqQuery API,激活 BigQuery。请记下该项目 ID,因为您会在 AdWords 脚本中用到这个 ID。
    • 通过点击高级 API 按钮并选中 BigQuery 旁的复选框,在 AdWords 脚本中启用 BigQuery。点击保存

    工作原理

    首先,该脚本创建 BigQuery 数据集。然后,该脚本为每个配置的报告创建 BigQuery 。最后,对每个报告进行处理。报告处理过程包括:从 AdWords 中以 CSV 格式检索报告,将其转换为 Blob,并创建插入作业,将数据加载到 BigQuery。然后,脚本轮询每个插入作业的状态,直到所有作业为 DONE。完成时会发送电子邮件,通知收件人。

    运行时间设置

    报告依据的是前一天的统计信息。请将其设为每天凌晨 3 点 (Daily, 3am) 或更晚时间运行,以保证准确性,因为 AdWords 统计信息最多可能会有 3 小时的延迟

    设置

    • 使用下面的源代码创建新脚本。
    • 更新 BIGQUERY_PROJECT_IDBIGQUERY_DATASET_ID,以关联到您的 BigQuery 项目和数据集。如果还没有数据集,则可使用任何 ID。
    • 设置 TRUNCATE_EXISTING_DATASETTRUNCATE_EXISTING_TABLES。如果设置为 true,则将删除现有的所有数据。如果设置为 false,数据将被附加到现有表。针对架构的任何更改,都不必截断数据集,但应截断表。
    • WRITE_DATA_TO_DRIVE 设置为 true,会将您的报告数据备份至 Google 云端硬盘。通过设置 DRIVE_FOLDER,指定云端硬盘的文件夹名。
    • 通过指定每个报告的 NAMECONDITIONSFIELDS,配置您的报告。报告的 NAME 也是 BigQuery 中的表名。而 FIELDS 用于通过设置列名和类型推断表的架构。任何类型转换(如从 FLOAT 中去掉“%”,或将日期转换为 YYYY-MM-DD HH:MM:SS 格式)都应在 retrieveAdwordsReport 中处理。
    • 更新 RECIPIENT_EMAIL 以指定您的电子邮件偏好设置。
    • 将脚本设为每天凌晨 3 点 (Daily, 3am) 运行。

    源代码

    // Copyright 2016, Google Inc. All Rights Reserved.
    //
    // Licensed under the Apache License, Version 2.0 (the "License");
    // you may not use this file except in compliance with the License.
    // You may obtain a copy of the License at
    //
    //     http://www.apache.org/licenses/LICENSE-2.0
    //
    // Unless required by applicable law or agreed to in writing, software
    // distributed under the License is distributed on an "AS IS" BASIS,
    // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    // See the License for the specific language governing permissions and
    // limitations under the License.
    
    /**
     * @name Export Data to BigQuery
     *
     * @overview The Export Data to BigQuery script sets up a BigQuery
     *       dataset and tables, downloads a report from AdWords and then
     *       loads the report to BigQuery.
     *
     * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
     *
     * @version 1.4
     *
     * @changelog
     * - version 1.4
     *   - Inserts are split into <10Mb chunks.
     *   - Compress backups to Drive.
     *
     * @changelog
     * - version 1.3
     *   - Global string replace to escape quotes.
     *
     * @changelog
     * - version 1.2
     *   - Global string replace to remove commas.
     *
     * @changelog
     * - version 1.1
     *   - Removed commas from numbers to fix formatting issues.
     *
     * @changelog
     * - version 1.0
     *   - Released initial version.
     */
    
    var CONFIG = {
      BIGQUERY_PROJECT_ID: 'INSERT_PROJECT_ID',
      BIGQUERY_DATASET_ID: 'INSERT_DATASET_ID',
    
      // Truncate existing data, otherwise will append.
      TRUNCATE_EXISTING_DATASET: false,
      TRUNCATE_EXISTING_TABLES: false,
    
      // Back up reports to Google Drive.
      WRITE_DATA_TO_DRIVE: false,
      // Folder to put all the intermediate files.
      DRIVE_FOLDER: 'INSERT_FOLDER_NAME',
    
      // Default date range over which statistics fields are retrieved.
      DEFAULT_DATE_RANGE: 'YESTERDAY',
    
      // Lists of reports and fields to retrieve from AdWords.
      REPORTS: [{NAME: 'ACCOUNT_PERFORMANCE_REPORT',
         CONDITIONS: '',
         FIELDS: {'Cost' : 'FLOAT',
                  'AverageCpc' : 'FLOAT',
                  'Ctr' : 'FLOAT',
                  'AveragePosition' : 'FLOAT',
                  'Impressions' : 'INTEGER',
                  'Clicks' : 'INTEGER',
                  'Date' : 'STRING'
                 }
        }, {NAME: 'KEYWORDS_PERFORMANCE_REPORT',
         CONDITIONS: 'WHERE CampaignStatus = ENABLED',
         FIELDS: {'CampaignName' : 'STRING',
                  'AdGroupName' : 'STRING',
                  'Criteria' : 'STRING',
                  'Impressions' : 'INTEGER',
                  'Cost' : 'FLOAT',
                  'Clicks' : 'INTEGER',
                  'QualityScore' : 'FLOAT',
                  'Date' : 'STRING'
                 }
        }, {NAME: 'SEARCH_QUERY_PERFORMANCE_REPORT',
         CONDITIONS: '',
         FIELDS: {'Query' : 'STRING',
                  'Cost' : 'FLOAT',
                  'AverageCpc' : 'FLOAT',
                  'Ctr' : 'FLOAT',
                  'AveragePosition' : 'FLOAT',
                  'Impressions' : 'INTEGER',
                  'Clicks' : 'INTEGER',
                  'Date' : 'STRING'
                 }
        }],
    
      RECIPIENT_EMAILS: [
        'RECIPIENT_EMAIL'
      ]
    };
    
    // 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);
      sendEmail(jobIds);
    }
    
    
    /**
     * Creates a new dataset.
     *
     * If a dataset with the same id already exists and the truncate flag
     * is set, will truncate the old dataset. If the truncate flag is not
     * set, then will not create 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.
     *
     * @return {boolean} Returns true if dataset already exists.
     */
    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.
     *
     * If a table with the same id already exists and the truncate flag
     * is set, will truncate the old table. If the truncate flag is not
     * set, then will not create a new table.
     *
     * @param {Object} reportConfig Report configuration including report name,
     *    conditions, and fields.
     */
    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.
     *
     * @param {string} tableId The table id to check existence.
     *
     * @return {boolean}  Returns true if table already exists.
     */
    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
     *
     * Iterates through each report to: retrieve AdWords data,
     * backup data to Drive (if configured), load data to BigQuery.
     *
     * @return {Array.<string>} jobIds The list of all job ids.
     */
    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.
     *
     * @param {!Folder} folder The parent folder for the file.
     * @param {string} fileName The name for the file.
     * @param {string} csvData The CSV data to write to the 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
     * to BigQuery expected format.
     *
     * @param {Object} reportConfig Report configuration including report name,
     *    conditions, and fields.
     *
     * @return {!Array.<string>} a chunked report in csv format.
     */
    function retrieveAdwordsReport(reportConfig) {
      var fieldNames = Object.keys(reportConfig.FIELDS);
      var report = AdWordsApp.report(
        'SELECT ' + fieldNames.join(',') +
        ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
        ' DURING ' + CONFIG.DEFAULT_DATE_RANGE);
      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. If folder name is already in
     * use will pick the first folder with a matching name.
     *
     * @return {Folder} Google Drive folder to store reports.
     */
    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.
     *
     * @param {Object} reportConfig Report configuration including report name,
     *    conditions, and fields.
     * @param {Blob} data Csv report data as an 'application/octet-stream' blob.
     * @param {number=} skipLeadingRows Optional number of rows to skip.
     *
     * @return {string} jobId The job id for upload.
     */
    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;
    }
    
    /**
     * Polls until all jobs are 'DONE'.
     *
     * @param {Array.<string>} jobIds The list of all job ids.
     */
    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.');
    }
    
    /**
     * Iterates through jobs and returns the ids for those jobs
     * that are not 'DONE'.
     *
     * @param {Array.<string>} jobIds The list of job ids.
     *
     * @return {Array.<string>} remainingJobIds The list of remaining job ids.
     */
    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;
    }
    
    
    /**
     * Sends a notification email that jobs have completed loading.
     *
     * @param {Array.<string>} jobIds The list of all job ids.
     */
    function sendEmail(jobIds) {
      var html = [];
      html.push(
        '<html>',
          '<body>',
            '<table width=800 cellpadding=0 border=0 cellspacing=0>',
              '<tr>',
                '<td colspan=2 align=right>',
                  "<div style='font: italic normal 10pt Times New Roman, serif; " +
                      "margin: 0; color: #666; padding-right: 5px;'>" +
                      'Powered by AdWords Scripts</div>',
                '</td>',
              '</tr>',
              "<tr bgcolor='#3c78d8'>",
                '<td width=500>',
                  "<div style='font: normal 18pt verdana, sans-serif; " +
                  "padding: 3px 10px; color: white'>Adwords data load to " +
                  "Bigquery report</div>",
                '</td>',
                '<td align=right>',
                  "<div style='font: normal 18pt verdana, sans-serif; " +
                  "padding: 3px 10px; color: white'>",
                   AdWordsApp.currentAccount().getCustomerId(),
                '</tr>',
              '</table>',
              '<table width=800 cellpadding=0 border=1 cellspacing=0>',
                "<tr bgcolor='#ddd'>",
                  "<td style='font: 12pt verdana, sans-serif; " +
                      'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                      "text-align: left'>Report</td>",
                  "<td style='font: 12pt verdana, sans-serif; " +
                      'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                      "text-align: left'>JobId</td>",
                  "<td style='font: 12pt verdana, sans-serif; " +
                      'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                      "text-align: left'>Rows</td>",
                  "<td style='font: 12pt verdana, sans-serif; " +
                      'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                      "text-align: left'>State</td>", 
                  "<td style='font: 12pt verdana, sans-serif; " +
                      'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                      "text-align: left'>ErrorResult</td>",
                '</tr>',
                createTableRows(jobIds),
            '</table>',
          '</body>',
        '</html>');
    
      MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
          'Adwords data load to Bigquery Complete', '',
          {htmlBody: html.join('\n')});
    }
    
    /**
     * Creates table rows for email report.
     *
     * @param {Array.<string>} jobIds The list of all job ids.
     */
    function createTableRows(jobIds) {
      var html = [];
      for (var i = 0; i < jobIds.length; i++) {
        var jobId = jobIds[i];
        var job = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
        var errorResult = '';
        if (job.status.errorResult) {
          errorResult = job.status.errorResult;
        }
    
        html.push('<tr>',
          "<td style='padding: 0px 10px'>" +
            job.configuration.load.destinationTable.tableId + '</td>',
          "<td style='padding: 0px 10px'>" + jobId + '</td>',
            "<td style='padding: 0px 10px'>" +
              (job.statistics.load ? job.statistics.load.outputRows : 0) +'</td>',
          "<td style='padding: 0px 10px'>" + job.status.state + '</td>',
          "<td style='padding: 0px 10px'>" + errorResult + '</td>',
          '</tr>');
      }
      return html.join('\n');
    }
    
    

    Looking for the Manager Account (MCC) version? Click here

    发送以下问题的反馈:

    此网页
    AdWords Scripts
    AdWords Scripts
    需要帮助?请访问我们的支持页面