Automating Google Ads Campaign Pacing with Google Ads Script

In online advertising, monitoring campaign performance and pacing is essential to ensure efficient spending and achieve desired goals. However, it can be time-consuming to gather this data manually. To streamline the process, this blog post will present a step-by-step guide on how to create a script that automates the collection and visualization of campaign pacing data with separate start and end dates. We will use Google Sheets and Google Ads scripting to accomplish this task.

Prerequisites:

  1. Basic knowledge of Google Sheets and Google Ads.
  2. A Google Ads account with active campaigns.
  3. Familiarity with JavaScript programming.

Step 1: Setting up the Google Sheet

  1. Open Google Sheets and create a new spreadsheet.
  2. Rename the default sheet to a meaningful name (e.g., “Pacing Data”).
  3. In cell A1, enter the following headers: “Campaign Name,” “Start Date,” “End Date,” “Cost,” “Status,” “Yesterday’s Cost,” “Campaign Type.”
  4. Save the Google Sheet and copy its URL.

Step 2: Accessing the Script Editor

  1. In the Google Sheet, click on “Extensions” in the top menu.
  2. Select “Apps Script” to open the Script Editor.

Step 3: Writing the Google Ads Script Copy and paste the provided code into the Script Editor.

Step 4: Modifying the Script

  1. Replace ‘Spreadsheet URL’ in the script with the URL of the Google Sheet you created earlier.
  2. Adjust the ‘customStartDate’ variable to your preferred custom start date.
  3. (Optional) Modify the ‘realendDate’ variable to specify a different end date if required.

Step 5: Running the Script

  1. Save the script and close the Script Editor.
  2. Go back to your Google Sheet.
  3. In the top menu, click on “Add-ons” > “Your add-ons” > Select the script you just created.
  4. Click on “Run” > “main” to execute the script.

Step 6: Understanding the Script The script uses Google Ads scripting to fetch campaign performance data within the custom date range specified. It then writes this data to the Google Sheet, along with additional information, including the campaign status (live or ended), yesterday’s cost, and campaign type.

Step 7: Automating the Script

  1. To automate the script, click on “Edit” in the Script Editor.
  2. Go to “Triggers” (clock icon) on the left sidebar.
  3. Click on “Add trigger” in the bottom right corner.
  4. Choose the function “main” and set the trigger to run “Time-driven,” e.g., daily or weekly, depending on your needs.
  5. Save the trigger settings.

Code

