Campaign performance (last 90 days)

This script fetches performance data from a specific campaign, then writes this data into a spreadsheet on scheduled cadence. It structures the data to show campaign performance on a day-to-day basis for the specified number of days. The script extracts the following metrics: Date, impressions, clicks, cost, conversions, CTR's, average CPC, conversion rate, and conversion value. 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 spreadsheet URL
    var SHEET_NAME = 'INSERT-SHEET-NAME'; // Specify sheet name 
    var TARGET_CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME'; // Specify campaign name (case sensitive) 

    var currentDate = new Date();
    var ninetyDaysAgo = new Date(currentDate.getTime() - (90 * 24 * 60 * 60 * 1000));  // 90 days before the current date. Change this number if you want to go back further. 
    var formattedCurrentDate = Utilities.formatDate(currentDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    var formattedNinetyDaysAgo = Utilities.formatDate(ninetyDaysAgo, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    
    var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    var sheet = spreadsheet.getSheetByName(SHEET_NAME);

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

    // Clear existing content from the sheet
    sheet.clear();

    // After clearing, append headers
    var headers = ['Date', 'CampaignId', 'CampaignName', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Ctr', 'AverageCpc', 'ConversionRate', 'ConversionValue'];
    sheet.appendRow(headers);

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

    var report = AdsApp.report(
        "SELECT Date, CampaignId, CampaignName, Impressions, Clicks, Cost, Conversions, Ctr, AverageCpc, ConversionRate, ConversionValue " +
        "FROM CAMPAIGN_PERFORMANCE_REPORT " +
        "WHERE Impressions > 0 AND CampaignName = '" + TARGET_CAMPAIGN_NAME + "'" +
        "DURING " + formattedNinetyDaysAgo + "," + formattedCurrentDate
    );

    var rows = report.rows();
    while (rows.hasNext()) {
        var row = rows.next();
        sheet.appendRow([
            row['Date'],
            row['CampaignId'], 
            row['CampaignName'], 
            row['Impressions'], 
            row['Clicks'], 
            row['Cost'], 
            row['Conversions'], 
            row['Ctr'], 
            row['AverageCpc'], 
            row['ConversionRate'],
            row['ConversionValue']
        ]);
    }
}

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.