Navigate back to the homepage

Hourly Ad Schedule Management Script for Google Ads

Saïd Tezel
January 7th, 2019 · 3 min read

I have been using and admiring the powerful scripting features provided on Google Ads. It truly gives you a better control over your ad campaigns instead of trying to fiddle through Google Ads’ god-awful redesign.

Out of a recent necessity, I’ve come across the challenge of creating a script that provides selective control over select MCC accounts and ad campaigns. Upon some research, I’ve stumbled upon brainlabsdigital.com, who have been creating some great scripts for managing Google Ads accounts.

Even though they already have a well-functioning script for day parting, I had to make some adjustments in how the script functions to satisfy my requirements. The final result is this script, which is my first attempt at properly writing/customising ad scripts. The script is a heavily customised version of what they have created. The three main changes in this script are:

  • It can be used to manage your all MCC (Ads Manager) accounts with a limit of up to 50 accounts. Perfect for ad agencies!
  • Instead of applying the bidding schedules across all the campaigns in an ad account, it instead utilises a label-based approach to provide you with the flexibility to adjust scheduling for your select ad campaigns.
  • It gives you to ability to enable/disable mobile bid adjustments based on your choice.

What It Does

Currently, Google Ads already provides the option to set up bid adjustment schedules. However you can only set up schedules in 4-hour intervals, which can prevent you from having a more granular method of changing the bid schedules.

This script is the perfect solution for setting up hourly adjustments to your bidding schedule, across many campaigns and even ad accounts under your MCC. Everything is label based; so you can easily create a bidding schedule that applies to ad campaigns of your choice.

You can create multiple strategies and apply them across different campaigns in different ad accounts simply by utilising the labels on Google Ads. It also gives you the ability to combine account level bidding strategies with mobile bid adjustments to give you even more power with controlling your campaigns.

How It Works

A Google Sheet document will keep track of all the individual ad bidding schedules and automatically apply them to their respective campaigns. You can find and copy the template sheet here.

You can find the full script at the bottom of this page. Once you copy/paste the script in your Google Ads interface, you will need to replace the spreadsheetUrl variable in the script with the full URL of the Google sheet you’ve just duplicated.

  1. You will need to create and apply a label called dayPartingEnabled to each MCC account you would like to schedule bids for. The script supports up to 50 ad accounts. Remember, the ad schedule will only work across the ad accounts with that label.
  2. To create a new schedule, make a duplicate of the template sheet. Do not make any changes on the template sheet.
  3. Rename the duplicate sheet to something distinguishable, without using any special characters or spaces. You will use the sheet name when applying labels to campaigns later.
  4. Each sheet consists of three separate tables (blue, green and purple).
  5. For setting up campaign-level bid adjustments, fill in the schedule on the BLUE sheet accordingly.
  6. If you’d like to add mobile bid adjustments, fill in the schedule on the GREEN sheet as well.
  7. If you want mobile bid adjustments, you will also need to rename the sheet with the _incMobile suffix. For example, a sheet named dayParting will not apply mobile bid adjustments; whereas a sheet named dayParting_incMobile will.
  8. Once you have completed filling in the ad schedule, you will need to create a new campaign-level label on Google Ads. The name of the label needs to precisely match the name of the sheet you have created here.
  9. Apply the new label across all the ad campaigns you’d like to run a specific ad scheduling for.
  10. Test and preview the changes the script makes. The total number of accounts and total number of ad campaigns processed by the script should be output in console.
  11. Set up scheduling for the script you’ve just created to run every hour.
  12. Celebrate saving time with a cup of tea!
