Jak na optimalizaci skóre kvality – aneb nové možnosti díky Adwords API v201601

/**
 * Stores keyword performance report to BigQuery
 * 
 * Changelog:
 *   2.0 (2015-10-16): migration from KeywordText to Criteria for AdWords API v201509.
 *   1.0 (2015-07-01): initial version.
 *
 * @author Jan Smitka <jan.smitka@lynt.cz>
 * @version 2.0
 */

// Name of the table. Must be changed for each client!
var TABLE_ID = '';

// Project and dataset settings:
var PROJECT_ID = 'lynt-marketing';
var DATASET_ID = 'adwords_reports';

/**
 * Map of changed field names: new name => old name (used in table).
 * The new names are renamed to old names to conform with the table schema.
 */
var RENAMED_FIELDS = {
  'Criteria': 'KeywordText'
};

/**
 * Main entry point.
 */
function main() { 
  // Get the report from AdWords. Remember, if you change the columns of the report, you must also change the table schema (in the getTableSchema() function).
  // Renaming of the columns can be handled in the insertToBigQuery function - see the RENAMED_FIELDS variable above.
  // Docs: https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report
  var report = AdWordsApp.report(
    "SELECT Date, Id, Criteria, CampaignName, AdGroupName, CpcBid,FirstPageCpc, TopOfPageCpc, QualityScore, Cost, Ctr, AverageCpc, Clicks, AveragePosition, Impressions, ConvertedClicks, CostPerConvertedClick, ConversionValue, AverageTimeOnSite, SearchRankLostImpressionShare, SearchImpressionShare " +
    " FROM KEYWORDS_PERFORMANCE_REPORT " +
    " WHERE Impressions > 0 " +
    " AND AdGroupStatus = 'ENABLED'" + 
    " AND CampaignStatus = 'ENABLED'" + 
    " AND CampaignName STARTS_WITH 'S:'" +
    " AND Status = 'ENABLED'" + 
    " DURING YESTERDAY"
  );

  insertToBigQuery(report, TABLE_ID, getTableSchema());
}

/**
 * Gets the schema definition for tables. If you change the report, the schema must be changed accordingly.
 */
function getTableSchema() {
  return {
    fields: [
      {
        "name": "Date",
        "type": "TIMESTAMP",
        "mode": "REQUIRED"
      },
      {
        "name": "Id",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "KeywordText",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "CampaignName",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "AdGroupName",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "CpcBid",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "FirstPageCpc",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "TopOfPageCpc",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "QualityScore",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "Cost",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "Ctr",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "AverageCpc",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "Clicks",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "AveragePosition",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "Impressions",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "ConvertedClicks",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "CostPerConvertedClick",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "ConversionValue",
        "type": "FLOAT",
        "mode": "REQUIRED"
      },
      {
        "name": "AverageTimeOnSite",
        "type": "INTEGER",
        "mode": "REQUIRED"
      },
      {
        "name": "SearchRankLostImpressionShare",
        "type": "FLOAT"
      },
      {
        "name": "SearchImpressionShare",
        "type": "FLOAT"
      }
    ]
  };
}

/**
 * Inserts the specified report to Big Query.
 */
function insertToBigQuery(report, table, tableSchema) {
  var rows = report.rows();
  var records = [];

  var tableColName;
  while (rows.hasNext()) {
    var row = rows.next();
    var record = {};
    for (var col in row) {
      if (row.hasOwnProperty(col) && typeof(row[col]) != 'function') {         
        if (RENAMED_FIELDS[col]) {
          tableColName = RENAMED_FIELDS[col];
        } else {
          tableColName = col;
        }
        record[tableColName] = normalizeValue(row[col]);
      }
    }
    records.push(JSON.stringify(record));
  }

  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: PROJECT_ID,
          datasetId: DATASET_ID,
          tableId: table
        },
        sourceFormat: "NEWLINE_DELIMITED_JSON",
        schema: tableSchema
      }
    }
  };

  var blob = Utilities.newBlob(records.join("\n"), "application/octet-stream");
  var jobResp = BigQuery.Jobs.insert(job, PROJECT_ID, blob);
  Logger.log('Load job queued. Status: ' +
      'https://bigquery.cloud.google.com/jobs/%s', PROJECT_ID);
}

// Predefined patterns - do not touch!
var PERCENT_PATTERN = /\d+(\.\d*)?%$/;
var DATE_PATTERN = /^\d{4}-\d{2}-\d{2}$/;

/**
 * Prepares the values for insertion to Big Query.
 */
function normalizeValue(value) {
  if (value == '--') {
    return null;
  } else if (value == "< 10") { return 10; } else if (value == "> 90") {
    return 90;
  } else if (PERCENT_PATTERN.test(value)) {
    return value.substring(0, value.length - 1);
  } else if (DATE_PATTERN.test(value)) {
    return value + " 00:00:00";
  } else {
    return value;
  }
}