Conversions heatmap (date range)

This script fetches conversion data from a specific campaign, then writes this data into a Google Sheet. It structures the data into a heatmap showing conversions per hour of 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'; 
  var SHEET_NAME = 'INSERT-SHEET-NAME'; 
  var CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME'; 
  var startDate = new Date('2023-09-16');
  var formattedStartDate = Utilities.formatDate(startDate, 'GMT', 'yyyyMMdd');
  var endDate = new Date('2023-10-15');
  var formattedEndDate = Utilities.formatDate(endDate, 'GMT', 'yyyyMMdd');

  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, Conversions ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE CampaignName = "' + CAMPAIGN_NAME + '" ' +
    'DURING ' + formattedStartDate + ',' + formattedEndDate
  );

  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, '', '', '', '', '', '', ''];
  }

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

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

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

  var maxConversions = 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);

  range.clearFormat();

  var colorScale = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpoint('#FFFFFF') 
    .setGradientMidpointWithValue('#B0A8FE', SpreadsheetApp.InterpolationType.PERCENTILE, 50)
    .setGradientMaxpointWithValue('#6052fb', SpreadsheetApp.InterpolationType.NUMBER, maxConversions)
    .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.