Conversion rate heatmap (date range)

This script fetches conversion rates from a specific campaign, then writes this data into a spreadsheet. It structures the data into a heatmap showing conversion 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'; 
  var SHEET_NAME = 'INSERT-SHEET_NAME'; 
  var CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME'; 

  var startDate = new Date('2023-09-15');
  var formattedStartDate = Utilities.formatDate(startDate, 'GMT', 'yyyyMMdd');

  var endDate = new Date('2023-10-01');
  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, Clicks ' +
    '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 = parseFloat(row['Conversions']);
    var clicks = parseFloat(row['Clicks']);
    var conversionRate = clicks ? (conversions / clicks) * 100 : 0;

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

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

  var maxConversionRate = Math.max.apply(Math, Object.values(data).map(function (row) { return Math.max.apply(Math, row.slice(1).map(function (rate) { return parseFloat(rate) || 0; })); }));
  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, maxConversionRate)
    .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.