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