Script to manage budget pacing

[Smarth Sachdeva]

Hi Team,

I have been looking for a script wherein I can input start and end date of different campaigns (as per client's IO) and my monthly budget in a spreadsheet and the columns like yesterday's spent, spend till now for the flight, flight percentage and other columns like impression share and others can be fetched directly from the UI. 

Please see the image to better understand what I am looking for. 

I have got a script that does this work at the MCC level, but I am looking for similar set up at the account level since I do not manage all the campaigns of the account (s) but some campaigns with a specific filter.

Awaiting your kind reply.

Best,

Smarth

[Google Ads Scripts Forum Advisor Prod]

Hi Smarth,

If you have a similar script at the MCC level then you should be able to implement this on the account level, since MCC scripts simply iterate over accounts (or execute the same jobs in parallel). If you can provide your CID and script name, I can help guide you on the single account implementation.

Thanks,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5001UEHdSd:ref

[Smarth Sachdeva]

Here you go with the script:

var master_id = '1nf6xk234LzTjIFygcKP-SM2yGGFDyi2rb0G0xwiCZPY';

var too_much = "#ffa0a0";

var too_less = "#ffe4c9";

var just_right = "#d6fcd6";

var header_bg = "#e5e5e5";

  

/* === STOP EDITING === */

var now = new Date();

var now = new Date(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate(),  0, 0, 0);

var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1,  now.getHours(), now.getMinutes(), now.getSeconds());

function main() {

  

    var spreadsheet_keys = []

    

    var master = SpreadsheetApp.openById(master_id);

    var idSheet = getSheet( "ids", master ); //what sheet do i want to open

    var ss_ids = idSheet.getRange( 2, 1, idSheet.getLastRow()-1, 1 ).getValues();

  

      

    for ( j=0; j < ss_ids.length; j++ ) {

   

       spreadsheet_keys.push(ss_ids[j][0]);

   

    }

  

for ( i=0; i<spreadsheet_keys.length; i++ ) {

      

        var ss_id = spreadsheet_keys[i];

     

var ss = SpreadsheetApp.openById(ss_id);

var sheet = getSheet(  Utilities.formatDate( now, "CET", "yy" ) + "-" + getMonthName( now.getMonth() ), ss );

write_spendings_in_sheet( sheet )

if ( now.getDate() == 1 ) {

    // if first day of month, also write in the sheet of last month

var sheet_last_month = getSheet(  Utilities.formatDate( yesterday, "CET", "yy" ) + "-" + getMonthName( yesterday.getMonth() ), ss );

write_spendings_in_sheet( sheet_last_month );

}

}

}

function write_spendings_in_sheet( sheet ) {

  var headings = [ [ "ID","Planbudget","Start","End","Name","Label/Mitarbeiter","Daily Run Rate", "Spent yesterday", "Difference", "Daily spent L7D","Spent",  "Budget left","Spent-%", "Days Gone-%" ] ];

  sheet.getRange( 1, 1, 1, headings[0].length ).setValues( headings ).setFontWeight("bold").setBackground(header_bg);

  

  var ids = sheet.getRange("A2:A150").getValues();

  var row_last_id = ids.filter(String).length; // get the number of ids in the sheet

for ( row = 2; row <= row_last_id+1; row++ ) {

        // get the values from the sheet

var id = sheet.getRange( row, 1).getValue();

      //Logger.log(id);

      

var budget = sheet.getRange( row, 2).getValue();

      

      if ( sheet.getRange( row, 3).getValue() == "" ) {

        // when start date is empty, set to yesterday

        

        var start = yesterday;

        sheet.getRange( row, 3).setValue( yesterday ).setBackground(header_bg);

        

      } else {        

        

        var start = sheet.getRange( row, 3).getValue();

      

      }

      

var startDateApi = Utilities.formatDate( start, "CET", "yyyyMMdd" ); // for use as daterange to get the stats

      if ( sheet.getRange( row, 4).getValue() == "" ) {

         // when end date is empty, set to yesterday

        

        var end = yesterday;

        sheet.getRange( row, 4).setValue( yesterday ).setBackground(header_bg);

        

      } else {

        

        var end = sheet.getRange( row, 4).getValue();

      

      }

      

var yesterdayApi =  Utilities.formatDate( yesterday, "UTC", "yyyyMMdd" ); // for use as daterange to get the stats

var accountIterator = MccApp.accounts().withIds( [id] ).get();

while (accountIterator.hasNext()) {

var account  = accountIterator.next();

          

// Write name of the account in the sheet as a check

sheet.getRange( row, 5 ).setValue( account.getName() );

          

          // Get the names of manager if applicable. Labels have to start with 'MA_'

            var accountLabelSelector = account.labels().withCondition("Name STARTS_WITH_IGNORE_CASE 'MA_'");

var accountLabelIterator = accountLabelSelector.get();

while (accountLabelIterator.hasNext()) {

var accountLabel = accountLabelIterator.next();

sheet.getRange(row,6).setValue( accountLabel.getName().substr(3) );

break;

}

          

          

            // Calculating the metrics

          

var accountStatsYes = account.getStatsFor( "YESTERDAY" );

var accountStatsL7D = account.getStatsFor( "LAST_7_DAYS" );

if ( Utilities.formatDate( now, "CET", "yyyyMMdd" ) == startDateApi ) {

var spendThisMonth = 0;

} else {

var accountStats = account.getStatsFor( startDateApi, yesterdayApi );

var spendThisMonth = accountStats.getCost(); 

}

var spentPct = spendThisMonth/budget;

var toSpent = budget - spendThisMonth;

var daysInTimeFrame =  Math.round((end - start)/(3600000*24)) + 1;      

          

          

var daysLeft = Math.round((end - yesterday)/(3600000*24));

                    

            var daysGonePct = 1 - daysLeft/daysInTimeFrame

var runRate = toSpent / daysLeft;

var spendYesterday = accountStatsYes.getCost();          

          

var difference = spendYesterday - runRate;

            

            var spendL7D = accountStatsL7D.getCost() / 7;

var metrics = [ [ runRate, spendYesterday, difference, spendL7D, spendThisMonth, toSpent, spentPct, daysGonePct ] ];

          

sheet.getRange( row, 7, 1, metrics[0].length ).setValues( metrics );

          

var bgSpentPct = just_right;

          

if ( spentPct > daysGonePct*1.05  ) { bgSpentPct = too_much; }

if ( spentPct < daysGonePct*0.95  ) { bgSpentPct = too_less; }

          

sheet.getRange( row, 13 ).setBackground( bgSpentPct );

          

var bgspendYesterday = just_right;          

          

if ( spendYesterday*0.95 > runRate && runRate > 0 ) { bgspendYesterday = too_much; }

if ( spendYesterday*1.05 < runRate && runRate > 0 ) { bgspendYesterday = too_less; }

if ( runRate <= 0 && spendYesterday > 0 ) { bgspendYesterday = too_much; }

          

sheet.getRange( row, 9 ).setBackground( bgspendYesterday );

          

}

}  

}

