Making Changes to AdWords From Google Sheet Data

[Kelly Konechny]

Hello - 

We have created a script that will pull current CPAs by campaign and device type and then calculate an adjustment to the device bid based on a target set. All of this data is pulled into a Google Sheet via the script now.

We would like to make some changes to have the calculated CPA updated in the AdWords account from the Google Sheet automatically. Can you please comment on how we would go about this? As well, if you see any optimizations in the current script we would love to fix them :)

function main() {

  

  //Main variables & MCC Selector

  

  var cid = “XXX-XXX-XXXX”

  var target_cpa = XXX

  var date_range = "LAST_14_DAYS"

  

  var mccAccount = AdWordsApp.currentAccount();

  var childAccounts = MccApp.accounts().withIds([cid]).get();

  var childAccount = childAccounts.next();

  MccApp.select(childAccount);  

  

  

  // Date and Timestamp

  

  var now = new Date();

  

  //Campaign criteria, iterators and selectors

  

  var campaignIterator = AdWordsApp

  .campaigns()

  .forDateRange(date_range)

  .orderBy("Conversions DESC")

  .withCondition("Status = ENABLED")

  .withCondition("Conversions >0")

  .get();

  

  while (campaignIterator.hasNext()) {

  var campaign = campaignIterator.next();

    

  var platformIterator = campaign.targeting().platforms().forDateRange(date_range).orderBy("Conversions DESC").withCondition("Conversions >0").get();

  while (platformIterator.hasNext()) {

  var platform = platformIterator.next();

  Logger.log(platform.getName() + ' bid modifier: ' +

  platform.getBidModifier());

      

  var stats = platform.getStatsFor(date_range);

  var conversions = stats.getConversions();

  var conversion_rate = stats.getConversionRate();

  var cost = stats.getCost();

    

  

  //CPA and Device Bid. Adjustment Calculations  

    

  var cpa = cost / conversions

  var device_bid_adj = target_cpa / cpa - 1

    

  

  

  // Name of the specific spreadsheet

  var SPREADSHEET_URL = ‘SPREADSHEET_URL’;

 

  // Name of the specific sheet

  var SHEET_NAME = ‘SHEET_NAME’;

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = ss.getSheetByName(SHEET_NAME);

  // Appends rows to bottom of sheet

  sheet.appendRow([now, cid, campaign.getName(), platform.getName(), conversions, platform.getBidModifier(), cost, cpa, device_bid_adj]);

  sheet.sort(5, false);

  

    

  // Show Bid Adjustment as % with Two Decimals

    

  var cell = sheet.getRange("I:I");

  cell.setNumberFormat("0.00%");

}

  

  //Logger

  

  Logger.log(cid)  

  Logger.log('Campaign Name: ' + campaign.getName());

  Logger.log('Conversions: ' + conversions);

  Logger.log('Conversion Rate: ' + conversion_rate);

}

 }

Thanks!

Kelly

[Anthony Madrigal]

Hi Kelly,

It is not possible to set CPA from the campaign or platform level. You can, however, update a campaign's bidding strategy and set a platform's bid modifier. In order to read values from a spreadsheet, you can use functions such as getSheetValues to do so.

Here are some of our Best Practices to help optimize your code.

Please let me know if you have any other questions.

Regards,

Anthony

AdWords Scripts Team