Account performance (last 90 days)

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 spreadsheetUrl = 'INSERT-SPREADSHEET-URL';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName('INSERT-SHEET-NAME');
  if (!sheet) {
    sheet = spreadsheet.insertSheet('INSERT-SHEET-NAME', 0);
  }
  sheet.clear(); // Clear existing content

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

  // Bold the header row
  sheet.getRange('A1:J1').setFontWeight('bold');

  // 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 without specific campaign names
  var query = 'SELECT CampaignName, Date, Impressions, Clicks, Cost, Conversions, Ctr, AverageCpc, ConversionRate, ConversionValue ' +
              'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
              '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, 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']);
    data[date][9] = data[date][8] / data[date][3]; // ROAS
  }

  // 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)', '=SUM(I3:I)/SUM(D3:D)'];
  sheet.getRange('A2:J2').setValues([summaryFormulas]);
}

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.