Dan Antony
Library / PPC & Paid Search

Google Ads Script to Report Brand vs Non-Brand Keyword Performance

PPC & Paid Search·Google Ads Script·Google AdsGoogle Sheets

What it does

Pulls keyword-level impressions, clicks, CTR and cost from Google Ads, splits each keyword into Brand or Non-Brand by matching its text against your brand terms, and writes two tabs to a Google Sheet: a Daily Report (one Brand and one Non-Brand row per date) and a Daily Trend (date rolled up with spend percentages). On first run it backfills from a HISTORICAL_START date to yesterday; after that it runs for yesterday only, driven by a Status cell on a Setup tab. Only Search campaigns whose name contains your campaign prefix are included.

The code

/**
 * ============================================================
 * GOOGLE ADS BRAND VS NON-BRAND DAILY REPORT
 * ============================================================
 *
 * Splits keyword performance into Brand vs Non-Brand and writes a
 * daily report + daily trend to Google Sheets.
 *
 * INITIAL RUN:  Backfills from HISTORICAL_START to yesterday.
 * AFTER INITIAL RUN: Runs for yesterday only (driven by a Setup sheet
 *   Status cell = BACKFILL_COMPLETED).
 *
 * CAMPAIGNS: Search campaigns whose name contains CAMPAIGN_NAME_PREFIX.
 *   Enabled + paused campaigns and keywords included.
 *
 * BRAND: keywords containing any BRAND_TERMS substring.
 *
 * METRICS: Impressions, Clicks, CTR, Cost.
 *
 * SHEETS: Daily Report | Daily Trend | Setup
 * ============================================================
 */

/* ============================================================
   MAIN FUNCTION
   ============================================================ */

function main() {

  // ---- CONFIG: set these for your account -------------------
  var SHEET_URL = 'YOUR_GOOGLE_SHEET_URL';   // target spreadsheet
  var REPORT_TAB = 'Daily Report';
  var TREND_TAB = 'Daily Trend';
  var SETUP_TAB = 'Setup';
  var HISTORICAL_START = 'YYYY-MM-DD';       // e.g. '2026-06-01'
  var CAMPAIGN_NAME_PREFIX = 'YOUR_CAMPAIGN_PREFIX';  // e.g. 'brand_search'
  var BRAND_TERMS = [                        // keywords that count as brand
    'yourbrand',
    'your brand'
  ];
  // -----------------------------------------------------------

  var ss = SpreadsheetApp.openByUrl(SHEET_URL);

  var report = getOrCreateSheet(ss, REPORT_TAB);
  var trend = getOrCreateSheet(ss, TREND_TAB);
  var setup = getOrCreateSheet(ss, SETUP_TAB);


  // ==========================================================
  // CREATE DAILY REPORT HEADER
  // ==========================================================

  if (report.getLastRow() === 0) {
    report.appendRow([
      'Date', 'Keyword Type', 'Impressions', 'Clicks', 'CTR', 'Cost'
    ]);
  }


  // ==========================================================
  // GET YESTERDAY
  // ==========================================================

  var yesterday = getYesterday();


  // ==========================================================
  // DETERMINE RUN MODE
  // ==========================================================

  var status = setup.getRange('B1').getValue();

  var startDate;
  var endDate;

  if (status !== 'BACKFILL_COMPLETED') {
    // First run: HISTORICAL_START -> yesterday
    startDate = HISTORICAL_START;
    endDate = yesterday;
    Logger.log('MODE: HISTORICAL BACKFILL');
    Logger.log('DATE RANGE: ' + startDate + ' -> ' + endDate);
  } else {
    // Future runs: yesterday only
    startDate = yesterday;
    endDate = yesterday;
    Logger.log('MODE: DAILY UPDATE');
    Logger.log('DATE: ' + yesterday);
  }


  // ==========================================================
  // GET GOOGLE ADS DATA
  // ==========================================================

  var data = getAdsData(startDate, endDate, CAMPAIGN_NAME_PREFIX, BRAND_TERMS);


  // ==========================================================
  // WRITE DAILY REPORT
  // ==========================================================

  writeReport(report, data);


  // ==========================================================
  // MARK BACKFILL COMPLETE
  // ==========================================================

  if (status !== 'BACKFILL_COMPLETED') {
    setup.getRange('A1').setValue('Status');
    setup.getRange('B1').setValue('BACKFILL_COMPLETED');
    setup.getRange('A2').setValue('Historical Start');
    setup.getRange('B2').setValue(HISTORICAL_START);
    setup.getRange('A3').setValue('Backfill Through');
    setup.getRange('B3').setValue(yesterday);
    setup.getRange('A4').setValue('Daily Mode');
    setup.getRange('B4').setValue('Yesterday');
    setup.getRange('A1:B4').setFontWeight('bold');
  }


  // ==========================================================
  // BUILD DAILY TREND
  // ==========================================================

  buildTrend(report, trend);


  // ==========================================================
  // FORMAT SHEETS
  // ==========================================================

  formatReport(report);
  formatTrend(trend);


  Logger.log('====================================');
  Logger.log('REPORT COMPLETED SUCCESSFULLY');
  Logger.log('====================================');
}


