Advanced Quality Score Tracker by Clicteq – Updated to use GAQL Google Ads Script

Here is an updated version of the Advanced Quality Score Tracker by Clicteq Google Ads script, rebuilt from the ground up to take advantage of GAQL (Google Ads Query Language).

The original script was designed to pull keyword-level Quality Score data directly into Google Sheets, giving PPC managers a clear view of how Quality Scores change over time and which campaigns need optimisation. With Google’s migration towards GAQL for reporting, this version modernises the script to fix broken parts and ensure continued reliability, faster execution, and better flexibility for future updates.

What’s New in the GAQL Version?

Faster Data Retrieval – GAQL queries are more efficient, meaning the script runs quicker even for accounts with thousands of keywords.
More Accurate Data – Direct integration with the latest Ads API fields ensures Quality Score, Expected CTR, Ad Relevance, and Landing Page Experience data is pulled exactly as reported by Google Ads.
Future-Proof – As Google continues to retire legacy reporting methods, this GAQL-based approach ensures compatibility going forward.

Why Track Quality Score?

Quality Score isn’t just a number — it’s a key driver of ad rank and cost efficiency. By monitoring changes over time, you can spot:

Keywords with declining relevance.
Landing pages that may be impacting user experience.
Opportunities to improve ad copy and CTR.

How It Works

The script queries your account using GAQL to fetch keyword Quality Score metrics.
Data is automatically appended to a Google Sheet each day.

Note: The script outputs keyword-level Quality Score data including Expected CTR, Ad Relevance, and Landing Page Experience scores — giving you a historical log you can chart over time.

The Script

Paste this into your Google Ads Scripts editor. Before running, create a new Google Sheet and paste its URL into the SHEETURL variable at the top.

/**
 *
 * Advanced Quality Score Tracker by Clicteq - Updated to use GAQL Google Ads Script
 * The scripts plots Quality Score and impression weighed Quality Score on a daily basis
 * It has 3 graphs to show the split by expected CTR, landing page experience and ad relevance
 *
 * Version: 2.0
 * Originally created by Clicteq
 * Updated by EDPC
 **/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//Control Panel

//URL of Google Sheet. Create a new Google sheet that you want the data to be outputted to and paste the URL between then quotation marks below
SHEETURL =
  "";

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function getDetailedReport(enums, metrics) {
  //Downloads a report with QS components and calculates weighted averages. Returns a sheet-friendly row.

  var result = {};
  result = {
    "ad_group_criterion.quality_info.search_predicted_ctr_BELOW_AVERAGE": 1,
    "ad_group_criterion.quality_info.search_predicted_ctr_AVERAGE": 2,
    "ad_group_criterion.quality_info.search_predicted_ctr_ABOVE_AVERAGE": 3,
  };
  var total = 0;
  var total_impr = 0;
  for (metric in metrics) {
    for (systemConstant in enums) {
      var m = metrics[metric];
      var e = enums[systemConstant];
      var query =
        "SELECT metrics.impressions FROM keyword_view WHERE %m = %e AND segments.date DURING YESTERDAY";
      var temp = query.replace("%m", m).replace("%e", e);
      var report = AdsApp.report(temp).rows();
      var i = 0;
      var impr = 0;

      while (report.hasNext()) {
          row = report.next();
        i = i + 1;
          var impr_part = parseInt(row["metrics.impressions"]);
        impr = impr + impr_part;
      }
      result[metrics[metric] + "_" + enums[systemConstant]] = {
        impressions: parseInt(impr),
        total: parseInt(i),
      };
      total = total + i;
      total_impr = total_impr + impr;
    }
  }
  total = parseInt(total / 3);
  total_impr = parseInt(total_impr / 3);
  var sheetRow = [];

  for (metric in metrics) {
    for (systemConstant in enums) {
      m = metrics[metric];
      e = enums[systemConstant];
      var single = result[m + "_" + e];
      single["total"] = (single["total"] / total).toFixed(5);
      sheetRow.push(result[m + "_" + e]["total"]);
      if (e == "BELOW_AVERAGE") {
        var weighted = 1 * (single["impressions"] / total_impr);
      } else if (e == "AVERAGE") {
        var weighted = 2 * (single["impressions"] / total_impr);
      } else if (e == "ABOVE_AVERAGE") {
        var weighted = 3 * (single["impressions"] / total_impr);
      }

      single["weighted"] = weighted;
    }
  }

  var weightedAll = {};

  for (metric in metrics) {
    m = metrics[metric];
    weightedAll[m] = 0;
    for (systemConstant in enums) {
      e = enums[systemConstant];
      var single = result[m + "_" + e];
      weightedAll[m] = weightedAll[m] + single["weighted"];
    }
    weightedAll[m] = weightedAll[m].toFixed(4);
  }
  var metrics_temp = [
    "ad_group_criterion.quality_info.search_predicted_ctr",
    "ad_group_criterion.quality_info.post_click_quality_score",
    "ad_group_criterion.quality_info.creative_quality_score",
  ];
  for (item in metrics_temp) {
    sheetRow.push(weightedAll[metrics_temp[item]]);
  }
  return sheetRow;
}