function main() {
// Replace ‘Spreadsheet URL’ with the URL of your Google Sheet
var spreadsheetUrl = ‘

function main() {
  // Replace 'Spreadsheet URL' with the URL of your Google Sheet
  var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1E9nWMaplqcuJHBANbWfLPVm_7gNMxIf_UsBwIVn2g0k/edit#gid=1792801411';
  var sheetName = 'Pacing ( Diff. Date Range)';
  var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(sheetName);

  // Clear the sheet from A2 before writing new data
  sheet.getRange('A2:G5').clearContent();

  // Custom start date is set to '2023-05-22'
  var customStartDate = new Date("2023-05-22"); // Replace with your custom start date

  // End date is set to yesterday's date
  var endDate = new Date(); // Yesterday's date will be used as the end date
  endDate.setDate(endDate.getDate() - 1); // Subtract 1 day from today

  // Format the custom start date in 'yyyyMMdd' format
  var customStartDateStr = Utilities.formatDate(customStartDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var year = customStartDateStr.substring(0, 4);
  var month = customStartDateStr.substring(4, 6);
  var day = customStartDateStr.substring(6, 8);
  var convertedStartDateStr = year + '-' + month + '-' + day;

  // Format the end date in 'yyyyMMdd' format
  var endDateStr = Utilities.formatDate(endDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  // A specific end date '2023-07-31' is set and formatted in 'yyyyMMdd' format
  var realendDate = new Date("2023-07-31");
  var customrealendDate = Utilities.formatDate(realendDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  // Query Google Ads to fetch campaign performance data
  var report = AdsApp.report(
    "SELECT CampaignName, StartDate, EndDate, Cost, AdvertisingChannelType " +
    "FROM CAMPAIGN_PERFORMANCE_REPORT " +
    "WHERE CampaignName CONTAINS_IGNORE_CASE 'Brand MoneySweetspot' " +
    "DURING " + customStartDateStr + "," + endDateStr
  );

  var rows = report.rows();
  var data = [];

  while (rows.hasNext()) {
    var row = rows.next();
    var campaignName = row['CampaignName'];
    var startDate = row['StartDate'];
    var endDate = row['EndDate'];
    var cost = row['Cost'];
    var endDateTime = new Date(endDate.replace(/-/g, '/')).getTime();
    var todayTime = new Date().getTime();
    var status = (endDateTime < todayTime) ? 'Ended' : 'Live';
    var campaignType = row['AdvertisingChannelType'];

    // Get today's spend for the campaign
    var query = "SELECT Cost " +
      "FROM CAMPAIGN_PERFORMANCE_REPORT " +
      "WHERE CampaignName = '" + campaignName.replace(/'/g, "\\'") + "' " +
      "DURING TODAY";

    var reportToday = AdsApp.report(query);
    var rowsToday = reportToday.rows();
    var costToday = 0;
    if (rowsToday.hasNext()) {
      var rowToday = rowsToday.next();
      costToday = rowToday['Cost'];
    }

    // Get Yesterday's spend for the campaign
    var query1 = "SELECT Cost " +
      "FROM CAMPAIGN_PERFORMANCE_REPORT " +
      "WHERE CampaignName = '" + campaignName.replace(/'/g, "\\'") + "' " +
      "DURING YESTERDAY";

    var reportYesterday = AdsApp.report(query1);
    var rowsYesterday = reportYesterday.rows();
    var costYesterday = 0;
    if (rowsYesterday.hasNext()) {
      var rowYesterday = rowsYesterday.next();
      costYesterday = rowYesterday['Cost'];
    }

    // Push the collected data into the 'data' array
    data.push([campaignName, convertedStartDateStr, endDate, cost, status, costYesterday, campaignType]);
  }

  // Write the data to the sheet starting from A2
  var numRows = data.length;
  var numCols = data[0].length;
  sheet.getRange(2, 1, numRows, numCols).setValues(data);
}

Code Explanation:

  1. The script starts by defining the main() function, which serves as the entry point for the script execution.
  2. The spreadsheetUrl variable holds the URL of the Google Sheet where the campaign data will be written. The sheet name is stored in the sheetName variable.
  3. The script opens the Google Sheet specified in spreadsheetUrl and selects the sheet with the name given in sheetName.
  4. The range ‘A2:G5’ is cleared using sheet.getRange('A2:G5').clearContent() to ensure that any existing data is removed before writing new data.
  5. The customStartDate variable is set to the custom start date for the campaign, which is ‘2023-05-22’ in this case.
  6. The endDate variable is set to yesterday’s date. It will be used as the end date to get campaign performance data up until the previous day.
  7. The customStartDate and endDate are formatted into ‘yyyyMMdd’ format using Utilities.formatDate() and stored in customStartDateStr and endDateStr, respectively.
  8. The realendDate variable is set to ‘2023-07-31’ and formatted into ‘yyyyMMdd’ format, but it’s not used later in the script.
  9. The script uses the AdsApp.report() method to fetch campaign performance data from the Google Ads account. The query selects the campaign name, start date, end date, cost, and advertising channel type for campaigns containing the phrase “Brand MoneySweetspot” in their names and within the specified custom date range.
  10. The script iterates through the rows of the fetched report data and extracts relevant information such as campaign name, start date, end date, cost, and advertising channel type.
  11. It calculates the campaign status (live or ended) based on the end date compared to the current date.
  12. The script then queries Google Ads again to fetch today’s and yesterday’s costs for each campaign.
  13. The collected data (campaign name, converted start date, end date, cost, status, yesterday’s cost, and campaign type) is pushed into the data array.
  14. Finally, the script writes the collected data to the Google Sheet starting from cell A2 using sheet.getRange(2, 1, numRows, numCols).setValues(data).

The script automates the process of fetching campaign performance data within a custom date range, allowing advertisers to track pacing efficiently and make data-driven decisions to optimize their advertising efforts.

With this automated campaign pacing script, you can effortlessly track the performance of your Google Ads campaigns within custom date ranges. The data will be regularly updated in the Google Sheet, providing you with valuable insights into the campaign’s progress and spend. By automating this process, you can save time and make data-driven decisions to optimize your advertising efforts effectively.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top