Account performance (daily export)

Works in combination of account performance (date range)

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. You can set this up and run it daily to grab performance from the previous 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'; // specify your spreadsheet URL
    var SHEET_NAME = 'INSERT-SHEET-NAME'; // specify your sheet name

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

    if (!sheet) {
        Logger.log("Error: Sheet name not found.");
        return;
    }

    // Set headers if sheet is empty
    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');
    }

    function formatDateToHyphenFormat(dateString) {
        return dateString.slice(0, 4) + '-' + dateString.slice(4, 6) + '-' + dateString.slice(6, 8);
    }

    var today = new Date();
    var yesterday = new Date(today);
    yesterday.setDate(today.getDate() - 1);

    var formattedYesterday = Utilities.formatDate(yesterday, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    var hyphenFormattedYesterday = formatDateToHyphenFormat(formattedYesterday);

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

    var aggregatedData = {
        AccountDescriptiveName: AdsApp.currentAccount().getName(),
        Impressions: 0,
        Clicks: 0,
        Cost: 0,
        Conversions: 0,
        ConversionValue: 0
    };

    var rows = report.rows();
    while (rows.hasNext()) {
        var row = rows.next();
        aggregatedData.Impressions += parseFloat(row['Impressions']);
        aggregatedData.Clicks += parseFloat(row['Clicks']);
        aggregatedData.Cost += parseFloat(row['Cost']);
        aggregatedData.Conversions += parseFloat(row['Conversions']);
        aggregatedData.ConversionValue += parseFloat(row['ConversionValue']);
    }

    // Derived metrics
    var ctr = aggregatedData.Impressions === 0 ? 0 : aggregatedData.Clicks / aggregatedData.Impressions;
    var avgCpc = aggregatedData.Clicks === 0 ? 0 : aggregatedData.Cost / aggregatedData.Clicks;
    var costPerConversion = aggregatedData.Conversions === 0 ? 0 : aggregatedData.Cost / aggregatedData.Conversions;
    var conversionRate = aggregatedData.Clicks === 0 ? 0 : aggregatedData.Conversions / aggregatedData.Clicks;
    var roas = aggregatedData.Cost === 0 ? 0 : aggregatedData.ConversionValue / aggregatedData.Cost;

    // Append row to sheet
    sheet.appendRow([
        hyphenFormattedYesterday,
        aggregatedData.AccountDescriptiveName,
        aggregatedData.Impressions,
        aggregatedData.Clicks,
        ctr,
        avgCpc,
        aggregatedData.Cost,
        costPerConversion,
        conversionRate,
        aggregatedData.Conversions,
        aggregatedData.ConversionValue,
        roas
    ]);
}

1. Replace the spreadsheet URL, and sheet 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.