Efficient campaign automation in Google AdWords

Prehistory

It all started with the words “Do the xml upload for AdWords,” and then it started. Oddly enough, but it was this task that was performed fairly quickly, but then it was more interesting. As it turned out, AdWords had the opportunity to write scripts (javascript) to automate the campaign management process and everything would be fine if it were not for the limits on execution time and xml. Yes, it is xml. I do not know why everybody is so enthusiastic about this format, but I never liked it. I coped with 95% of the task and, frankly, I didn't get any pleasure from it and 5% of the task remained. It was these 5% that I threw no longer on xml, but on json, and here it was fun.

More specifics

Let's specify what is at all about. There is an online store with ~ 25 000 items. Marketers need unloading to drive it all into a campaign: create ad groups, advertisements themselves, keys, etc. As it turned out later, it doesn’t matter what format of the incoming data (xml / json), so I chose the one that I prefer - json.

{ elems: [ { id: 555233, n: "Agent Provocateur Maitresse", p: 346, u: "http://site.ua/555233.html", v: "Agent Provocateur", c: " " }, { id: 559675, n: "Angel Schlesser Essential for Men", p: 191, u: "http://site.ua/559675.html", v: "Angel Schlesser", c: " " } ]} 

And here we have N elements with the structure id, n (Name), p (Price), u (Url), v (Vendor), c (Category), in any case, this is exactly the data that I needed. Let's start automation?
')

Script 1. Creation of groups of announcements

 //  google-       var doc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/15_W4y3GpivCjuNRWPN8HKy27MjtUeW2NTThAAPPXdkc/edit#gid=0'); //     var sheet = doc.getSheetByName('parfums'); // ,         var i_cell = sheet.getRange('B2'); var date_cell = sheet.getRange('B3'); function main() { var i_cell_val = ( i_cell.isBlank() ) ? 0 : i_cell.getValue(); //  JSON var json = JSON.parse(UrlFetchApp.fetch('http://site.ua/adwords.json').getContentText()); //      var tmp = AdWordsApp.campaigns().withCondition('Name = ""').get(); var unloaded = json.elems; var export_l = unloaded.length; if(is_exported()) { Logger.log('Already exported'); return; } if (tmp.hasNext()) { var campaign = tmp.next(); } else { Logger.log('Company not found'); return; } for (i= i_cell_val; i<=export_l-1; i++) { el = unloaded[i]; var tmp = campaign.adGroups().withCondition('Name CONTAINS "__ID-' + el.id +'"').get(); if (tmp.hasNext()) { var tmp_g = tmp.next(); tmp_g.enable(); } else { var adGroupName = el.c + '_' + el.v + '_' + el.n + '__ID-' + el.id; addAdGroup(adGroupName, campaign); } i_cell.getValue(); i_cell.setValue(i); if (i == export_l-1) { date_cell.setValue(Utilities.formatDate(new Date(), "GMT+3", "dMyyyy")); i_cell.setValue(0); } } } function addAdGroup(adGroupName, ci) { var adGroup = ci.newAdGroupBuilder(); adGroup = adGroup.withName(adGroupName).withStatus("ENABLED").withKeywordMaxCpc(1).create(); } function is_exported() { var exp_date = Number(Utilities.formatDate(new Date(date_cell.getValue()), "GMT+3", "dd")); var today = Utilities.formatDate(new Date(), "GMT+3", "dd HH").split(' '); if (Number(today[1]) < 6) return true; if ( (exp_date < Number(today[0])) || date_cell.isBlank()) return false; else return true; } 

This script will be a goat for understanding the following. Who ran his eyes on the script, clearly asked the questions "Why? Why is there a google doc? What kind of nonsense? ” I tell. As much as I do not like Google, but, alas, these automation scripts run extremely long, and the schedule is extremely tight, and that's the presence of crutches.

Why do we need a google doc?

Spreadsheet in Google-doc will be for us a repository, for access to which there is a described and supported API. There we will write data on which the scripts will understand whether you need to do something else or is it worth stopping.

The plate will be something like this:



Cell B2 - this is where we write the current iterator of the elements in the loop. It is equal to zero when everything is unloaded on the current day, as well as the value in cell B3 should be equal to the current date, the set of these equations will mean that all elements are unloaded on the current day. What is it for? In order to be able to put the script on a schedule for every hour and after full execution, it was simply turned off with the message “Everything is unloaded”.

What is the is_exported function?

This function is present in each script and will check whether it is necessary to drive all the data on the new one.
Specifically in my case, it looks like this:

 function is_exported() { var exp_date = Number(Utilities.formatDate(new Date(date_cell.getValue()), "GMT+3", "dd")); var today = Utilities.formatDate(new Date(), "GMT+3", "dd HH").split(' '); if (Number(today[1]) < 6) return true; if ( (exp_date < Number(today[0])) || date_cell.isBlank()) return false; else return true; } 

