July 19, 2018
AdWords scripts are a great tool for managing bid adjustments. In previous articles, such as Automating Bidding with Google Sheets and AdWords Scripts, we’ve covered different ways to change bids and handle uploads. This article will take those ideas and combine them into your own bidding system!
We’ll still keep a high-level approach in this article to keep is accessible or wherever you want to take it. If you are less experienced with scripts this makes it easier to follow (which still being something you can apply on your own). If you are more experienced, you can easily make your own edits to enhance the tool.
Throughout this process, we will take advantage of free tools and add-ons to create a system that pulls the data, processes the data, and uploads a set of bid changes. Instead of being a full script, we’ll utilize sheets and upload features to make editing more accessible to non-coders.
What We’ll Use
We’ll use the Google AdWords add-on to collect data, Google Sheets to manage bids, AdWords scripts to format and upload the changes, and finally we’ll use the Google Sheets app to copy the changes and save them for reference.
- Google AdWords Add-on – Collect data
- Google Sheets – Apply changes via formulas and return changes.
- AdWords Scripts – Check changes, build upload file, push to AdWords, save change log to Google Drive.
Pulling the Data
First, we need to set up our data pull. You can either use a tool to pull the data in, use a script, or paste the data manually. Since this is about automation we’ll skip the last option. The first example will use the AdWords Add-on (if you want to limit your code exposure for now) and we’ll circle back on resources for scripting the data pull.
Once you’ve installed the AdWords Add-on, go to Sheets and create a new sheet. Open the add-on and start setting up your sheet. We’ll want to pull any performance metrics plus the max CPC column. If you are changing bids as a percentage of the last bid you’ll need the current bid. If you want to keep a record you’ll need the column as well.
If you prefer, you could also use a tool like SuperMetrics. Or you could use the reporting functionality in AdWords scripts to import changes. Both of these offer slightly more automated solutions.
If you’d like to know more about the reporting functionality, I highly recommend the the following articles,
Google Developers, Reporting – AdWords Scripts Reporting reference
Fred Vallaeys, Search Engine Land – This Script Automates adding any AdWords data to a Google Spreadsheet
Make Bid Adjustments
Once we have our performance data we can start calculating the new bids! In this example we’ll adjust based on CPA with a basic formula in a new column.
Next we’ll add a helper column to define which columns changed.
Now we’ll make a new sheet to hold our changes, something obvious like “Keyword Changes to Upload”. I recommend using query() to return all changed rows to their own sheet. This will make it easy to pull into AdWords and limit the code we have to write to select the correct range.
Formatting the Upload
This requires a few steps. We need to,
- Access the spreadsheet.
- Pull the data.
- Format it for bulkUpload and build a file
- Push the file to AdWords.
Accessing the spreadsheet is easy enough, we’ll use the URL and file name to access the sheet.
Now the slightly tricky part. If you aren’t familiar with the process. We need to create a csv file for upload. We already have the rows we want to upload. We will essentially create a blank file then fill it with the rows from the “Keyword Changes to Upload” sheet.
Once we have the upload built, we are one line away from the upload.
Creating the Backup Log
Often times users worry about the ability to revert changes or the system running amok. It’s unlikely but in order to be extra careful, we can make a copy and save it to our Google Drive.
I recommend creating a folder ahead of time. Copying the folder ID from the URL and inserting it into the script. This keeps your top-level drive from filling up with copies and helps keep the drive nice and tidy!
Send an Email with Changes
Email updates help you and your team keep up with changes and keep an eye on the magnitude of changes. We can use the emailApp to send an email to team members , along with a brief message with links to the change sheets we created in the last step. We can also pull a quick count of the rows in the upload file to count the number of bid changes.
In order to include the links we’ll format the body of the message as HTML, add a few breaks and make it look slightly nicer.
Wrapping Up
If you are automatically updating your keyword data, go ahead and schedule your script to automatically push changes.
If you are loading keyword data manually, you can still schedule the upload, but it would make more sense to run the script manually once you update the data.
Improving the Process
There are two main avenues for improvement. You could fully script the entire process and remove the spreadsheet portion. This streamlines the steps and can make things easier to manage if you don’t need the sheet.
The second option to update your bidding sheet with more complex rules and decisions. Adding more variables to your bid rule, implementing multiple conversion types, or including third party data can help you build a more effective bidding model.
Full Script
function main() {
//email for bidding updates
var email = [“youremail@email.com”];
//ID of folder you’ve set aside to hold bid sheet copies
//pull from the end of your Drive folder URL
var bidFolderId = ”;
//URL of your bid change book
var biddingWorkBookUrl = ”;
//Open workbook
var biddingWorkBook = SpreadsheetApp.openByUrl(biddingWorkBookUrl);
//Sheet with bid changes
//string can be updated to name of your own file
var keywordBidUpload = brandBook.getSheetByName(‘Keyword Changes to Upload’);
//Get the sheet you want to copy
var keywordBids = brandBook.getSheetByName(‘Keyword Bids’);
//Get last row to calculate number of bid changes.
var bidChangeCount = keywordBids.getLastRow()-1;
function createCopy(sheetObject) {
var destinationFolder = DriveApp.getFolderById(bidFolderId);
var file = DriveApp.getFileById(sheetObject.getId()).makeCopy( ” Bid Copy – ” + createDateString(), destinationFolder);
return file.getUrl();
}
var brandCopy = createCopy(keywordBids);
function uploadBids(sheet) {
//create column names for upload
var columns = [“Campaign”, “Ad group”, “Keyword”,”Match type”, “Max. CPC”];
//create csv upload
var upload = AdWordsApp.bulkUploads().newCsvUpload(columns);
//get all values from sheet
var values = sheet.getDataRange().getValues();
//convert sheet rows and append to upload file
for(i=1; i < values.length; i++){
var campaign = values[i][0];
var ad_group = values[i][1];
var keyword = values[i][2];
var match_type = values[i][3];
var max_cpc = values[i][4];
upload.append({
‘Campaign’: campaign,
‘Ad group’: ad_group,
‘Keyword’: keyword,
‘Match type’: match_type,
‘Max. CPC’: max_cpc});
};
};
uploadBids(keywordBidUpload);
//Create date text to append to file name
function createDateString(){
var newDate = new Date();
var month = String(newDate.getMonth());
var day = String(newDate.getDate());
var year = String(newDate.getFullYear());
return month + “-” + day + “-” + year;
}
var dateString = createDateString();
var subject = “Account Bid Changes ” + dateString;
var options = {
htmlBody : “Hello, <br /><br /> The bid changes have been implemented. <br /> <br /> There were ” +
bidChangeCount + ” keyword bid changes.<br /> <br />” +
“Changes and previous settings can be found in, <br /><br />” +
‘<a href=”‘ + brandCopy +
‘”>Brand Changes</a>’;
}
MailApp.sendEmail(email, subject, ”, options)
}
//End script
