Automação Google Ads: Script de Modificação de Lances

Nessa série especial, reunimos alguns scripts úteis para você automatizar e aumentar o retorno de suas campanhas no Google Ads. Confira abaixo como automatizar o Google Ads para “Modificação de Lances”.

 

1. Programação de Alteração de Lances 24 horas – Pelo Brainlabs. Esse script permite que você ajuste automaticamente seus lances por hora, durante toda a semana. Desta maneira é possível otimizar o gasto do orçamento de sua conta mantendo seus anúncios nas posições que trazem maiores conversões.

/*
*
* Advanced ad scheduling
*
* This script will apply ad schedules to campaigns or shopping campaigns and set
* the ad schedule bid modifier and mobile bid modifier at each hour according to
* multiplier timetables in a Google sheet.
*
* This version creates schedules with modifiers for 4 hours, then fills the rest
* of the day and the other days of the week with schedules with no modifier as a
* fail safe.
*
* Version: 3.1
* Updated to allow -100% bids, change mobile adjustments and create fail safes.
* brainlabsdigital.com
*
*/
function main() {
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options
//The Google sheet to use
//The default value is the example sheet
//Shopping or regular campaigns
//Use true if you want to run script on shopping campaigns (not regular campaigns).
//Use false for regular campaigns.
var shoppingCampaigns = false;
//Use true if you want to set mobile bid adjustments as well as ad schedules.
//Use false to just set ad schedules.
var runMobileBids = false;
//Optional parameters for filtering campaign names. The matching is case insensitive.
//Select which campaigns to exclude e.g ["foo", "bar"] will ignore all campaigns
//whose name contains 'foo' or 'bar'. Leave blank [] to not exclude any campaigns.
var excludeCampaignNameContains = [];
//Select which campaigns to include e.g ["foo", "bar"] will include only campaigns
//whose name contains 'foo' or 'bar'. Leave blank [] to include all campaigns.
var includeCampaignNameContains = [];
//When you want to stop running the ad scheduling for good, set the lastRun
//variable to true to remove all ad schedules.
var lastRun = false;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Initialise for use later.
var weekDays = ["MONDAY""TUESDAY""WEDNESDAY""THURSDAY""FRIDAY""SATURDAY""SUNDAY"];
var adScheduleCodes = [];
var campaignIds = [];
//Retrieving up hourly data
var scheduleRange = "B2:H25";
var accountName = AdWordsApp.currentAccount().getName();
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheets = spreadsheet.getSheets();
var timeZone = AdWordsApp.currentAccount().getTimeZone();
var date = new Date();
var dayOfWeek = parseInt(Utilities.formatDate(date, timeZone, "uu"), 10) - 1;
var hour = parseInt(Utilities.formatDate(date, timeZone, "HH"), 10);
var sheet = sheets[0];
var data = sheet.getRange(scheduleRange).getValues();
//This hour's bid multiplier.
var thisHourMultiplier = data[hour][dayOfWeek];
var lastHourCell = "I2";
sheet.getRange(lastHourCell).setValue(thisHourMultiplier);
//The next few hours' multipliers
var timesAndModifiers = [];
var otherDays = weekDays.slice(0);
for (var h=0; h<5; h++) {
var newHour = (hour + h)%24;
if (hour + h > 23) {
var newDay = (dayOfWeek + 1)%7;
else {
var newDay = dayOfWeek;
}
otherDays[newDay] = "-";
if (h<4) {
// Use the specified bids for the next 4 hours
var bidModifier = data[newHour][newDay];
if (isNaN(bidModifier) || (bidModifier < -0.9 && bidModifier > -1) || bidModifier > 9) {
Logger.log("Bid modifier '" + bidModifier + "' for " + weekDays[newDay] + " " + newHour + " is not valid.");
timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], 0]);
else if (bidModifier != -1 && bidModifier.length != 0) {
timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], bidModifier]);
}
else {
// Fill in the rest of the day with no adjustment (as a back-up incase the script breaks)
timesAndModifiers.push([newHour, 24, weekDays[newDay], 0]);
}
}
if (hour>0) {
timesAndModifiers.push([0, hour, weekDays[dayOfWeek], 0]);
}
for (var d=0; d<otherDays.length; d++) {
if (otherDays[d] != "-") {
timesAndModifiers.push([0, 24, otherDays[d], 0]);
}
}
//Pull a list of all relevant campaign IDs in the account.
var campaignSelector = ConstructIterator(shoppingCampaigns);
for(var i = 0; i < excludeCampaignNameContains.length; i++){
campaignSelector = campaignSelector.withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE "' + excludeCampaignNameContains[i] + '"');
}
campaignSelector = campaignSelector.withCondition("Status IN [ENABLED,PAUSED]");
var campaignIterator = campaignSelector.get();
while(campaignIterator.hasNext()){
var campaign = campaignIterator.next();
var campaignName = campaign.getName();
var includeCampaign = false;
if(includeCampaignNameContains.length === 0){
includeCampaign = true;
}
for(var i = 0; i < includeCampaignNameContains.length; i++){
var index = campaignName.toLowerCase().indexOf(includeCampaignNameContains[i].toLowerCase());
if(index !== -1){
includeCampaign = true;
break;
}
}
if(includeCampaign){
var campaignId = campaign.getId();
campaignIds.push(campaignId);
}
}
//Return if there are no campaigns.
if(campaignIds.length === 0){
Logger.log("There are no campaigns matching your criteria.");
return;
}
//Remove all ad scheduling for the last run.
if(lastRun){
checkAndRemoveAdSchedules(campaignIds, []);
return;
}
// Change the mobile bid adjustment
if(runMobileBids){
if (sheets.length < 2) {
Logger.log("Mobile ad schedule sheet was not found in the Google spreadsheet.");
else {
var sheet = sheets[1];
var data = sheet.getRange(scheduleRange).getValues();
var thisHourMultiplier_Mobile = data[hour][dayOfWeek];
if (thisHourMultiplier_Mobile.length === 0) {
thisHourMultiplier_Mobile = -1;
}
if (isNaN(thisHourMultiplier_Mobile) || (thisHourMultiplier_Mobile < -0.9 && thisHourMultiplier_Mobile > -1) || thisHourMultiplier_Mobile > 3) {
Logger.log("Mobile bid modifier '" + thisHourMultiplier_Mobile + "' for " + weekDays[dayOfWeek] + " " + hour + " is not valid.");
thisHourMultiplier_Mobile = 0;
}
var totalMultiplier = ((1+thisHourMultiplier_Mobile)*(1+thisHourMultiplier))-1;
sheet.getRange("I2").setValue(thisHourMultiplier_Mobile);
sheet.getRange("T2").setValue(totalMultiplier);
ModifyMobileBidAdjustment(campaignIds, thisHourMultiplier_Mobile);
}
}
// Check the existing ad schedules, removing those no longer necessary
var existingSchedules = checkAndRemoveAdSchedules(campaignIds, timesAndModifiers);
// Add in the new ad schedules
AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns);
}
/**
* Function to add ad schedules for the campaigns with the given IDs, unless the schedules are
* referenced in the existingSchedules array. The scheduling will be added as a hour long periods
* as specified in the passed parameter array and will be given the specified bid modifier.
*
* @param array campaignIds array of campaign IDs to add ad schedules to
* @param array timesAndModifiers the array of [hour, day, bid modifier] for which to add ad scheduling
* @param array existingSchedules array of strings identifying already existing schedules.
* @param bool shoppingCampaigns using shopping campaigns?
* @return void
*/
function AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns){
// times = [[hour,day],[hour,day]]
var campaignIterator = ConstructIterator(shoppingCampaigns)
.withIds(campaignIds)
.get();
while(campaignIterator.hasNext()){
var campaign = campaignIterator.next();
for(var i = 0; i < timesAndModifiers.length; i++){
if (existingSchedules.indexOf(
timesAndModifiers[i][0] + "|" + (timesAndModifiers[i][1]) + "|" + timesAndModifiers[i][2]
"|" + Utilities.formatString("%.2f",(timesAndModifiers[i][3]+1)) + "|" + campaign.getId())
> -1) {
continue;
}
campaign.addAdSchedule({
dayOfWeek: timesAndModifiers[i][2],
startHour: timesAndModifiers[i][0],
startMinute: 0,
endHour: timesAndModifiers[i][1],
endMinute: 0,
bidModifier: Math.round(100*(1+timesAndModifiers[i][3]))/100
});
}
}
}
/**
* Function to remove ad schedules from all campaigns referenced in the passed array
* which do not correspond to schedules specified in the passed timesAndModifiers array.
*
* @param array campaignIds array of campaign IDs to remove ad scheduling from
* @param array timesAndModifiers array of [hour, day, bid modifier] of the wanted schedules
* @return array existingWantedSchedules array of strings identifying the existing undeleted schedules
*/
function checkAndRemoveAdSchedules(campaignIds, timesAndModifiers) {
var adScheduleIds = [];
var report = AdWordsApp.report(
'SELECT CampaignId, Id ' +
'FROM CAMPAIGN_AD_SCHEDULE_TARGET_REPORT ' +
'WHERE CampaignId IN ["' + campaignIds.join('","')  + '"]'
);
var rows = report.rows();
while(rows.hasNext()){
var row = rows.next();
var adScheduleId = row['Id'];
var campaignId = row['CampaignId'];
if (adScheduleId == "--") {
continue;
}
adScheduleIds.push([campaignId,adScheduleId]);
}
var chunkedArray = [];
var chunkSize = 10000;
for(var i = 0; i < adScheduleIds.length; i += chunkSize){
chunkedArray.push(adScheduleIds.slice(i, i + chunkSize));
}
var wantedSchedules = [];
var existingWantedSchedules = [];
for (var j=0; j<timesAndModifiers.length; j++) {
wantedSchedules.push(timesAndModifiers[j][0] + "|" + (timesAndModifiers[j][1]) + "|" + timesAndModifiers[j][2] + "|" + Utilities.formatString("%.2f",timesAndModifiers[j][3]+1));
}
for(var i = 0; i < chunkedArray.length; i++){
var unwantedSchedules = [];
var adScheduleIterator = AdWordsApp.targeting()
.adSchedules()
.withIds(chunkedArray[i])
.get();
while (adScheduleIterator.hasNext()) {
var adSchedule = adScheduleIterator.next();
var key = adSchedule.getStartHour() + "|" + adSchedule.getEndHour() + "|" + adSchedule.getDayOfWeek() + "|" + Utilities.formatString("%.2f",adSchedule.getBidModifier());
if (wantedSchedules.indexOf(key) > -1) {
existingWantedSchedules.push(key + "|" + adSchedule.getCampaign().getId());
else {
unwantedSchedules.push(adSchedule);
}
}
for(var j = 0; j < unwantedSchedules.length; j++){
unwantedSchedules[j].remove();
}
}
return existingWantedSchedules;
}
/**
* Function to construct an iterator for shopping campaigns or regular campaigns.
*
* @param bool shoppingCampaigns Using shopping campaigns?
* @return AdWords iterator Returns the corresponding AdWords iterator
*/
function ConstructIterator(shoppingCampaigns){
if(shoppingCampaigns === true){
return AdWordsApp.shoppingCampaigns();
}
else{
return AdWordsApp.campaigns();
}
}
/**
* Function to set a mobile bid modifier for a set of campaigns
*
* @param array campaignIds An array of the campaign IDs to be affected
* @param Float bidModifier The multiplicative mobile bid modifier
* @return void
*/
function ModifyMobileBidAdjustment(campaignIds, bidModifier){
var platformIds = [];
var newBidModifier = Math.round(100*(1+bidModifier))/100;
for(var i = 0; i < campaignIds.length; i++){
platformIds.push([campaignIds[i],30001]);
}
var platformIterator = AdWordsApp.targeting()
.platforms()
.withIds(platformIds)
.get();
while (platformIterator.hasNext()) {
var platform = platformIterator.next();
platform.setBidModifier(newBidModifier);
}
}

 

2. Calcular e Definir Modificador de Lances para Mobile – Pelo Frederick Vallaeys do Optmyzr. Esse script revisa a performance mobile e desktop/tablet do ROAS (Retorno por gasto de anúncio) ou CPC (Custo por clique). Após a revisão, ele analisa os dados das palavras-chave e sugere um modificador de lances para o tráfego mobile dos grupos de anúncios ou campanhas.

 

 

/*********
* Calculate Mobile Bid Adjustments
* Based on the post by Kohki Yamaguchi found here: http://goo.gl/iJBCdJ
* Author: Russell Savage
* Date: 2013-08-15
* Verson: v1.1
**********/
// Use this to set the look back window for gathering stats
// You can also use the format YYYYMMDD,YYYYMMDD
var DATE_RANGE = 'LAST_30_DAYS';

// Set to Campaign if you want to calculate at the Campaign level
// Set to AdGroup if you want to calculate at the AdGroup level
var LEVEL = 'AdGroup';
//If you set the LEVEL to AdGroup, the spreadsheet will be mailed to these emails
var TO = ['[email protected]','[email protected]'];

// Set to ROAS to use Return On Ad Spend for the calulcation
// Set to RPC to use Revenue Per Click for the calculation
var METRIC = 'ROAS';

// These will be the min and max values that the mobile bid adjustment could be set to
var MINIMUM_BID_ADJUSTMENT = .5;
var MAXIMUM_BID_ADJUSTMENT = 1;

// Use this to adjust the number of decimal places to keep for calculations
var DECIMAL_PLACES = 3;

function main() {
//Check the options to make sure they are valid
hasValidOptions();

//first, calculate stats for each keyword
var accountMap = getKeywordStats();

//then get the mobileBidAdjustments for the given LEVEL
var mobileBidAdjustments = getMobileBidAdjustments(accountMap);

if(LEVEL === 'Campaign') {
//Apply the calculated modifiers to the campaigns
setCampaignBidModifiers(mobileBidAdjustments);
} else {
//Someday, we'll be able to set the AdGroup modifiers, but until then, here is a spreadsheet
//that you can upload into AdWords Editor
var report = getAdGroupBidModifierReport(mobileBidAdjustments);
sendEmail(report,'adgroup_mobile_modifier_','AdGroup Mobile Modifiers - ','See attached.');
}
}

// This will set the Mobile Bid Modifiers at the Campaign level
function setCampaignBidModifiers(mobileBidAdjustments) {
var campaignIterator = AdWordsApp.campaigns().get();
while(campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var currentMobileBidAdjustment = campaign.targeting().platforms().mobile().get().next();
if(mobileBidAdjustments[campaign.getId()]) {
Logger.log(campaign.getName() + ' MBA: ' + mobileBidAdjustments[campaign.getId()]);
currentMobileBidAdjustment.setBidModifier(mobileBidAdjustments[campaign.getId()]);
}
}
}

// This function will send an email with the report attached as a
// zipped csv file in case the file is large.
function sendEmail(report,attachmentPrefix,subjectPrefix,body) {
var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
var blob = Utilities.newBlob(report, 'text/csv', attachmentPrefix+date_str+'.csv')
if(report.length > 1000) {
var zipped_blob = Utilities.zip([blob], attachmentPrefix+date_str+'.zip');
blob = zipped_blob;
}
var options = { attachments: [blob] };
var subject = subjectPrefix + date_str;
for(var i in TO) {
MailApp.sendEmail(TO[i], subject, body, options);
}
}

// This function will build the report for AdGroup Mobile Bid Modifiers
// so that you can upload them into AdWords Editor
function getAdGroupBidModifierReport(accountMap) {
var report = '"' + ['Campaign','Ad Group','Bid Adjustment'].join('","') + '"\n';
var adGroupIterator = AdWordsApp.adGroups().get();
while(adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
var campaignId = adGroup.getCampaign().getId();
var adGroupId = adGroup.getId();
if(accountMap[campaignId] && accountMap[campaignId][adGroupId]) {
var formattedMba = round((accountMap[campaignId][adGroupId] - 1)*100);
report += '"' + [adGroup.getCampaign().getName(),adGroup.getName(),formattedMba].join('","') + '"\n';
Logger.log([adGroup.getCampaign().getName(),adGroup.getName(),formattedMba].join('", "'));
}
}
return report;
}

function getWeightedRatio(keyword,spendOrClick) {
if(['spend','click'].indexOf(spendOrClick) == -1) {
throw 'getWeightedRatio needs spendOrClick to be equal to spend or click. Current value is: ' + spendOrClick;
}

var total = getTotal(keyword,spendOrClick);
var mobileMetric = 0;
var desktopMetric = 0;

if(!keyword.Mobile) { return 0; }
if(!keyword.DesktopTablet) {
// If we are here, that means there is mobile data, but no desktop data.
// In this case, we want to return a large number to influence this metric more
// We use 3 because the max bid multiplier is 300%
return (3 * total);
}

var metricKey = (METRIC === 'ROAS') ? 'Roas' : 'Rpc';

mobileMetric = keyword.Mobile[metricKey];
desktopMetric = keyword.DesktopTablet[metricKey];

if(mobileMetric == 0) { return 0; }

if(desktopMetric == 0) {
// We use the same reasoning as above
return (3 * total);
}

var weightedRatio = round(mobileMetric/desktopMetric) * total;
return weightedRatio;
}

// Helper function to calculate the total clicks or cost of the keyword
function getTotal(keyword,spendOrClick) {
if(['spend','click'].indexOf(spendOrClick) == -1) {
throw 'getTotal needs spendOrClick to be equal to spend or click. Current value is: ' + spendOrClick;
}

var total = 0;
var metricKey = (spendOrClick === 'spend') ? 'Roas' : 'Rpc';

var mobileMetric = (keyword.Mobile) ? keyword.Mobile[metricKey] : 0;
var desktopMetric = (keyword.DesktopTablet) ? keyword.DesktopTablet[metricKey] : 0;

if(mobileMetric == 0 && desktopMetric == 0) {
//ignore this keyword
return 0;
}

var deviceMetricKey = (spendOrClick === 'spend') ? 'Cost' : 'Clicks';
for(var device in keyword) {
total += keyword[device][deviceMetricKey];
}
return total;
}

// This function sums the results of the click weighted ROAS ratios and calculates
// the final mobile bid adjustment of the campaign.
function getMobileBidAdjustments(entities) {
var mbas = {};
var numerator = 0;
var denominator = 0;

for(var campaignId in entities) {
for(var adGroupId in entities[campaignId]) {
for(var keywordId in entities[campaignId][adGroupId]) {
var keywordData = entities[campaignId][adGroupId][keywordId];
switch(METRIC) {
case 'ROAS' :
numerator += getWeightedRatio(keywordData,'spend');
denominator += getTotal(keywordData,'spend');
break;
case 'RPC' :
numerator += getWeightedRatio(keywordData,'click');
denominator += getTotal(keywordData,'click');
break;
default :
throw 'METRIC needs to be either ROAS, or RPC. Current value is: ' + METRIC;
}
}
if(LEVEL === 'AdGroup') {
Logger.log('Campaign Id: ' + campaignId + ' AdGroupId: ' + adGroupId);
if(!mbas[campaignId]) { mbas[campaignId] = {}; }
mbas[campaignId][adGroupId] = getMba(numerator,denominator);
if(mbas[campaignId][adGroupId] == -1) {
Logger.log('Mobile Bid Adjustment could not be calculated for CampaignId: ' + campaignId + ' and AdGroupId: ' + adGroupId);
delete mbas[campaignId][adGroupId];
}
// Reset the values for the next run
denominator = 0;
numerator = 0;
}
}
if(LEVEL === 'Campaign') {
Logger.log('Campaign Id: ' + campaignId);
mbas[campaignId] = getMba(numerator,denominator);
if(mbas[campaignId] == -1) {
Logger.log('Mobile Bid Adjustment could not be calculated for CampaignId: ' + campaignId);
delete mbas[campaignId];
}
numerator = 0;
denominator = 0;
}
}
return mbas;
}

//This function calculates the Mobile Bid Modifier and trims using MIN and MAX
function getMba(sumOfRatios,total) {
Logger.log('Numerator: ' + sumOfRatios + ' Denominator: ' + total);
var mba = (total != 0) ? round(sumOfRatios/total) : -1;
Logger.log('MBA: ' + (mba==-1) ? 'N/A' : mba + ' (-1 is an error code and the value will be ignored)');
if(mba == -1) {
return mba;
} else {
return (mba < MINIMUM_BID_ADJUSTMENT) ? MINIMUM_BID_ADJUSTMENT :
(mba > MAXIMUM_BID_ADJUSTMENT) ? MAXIMUM_BID_ADJUSTMENT : mba;
}
}

// This function pulls the keyword performance report to get the values needed
// for calculating the ROAS for the keyword.
function getKeywordStats() {
var API_VERSION = { includeZeroImpressions : false };
var query = buildAWQLQuery();
var reportIter = AdWordsApp.report(query, API_VERSION).rows();
var accountMapping = {}; // { campaignId : { adGroupId : { keywordId : { stat : value } } } }
while(reportIter.hasNext()) {
var row = reportIter.next();
//Let's convert all the metrics to floats so we don't have to worry about it later
for(var i in row) {
if(i.indexOf('Id') == -1 && !isNaN(parseFloat(row[i]))) {
row[i] = parseFloat(row[i]);
}
}
var rpc = (row.Clicks != 0) ? round(row.ConversionValue/row.Clicks) : 0;
var roas = (row.AverageCpc != 0) ? round(rpc/row.AverageCpc) : 0;
var cpa = (row.Conversions != 0) ? round(row.Cost/row.Conversions) : 0;
row['Rpc'] = rpc;
row['Roas'] = roas;
row['Cpa'] = cpa;

//Build out the account mapping if needed
if(!accountMapping[row.CampaignId]) {
accountMapping[row.CampaignId] = {};
}
if(!accountMapping[row.CampaignId][row.AdGroupId]) {
accountMapping[row.CampaignId][row.AdGroupId] = {};
}
if(!accountMapping[row.CampaignId][row.AdGroupId][row.Id]) {
accountMapping[row.CampaignId][row.AdGroupId][row.Id] = {};
}
accountMapping[row.CampaignId][row.AdGroupId][row.Id][row.Device.split(' ')[0]] = row;
}
accountMapping = addDesktopTabletEntry(accountMapping);
return accountMapping;
}

// This function will return a valid AWQL query to find keyword performance
function buildAWQLQuery() {
var cols = ['CampaignId','AdGroupId','Id','Device','Clicks','Cost','Conversions','ConversionValue','AverageCpc'];
var report = 'KEYWORDS_PERFORMANCE_REPORT';
return ['select',cols.join(','),'from',report,'during',DATE_RANGE].join(' ');
}

// This function will add an entry into the accountMapping for combining Computers and Tablets
// since the Device targeting is only at that level
function addDesktopTabletEntry(accountMapping) {
for(var campaignId in accountMapping) {
var campaign = accountMapping[campaignId];
for(var adGroupId in campaign) {
var adGroup = campaign[adGroupId];
for(var keywordId in adGroup) {
var devices = adGroup[keywordId];

if(devices.Computers || devices.Tablets) {
accountMapping[campaignId][adGroupId][keywordId]['DesktopTablet'] = combineStats(devices.Computers,devices.Tablets);
}

//Since we combined these, we don't need them anymore
delete accountMapping[campaignId][adGroupId][keywordId]['Computers'];
delete accountMapping[campaignId][adGroupId][keywordId]['Tablets'];
}
}
}
return accountMapping;
}

// This function simply combines the stats of desktop and tablet performance
function combineStats(desktop,tablet) {
if(!desktop) {
desktop = {Cost : 0, Clicks: 0, ConversionValue : 0, Conversions : 0};
}
if(!tablet) {
tablet = {Cost : 0, Clicks: 0, ConversionValue : 0, Conversions : 0};
}
var totalCost = desktop.Cost + tablet.Cost;
var totalClicks = desktop.Clicks + tablet.Clicks;
var totalRevenue = desktop.ConversionValue + tablet.ConversionValue;
var totalConversions = desktop.Conversions + tablet.Conversions;
var averageCpc = (totalClicks != 0) ? round(totalCost/totalClicks) : 0;
var rpc = (totalClicks != 0) ? round(totalRevenue/totalClicks) : 0;
var roas = (averageCpc != 0) ? round(rpc/averageCpc) : 0;
var cpa = (totalConversions != 0) ? round(totalCost/totalConversions) : 0;

return {
Cost : totalCost,
Clicks: totalClicks,
ConversionValue : totalRevenue,
AverageCpc : averageCpc,
Rpc : rpc,
Roas : roas,
Cpa : cpa
};
}

// A helper function to make rounding a little easier
function round(value) {
var decimals = Math.pow(10,DECIMAL_PLACES);
return Math.round(value*decimals)/decimals;
}

// Validate some of the user options just in case
function hasValidOptions() {
var validDates = ['TODAY','YESTERDAY','LAST_7_DAYS','THIS_WEEK_SUN_TODAY',
'THIS_WEEK_MON_TODAY','LAST_WEEK','LAST_14_DAYS',
'LAST_30_DAYS','LAST_BUSINESS_WEEK','LAST_WEEK_SUN_SAT','THIS_MONTH'];
if(DATE_RANGE.indexOf(',') == -1 && validDates.indexOf(DATE_RANGE) == -1) {
throw 'DATE_RANGE is invalid. Current value is: '+DATE_RANGE;
}
if(DECIMAL_PLACES <= 0) {
throw 'You should set DECIMAL_PLACES to be >= 1. You entered: '+DECIMAL_PLACES;
}
if(['Campaign','AdGroup'].indexOf(LEVEL) == -1) {
throw 'LEVEL should be either Campaign or AdGroup. You entered: '+LEVEL;
}
if(['ROAS','RPC'].indexOf(METRIC) == -1) {
throw 'METRIC should be ROAS or RPC. You entered: '+METRIC;
}
if(LEVEL === 'AdGroup' && (!TO || TO.length == 0)) {
throw 'If you set LEVEL to AdGroup, you need to add at least one email.';
}
if(MINIMUM_BID_ADJUSTMENT < 0) {
throw 'MINIMUM_BID_ADJUSTMENT needs to be >= 0. Current value is: '+MINIMUM_BID_ADJUSTMENT;
}
if(MAXIMUM_BID_ADJUSTMENT > 3) {
throw 'MAXIMUM_BID_ADJUSTMENT needs to be <= 3. Current value is: '+MAXIMUM_BID_ADJUSTMENT;
}
}

 

3. Definir e Sugerir Modificador de Lances Mobile – Por Russel Savage. Com esse script você pode ajustar os modificadores de lances para mobile. Tenha maior controle adicionando um ajuste mínimo e um máximo por nível de campanha ou grupo de anúncios.

 

/*********
* Calculate Mobile Bid Adjustments
* Based on the post by Kohki Yamaguchi found here: http://goo.gl/iJBCdJ
* Author: Russell Savage
* Date: 2013-08-15
* Verson: v1.1
**********/
// Use this to set the look back window for gathering stats
// You can also use the format YYYYMMDD,YYYYMMDD
var DATE_RANGE = 'LAST_30_DAYS';

// Set to Campaign if you want to calculate at the Campaign level
// Set to AdGroup if you want to calculate at the AdGroup level
var LEVEL = 'AdGroup';
//If you set the LEVEL to AdGroup, the spreadsheet will be mailed to these emails
var TO = ['[email protected]','[email protected]'];

// Set to ROAS to use Return On Ad Spend for the calulcation
// Set to RPC to use Revenue Per Click for the calculation
var METRIC = 'ROAS';

// These will be the min and max values that the mobile bid adjustment could be set to
var MINIMUM_BID_ADJUSTMENT = .5;
var MAXIMUM_BID_ADJUSTMENT = 1;

// Use this to adjust the number of decimal places to keep for calculations
var DECIMAL_PLACES = 3;

function main() {
//Check the options to make sure they are valid
hasValidOptions();

//first, calculate stats for each keyword
var accountMap = getKeywordStats();

//then get the mobileBidAdjustments for the given LEVEL
var mobileBidAdjustments = getMobileBidAdjustments(accountMap);

if(LEVEL === 'Campaign') {
//Apply the calculated modifiers to the campaigns
setCampaignBidModifiers(mobileBidAdjustments);
} else {
//Someday, we'll be able to set the AdGroup modifiers, but until then, here is a spreadsheet
//that you can upload into AdWords Editor
var report = getAdGroupBidModifierReport(mobileBidAdjustments);
sendEmail(report,'adgroup_mobile_modifier_','AdGroup Mobile Modifiers - ','See attached.');
}
}

// This will set the Mobile Bid Modifiers at the Campaign level
function setCampaignBidModifiers(mobileBidAdjustments) {
var campaignIterator = AdWordsApp.campaigns().get();
while(campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var currentMobileBidAdjustment = campaign.targeting().platforms().mobile().get().next();
if(mobileBidAdjustments[campaign.getId()]) {
Logger.log(campaign.getName() + ' MBA: ' + mobileBidAdjustments[campaign.getId()]);
currentMobileBidAdjustment.setBidModifier(mobileBidAdjustments[campaign.getId()]);
}
}
}

// This function will send an email with the report attached as a
// zipped csv file in case the file is large.
function sendEmail(report,attachmentPrefix,subjectPrefix,body) {
var date_str = Utilities.formatDate(new Date(),AdWordsApp.currentAccount().getTimeZone(),'yyyy-MM-dd');
var blob = Utilities.newBlob(report, 'text/csv', attachmentPrefix+date_str+'.csv')
if(report.length > 1000) {
var zipped_blob = Utilities.zip([blob], attachmentPrefix+date_str+'.zip');
blob = zipped_blob;
}
var options = { attachments: [blob] };
var subject = subjectPrefix + date_str;
for(var i in TO) {
MailApp.sendEmail(TO[i], subject, body, options);
}
}

// This function will build the report for AdGroup Mobile Bid Modifiers
// so that you can upload them into AdWords Editor
function getAdGroupBidModifierReport(accountMap) {
var report = '"' + ['Campaign','Ad Group','Bid Adjustment'].join('","') + '"\n';
var adGroupIterator = AdWordsApp.adGroups().get();
while(adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
var campaignId = adGroup.getCampaign().getId();
var adGroupId = adGroup.getId();
if(accountMap[campaignId] && accountMap[campaignId][adGroupId]) {
var formattedMba = round((accountMap[campaignId][adGroupId] - 1)*100);
report += '"' + [adGroup.getCampaign().getName(),adGroup.getName(),formattedMba].join('","') + '"\n';
Logger.log([adGroup.getCampaign().getName(),adGroup.getName(),formattedMba].join('", "'));
}
}
return report;
}

function getWeightedRatio(keyword,spendOrClick) {
if(['spend','click'].indexOf(spendOrClick) == -1) {
throw 'getWeightedRatio needs spendOrClick to be equal to spend or click. Current value is: ' + spendOrClick;
}

var total = getTotal(keyword,spendOrClick);
var mobileMetric = 0;
var desktopMetric = 0;

if(!keyword.Mobile) { return 0; }
if(!keyword.DesktopTablet) {
// If we are here, that means there is mobile data, but no desktop data.
// In this case, we want to return a large number to influence this metric more
// We use 3 because the max bid multiplier is 300%
return (3 * total);
}

var metricKey = (METRIC === 'ROAS') ? 'Roas' : 'Rpc';

mobileMetric = keyword.Mobile[metricKey];
desktopMetric = keyword.DesktopTablet[metricKey];

if(mobileMetric == 0) { return 0; }

if(desktopMetric == 0) {
// We use the same reasoning as above
return (3 * total);
}

var weightedRatio = round(mobileMetric/desktopMetric) * total;
return weightedRatio;
}

// Helper function to calculate the total clicks or cost of the keyword
function getTotal(keyword,spendOrClick) {
if(['spend','click'].indexOf(spendOrClick) == -1) {
throw 'getTotal needs spendOrClick to be equal to spend or click. Current value is: ' + spendOrClick;
}

var total = 0;
var metricKey = (spendOrClick === 'spend') ? 'Roas' : 'Rpc';

var mobileMetric = (keyword.Mobile) ? keyword.Mobile[metricKey] : 0;
var desktopMetric = (keyword.DesktopTablet) ? keyword.DesktopTablet[metricKey] : 0;

if(mobileMetric == 0 && desktopMetric == 0) {
//ignore this keyword
return 0;
}

var deviceMetricKey = (spendOrClick === 'spend') ? 'Cost' : 'Clicks';
for(var device in keyword) {
total += keyword[device][deviceMetricKey];
}
return total;
}

// This function sums the results of the click weighted ROAS ratios and calculates
// the final mobile bid adjustment of the campaign.
function getMobileBidAdjustments(entities) {
var mbas = {};
var numerator = 0;
var denominator = 0;

for(var campaignId in entities) {
for(var adGroupId in entities[campaignId]) {
for(var keywordId in entities[campaignId][adGroupId]) {
var keywordData = entities[campaignId][adGroupId][keywordId];
switch(METRIC) {
case 'ROAS' :
numerator += getWeightedRatio(keywordData,'spend');
denominator += getTotal(keywordData,'spend');
break;
case 'RPC' :
numerator += getWeightedRatio(keywordData,'click');
denominator += getTotal(keywordData,'click');
break;
default :
throw 'METRIC needs to be either ROAS, or RPC. Current value is: ' + METRIC;
}
}
if(LEVEL === 'AdGroup') {
Logger.log('Campaign Id: ' + campaignId + ' AdGroupId: ' + adGroupId);
if(!mbas[campaignId]) { mbas[campaignId] = {}; }
mbas[campaignId][adGroupId] = getMba(numerator,denominator);
if(mbas[campaignId][adGroupId] == -1) {
Logger.log('Mobile Bid Adjustment could not be calculated for CampaignId: ' + campaignId + ' and AdGroupId: ' + adGroupId);
delete mbas[campaignId][adGroupId];
}
// Reset the values for the next run
denominator = 0;
numerator = 0;
}
}
if(LEVEL === 'Campaign') {
Logger.log('Campaign Id: ' + campaignId);
mbas[campaignId] = getMba(numerator,denominator);
if(mbas[campaignId] == -1) {
Logger.log('Mobile Bid Adjustment could not be calculated for CampaignId: ' + campaignId);
delete mbas[campaignId];
}
numerator = 0;
denominator = 0;
}
}
return mbas;
}

//This function calculates the Mobile Bid Modifier and trims using MIN and MAX
function getMba(sumOfRatios,total) {
Logger.log('Numerator: ' + sumOfRatios + ' Denominator: ' + total);
var mba = (total != 0) ? round(sumOfRatios/total) : -1;
Logger.log('MBA: ' + (mba==-1) ? 'N/A' : mba + ' (-1 is an error code and the value will be ignored)');
if(mba == -1) {
return mba;
} else {
return (mba < MINIMUM_BID_ADJUSTMENT) ? MINIMUM_BID_ADJUSTMENT :
(mba > MAXIMUM_BID_ADJUSTMENT) ? MAXIMUM_BID_ADJUSTMENT : mba;
}
}

// This function pulls the keyword performance report to get the values needed
// for calculating the ROAS for the keyword.
function getKeywordStats() {
var API_VERSION = { includeZeroImpressions : false };
var query = buildAWQLQuery();
var reportIter = AdWordsApp.report(query, API_VERSION).rows();
var accountMapping = {}; // { campaignId : { adGroupId : { keywordId : { stat : value } } } }
while(reportIter.hasNext()) {
var row = reportIter.next();
//Let's convert all the metrics to floats so we don't have to worry about it later
for(var i in row) {
if(i.indexOf('Id') == -1 && !isNaN(parseFloat(row[i]))) {
row[i] = parseFloat(row[i]);
}
}
var rpc = (row.Clicks != 0) ? round(row.ConversionValue/row.Clicks) : 0;
var roas = (row.AverageCpc != 0) ? round(rpc/row.AverageCpc) : 0;
var cpa = (row.Conversions != 0) ? round(row.Cost/row.Conversions) : 0;
row['Rpc'] = rpc;
row['Roas'] = roas;
row['Cpa'] = cpa;

//Build out the account mapping if needed
if(!accountMapping[row.CampaignId]) {
accountMapping[row.CampaignId] = {};
}
if(!accountMapping[row.CampaignId][row.AdGroupId]) {
accountMapping[row.CampaignId][row.AdGroupId] = {};
}
if(!accountMapping[row.CampaignId][row.AdGroupId][row.Id]) {
accountMapping[row.CampaignId][row.AdGroupId][row.Id] = {};
}
accountMapping[row.CampaignId][row.AdGroupId][row.Id][row.Device.split(' ')[0]] = row;
}
accountMapping = addDesktopTabletEntry(accountMapping);
return accountMapping;
}

// This function will return a valid AWQL query to find keyword performance
function buildAWQLQuery() {
var cols = ['CampaignId','AdGroupId','Id','Device','Clicks','Cost','Conversions','ConversionValue','AverageCpc'];
var report = 'KEYWORDS_PERFORMANCE_REPORT';
return ['select',cols.join(','),'from',report,'during',DATE_RANGE].join(' ');
}

// This function will add an entry into the accountMapping for combining Computers and Tablets
// since the Device targeting is only at that level
function addDesktopTabletEntry(accountMapping) {
for(var campaignId in accountMapping) {
var campaign = accountMapping[campaignId];
for(var adGroupId in campaign) {
var adGroup = campaign[adGroupId];
for(var keywordId in adGroup) {
var devices = adGroup[keywordId];

if(devices.Computers || devices.Tablets) {
accountMapping[campaignId][adGroupId][keywordId]['DesktopTablet'] = combineStats(devices.Computers,devices.Tablets);
}

//Since we combined these, we don't need them anymore
delete accountMapping[campaignId][adGroupId][keywordId]['Computers'];
delete accountMapping[campaignId][adGroupId][keywordId]['Tablets'];
}
}
}
return accountMapping;
}

// This function simply combines the stats of desktop and tablet performance
function combineStats(desktop,tablet) {
if(!desktop) {
desktop = {Cost : 0, Clicks: 0, ConversionValue : 0, Conversions : 0};
}
if(!tablet) {
tablet = {Cost : 0, Clicks: 0, ConversionValue : 0, Conversions : 0};
}
var totalCost = desktop.Cost + tablet.Cost;
var totalClicks = desktop.Clicks + tablet.Clicks;
var totalRevenue = desktop.ConversionValue + tablet.ConversionValue;
var totalConversions = desktop.Conversions + tablet.Conversions;
var averageCpc = (totalClicks != 0) ? round(totalCost/totalClicks) : 0;
var rpc = (totalClicks != 0) ? round(totalRevenue/totalClicks) : 0;
var roas = (averageCpc != 0) ? round(rpc/averageCpc) : 0;
var cpa = (totalConversions != 0) ? round(totalCost/totalConversions) : 0;

return {
Cost : totalCost,
Clicks: totalClicks,
ConversionValue : totalRevenue,
AverageCpc : averageCpc,
Rpc : rpc,
Roas : roas,
Cpa : cpa
};
}

// A helper function to make rounding a little easier
function round(value) {
var decimals = Math.pow(10,DECIMAL_PLACES);
return Math.round(value*decimals)/decimals;
}

// Validate some of the user options just in case
function hasValidOptions() {
var validDates = ['TODAY','YESTERDAY','LAST_7_DAYS','THIS_WEEK_SUN_TODAY',
'THIS_WEEK_MON_TODAY','LAST_WEEK','LAST_14_DAYS',
'LAST_30_DAYS','LAST_BUSINESS_WEEK','LAST_WEEK_SUN_SAT','THIS_MONTH'];
if(DATE_RANGE.indexOf(',') == -1 && validDates.indexOf(DATE_RANGE) == -1) {
throw 'DATE_RANGE is invalid. Current value is: '+DATE_RANGE;
}
if(DECIMAL_PLACES <= 0) {
throw 'You should set DECIMAL_PLACES to be >= 1. You entered: '+DECIMAL_PLACES;
}
if(['Campaign','AdGroup'].indexOf(LEVEL) == -1) {
throw 'LEVEL should be either Campaign or AdGroup. You entered: '+LEVEL;
}
if(['ROAS','RPC'].indexOf(METRIC) == -1) {
throw 'METRIC should be ROAS or RPC. You entered: '+METRIC;
}
if(LEVEL === 'AdGroup' && (!TO || TO.length == 0)) {
throw 'If you set LEVEL to AdGroup, you need to add at least one email.';
}
if(MINIMUM_BID_ADJUSTMENT < 0) {
throw 'MINIMUM_BID_ADJUSTMENT needs to be >= 0. Current value is: '+MINIMUM_BID_ADJUSTMENT;
}
if(MAXIMUM_BID_ADJUSTMENT > 3) {
throw 'MAXIMUM_BID_ADJUSTMENT needs to be <= 3. Current value is: '+MAXIMUM_BID_ADJUSTMENT;
}
}