/* ============================================================
   GET OR CREATE SHEET
   ============================================================ */

function getOrCreateSheet(ss, name) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) sheet = ss.insertSheet(name);
  return sheet;
}


/* ============================================================
   GET YESTERDAY
   ============================================================ */

function getYesterday() {
  var timezone = AdsApp.currentAccount().getTimeZone();
  var date = new Date();
  date.setDate(date.getDate() - 1);
  return Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
}


/* ============================================================
   GET GOOGLE ADS DATA
   ============================================================ */

function getAdsData(startDate, endDate, campaignNameCondition, brandTerms) {

  var data = {};

  // NOTE: No campaign.status / ad_group_criterion.status filter, so
  // enabled + paused campaigns and keywords are all included.

  var query =
    'SELECT ' +
    'segments.date, ' +
    'campaign.name, ' +
    'campaign.advertising_channel_type, ' +
    'ad_group_criterion.keyword.text, ' +
    'metrics.impressions, ' +
    'metrics.clicks, ' +
    'metrics.cost_micros ' +
    'FROM keyword_view ' +
    'WHERE segments.date BETWEEN "' + startDate + '" AND "' + endDate + '"';

  Logger.log('Running Google Ads query...');

  var rows = AdsApp.search(query);
  var totalRows = 0;
  var includedRows = 0;

  while (rows.hasNext()) {
    var row = rows.next();
    totalRows++;

    // SEARCH CAMPAIGNS ONLY
    if (row.campaign.advertisingChannelType !== 'SEARCH') continue;

    // CAMPAIGN NAME CONDITION
    var campaignName = row.campaign.name.toLowerCase();
    if (campaignName.indexOf(campaignNameCondition.toLowerCase()) === -1) continue;

    var date = row.segments.date;
    var keyword = '';
    if (row.adGroupCriterion && row.adGroupCriterion.keyword && row.adGroupCriterion.keyword.text) {
      keyword = row.adGroupCriterion.keyword.text.toLowerCase();
    }

    var impressions = Number(row.metrics.impressions || 0);
    var clicks = Number(row.metrics.clicks || 0);
    var cost = Number(row.metrics.costMicros || 0) / 1000000;

    if (!data[date]) {
      data[date] = {
        brandImpressions: 0, brandClicks: 0, brandCost: 0,
        nonBrandImpressions: 0, nonBrandClicks: 0, nonBrandCost: 0
      };
    }

    var isBrand = false;
    for (var i = 0; i < brandTerms.length; i++) {
      if (keyword.indexOf(brandTerms[i]) !== -1) { isBrand = true; break; }
    }

    if (isBrand) {
      data[date].brandImpressions += impressions;
      data[date].brandClicks += clicks;
      data[date].brandCost += cost;
    } else {
      data[date].nonBrandImpressions += impressions;
      data[date].nonBrandClicks += clicks;
      data[date].nonBrandCost += cost;
    }

    includedRows++;
  }

  Logger.log('Rows processed: ' + totalRows);
  Logger.log('Rows included: ' + includedRows);

  return data;
}


/* ============================================================
   WRITE DAILY REPORT
   ============================================================ */

function writeReport(sheet, data) {

  var dates = Object.keys(data);
  dates.sort();

  if (dates.length === 0) { Logger.log('No matching data found.'); return; }

  var existing = {};
  var lastRow = sheet.getLastRow();

  if (lastRow > 1) {
    var values = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
    var timezone = AdsApp.currentAccount().getTimeZone();
    for (var i = 0; i < values.length; i++) {
      var existingDate = values[i][0];
      if (existingDate instanceof Date) {
        existingDate = Utilities.formatDate(existingDate, timezone, 'yyyy-MM-dd');
      }
      if (existingDate) existing[String(existingDate)] = true;
    }
  }

  for (var j = 0; j < dates.length; j++) {
    var date = dates[j];
    if (existing[date]) continue;

    var d = data[date];
    var brandCTR = d.brandImpressions > 0 ? d.brandClicks / d.brandImpressions : 0;
    var nonBrandCTR = d.nonBrandImpressions > 0 ? d.nonBrandClicks / d.nonBrandImpressions : 0;

    sheet.appendRow([date, 'Brand', d.brandImpressions, d.brandClicks, brandCTR, d.brandCost]);
    sheet.appendRow([date, 'Non-Brand', d.nonBrandImpressions, d.nonBrandClicks, nonBrandCTR, d.nonBrandCost]);
    existing[date] = true;
  }
}


/* ============================================================
   BUILD DAILY TREND
   ============================================================ */

