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

var spreadsheetUrl = "https://docs.google.com/a/brainlabsdigital.com/spreadsheets/d/1JDGBPs2qyGdHd94BRZw9lE9JFtoTaB2AmlL7xcmLx2g/edit#gid=0";

//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;
}
}