function getSheet( sheetName, spreadsheet ) {

if ( spreadsheet.getSheetByName( sheetName ) == null ) {

//if sheet doesnt exist yet, create a new one

var sheet = spreadsheet.insertSheet( sheetName );

} else {

var sheet = spreadsheet.getSheetByName( sheetName );

}

return sheet;

}

function getDaysInMonth ( month, year ) {

var d31 = new Date(year,month,31);

var d30 = new Date(year,month,30);

var d29 = new Date(year,month,29);

if ( d31.getMonth() == month ) {

var days = 31;

} else if ( d30.getMonth() == month ) { 

var days = 30;

} else if ( d29.getMonth() == month ) { 

var days = 29;

} else {

var days = 28;

}

return days;

}

function getMonthName ( monthIndex ) {

var months = ["Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"];

return months[monthIndex];

}

I am not sure why account ID is required since the script would be implemented at the account level itself. 

Best,

SMarth

[Google Ads Scripts Forum Advisor Prod]

Hi Smarth,

I requested access to the spreadsheet. I need to see the spreadsheet in order to fully understand the script.

[Lara Azpeitia]

Hello! 

Hope you are well! 

I am looking for something similar. I used a similar spreadsheet for an old client and would like to use a Script to manage budget pacing for my new client as it makes my life so much easier. I have a copy of the spreadsheet but I don´t know how to create the script. Can you please help me? I would not mind to utilize a new spreadsheet if you can provide the script, please? Hey Smarth, does your script work only for your spreadsheet? 

Thank you so much, 

Lara

[Google Ads Scripts Forum Advisor]

Hi Lara,

I would suggest that you refer to the documents “AdsApp.​BudgetSelector” and “Budgets” for sample code available for getting or setting a campaign budget. I would recommend that you go through the “SpreadsheetApp” document which will help you to export data to a spreadsheet.

I hope this helps! Feel free to get back to us if you still have any concerns. 

This message is in relation to case "ref:!00D1U01174p.!5001U0EHdSd:ref" (ADR-00011119)

Thanks,
 

Google Ads Scripts Team

[Lara Azpeitia]

Hello! 

Sorry for my super late response.

Thanks for the information provided, its much appreciated, however I don´t think its what I needed?! basically what I need is a script/spreadsheet like the one attached, that is located in Google Ads scripts, and where I can go and check mainly the pacing (column R) which tells me if my campaigns are spending what should be. On this spreadsheet/script, the "spent" gets automatically updated.

Thank you so much!

Lara

[Google Ads Scripts Forum Advisor]

Hi Lara,

I would suggest you check the flexible budget solution script for a single account. By default this script simulates a budget strategy with $500 over 10 days. The logger output reflects the day being simulated, the allocated budget for that day, and the total amount spent to date. Also, this script will dynamically adjust your campaign budget daily with a custom budget distribution scheme.

If you face any issues, please share the following details to analyze the issue further: 

• Google Ads account ID / CID
• Name of the script
• Error details or screenshot of the issue (if any)
• Shareable link of the spreadsheet the script is using, you may check this article to share a file publicly. 

You can send the details via Reply privately to the author option, or direct private reply to this email.