CPC heatmap (date range)

This script fetches cost per click data from a specific campaign, then writes this data into a spreadsheet. It structures the data into a heatmap showing cpc 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 START_DATE = 'YYYYMMDD'; // Replace with the start date in YYYYMMDD format
  var END_DATE = 'YYYYMMDD'; // Replace with the end date in YYYYMMDD format

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);

  // Optional: Clear the contents of the sheet before running the script
  sheet.clearContents();

  // Define the header row
  sheet.appendRow(['HourOfDay', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']);

  // Build and run the report
  var report = AdsApp.report(
    'SELECT DayOfWeek, HourOfDay, AverageCpc ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE CampaignName = "' + CAMPAIGN_NAME + '" ' +
    'DURING ' + START_DATE + ',' + END_DATE
  );

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

  // Convert 24-hour time format to 12-hour format
  function convertTo12Hour(time) {
    return time == 0 ? '12:00 AM' : (time < 12 ? time + ':00 AM' : (time == 12 ? '12:00 PM' : time - 12 + ':00 PM'));
  }

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

  while (rows.hasNext()) {
    var row = rows.next();
    var dayOfWeek = row['DayOfWeek'];
    var hourOfDay = convertTo12Hour(parseInt(row['HourOfDay']));
    var averageCpc = row['AverageCpc'];

    // Update data
    var columnIndex = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'].indexOf(dayOfWeek) + 1;
    data[hourOfDay][columnIndex] = averageCpc;
  }

  // Append the data to the sheet
  for (var hourOfDay in data) {
    sheet.appendRow(data[hourOfDay]);
  }

  // Set color scales (pseudo-heatmap) for Average CPC
  var maxCpc = 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

  // Clear any previous conditional formatting rules
  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, maxCpc) // Red for max value
    .setRanges([range])
    .build();

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

1. Replace the spreadsheet URL, sheet name, 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.