Account performance (date range)

Works in combination of account performance (daily export)

This script fetches performance data from a specific account, then writes this data into a spreadsheet on scheduled cadence. It structures the data to show your account performance on a day to day basis.

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'; // specify the spreadsheet URL
    var SHEET_NAME = 'INSERT-SHEET-NAME'; // specify the sheet name
    var START_DATE = '20230901'; // start date in yyyymmdd format
    var END_DATE = '20240817'; // end date in yyyymmdd format

    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName(SHEET_NAME);

    if (!sheet) {
        Logger.log("Error: Sheet named specified above not found in the spreadsheet.");
        return;
    }

    // If the sheet is empty, append headers and make them bold
    if (sheet.getLastRow() == 0) {
        var headers = ['Date', 'AccountDescriptiveName', 'Impressions', 'Clicks', 'Ctr', 'AverageCpc', 'Cost', 'CostPerConversion', 'ConversionRate', 'Conversions', 'ConversionValue', 'ROAS'];
        sheet.appendRow(headers);

        // Bold the headers
        var headersRange = sheet.getRange(1, 1, 1, headers.length);
        headersRange.setFontWeight('bold');
    }

    var report = AdsApp.report(
        "SELECT Date, AccountDescriptiveName, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, ConversionValue " +
        "FROM CAMPAIGN_PERFORMANCE_REPORT " +
        "WHERE Impressions > 0 " +
        "DURING " + START_DATE + "," + END_DATE
    );

    var aggregatedData = {};

    var rows = report.rows();
    while (rows.hasNext()) {
        var row = rows.next();
        var date = row['Date'] + '|' + row['AccountDescriptiveName']; // Using a combination of Date and AccountDescriptiveName as the key

        // If the date is not in aggregatedData, initialize it
        if (!aggregatedData[date]) {
            aggregatedData[date] = {
                AccountDescriptiveName: row['AccountDescriptiveName'],
                Impressions: 0,
                Clicks: 0,
                Cost: 0,
                Conversions: 0,
                ConversionValue: 0
            };
        }

        // Accumulate values for each metric
        aggregatedData[date].Impressions += parseFloat(row['Impressions']);
        aggregatedData[date].Clicks += parseFloat(row['Clicks']);
        aggregatedData[date].Cost += parseFloat(row['Cost']);
        aggregatedData[date].Conversions += parseFloat(row['Conversions']);
        aggregatedData[date].ConversionValue += parseFloat(row['ConversionValue']);
    }

    // Write the aggregated data to the sheet
    for (var dateKey in aggregatedData) {
        var data = aggregatedData[dateKey];
        var dateParts = dateKey.split('|'); // Splitting the combined key to get the Date and AccountDescriptiveName separately
        var ctr = data.Clicks === 0 ? 0 : data.Clicks / data.Impressions;
        var averageCpc = data.Clicks === 0 ? 0 : data.Cost / data.Clicks;
        var conversionRate = data.Clicks === 0 ? 0 : data.Conversions / data.Clicks;
        var costPerConversion = data.Conversions === 0 ? 0 : data.Cost / data.Conversions;
        var roas = data.ConversionValue - data.Cost;

        sheet.appendRow([
            dateParts[0], // Date
            data.AccountDescriptiveName,
            data.Impressions, 
            data.Clicks, 
            ctr, // CTR
            averageCpc, // Average CPC
            data.Cost, 
            costPerConversion, // Cost Per Conversion
            conversionRate, // Conversion Rate
            data.Conversions,
            data.ConversionValue,
            roas // ROAS (Conversion Value - Cost)
        ]);
    }
}

1. Replace the spreadsheet URL, sheet name, and dates.

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.