function getTotalReport() {
  //Downloads a QS report and calculates weighted average. Returns a sheet-friendly row.

  var query =
    "SELECT metrics.impressions, ad_group_criterion.quality_info.quality_score FROM keyword_view WHERE ad_group_criterion.quality_info.quality_score IS NOT NULL AND segments.date DURING YESTERDAY";
  var report = AdsApp.report(query).rows();
  var result = {
    1: { impressions: 0, total: 0, weighted: 0 },
    2: { impressions: 0, total: 0, weighted: 0 },
    3: { impressions: 0, total: 0, weighted: 0 },
    4: { impressions: 0, total: 0, weighted: 0 },
    5: { impressions: 0, total: 0, weighted: 0 },
    6: { impressions: 0, total: 0, weighted: 0 },
    7: { impressions: 0, total: 0, weighted: 0 },
    8: { impressions: 0, total: 0, weighted: 0 },
    9: { impressions: 0, total: 0, weighted: 0 },
    10: { impressions: 0, total: 0, weighted: 0 },
  };
  var impr = 0;
  var total = 0;
  var totalQs = 0;
  while (report.hasNext()) {
    var row = report.next();
    var impr_part = parseInt(row["metrics.impressions"]);
    var qs = row["ad_group_criterion.quality_info.quality_score"];
    if (typeof qs !== "undefined") {
      result[qs]["impressions"] = result[qs]["impressions"] + impr_part;
      result[qs]["total"] = result[qs]["total"] + 1;
      impr = impr + impr_part;
      var total = total + 1;
      totalQs = totalQs + parseInt(qs);
    }
  }
  var temp = [];
  var keys = Object.keys(result);
  var weighted = 0;
  for (place in keys) {
    var key = keys[place];
    var weighted_part = (result[key]["impressions"] / impr) * key;
    result[key]["total"] = (result[key]["total"] / total).toFixed(4);
    weighted = weighted + weighted_part;
  }
  weighted = weighted.toFixed(2);
  var qses = ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10"];
  for (place in qses) {
    var key = qses[place];
    temp.push(result[key]["total"]);
  }

  temp.push(weighted);
  temp.push((totalQs / total).toFixed(2));

  return temp;
}