Copying it to yourself, do not forget about a couple of important points. First, set your time zone, I have GMT + 3, and second, put if (Number (today [1]) <6) here, instead of 6 , your “HOURS” values, after which the script will be executed. I have 6 hours, because, approximately, by that time the whole unloading will be ready.

Script 2. Creating texts of announcements in groups

 //  google-       var doc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/15_W4y3GpivCjuNRWPN8HKy27MjtUeW2NTThAAPPXdkc/edit#gid=0'); var sheet = doc.getSheetByName('parfums'); // ,         var i_cell = sheet.getRange('C2'); var date_cell = sheet.getRange('C3'); function main() { var i_cell_val = ( i_cell.isBlank() ) ? 0 : i_cell.getValue(); //  JSON var json = JSON.parse(UrlFetchApp.fetch('http://site.ua/adwords.json').getContentText()); //      var tmp = AdWordsApp.campaigns().withCondition('Name = ""').get(); var unloaded = json.elems; var export_l = unloaded.length; if(is_exported()) { Logger.log('Already exported'); return; } if (tmp.hasNext()) { var campaign = tmp.next(); } else { Logger.log('Company not found'); return; } for (i= i_cell_val; i<=export_l-1; i++) { el = unloaded[i]; var tmp_g = campaign.adGroups().withCondition('Name CONTAINS "__ID-' + el.id +'"').get(); if (tmp_g.hasNext()) { var adGroup = tmp_g.next(); var lb = adGroup.labels().withCondition('Name = "with_text"').get(); //    ADG if (!lb.hasNext()) { //   ADG  ,   adGroup.createTextAd('{KeyWord: }', '   {param1: ' + el.p + '} ', '  !', 'site.ua/' + el.v.replace(/ /g, '_'), el.u); adGroup.applyLabel('with_text'); } } else { Logger.log("  '" + el.id + "'  ."); } i_cell.getValue(); i_cell.setValue(i); if (i == export_l-1) { //   date_cell.setValue(Utilities.formatDate(new Date(), "GMT+3", "dMyyyy")) i_cell.setValue(0); } } } function is_exported() { var exp_date = Number(Utilities.formatDate(new Date(date_cell.getValue()), "GMT+3", "dd")); var today = Utilities.formatDate(new Date(), "GMT+3", "dd HH").split(' '); if (Number(today[1]) < 8) return true; if ( (exp_date < Number(today[0])) || date_cell.isBlank()) return false; else return true; } 

In this script, I think everyone understands everything, but one thing is worth explaining - the use of labels. What for? Yes, simply because I did not find how to check whether an advertisement exists in the group. That's all, if the label is found in the group, it means it exists, because label we assign only after adding text. It's simple.

Script 3. Creating keywords

 //  google-       var doc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/15_W4y3GpivCjuNRWPN8HKy27MjtUeW2NTThAAPPXdkc/edit#gid=0'); var sheet = doc.getSheetByName('parfums'); // ,         var i_cell = sheet.getRange('D2'); var date_cell = sheet.getRange('D3'); var flag_cell = sheet.getRange('D4'); function main() { var i_cell_val = ( i_cell.isBlank() ) ? 0 : i_cell.getValue(); //  JSON var json = JSON.parse(UrlFetchApp.fetch('http://site.ua/adwords.json').getContentText()); //      var tmp = AdWordsApp.campaigns().withCondition('Name = ""').get(); var unloaded = json.elems; var export_l = unloaded.length; if(is_exported()) { Logger.log('Already exported'); return; } if (tmp.hasNext()) { var campaign = tmp.next(); } else { Logger.log('Company not found'); return; } var flag_v = ( flag_cell.isBlank() ) ? 1 : flag_cell.getValue(); for (i= i_cell_val; i<=export_l-1; i++) { el = unloaded[i]; var tmp_g = campaign.adGroups().withCondition('Name CONTAINS "__ID-' + el.id +'"').get(); if (tmp_g.hasNext()) { var adGroup = tmp_g.next(); var key = el.n; var tmp_key = AdWordsApp.keywords().withCondition('Text = "' + key + '"').get(); //      if (!tmp.hasNext()) { adGroup.createKeyword(key); } key = '[' + el.n + ']'; tmp = AdWordsApp.keywords().withCondition('Text = "' + key + '"').get(); if (!tmp.hasNext()) { adGroup.createKeyword(key); } } else { flag_v = 0; Logger.log("  '" + el.id + "'  ."); } i_cell.getValue(); i_cell.setValue(i); flag_cell.setValue(flag_v); if (i == export_l-1) { //   if (Number(flag_v)) //     ADG date_cell.setValue(Utilities.formatDate(new Date(), "GMT+3", "dMyyyy")); i_cell.setValue(0); } } } function is_exported() { var exp_date = Number(Utilities.formatDate(new Date(date_cell.getValue()), "GMT+3", "dd")); var today = Utilities.formatDate(new Date(), "GMT+3", "dd HH").split(' '); if (Number(today[1]) < 8) return true; if ( (exp_date < Number(today[0])) || date_cell.isBlank()) return false; else return true; } 

