CTR heatmap (date range)

This script fetches CTR data from a specific campaign, then writes this data into a spreadsheet. It structures the data into a heatmap showing click through rates per hour for each day.

To set up this script, follow these steps:

1. Open your Google Ads account.

2. Go to the "Tools & Settings" menu and select "Scripts".

3. Click the "Create" or "+" button to create a new script.

4. Copy and paste the following script.

function main() {
  var SPREADSHEET_URL = 'INSERT-SPREADSHEET-URL'; // Replace with your Spreadsheet's URL
  var SHEET_NAME = 'INSERT-SHEET-NAME'; // Replace with your sheet name
  var CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME'; // Replace with your campaign name
  var dateRange = {
    startDate: '20230701',  // Replace with the start date
    endDate: '20230709'     // Replace with the end date
  };

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);
  sheet.clearContents();
  sheet.appendRow(['HourOfDay', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']);

  var report = AdsApp.report(
    'SELECT DayOfWeek, HourOfDay, Clicks, Impressions ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE CampaignName = "' + CAMPAIGN_NAME + '" ' +
    'DURING ' + dateRange.startDate + ',' + dateRange.endDate
  );

  var rows = report.rows();
  var data = {};

  function convertTo12Hour(time) {
    return time == 0 ? '12:00 AM' : (time < 12 ? time + ':00 AM' : (time == 12 ? '12:00 PM' : time - 12 + ':00 PM'));
  }

  for (var i = 0; i < 24; i++) {
    var time = convertTo12Hour(i);
    data[time] = [time, 0, 0, 0, 0, 0, 0, 0];
  }

  while (rows.hasNext()) {
    var row = rows.next();
    var dayOfWeek = row['DayOfWeek'];
    var hourOfDay = convertTo12Hour(parseInt(row['HourOfDay']));
    var impressions = parseInt(row['Impressions']);
    var clicks = parseInt(row['Clicks']);
    var ctr = impressions === 0 ? 0 : clicks / impressions;
    var columnIndex = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'].indexOf(dayOfWeek) + 1;
    data[hourOfDay][columnIndex] = ctr;
  }

  for (var hourOfDay in data) {
    sheet.appendRow(data[hourOfDay]);
  }

  // Format the CTR cells as percentages
  sheet.getRange(2, 2, 24, 7).setNumberFormat("0%");

  var maxCtr = Math.max.apply(Math, Object.values(data).map(function(row) { return Math.max.apply(Math, row.slice(1)); }));
  var range = sheet.getRange(2, 2, 24, 7);  // Adjust the range if needed

  range.clearFormat();

  var colorScale = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpoint('#FFFFFF')  // White for min value
    .setGradientMidpointWithValue('#B0A8FE', SpreadsheetApp.InterpolationType.PERCENTILE, 50)  // Yellow for median
    .setGradientMaxpointWithValue('#6052fb', SpreadsheetApp.InterpolationType.NUMBER, maxCtr)  // Red for max value
    .setRanges([range])
    .build();

  var rules = sheet.getConditionalFormatRules();
  rules.push(colorScale);
  sheet.setConditionalFormatRules(rules);
}

1. Replace the spreadsheet URL, sheet name, dates, and campaign name.

2. Save the script.

3. Review and authorize the script to access your Google Ads and Google Sheets accounts.

4. Run the script manually for the first time to populate the initial values in the spreadsheet.

5. (Optional) Schedule the script to run on a specific cadence (ie: daily, weekly, monthly, etc.)

Note: Ensure that you have the necessary permissions to access and modify the target Google Spreadsheet.