function createGraphs() {
  //Creates graphs when the script first runs

  var graphsData = {
    ctr: { all: "A2:C4", weighted: "K:K" },
    lp: { all: "A5:C7", weighted: "L:L" },
    ad: { all: "A8:C10", weighted: "M:M" },
    all: { all: "A14:C23", weighted: "X:X" },
  };
  var titles = {
    ctr: {
      all: "QS expected CTR % of all keywords",
      weighted: "QS expected CTR weighted impressions",
    },
    lp: {
      all: "QS Landing Page % of all keywords",
      weighted: "QS Landing Page weighted impressions",
    },
    ad: {
      all: "QS Ad Relevance % of all keywords",
      weighted: "QS Ad Relevance weighted impressions",
    },
    all: { all: "QS % of all keywords", weighted: "QS weighted impressions" },
  };
  var axes = {
    all_all: [0, 1],
    ctr_all: [0, 1],
    lp_all: [0, 1],
    ad_all: [0, 1],
    all_weighted: [0, 10],
    ctr_weighted: [0, 3],
    ad_weighted: [0, 3],
    lp_weighted: [0, 3],
  };
  var positions = [1, 4, 21, 38];

  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("dashboard");
  var data = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("data");
  var types = ["all", "ctr", "lp", "ad"];
  var subTypes = ["all", "weighted"];
  var xAxis = data.getRange("A1:A");
  var dataBar = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("data-bar");
  var xAxisBar = dataBar.getRange("A1:C1");

  if (sheet.getCharts().length == 0) {
    Logger.log("Creating graphs...");
  } else {
    return;
  }
  for (type in types) {
    for (subType in subTypes) {
      var seriesBar = dataBar.getRange(
        graphsData[types[type]][subTypes[subType]]
      );
      var series = data.getRange(graphsData[types[type]][subTypes[subType]]);
      if (subType != 0) {
        var pos = subType * 9;
      } else {
        var pos = 2;
      }

      if (subTypes[subType] == "all") {
        var chart = sheet
          .newChart()
          .asColumnChart()
          .setOption("vAxes", {
            0: {
              viewWindow: {
                min: axes[types[type] + "_" + subTypes[subType]][0],
                max: axes[types[type] + "_" + subTypes[subType]][1],
              },
            },
          })
          .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
          .addRange(xAxisBar)
          .addRange(seriesBar)
          .setPosition(positions[type], pos, 2, 2)
          .setNumHeaders(1)
          .setOption("title", titles[types[type]][subTypes[subType]])
          .setOption("legend", { position: "bottom" })
          .build();
      } else {
        var chart = sheet
          .newChart()
          .asLineChart()
          .addRange(xAxis)
          .addRange(series)
          .setPosition(positions[type], pos, 2, 2)
          .setNumHeaders(1)
          .setOption("title", titles[types[type]][subTypes[subType]])
          .setOption("legend", { position: "bottom" })
          .setOption("vAxes", {
            0: {
              viewWindow: {
                min: axes[types[type] + "_" + subTypes[subType]][0],
                max: axes[types[type] + "_" + subTypes[subType]][1],
              },
            },
          })
          .build();
      }
      sheet.insertChart(chart);
    }
  }
}

function prepareSheet() {
  //Prepares a new sheet by creating new tabs

  var sheet = SpreadsheetApp.openByUrl(SHEETURL);
  if (sheet.getSheetByName("dashboard") === null) {
    try {
      sheet.insertSheet("dashboard");
    } catch (e) {}
    try {
      sheet.insertSheet("data");
      sheet.getSheetByName("data").insertColumns(1, 12);
    } catch (e) {}
    try {
      sheet.insertSheet("data-bar");
      sheet.getSheetByName("data").insertColumnAfter(1);
    } catch (e) {}
    Logger.log("Preparing sheet...");
    try {
      sheet.deleteSheet(sheet.getSheetByName("Sheet1"));
    } catch (e) {}

    sheet.getSheetByName("dashboard").setHiddenGridlines(true);
    formatCells();
  }
}

function formatRange() {
  //Formats decimals so they are displayed as %

  var sheet = SpreadsheetApp.openByUrl(SHEETURL);
  sheet.getRange("data!B2:J").setNumberFormat("0%");
  sheet.getRange("data!N2:W").setNumberFormat("0%");
  sheet.getRange("data!A2:A").setNumberFormat("dd/mm/yyyy");
}

function transpose() {
  //Creates a transposed version of the first and last days, used for bar charts

  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("data-bar");
  var range = sheet.getRange("A:C");
  range.clear();
  var lastRow = SpreadsheetApp.openByUrl(SHEETURL)
    .getSheetByName("data")
    .getLastRow();
  sheet.getRange("A1:A1").setFormula("=TRANSPOSE(data!A1:AD1)");
  sheet.getRange("B1:B1").setFormula("=TRANSPOSE(data!A2:AD2)");
  sheet
    .getRange("C1:C1")
    .setFormula("=TRANSPOSE(data!A" + lastRow + ":AD" + lastRow + ")");
}