Here, too, everything is simple. There is an upload, by ID pulls the group, in the group we create keys. A curtain! But here, not without trifles. The variable flag_v appeared here. If it is zero, then the cycle does not close. This is done in order to avoid rassinhrona, if groups of announcements have not yet been created. Also, change the “CLOCK” parameter in the is_exported function, put it an hour or 2 later.

Script 4. Update parameters

 //  google-       var doc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/15_W4y3GpivCjuNRWPN8HKy27MjtUeW2NTThAAPPXdkc/edit#gid=0'); var sheet = doc.getSheetByName('parfums'); // ,         var i_cell = sheet.getRange('E2'); var date_cell = sheet.getRange('E3'); var flag_cell = sheet.getRange('E4'); function main() { var i_cell_val = ( i_cell.isBlank() ) ? 0 : i_cell.getValue(); //  JSON var json = JSON.parse(UrlFetchApp.fetch('http://site.ua/adwords.json').getContentText()); //      var tmp = AdWordsApp.campaigns().withCondition('Name = ""').get(); var unloaded = json.elems; var export_l = unloaded.length; if(is_exported()) { Logger.log('Already exported'); return; } if (tmp.hasNext()) { var campaign = tmp.next(); } else { Logger.log('Company not found'); return; } var flag_v = ( flag_cell.isBlank() ) ? 1 : flag_cell.getValue(); for (i= i_cell_val; i<=export_l-1; i++) { el = unloaded[i]; var tmp_g = campaign.adGroups().withCondition('Name CONTAINS "__ID-' + el.id +'"').get(); if (tmp_g.hasNext()) { var adGroup = tmp_g.next(); var keywordIter = adGroup.keywords().get(); while (keywordIter.hasNext()) { var keyword = keywordIter.next(); keyword.setAdParam(1, el.p); } } else { flag_v = 0; Logger.log("  '" + el.id + "'  ."); } i_cell.getValue(); i_cell.setValue(i); flag_cell.setValue(flag_v); if (i == export_l-1) { //   if (Number(flag_v)) //     ADG date_cell.setValue(Utilities.formatDate(new Date(), "GMT+3", "dMyyyy")); i_cell.setValue(0); } } } function is_exported() { var exp_date = Number(Utilities.formatDate(new Date(date_cell.getValue()), "GMT+3", "dd")); var today = Utilities.formatDate(new Date(), "GMT+3", "dd HH").split(' '); if (Number(today[1]) < 6) return true; if ( (exp_date < Number(today[0])) || date_cell.isBlank()) return false; else return true; } 

It's still easier. We received a group, received an iterator of keys, ran through all the keywords, updated parameter 1 (price) in all keys. Is done.

Script 5. Updating group status

 function main() { var json_ids = JSON.parse(UrlFetchApp.fetch('http://site.ua/adwords.json').getContentText()).ids; var tmp = AdWordsApp.campaigns().withCondition('Name = ""').get(); if (tmp.hasNext()) { var campaign = tmp.next(); var tmp = campaign.adGroups().get(); } else { Logger.log('Company not found'); } while (tmp.hasNext()) { group = tmp.next(); name = group.getName(); id = /__ID-(\d+)$/.exec(name)[1]; if ( json_ids.indexOf(id) == -1 ) { group.pause(); } } } 

This is the coolest and fastest script. In it we cycle through all the groups of announcements, regularly draw the product ID, check if it is in the unloading and, if not, put the group on pause to save money. If you do not include any loggers, the script runs through ~ 3000 ads in ~ 20 minutes. Yes, I forgot to mention, in the upload you should have a section with an array of all the ID's participating in this process. You can make for this a separate json, you can shove the same as the previous scripts involved - the taste and color.

Couple of interesting moments

• i_cell.getValue();
i_cell.setValue(i);
  This is done in order to update the data in the table in live mode.
• Forming a group of announcements, make it so that you can get out of it ID
• Think over the schedule of execution of scripts so that there are no overlays on "uncreated" elements.
• The last script can be put on execution every 15 minutes, therefore, if you have a lively store and high dynamics in changing the availability of goods, you will save money. The rest of the scripts can be put on execution every hour.
• This is all just a goat for a common understanding.
• Sorry for the sheets of the code, a lot of things are repeated there, but otherwise you can get very confused

Main source: developers.google.com/adwords/scripts/docs/reference/index
See what the dock will look like here: docs.google.com/spreadsheets/d/15_W4y3GpivCjuNRWPN8HKy27MjtUeW2NTThAAPPXdkc/edit?usp=sharing

Smaller you handmade and more saving reasonable. Thank you all for your attention.

UPD:

• Fixed errors in small scripts. Replaced the nonsense by using the isBlank () method
• I forgot to write at the beginning that to complete the work you need to create a shortcut, if you follow my example it should be called with