История Quality Score и анализ компонентов в Google Sheets

/**
 * Goodlabs Google Ads Script: Quality Score Tracker
 * Schedule: Daily at 05:00
 */
var CONFIG = {
  SPREADSHEET_URL: "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit",
  MIN_IMPRESSIONS: 10
};

function main() {
  var report = AdsApp.report(
    "SELECT segments.date, ad_group_criterion.keyword.text, campaign.name, ad_group.name, " +
    "metrics.historical_quality_score, metrics.historical_search_predicted_ctr, " +
    "metrics.historical_creative_quality_score, metrics.historical_landing_page_quality_score " +
    "FROM keyword_view " +
    "WHERE ad_group_criterion.status = 'ENABLED' " +
    "AND metrics.impressions > " + CONFIG.MIN_IMPRESSIONS + " " +
    "AND segments.date DURING YESTERDAY"
  );

  var ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  var sheet = ss.getSheetByName("QS_Log") || ss.insertSheet("QS_Log");
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(["Date", "Keyword", "Campaign", "Ad Group", "Quality Score", "Expected CTR", "Ad Relevance", "Landing Page Exp"]);
  }

  var rows = report.rows();
  var batch = [];

  while (rows.hasNext()) {
    var row = rows.next();
    batch.push([
      row["segments.date"],
      row["ad_group_criterion.keyword.text"],
      row["campaign.name"],
      row["ad_group.name"],
      row["metrics.historical_quality_score"],
      row["metrics.historical_search_predicted_ctr"],
      row["metrics.historical_creative_quality_score"],
      row["metrics.historical_landing_page_quality_score"]
    ]);
  }

  if (batch.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, batch.length, 8).setValues(batch);
  }
}