1/*
2*
3* Campaign label based ad scheduling
4*
5* This script is a heavily modified version of the day parting script provided by brainlabsdigital.com
6* It provides an MCC version of the script that heavily depeneds on the labels applied to the campaigns.
7*
8* It will apply ad schedules to campaigns or shopping campaigns and set
9* the ad schedule bid modifier and mobile bid modifier at each hour according to
10* multiplier timetables in a Google sheet.
11*
12* This version creates schedules with modifiers for 4 hours, then fills the rest
13* of the day and the other days of the week with schedules with no modifier as a
14* fail safe.
15*
16* Version: 1
17* Updated to allow -100% bids, change mobile adjustments and create fail safes.
18* saidtezel.com
19*
20*/
21
22function main() {
23 var spreadsheetUrl = "INSERT GOOGLE SHEET URL";
24
25 // Define the label name to be used for filtering accounts.
26 // You'll need to apply a label with this value to all MCC accounts you want to run this script, with a limit of 50.
27 // The 50 account limit is because parallel processing only allows up to 50 accounts at once.
28 var labelName = "dayPartingEnabled";
29
30 // Shopping or regular campaigns?
31 // Use true if you want to run script on shopping campaigns (not regular campaigns).
32 // Use false for regular campaigns.
33 var shoppingCampaigns = false;
34
35 // Optional parameters for filtering campaign names. The matching is case insensitive.
36 var excludeCampaignNameContains = [];
37
38 // Select which campaigns to include e.g ["foo", "bar"] will include only campaigns
39 var includeCampaignNameContains = [];
40
41 // When you want to stop running the ad scheduling for good, set the lastRun
42 // variable to true to remove all ad schedules.
43 var lastRun = false;
44
45 // Initialise for use later.
46 var weekDays = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];
47 var adScheduleCodes = [];
48
49 var scheduleRange = "B2:H25";
50 var mobileScheduleRange = "M2:S25";
51
52 // Initiate shared parameters to be used across all accounts in parallel processing
53 var sharedParams = {
54 "spreadsheetUrl": spreadsheetUrl,
55 "shoppingCampaigns": shoppingCampaigns,
56 "excludeCampaignNameContains": excludeCampaignNameContains,
57 "includeCampaignNameContains": includeCampaignNameContains,
58 "lastRun": lastRun,
59 "weekDays": weekDays,
60 "adScheduleCodes": adScheduleCodes,
61 "scheduleRange": scheduleRange,
62 "mobileScheduleRange": mobileScheduleRange,
63 };
64
65 var accountSelector = AdsManagerApp.accounts()
66 .withLimit(50)
67 .withCondition("LabelNames CONTAINS \"" + labelName + "\"");
68
69 accountSelector.executeInParallel("processAllAccounts", "allFinished", JSON.stringify(sharedParams));
70}
71
72function processAllAccounts(sharedParams) {
73 var sharedParams = JSON.parse(sharedParams);
74
75 var spreadsheetUrl = sharedParams.spreadsheetUrl;
76 var shoppingCampaigns = sharedParams.shoppingCampaigns;
77 var excludeCampaignNameContains = sharedParams.excludeCampaignNameContains;
78 var includeCampaignNameContains = sharedParams.includeCampaignNameContains;
79 var lastRun = sharedParams.lastRun;
80 var weekDays = sharedParams.weekDays;
81 var adScheduleCodes = sharedParams.adScheduleCodes;
82 var scheduleRange = sharedParams.scheduleRange;
83 var mobileScheduleRange = sharedParams.mobileScheduleRange;
84
85 //Retrieving up hourly data
86 var timeZone = AdWordsApp.currentAccount().getTimeZone();
87 if (timeZone === "Etc/GMT") {
88 timeZone = "GMT";
89 }
90 var date = new Date();
91 var dayOfWeek = parseInt(Utilities.formatDate(date, timeZone, "uu"), 10) - 1;
92 var hour = parseInt(Utilities.formatDate(date, timeZone, "HH"), 10);
93
94 // Keep track of total number of campaigns processed in this execution.
95 var campaignCount = 0;
96
97 var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
98 var sheets = spreadsheet.getSheets();
99
100 for (var s = 0; s < sheets.length; s++) {
101 var sheet = sheets[s];
102 var sheetName = sheets[s].getName();
103
104 if (sheetName == "READ THIS FIRST" || "template") {
105 continue;
106 }
107 var campaignIds = [];
108
109 var data = sheet.getRange(scheduleRange).getValues();
110
111 //This hour's bid multiplier.
112 var thisHourMultiplier = data[hour][dayOfWeek];
113 var lastHourCell = "I2";
114 sheet.getRange(lastHourCell).setValue(thisHourMultiplier);
115
116 //The next few hours' multipliers
117 var timesAndModifiers = [];
118 var otherDays = weekDays.slice(0);
119 for (var h = 0; h < 5; h++) {
120 var newHour = (hour + h) % 24;
121 if (hour + h > 23) {
122 var newDay = (dayOfWeek + 1) % 7;
123 } else {
124 var newDay = dayOfWeek;
125 }
126 otherDays[newDay] = "-";
127
128 if (h < 4) {
129 // Use the specified bids for the next 4 hours
130 var bidModifier = data[newHour][newDay];
131 if (isNaN(bidModifier) || (bidModifier < -0.9 && bidModifier > -1) || bidModifier > 9) {
132 Logger.log("Bid modifier '" + bidModifier + "' for " + weekDays[newDay] + " " + newHour + " is not valid.");
133 timesAndModifiers.push([newHour, newHour + 1, weekDays[newDay], 0]);
134 } else if (bidModifier != -1 && bidModifier.length != 0) {
135 timesAndModifiers.push([newHour, newHour + 1, weekDays[newDay], bidModifier]);
136 }
137 } else {
138 // Fill in the rest of the day with no adjustment (as a back-up incase the script breaks)
139 timesAndModifiers.push([newHour, 24, weekDays[newDay], 0]);
140 }
141 }
142
143 // Iterate through campaigns with labels
144 var labelIterator = AdsApp.labels()
145 .withCondition("Name = \"" + sheetName + "\"")
146 .get();
147
148 if (labelIterator.hasNext()) {
149 var label = labelIterator.next();
150 var campaignIterator = label.campaigns()
151 .withCondition("Status IN [ENABLED]")
152 .get();
153
154 while (campaignIterator.hasNext()) {
155 campaignCount += 1;
156 var campaign = campaignIterator.next();
157 var campaignName = campaign.getName();
158 var includeCampaign = false;
159 if (includeCampaignNameContains.length === 0) {
160 includeCampaign = true;
161 }
162 for (var i = 0; i < includeCampaignNameContains.length; i++) {
163 var index = campaignName.toLowerCase().indexOf(includeCampaignNameContains[i].toLowerCase());
164 if (index !== -1) {
165 includeCampaign = true;
166 break;
167 }
168 }
169 if (includeCampaign) {
170 var campaignId = campaign.getId();
171 campaignIds.push(campaignId);
172 }
173 }
174 }
175
176 // Continue with next iteration if this one has 0 campaigns.
177 if (campaignIds.length == 0) {
178 continue;
179 }
180
181 //Remove all ad scheduling for the last run.
182 if (lastRun) {
183 checkAndRemoveAdSchedules(campaignIds, []);
184 return;
185 }
186
187 // Change the mobile bid adjustment
188 var mobileCheck = /_incMobile/;
189 if (mobileCheck.test(sheetName)) {
190 var data = sheet.getRange(mobileScheduleRange).getValues();
191
192 var thisHourMultiplier_Mobile = data[hour][dayOfWeek];
193
194 if (thisHourMultiplier_Mobile.length === 0) {
195 thisHourMultiplier_Mobile = -1;
196 }
197
198 if (isNaN(thisHourMultiplier_Mobile) || (thisHourMultiplier_Mobile < -0.9 && thisHourMultiplier_Mobile > -1) || thisHourMultiplier_Mobile > 3) {
199 Logger.log("Mobile bid modifier '" + thisHourMultiplier_Mobile + "' for " + weekDays[dayOfWeek] + " " + hour + " is not valid.");
200 thisHourMultiplier_Mobile = 0;
201 }
202
203 var totalMultiplier = ((1 + thisHourMultiplier_Mobile) * (1 + thisHourMultiplier)) - 1;
204 sheet.getRange("T2").setValue(thisHourMultiplier_Mobile);
205 sheet.getRange("AE2").setValue(totalMultiplier);
206 ModifyMobileBidAdjustment(campaignIds, thisHourMultiplier_Mobile);
207 }
208
209 // Check the existing ad schedules, removing those no longer necessary
210 var existingSchedules = checkAndRemoveAdSchedules(campaignIds, timesAndModifiers);
211
212 // Add in the new ad schedules
213 AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns);
214 }
215
216 return campaignCount.toFixed(0);
217}
218
219/**
220 * Post-process the results from processAccount. This method will be called
221 * once all the accounts have been processed by the executeInParallel method
222 * call.
223 *
224 * @param {Array.<ExecutionResult>} results An array of ExecutionResult objects,
225 * one for each account that was processed by the executeInParallel method.
226 */
227function allFinished(results) {
228 var accountCount = results.length;
229 var totalCampaignCount = 0;
230
231 Logger.log("Parallel processing is complete");
232 Logger.log("*************");
233 for (var w = 0; w < results.length; w++) {
234 // Get the ExecutionResult for an account.
235 var result = results[w];
236
237 Logger.log("Customer ID: %s; Status: %s.", result.getCustomerId(), result.getStatus());
238
239 // Check the execution status. This can be one of ERROR, OK, or TIMEOUT.
240 if (result.getStatus() == "ERROR") {
241 Logger.log("-- Failed with error: '%s'.", result.getError());
242 } else if (result.getStatus() == "OK") {
243 var retval = parseInt(result.getReturnValue());
244 totalCampaignCount += retval;
245 } else {
246 Logger.log("The execution was not able to finish due to timeout");
247 }
248 }
249
250 Logger.log("*************");
251 Logger.log("Total number of accounts processed: " + accountCount);
252 Logger.log("Total number of campaigns processed: " + totalCampaignCount);
253}
254
255/**
256* Function to add ad schedules for the campaigns with the given IDs, unless the schedules are
257* referenced in the existingSchedules array. The scheduling will be added as a hour long periods
258* as specified in the passed parameter array and will be given the specified bid modifier.
259*
260* @param array campaignIds array of campaign IDs to add ad schedules to
261* @param array timesAndModifiers the array of [hour, day, bid modifier] for which to add ad scheduling
262* @param array existingSchedules array of strings identifying already existing schedules.
263* @param bool shoppingCampaigns using shopping campaigns?
264* @return void
265*/
266function AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns) {
267 // times = [[hour,day],[hour,day]]
268 var campaignIterator = ConstructIterator(shoppingCampaigns)
269 .withIds(campaignIds)
270 .get();
271 while (campaignIterator.hasNext()) {
272 var campaign = campaignIterator.next();
273 for (var i = 0; i < timesAndModifiers.length; i++) {
274 if (existingSchedules.indexOf(
275 timesAndModifiers[i][0] + "|" + (timesAndModifiers[i][1]) + "|" + timesAndModifiers[i][2]
276 + "|" + Utilities.formatString("%.2f", (timesAndModifiers[i][3] + 1)) + "|" + campaign.getId())
277 > -1) {
278 continue;
279 }
280
281 campaign.addAdSchedule({
282 dayOfWeek: timesAndModifiers[i][2],
283 startHour: timesAndModifiers[i][0],
284 startMinute: 0,
285 endHour: timesAndModifiers[i][1],
286 endMinute: 0,
287 bidModifier: Math.round(100 * (1 + timesAndModifiers[i][3])) / 100
288 });
289 }
290 }
291}
292
293/**
294* Function to remove ad schedules from all campaigns referenced in the passed array
295* which do not correspond to schedules specified in the passed timesAndModifiers array.
296*
297* @param array campaignIds array of campaign IDs to remove ad scheduling from
298* @param array timesAndModifiers array of [hour, day, bid modifier] of the wanted schedules
299* @return array existingWantedSchedules array of strings identifying the existing undeleted schedules
300*/
301function checkAndRemoveAdSchedules(campaignIds, timesAndModifiers) {
302
303 var adScheduleIds = [];
304
305 var report = AdWordsApp.report(
306 "SELECT CampaignId, Id " +
307 "FROM CAMPAIGN_AD_SCHEDULE_TARGET_REPORT " +
308 "WHERE CampaignId IN [\"" + campaignIds.join("\",\"") + "\"]"
309 );
310
311 var rows = report.rows();
312 while (rows.hasNext()) {
313 var row = rows.next();
314 var adScheduleId = row["Id"];
315 var campaignId = row["CampaignId"];
316 if (adScheduleId == "--") {
317 continue;
318 }
319 adScheduleIds.push([campaignId, adScheduleId]);
320 }
321
322 var chunkedArray = [];
323 var chunkSize = 10000;
324
325 for (var i = 0; i < adScheduleIds.length; i += chunkSize) {
326 chunkedArray.push(adScheduleIds.slice(i, i + chunkSize));
327 }
328
329 var wantedSchedules = [];
330 var existingWantedSchedules = [];
331
332 for (var j = 0; j < timesAndModifiers.length; j++) {
333 wantedSchedules.push(timesAndModifiers[j][0] + "|" + (timesAndModifiers[j][1]) + "|" + timesAndModifiers[j][2] + "|" + Utilities.formatString("%.2f", timesAndModifiers[j][3] + 1));
334 }
335
336 for (var k = 0; k < chunkedArray.length; k++) {
337 var unwantedSchedules = [];
338
339 var adScheduleIterator = AdWordsApp.targeting()
340 .adSchedules()
341 .withIds(chunkedArray[k])
342 .get();
343 while (adScheduleIterator.hasNext()) {
344 var adSchedule = adScheduleIterator.next();
345 var key = adSchedule.getStartHour() + "|" + adSchedule.getEndHour() + "|" + adSchedule.getDayOfWeek() + "|" + Utilities.formatString("%.2f", adSchedule.getBidModifier());
346
347 if (wantedSchedules.indexOf(key) > -1) {
348 existingWantedSchedules.push(key + "|" + adSchedule.getCampaign().getId());
349 } else {
350 unwantedSchedules.push(adSchedule);
351 }
352 }
353
354 for (var p = 0; p < unwantedSchedules.length; p++) {
355 unwantedSchedules[j].remove();
356 }
357 }
358
359 return existingWantedSchedules;
360}
361
362/**
363* Function to construct an iterator for shopping campaigns or regular campaigns.
364*
365* @param bool shoppingCampaigns Using shopping campaigns?
366* @return AdWords iterator Returns the corresponding AdWords iterator
367*/
368function ConstructIterator(shoppingCampaigns) {
369 if (shoppingCampaigns === true) {
370 return AdWordsApp.shoppingCampaigns();
371 }
372 else {
373 return AdWordsApp.campaigns();
374 }
375}
376
377/**
378* Function to set a mobile bid modifier for a set of campaigns
379*
380* @param array campaignIds An array of the campaign IDs to be affected
381* @param Float bidModifier The multiplicative mobile bid modifier
382* @return void
383*/
384function ModifyMobileBidAdjustment(campaignIds, bidModifier) {
385 var platformIds = [];
386 var newBidModifier = Math.round(100 * (1 + bidModifier)) / 100;
387
388 for (var q = 0; q < campaignIds.length; q++) {
389 platformIds.push([campaignIds[q], 30001]);
390 }
391
392 var platformIterator = AdWordsApp.targeting()
393 .platforms()
394 .withIds(platformIds)
395 .get();
396 while (platformIterator.hasNext()) {
397 var platform = platformIterator.next();
398 platform.setBidModifier(newBidModifier);
399 }
400}

More articles from Saïd Tezel

P-bundance: Marketing Mix in the Era of Digital

It was a simpler time when I was back in the uni studying marketing. The holy grail of marketing mix boiled down to 4Ps; product, price…

March 8th, 2016 · 3 min read

Reflections on a Dissertation: Brand Loyalty in Smartphones

With great relief from good riddance of a dissertation, I can’t help but feel obliged to share my findings. After all, this was a project…

September 16th, 2014 · 6 min read
© 2014–2020 Saïd Tezel
Link to $https://twitter.com/said_tezelLink to $https://github.com/saidtezelLink to $https://instagram.com/said_tezelLink to $https://www.linkedin.com/saidtezel