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:
Open your Google Ads account.
Go to the "Tools & Settings" menu and select "Scripts".
Click the "Create" or "+" button to create a new script.
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 (Daily)', 'ConversionValue - Cost (Last 7 Days)', 'ConversionValue - Cost (Last 14 Days)', 'ConversionValue - Cost (Last 30 Days)']);
// Function to fetch data for a specific duration
function fetchData(duration) {
var report = AdsApp.report(
`SELECT Date, Impressions, Clicks, Cost, Conversions, ConversionValue
FROM ACCOUNT_PERFORMANCE_REPORT
DURING ${duration}`
);
return report.rows();
}
// Fetch data for the last 30 days
var last30DaysRows = fetchData('LAST_30_DAYS');
// Convert rows to arrays
function convertRowsToArray(rows) {
var data = [];
while (rows.hasNext()) {
var row = rows.next();
data.push(row);
}
return data;
}
var last30DaysData = convertRowsToArray(last30DaysRows);
// Create a map to store data by date
var dataMap = {};
for (var i = 0; i < last30DaysData.length; i++) {
var row = last30DaysData[i];
var date = row['Date'];
dataMap[date] = {
impressions: row['Impressions'],
clicks: row['Clicks'],
cost: row['Cost'],
conversions: row['Conversions'],
conversionValue: row['ConversionValue']
};
}
// Write data to the sheet for columns A-J
function writeData(sheet, dataMap) {
for (var date in dataMap) {
var data = dataMap[date];
var impressions = data.impressions;
var clicks = data.clicks;
var cost = data.cost;
var conversions = data.conversions;
var conversionValue = data.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';
// Write data for columns A-J
sheet.appendRow([
date,
impressions,
clicks,
ctr,
conversions,
conversionRate,
cost,
costPerClick,
costPerConversion,
conversionValue,
'', '', '', '' // Placeholders for columns K-N
]);
}
}
writeData(sheet, dataMap);
// Calculate and write data for columns K-N
function updateData(sheet, dataMap) {
var lastRow = sheet.getLastRow();
for (var i = 3; i <= lastRow; i++) {
var date = sheet.getRange(i, 1).getValue();
var conversionValueMinusCostDaily = calculateValueMinusCost(date, dataMap, 1);
var conversionValueMinusCostLast7Days = calculateValueMinusCost(date, dataMap, 7);
var conversionValueMinusCostLast14Days = calculateValueMinusCost(date, dataMap, 14);
var conversionValueMinusCostLast30Days = calculateValueMinusCost(date, dataMap, 30);
sheet.getRange(i, 11).setValue(conversionValueMinusCostDaily);
sheet.getRange(i, 12).setValue(conversionValueMinusCostLast7Days);
sheet.getRange(i, 13).setValue(conversionValueMinusCostLast14Days);
sheet.getRange(i, 14).setValue(conversionValueMinusCostLast30Days);
}
}
// Calculate ConversionValue - Cost for a given date and duration
function calculateValueMinusCost(date, dataMap, days) {
var targetDate = new Date(date);
var totalConversionValue = 0;
var totalCost = 0;
for (var i = 0; i < days; i++) {
var currentDate = new Date(targetDate);
currentDate.setDate(targetDate.getDate() - i);
var currentDateString = Utilities.formatDate(currentDate, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
if (dataMap[currentDateString]) {
totalConversionValue += parseFloat(dataMap[currentDateString].conversionValue);
totalCost += parseFloat(dataMap[currentDateString].cost);
}
}
return totalConversionValue - totalCost;
}
updateData(sheet, dataMap);
// Insert a new row for totals
sheet.insertRowAfter(1);
// Set formulas for the 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("=G2/C2");
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("=SUM(K3:K" + lastRow + ")"); // ConversionValue - Cost (Daily)
sheet.getRange("L2").setFormula("=SUM(L3:L" + lastRow + ")"); // ConversionValue - Cost (Last 7 Days)
sheet.getRange("M2").setFormula("=SUM(M3:M" + lastRow + ")"); // ConversionValue - Cost (Last 14 Days)
sheet.getRange("N2").setFormula("=SUM(N3:N" + lastRow + ")"); // ConversionValue - Cost (Last 30 Days)
// 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)
costColumn.setNumberFormat("$0.00");
cpcColumn.setNumberFormat("$0.00");
cpcvColumn.setNumberFormat("$0.00");
conversionValueColumn.setNumberFormat("$0.00");
}
function finalize(results) {
Logger.log('Results: ' + results);
}
Add the spreadsheet ID above.
Save the script.
Review and authorize the script to access your Google Ads and Google Sheets accounts.
Run the script manually for the first time to populate the initial values in the spreadsheet.
(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.
