MCC account performance export

This script fetches all accounts managed by the MCC (My Client Center) account and retrieves daily performance metrics for each account for the past 30 days. It stores the results in a specified Google Sheets document, creating a separate sheet for each account. Each sheet contains columns for Date, Impressions, Clicks, Cost, and Conversions. The script runs the data retrieval process for each account in parallel, improving efficiency. This organized structure allows for easy access and analysis of performance data across multiple accounts in one place.

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() {
  // Spreadsheet where data will be written
  var spreadsheetId = 'INSERT-SPREADSHEET-ID';

  // Store the spreadsheet ID in the script properties
  PropertiesService.getScriptProperties().setProperty('SPREADSHEET_ID', spreadsheetId);

  // Get the list of accounts under the MCC
  var accountSelector = MccApp.accounts();

  accountSelector.executeInParallel('processClientAccount', 'finalize');

  Logger.log('Script finished running.');
}

function processClientAccount() {
  var spreadsheetId = PropertiesService.getScriptProperties().getProperty('SPREADSHEET_ID');
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);

  // Get the current account
  var account = AdsApp.currentAccount();
  var accountName = account.getName();
  var accountId = account.getCustomerId();

  // Create a sheet for the account if it doesn't exist, else clear existing sheet
  var sheet = spreadsheet.getSheetByName(accountName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(accountName);
  } else {
    // Clear all existing data
    sheet.clear();
  }

  // Set up headers
  sheet.appendRow(['Date', 'Impressions', 'Clicks', 'CTR', 'Conversions', 'Conversion Rate', 'Cost', 'Cost per click', 'Cost per conversion', 'ConversionValue', 'ConversionValue - Cost', 'Conversion Value - Cost (Last 7 Days)']);

  // Get the performance report for the past 30 days
  var report = AdsApp.report(
    'SELECT Date, Impressions, Clicks, Cost, Conversions, ConversionValue ' +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING LAST_30_DAYS'
  );

  var rows = report.rows();

  // Write data to the sheet
  while (rows.hasNext()) {
    var row = rows.next();
    var impressions = row['Impressions'];
    var clicks = row['Clicks'];
    var cost = row['Cost'];
    var conversions = row['Conversions'];
    var conversionValue = row['ConversionValue'];

    // Calculate derived metrics (assuming clicks and impressions are not zero)
    var ctr = (clicks > 0) ? (clicks / impressions * 100).toFixed(2) + '%' : '0.00%';
    var conversionRate = (conversions > 0) ? (conversions / clicks * 100).toFixed(2) + '%' : '0.00%';
    var costPerClick = (clicks > 0) ? (cost / clicks).toFixed(2) : '$0.00';
    var costPerConversion = (conversions > 0) ? (cost / conversions).toFixed(2) : '$0.00';
    var conversionValueMinusCost = conversionValue - cost;

    // Write data including derived metrics
    sheet.appendRow([
      row['Date'],
      impressions,
      clicks,
      ctr,
      conversions,
      conversionRate,
      cost,
      costPerClick,
      costPerConversion,
      conversionValue,
      conversionValueMinusCost
    ]);
  }

  // Insert a new row for totals
  sheet.insertRowAfter(1);

  // Set formulas for total row
  var lastRow = sheet.getLastRow();
  sheet.getRange("B2").setFormula("=SUM(B3:B" + lastRow + ")");
  sheet.getRange("C2").setFormula("=SUM(C3:C" + lastRow + ")");
  sheet.getRange("D2").setFormula("=C2/B2");
  sheet.getRange("D2").setNumberFormat("0.00%"); // Format as percentage
  sheet.getRange("E2").setFormula("=SUM(E3:E" + lastRow + ")");
  sheet.getRange("F2").setFormula("=E2/C2");
  sheet.getRange("F2").setNumberFormat("0.00%"); // Format as percentage
  sheet.getRange("G2").setFormula("=SUM(G3:G" + lastRow + ")");
  sheet.getRange("H2").setFormula("=G3/C3");
  sheet.getRange("I2").setFormula("=G2/E2"); // Corrected formula
  sheet.getRange("I2").setNumberFormat("0.00%"); // Format as percentage
  sheet.getRange("J2").setFormula("=SUM(J3:J" + lastRow + ")");
  sheet.getRange("K2").setFormula("=J2-G2");

  // Calculate and set values for the new column
  for (var i = 9; i <= lastRow; i++) { // Start from row 9
    var sumRange = sheet.getRange(Math.max(3, i - 6), 11, Math.min(i - 2, 7), 1); // Adjust column index if needed
    var sum = sumRange.getValues().flat().reduce((a, b) => a + b, 0);
    sheet.getRange(i, 12).setValue(sum);
  }

  // Format cost and conversion value columns as currency
  var costColumn = sheet.getRange(2, 7, sheet.getLastRow() - 1, 1); // Column G (Cost)
  var cpcColumn = sheet.getRange(2, 8, sheet.getLastRow() - 1, 1); // Column H (Cost per click)
  var cpcvColumn = sheet.getRange(2, 9, sheet.getLastRow() - 1, 1); // Column I (Cost per conversion)
  var conversionValueColumn = sheet.getRange(2, 10, sheet.getLastRow() - 1, 1); // Column J (Conversion Value)
  var conversionValueMinusCostColumn = sheet.getRange(2, 11, sheet.getLastRow() - 1, 1); // Column K (Conversion Value - Cost)

  costColumn.setNumberFormat("$0.00");
  cpcColumn.setNumberFormat("$0.00");
  cpcvColumn.setNumberFormat("$0.00");
  conversionValueColumn.setNumberFormat("$0.00");
  conversionValueMinusCostColumn.setNumberFormat("$0.00");
}

function finalize(results) {
  Logger.log('Results: ' + results);
}

1. Add the spreadsheet ID above.

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.