Conversions heatmap (last 30 days)

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 today = new Date();
  var endDate = Utilities.formatDate(today, 'GMT', 'yyyyMMdd');
  var startDate = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);
  var formattedStartDate = Utilities.formatDate(startDate, '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 ' + // Updated to fetch Conversions
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE CampaignName = "' + CAMPAIGN_NAME + '" ' +
    'DURING ' + formattedStartDate + ',' + 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, '', '', '', '', '', '', ''];
  }

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

    var columnIndex = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'].indexOf(dayOfWeek) + 1;
    data[hourOfDay][columnIndex] = conversions; // Updated to set 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)); })); // Updated to use maxConversions
  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) // Updated to use maxConversions
    .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.