function buildTrend(report, trend) {

  trend.clear();
  trend.appendRow([
    'Date', 'Brand Impressions', 'Non-Brand Impressions',
    'Brand Clicks', 'Non-Brand Clicks', 'Brand CTR', 'Non-Brand CTR',
    'Brand Cost', 'Non-Brand Cost', 'Total Cost',
    'Brand Spend %', 'Non-Brand Spend %'
  ]);

  var lastRow = report.getLastRow();
  if (lastRow <= 1) return;

  var values = report.getRange(2, 1, lastRow - 1, 6).getValues();
  var grouped = {};
  var timezone = AdsApp.currentAccount().getTimeZone();

  for (var i = 0; i < values.length; i++) {
    var date = values[i][0];
    if (date instanceof Date) date = Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
    date = String(date);
    var type = String(values[i][1]);
    var impressions = Number(values[i][2]) || 0;
    var clicks = Number(values[i][3]) || 0;
    var cost = Number(values[i][5]) || 0;

    if (!grouped[date]) {
      grouped[date] = {
        brandImpressions: 0, nonBrandImpressions: 0,
        brandClicks: 0, nonBrandClicks: 0,
        brandCost: 0, nonBrandCost: 0
      };
    }

    if (type === 'Brand') {
      grouped[date].brandImpressions += impressions;
      grouped[date].brandClicks += clicks;
      grouped[date].brandCost += cost;
    } else if (type === 'Non-Brand') {
      grouped[date].nonBrandImpressions += impressions;
      grouped[date].nonBrandClicks += clicks;
      grouped[date].nonBrandCost += cost;
    }
  }

  var dates = Object.keys(grouped);
  dates.sort();

  for (var j = 0; j < dates.length; j++) {
    var d = grouped[dates[j]];
    var brandCTR = d.brandImpressions > 0 ? d.brandClicks / d.brandImpressions : 0;
    var nonBrandCTR = d.nonBrandImpressions > 0 ? d.nonBrandClicks / d.nonBrandImpressions : 0;
    var totalCost = d.brandCost + d.nonBrandCost;
    var brandSpendPercentage = totalCost > 0 ? d.brandCost / totalCost : 0;
    var nonBrandSpendPercentage = totalCost > 0 ? d.nonBrandCost / totalCost : 0;

    trend.appendRow([
      dates[j], d.brandImpressions, d.nonBrandImpressions,
      d.brandClicks, d.nonBrandClicks, brandCTR, nonBrandCTR,
      d.brandCost, d.nonBrandCost, totalCost,
      brandSpendPercentage, nonBrandSpendPercentage
    ]);
  }
}


/* ============================================================
   FORMAT DAILY REPORT
   ============================================================ */

function formatReport(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow <= 1) return;
  sheet.getRange(1, 1, 1, 6).setFontWeight('bold');
  sheet.getRange(2, 5, lastRow - 1, 1).setNumberFormat('0.00%');
  sheet.getRange(2, 6, lastRow - 1, 1).setNumberFormat('#,##0.00');
  sheet.autoResizeColumns(1, 6);
}


/* ============================================================
   FORMAT DAILY TREND
   ============================================================ */

function formatTrend(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow <= 1) return;
  sheet.getRange(1, 1, 1, 12).setFontWeight('bold');
  sheet.getRange(2, 6, lastRow - 1, 2).setNumberFormat('0.00%');
  sheet.getRange(2, 8, lastRow - 1, 3).setNumberFormat('#,##0.00');
  sheet.getRange(2, 11, lastRow - 1, 2).setNumberFormat('0.00%');
  sheet.autoResizeColumns(1, 12);
}

When to use this

Run this when you want to see how much of your Search spend is going to brand terms versus non-brand terms, when checking whether brand campaigns are cannibalizing non-brand budget, or as a scheduled daily report so you can watch brand spend share over time without opening the Ads UI.

Prerequisites

  • Script access at the account or MCC level (Tools & Settings > Bulk Actions > Scripts).
  • A Google Sheet you own, with the spreadsheet URL to paste into CONFIG.
  • Set CONFIG.SHEET_URL, CONFIG.CAMPAIGN_NAME_PREFIX and CONFIG.BRAND_TERMS for your account.

How to implement

  1. 1

    Create a new script

    Tools & Settings > Bulk Actions > Scripts > + New Script. Paste the full file.

  2. 2

    Configure the CONFIG block

    Set SHEET_URL to your spreadsheet, CAMPAIGN_NAME_PREFIX to the text that appears in your Search campaign names, and BRAND_TERMS to the list of your brand keywords. Set HISTORICAL_START to the earliest date you want to backfill from.

  3. 3

    Run once to backfill

    Run the script manually. The first run writes history from HISTORICAL_START through yesterday and marks the Setup sheet Status as BACKFILL_COMPLETED.

  4. 4

    Schedule it

    Set a daily schedule (clock icon). Each run after the first writes yesterday's Brand and Non-Brand rows only, and rebuilds the Daily Trend tab.

Caveats

Only Search campaigns whose name contains the campaign prefix are included; other campaign types and channels are ignored. Brand/non-brand classification is substring-based, so a keyword containing any brand term counts as brand. Backfill is driven by a single Status cell, so if the Setup sheet is deleted or the cell is cleared, the next run will backfill from HISTORICAL_START again. Cost is converted from cost_micros to the account currency.

PPC & Paid SearchWork with me