function formatCells() {
  //Formats cells' size and fonts in the dashboard tab. Adds average quality scores as text

  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("dashboard");
  var rows = [1];
  var cols_avg = ["Y", "X"];
  var cols = ["H", "O"];
  var avr = ["Normal average", "Weighted average"];
  for (row in rows) {
    sheet.setRowHeight(rows[row], 75);
    sheet.setRowHeight(rows[row] + 2, 280);
    for (col in cols) {
      var _col = cols[col];
      sheet
        .getRange(_col + rows[row] + ":" + _col + rows[row])
        .setValue("Average QS")
        .setFontWeight("bold")
        .setFontSize(24);
      sheet
        .getRange(_col + (rows[row] + 1) + ":" + _col + (rows[row] + 1))
        .setValue(avr[col]);
      sheet
        .getRange(_col + (rows[row] + 2) + ":" + _col + (rows[row] + 2))
        .setFormula(
          "=AVERAGE(data!" + cols_avg[col] + "2:" + cols_avg[col] + ")"
        )
        .setNumberFormat("0.00")
        .setFontWeight("bold")
        .setFontSize(24);
      sheet
        .getRange(_col + rows[row] + ":" + _col + (rows[row] + 2))
        .setBorder(true, false, true, true, null, true);

      sheet
        .getRange(_col + ":" + _col)
        .setHorizontalAlignment("center")
        .setVerticalAlignment("middle");
    }
  }
  sheet.autoResizeColumn(8);
  sheet.autoResizeColumn(15);
}

function main() {
  //Combines all functions
  prepareSheet();

  var enums = ["BELOW_AVERAGE", "AVERAGE", "ABOVE_AVERAGE"];
  var metrics = [
    "ad_group_criterion.quality_info.search_predicted_ctr",
    "ad_group_criterion.quality_info.post_click_quality_score",
    "ad_group_criterion.quality_info.creative_quality_score",
  ];
  var sheet = SpreadsheetApp.openByUrl(SHEETURL).getSheetByName("data");

  Logger.log("Downloading detailed quality scores...");
  var detailedReport = getDetailedReport(enums, metrics);
  Logger.log("Downloading combined quality scores...");
  var totalReport = getTotalReport();

  var header = [
    [
      "Date",
      "Expected_CTR_Below_Average",
      "Expected_CTR_Average",
      "Expected_CTR_Above_Average",
      "LP_Below_Average",
      "LP_Average",
      "LP_Above_Average",
      "Ad_Copy_Below_Average",
      "Ad_Copy_Average",
      "Ad_Copy_Above_Average",
      "CTR_weighted",
      "LP_weighted",
      "Ad_Copy_weighted",
      '="1"',
      '="2"',
      '="3"',
      '="4"',
      '="5"',
      '="6"',
      '="7"',
      '="8"',
      '="9"',
      '="10"',
      "QS_weighted",
      "QS_average",
    ],
  ];
  var currentHeader = sheet.getRange("A1:Y1").getValues();

  if (currentHeader[0][0] == "") {
    sheet.getRange("A1:Y1").setValues(header);
  }

  var row = [];
  row.push(header);

  var temp = [];

  var yday = Utilities.formatDate(
    new Date(new Date() - 1000 * 60 * 60 * 24),
    "gmt",
    "dd/MM/yyyy"
  );
  temp.push(yday);

  for (item in detailedReport) {
    temp.push(detailedReport[item]);
  }

  temp = temp.concat(totalReport);

  sheet.appendRow(temp);

  createGraphs();
  formatRange();
  transpose();
}

Advanced Quality Score Tracker by Clicteq – Updated to use GAQL Google Ads Script

What’s New in the GAQL Version?

Why Track Quality Score?

How It Works

The Script

Need help with this?