Multiple campaign performance (last 90 days)

This script fetches performance data from multiple campaigns, then writes this data into a spreadsheet on scheduled cadence. It structures the data to show campaign by 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 spreadsheetUrl = 'INSERT-SPREADSHEET-URL';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName('INSERT-SHEET-NAME'); // Make sure your sheet name matches
  if (!sheet) {
    sheet = spreadsheet.insertSheet('INSERT-SHEET-NAME', 0); // Create the sheet if it does not exist
  }
  sheet.clear(); // Clear existing content

  // Define the headers
  var headers = ['Date', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'CTR', 'Average CPC', 'Conversion Rate', 'Conversion Value'];
  sheet.appendRow(headers);

  // Specify your campaign names here
  var campaignNames = ['Campaign 1', 'Campaign 2']; // Add your campaign names
  
  // Adjusting the date range to go back 90 days
  var today = new Date();
  var ninetyDaysAgo = new Date();
  ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90);
  var dateString = Utilities.formatDate(ninetyDaysAgo, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd') + ',' + 
                   Utilities.formatDate(today, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  // Construct the query with the updated date range
  var query = 'SELECT CampaignName, Date, Impressions, Clicks, Cost, Conversions, Ctr, AverageCpc, ConversionRate, ConversionValue ' +
              'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
              'WHERE CampaignName IN [' + campaignNames.map(function(name) { return '"' + name + '"'; }).join(',') + '] ' +
              'DURING ' + dateString;

  var report = AdsApp.report(query);
  var rows = report.rows();

  var data = {};
  while (rows.hasNext()) {
    var row = rows.next();
    var date = row['Date'];

    if (!data[date]) {
      data[date] = [date, 0, 0, 0, 0, 0, 0, 0, 0]; // Initialize the array if the date doesn't exist
    }

    // Aggregate the data
    data[date][1] += parseInt(row['Impressions']);
    data[date][2] += parseInt(row['Clicks']);
    data[date][3] += parseFloat(row['Cost']);
    data[date][4] += parseInt(row['Conversions']);
    data[date][5] = data[date][2] / data[date][1]; // CTR
    data[date][6] = data[date][3] / data[date][2]; // Average CPC
    data[date][7] = data[date][4] / data[date][2]; // Conversion Rate
    data[date][8] += parseFloat(row['ConversionValue']);
    // Note: Calculations for CTR, Average CPC, and Conversion Rate are removed because they will be calculated in spreadsheet formulas
  }

  // Write the aggregated data to the sheet
  for (var key in data) {
    sheet.appendRow(data[key]);
  }

  // Adding a summary row at the top
  sheet.insertRowBefore(2); // Insert a new row for summaries after the header
  var summaryFormulas = ['All Dates', '=SUM(B3:B)', '=SUM(C3:C)', '=SUM(D3:D)', '=SUM(E3:E)', '=AVERAGE(F3:F)', '=AVERAGE(G3:G)', '=AVERAGE(H3:H)', '=SUM(I3:I)'];
  sheet.getRange('A2:I2').setValues([summaryFormulas]);
}

1. Replace the spreadsheet URL, sheet name and campaign names.

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.