
O Criador de grupos de anúncios do Shopping em massa possibilita criar grupos de anúncios e produtos em massa em Campanhas do Shopping existentes. Ele lê configurações de grupos de anúncios (como o lance e status padrão) e hierarquias de grupos de produtos, além de lances em uma planilha, e os cria no Google AdWords. A hierarquia do grupo de produtos precisa ser especificada no formato do AdWords Editor para Campanhas do Shopping.
Como funciona
O script usa a planilha de entrada e a processa linha por linha. Os nomes das campanhas especificados na planilha precisam ser existentes no Google AdWords. Caso contrário, um erro será exibido. Veja o que acontece se:
- Um grupo de anúncios especificado não existe
- O grupo de anúncios é criado com as configurações especificadas (lance padrão, status e promoção) junto aos seus novos grupos de produtos
- Um grupo de anúncios especificado já existe e não tem grupos de produtos (pouco provável)
- As configurações do grupo de anúncios não são alteradas e os novos grupos de produtos são criados
- Um grupo de anúncios especificado já existe e tem grupos de produtos
- As configurações do grupo de anúncios e os grupos de produtos existentes não são alterados
O script anexa o resultado do processamento de cada linha no fim da respectiva linha.
Observação: a visualização não está funcionando no momento com este script. Ela cria grupos de anúncios e os subdivide ainda mais. No entanto, como os grupos de produtos não são criados no modo de visualização, fazer uma operação com eles resulta em falha. O script ainda funcionará quando executado normalmente.
Especificações da planilha
O script exige a presença dos títulos de coluna a seguir na planilha:
- Campanha: nome da campanha
- Grupo de anúncios: nome do grupo de anúncios
- CPC máx.: lance padrão do grupo de anúncios, se estiver na linha do grupo de anúncios, e lance do grupo de produtos, se estiver na linha do grupo de produtos
- Grupo de produtos: especificação da hierarquia do grupo de produtos no formato do AdWords Editor
- Status do grupo de anúncios: status do grupo de anúncios Os valores podem ser "ativado" ou "pausado"
- Promoção: texto promocional do grupo de anúncios
Ele ignorará todas as outras colunas. Isso significa que somente essas configurações serão copiadas, e as outras configurações do grupo de anúncios NÃO estarão presentes no grupo de anúncios recém-criado.
A linha das configurações do grupo de anúncios precisa estar antes de qualquer linha de grupo de produtos no mesmo grupo de anúncios.
Se os grupos de produtos "todo o resto" forem especificados explicitamente na planilha, a linha precisa estar antes de todos os grupos de produtos no mesmo nível no mesmo grupo de anúncios. Se eles não forem especificados explicitamente, serão criados com o lance padrão do grupo de anúncios.
Configuração
- Crie uma planilha com os títulos de coluna acima e preencha os detalhes dos grupos de anúncios e de produtos. A aparência deve ser semelhante a este exemplo.
- Como alternativa, você pode fazer o download de uma campanha existente com a função exportar para .csv do AdWords Editor como um modelo, modificar o .csv e fazer o upload dele nas Planilhas Google. Se você optar por fazer isso, verifique se os grupos de anúncios na planilha são novos (não existentes).
- Verifique se as campanhas de destino existem no Google AdWords.
- Crie um novo script do Google AdWords usando o código-fonte abaixo.
- Não se esqueça de atualizar os parâmetros a seguir no script:
- SPREADSHEET_URL: URL a ser processado pela planilha
- SHEET_NAME: nome da planilha com dados a serem processados
Código-fonte
// Copyright 2015, Google Inc. All Rights Reserved. // // Licensed under the Apache License, Version 2.0 (the "License"); // you may not use this file except in compliance with the License. // You may obtain a copy of the License at // // http://www.apache.org/licenses/LICENSE-2.0 // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License. /** * @name Bulk Shopping AdGroup Creator * * @overview The Bulk Shopping AdGroup Creator provides a way to bulk create ad * groups and product groups in existing Shopping Campaigns. See * https://developers.google.com/adwords/scripts/docs/solutions/bulk-shopping-ad-group-creator * for more details. * * @author AdWords Scripts Team [adwords-scripts@googlegroups.com] * * @version 1.0.2 * * @changelog * - version 1.0.2 * - Removed use of ProductAdBuilder.withPromotionLine as it is deprecated. * - version 1.0.1 * - Added validation for external spreadsheet setup. * - version 1.0 * - Released initial version. */ /** * SPREADSHEET_URL: URL for spreadsheet to read * SHEET_NAME: Name of sheet in spreadsheet to read */ var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL'; var SHEET_NAME = 'YOUR_SHEET_NAME'; /** * Column header specification * These are the actual text the script looks for in the spreadsheet header. */ var CAMPAIGN_COLUMN = 'Campaign'; var AD_GROUP_COLUMN = 'Ad Group'; var MAX_CPC_COLUMN = 'Max CPC'; var PRODUCT_GROUP_COLUMN = 'Product Group'; var AD_GROUP_STATUS_COLUMN = 'AdGroup Status'; var REQUIRED_COLUMN_NAMES = {}; REQUIRED_COLUMN_NAMES[CAMPAIGN_COLUMN] = true; REQUIRED_COLUMN_NAMES[AD_GROUP_COLUMN] = true; REQUIRED_COLUMN_NAMES[MAX_CPC_COLUMN] = true; REQUIRED_COLUMN_NAMES[PRODUCT_GROUP_COLUMN] = true; REQUIRED_COLUMN_NAMES[AD_GROUP_STATUS_COLUMN] = true; /** End of column header specification */ /** * Reads campaign and bid data from spreadsheet and writes it to AdWords. */ function main() { Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL); var sheet = validateAndGetSpreadsheet(SPREADSHEET_URL, SHEET_NAME); parseSheetAndConstructProductGroups(sheet); Logger.log('Parsed spreadsheet and completed shopping campaign ' + 'construction.'); } //Stores results of each row, along with formatting details var resultsByRow = ['Result', 'Notes']; var resultColors = ['Black', 'Black']; /** * Validates header of sheet to make sure that header matches expected format. * Throws exception if problems are found. Saves the column number of each * expected column to global variable. * * @param {!Array.<string>} headerRow A list of column header names. * @return {!Object.<number>} A mapping from column name to column index. * @throws If required column is missing. */ function validateHeader(headerRow) { var result = {}; var missingColumns = Object.keys(REQUIRED_COLUMN_NAMES); // Grab the column # for each expected input column. for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) { var columnName = headerRow[columnIndex]; if (columnName in REQUIRED_COLUMN_NAMES) { result[columnName] = columnIndex; var index = missingColumns.indexOf(columnName); if (index >= 0) { missingColumns.splice(index, 1); } } } if (missingColumns.length > 0) { throw 'Bid sheet data format doesn\'t match expected format. ' + 'Missing columns: ' + missingColumns.join(); } return result; } /** * Converts a spreadsheet row into map representation. * * @param {!Array.<!Object>} row The spreadsheet row. * @param {!Object.<number>} headers Mapping from column name to column index. * @return {?Object} The row in object form, or null for a parsing error. */ function parseRow(row, headers) { var parsedRow = {}; for (var header in headers) { var colNum = headers[header]; var val = row[colNum].toString().trim(); if (!val) { continue; } // AWEditor will add double quotes (") around string if it contains // commas or double quotes, so we need to strip those out. if (val.charAt(0) == '"' && val.charAt(val.length - 1) == '"') { val = val.substring(1, val.length - 1); } // AWEditor escapes double quotes (") with another double quote (""). val = val.replace(/""/g, '"'); if (header == PRODUCT_GROUP_COLUMN) { var productGroups = []; var parsedProductGroups = parseAWEditorFormat(val); if (parsedProductGroups.error) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = parsedProductGroups.error; resultColors[0] = 'Red'; return null; } for (var x = 0; x < parsedProductGroups.length; x++) { // Product group type and value indices are level-1 // (e.g. for L1, x=0). productGroups[x] = { type: parsedProductGroups[x][0], value: parsedProductGroups[x][1] }; } parsedRow[header] = productGroups; } else { parsedRow[header] = val; } } // Ignore rows that don't have any useful information. var testRow = []; for (var k in parsedRow) { // Remove campaign, ad group columns and test if the rest is empty. if (k == CAMPAIGN_COLUMN || k == AD_GROUP_COLUMN) { continue; } testRow.push(parsedRow[k]); } if (testRow.toString().replace(/[,]+/g, '') == '') { resultsByRow[0] = 'SKIPPED'; resultsByRow[1] = 'Superfluous row'; return null; } return parsedRow; } /** * Parses spreadsheet and constructs ad groups and product groups in AdWords. * * @param {!Sheet} sheet The sheet to parse. */ function parseSheetAndConstructProductGroups(sheet) { var headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; var headers = validateHeader(headerRow); var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues(); var campaigns = {}; var outputColumn = sheet.getLastColumn() + 1; var resultHeaderRange = sheet.getRange(1, outputColumn, 1, 2); resultHeaderRange.setValues([resultsByRow]); resultHeaderRange.setFontColors([resultColors]); resultHeaderRange.setFontWeights([['Bold', 'Bold']]); // Iterate through rows. for (var r = 0; r < values.length; r++) { resultsByRow = ['', '']; resultColors = ['Black', 'Black']; var row = values[r]; var parsedRow = parseRow(row, headers); if (parsedRow) { var bid = parsedRow[MAX_CPC_COLUMN]; if (bid) { // For European locale, decimal points are commas. bid = bid.toString().toLowerCase().replace(/,/g, '.'); if (bid != 'excluded' && (isNaN(parseFloat(bid)) || !isFinite(bid))) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Invalid bid'; } } var campaignName = parsedRow[CAMPAIGN_COLUMN]; campaigns[campaignName] = fetchCampaignIfNecessary(campaigns, campaignName); if (campaigns[campaignName]) { var adGroupName = parsedRow[AD_GROUP_COLUMN]; campaigns[campaignName].createdAdGroups[adGroupName] = buildAdGroupIfNecessary(parsedRow, campaigns[campaignName], adGroupName, bid); if (campaigns[campaignName].createdAdGroups[adGroupName]) { if (!campaigns[campaignName].createdAdGroups[adGroupName].skip) { buildProductGroups(parsedRow, campaigns[campaignName].createdAdGroups[adGroupName], bid); } else { resultsByRow[0] = 'SKIPPED'; resultsByRow[1] = 'Ad group already exists with product groups'; } } } if (!resultsByRow[0]) { resultsByRow[0] = 'SUCCESS'; } } switch (resultsByRow[0]) { case 'ERROR': resultColors[0] = 'Red'; break; case 'SUCCESS': resultColors[0] = 'Green'; break; case 'WARNING': resultColors[0] = 'Yellow'; } var resultRange = sheet.getRange(r + 2, outputColumn, 1, 2); resultRange.setValues([resultsByRow]); resultRange.setFontColors([resultColors]); resultRange.setFontWeights([['Bold', 'Normal']]); } } /** * Fetches campaign from AdWords if it hasn't already been done. * * @param {!Object.<ShoppingCampaign>} campaigns A cache of campaigns. * @param {string} campaignName The name of the campaign to fetch. * @return {?ShoppingCampaign} The campaign, or null if not found. */ function fetchCampaignIfNecessary(campaigns, campaignName) { //Find the campaign if (!campaignName) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Missing campaign name'; return null; } var campaign = campaigns[campaignName]; if (!campaign) { campaign = findCampaignByName(campaignName); campaign.createdAdGroups = {}; if (!campaign) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Could not find campaign'; } } return campaign; } /** * Builds ad group if necessary, otherwise returns existing ad group. * * @param {!Array.<Object>} row A spreadsheet row. * @param {!ShoppingCampaign} campaign * @param {string} adGroupName The ad group to build or fetch. * @param {number} bid * @return {?ShoppingAdGroup} The ad group of null if there is an error. */ function buildAdGroupIfNecessary(row, campaign, adGroupName, bid) { if (!adGroupName) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Missing ad group name'; return null; } // See if we already fetched/created the ad group. var adGroup = campaign.createdAdGroups[adGroupName]; if (!adGroup) { // Only use the bid on this line for the ad group default bid if there are // no product groups specified for it. Ad group default bid must be // specified. if (row[PRODUCT_GROUP_COLUMN]) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Ad Group is missing a default bid.'; return null; } // Set default status to enabled. var status = 'ENABLED'; // If ad group status is specified, make sure it's "active", "enabled", or // "paused", and set status. Ad group status must be set on the first row // that the ad group appears in. if (row[AD_GROUP_STATUS_COLUMN]) { status = row[AD_GROUP_STATUS_COLUMN].toUpperCase(); if (status == 'ACTIVE') { status = 'ENABLED'; } } adGroup = createAdGroup(adGroupName, status, bid, campaign); if (adGroup) { adGroup.rootProductGroup = adGroup.rootProductGroup(); adGroup.rootProductGroup.childMap = {}; } } return adGroup; } /** * Builds product groups from row. * * @param {!Array.<Object>} row A spreadsheet row. * @param {!ShoppingAdGroup} adGroup The ad group to operate on. * @param {number} bid The product group bid. */ function buildProductGroups(row, adGroup, bid) { if (!row[PRODUCT_GROUP_COLUMN]) { return; } // Iterate through product groups in row. var maxLevel = -1; var productGroupsToAdd = row[PRODUCT_GROUP_COLUMN]; var productGroup = adGroup.rootProductGroup; for (var i = 0; i < productGroupsToAdd.length; i++) { var type = productGroupsToAdd[i].type.toString().toLowerCase() .replace(/[ ]+/g, ''); var val = productGroupsToAdd[i].value.toString().trim(); if (type) { //For each Group level n, row must contain values for 1...n-1 if (i - maxLevel > 1) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Every level of the product ' + 'group type must have all higher ' + 'level values. L' + i + ' is filled but missing L' + (maxLevel + 1); return; } maxLevel = i; // Each row must have matching # of bidding attribute type and value. if (!val) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Every product group type must have an associated value. L' + i + ' has a type but no value'; return; } // Build product groups. if (!productGroup.childMap[val.toLowerCase()]) { if (val == '*') { if (Object.keys(productGroup.childMap).length == 0) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = '"Everything else" product group must come after all others'; return; } else { var child = productGroup.childMap[val]; if (!child) { var children = productGroup.children().get(); while (children.hasNext()) { child = children.next(); if (child.isOtherCase()) { break; } } child.childMap = {}; } productGroup.childMap[val] = child; productGroup = child; productGroup.setMaxCpc(adGroup.getMaxCpc()); //Only assign the bid to the lowest level product group //on that row if (i + 1 == productGroupsToAdd.length) { if (bid != 'excluded') { productGroup.setMaxCpc(bid); } else { productGroup.exclude(); } } } } else { var productGroupBuilder = productGroup.newChild(); // Verify that bidding attribute type is valid, construct // productGroupBuilder. switch (type) { case 'producttype': val = val.toLowerCase(); productGroupBuilder = productGroupBuilder.productTypeBuilder() .withValue(val); break; case 'brand': val = val.toLowerCase(); productGroupBuilder = productGroupBuilder.brandBuilder() .withName(val); break; case 'category': productGroupBuilder = productGroupBuilder.categoryBuilder() .withName(val); break; case 'condition': val = val.toUpperCase(); productGroupBuilder = productGroupBuilder.conditionBuilder() .withCondition(val); break; case 'itemid': val = val.toLowerCase(); productGroupBuilder = productGroupBuilder.itemIdBuilder() .withValue(val); break; default: if (type.match(/^custom((\\s)?(label|attribute))?/)) { val = val.toLowerCase(); //make sure there's a number at the end that's between 0-4 if (type.match(/[0-4]$/)) { productGroupBuilder = productGroupBuilder.customLabelBuilder() .withType('CUSTOM_LABEL_' + type.substring(type.length - 1)) .withValue(val); } else { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Invalid custom attribute type: ' + productGroupsToAdd[i].type; return; } } else { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Invalid bidding attribute type: ' + productGroupsToAdd[i].type; return; } } var productGroupOp = productGroupBuilder.build(); if (!productGroupOp.isSuccessful()) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Error creating product group ' + 'level ' + (i + 1) + ': ' + productGroupOp.getErrors(); return; } var result = productGroupOp.getResult(); // Only assign the bid to the lowest level product group on that row. if (i + 1 == productGroupsToAdd.length) { if (bid == 'excluded') { result.exclude(); } else if (bid) { result.setMaxCpc(bid); } } result.childMap = {}; productGroup.childMap[val.toLowerCase()] = result; // Set current product group to the newly created product group. productGroup = result; } } else { // Set current product group to the last read product group. productGroup = productGroup.childMap[val.toLowerCase()]; } } } } /** * Parses AdWords Editor product group format * (e.g. * / Condition='New' / Custom label 2='furniture' / * Product type='bar carts'). * * @param {string} productGroupPath The product group path. * @return {!Array.<!Array.<string>>} A list of product group component name/ * value pairs. */ function parseAWEditorFormat(productGroupPath) { // Ignore * / case which is the root product group. if (productGroupPath == '* /' || !productGroupPath) { return []; } var regexVals = productGroupPath.match(new RegExp(/'(.*?)'/g)); if (regexVals) { for (var i = 0; i < regexVals.length; i++) { productGroupPath = productGroupPath.replace(regexVals[i], '$' + i); } } var result = []; var productGroup = productGroupPath.split('/'); // AW Editor format starts with '* /' so we ignore first one. for (var x = 1; x < productGroup.length; x++) { if (!productGroup[x]) { continue; } // AW Editor format looks like: Brand='nike'. var pair = productGroup[x].trim().split('='); if (pair.length != 2) { return {error: 'Product group string malformed. Should have 1 "=", ' + 'but has ' + (pair.length - 1)}; } var val = pair[1]; if (val.charAt(0) != '$' && val.charAt(0) != '*') { return {error: 'Product group string malformed. Please ensure you are ' + 'using AdWords Editor format'}; } // '*' value doesn't have single quotes around it. if (val != '*') { var values = pair[1].split('$'); // Skip 0 because it's always blank. String always starts with $. for (var i = 1; i < values.length; i++) { val = val.replace('$' + values[i], regexVals[values[i]]); } val = val.substring(1, val.length - 1).replace(/''/g, '\''); } result.push([pair[0], val]); } return result; } /** * Fetches campaign from AdWords by name. * * @param {string} name The campaign name. * @return {?ShoppingCampaign} The found campaign, or null if not found. */ function findCampaignByName(name) { var campaignName = name.replace(/'/g, '\\\''); var shoppingCampaignSelector = AdWordsApp .shoppingCampaigns() .withCondition('Name = \'' + campaignName + '\''); var campaign = null; var shoppingCampaignIterator = shoppingCampaignSelector.get(); if (shoppingCampaignIterator.hasNext()) { campaign = shoppingCampaignIterator.next(); } return campaign; } /** * Fetches ad group from AdWords given ad group name and campaign. * * @param {string} agName The name of the ad group. * @param {!ShoppingCampaign} campaign The campaign within which to search. * @return {?ShoppingAdGroup} The ad group or null if not found. */ function findAdGroupByName(agName, campaign) { var adGroupName = agName.replace(/'/g, '\\\''); var adGroupSelector = campaign .adGroups() .withCondition('Name = \'' + adGroupName + '\''); var adGroup = null; var adGroupIterator = adGroupSelector.get(); if (adGroupIterator.hasNext()) { adGroup = adGroupIterator.next(); } return adGroup; } /** * Creates ad group in AdWords if it doesn't already exist, along with ad group * ad. * * @param {string} name The name of the ad group. * @param {string} status The desired status of the ad group. * @param {number} defaultBid The max CPC for the ad group. * @param {!ShoppingCampaign} campaign The campaign to create the ad group for. * @return {?ShoppingAdGroup} The created ad group or null if there is an error. */ function createAdGroup(name, status, defaultBid, campaign) { // See if ad group exists. If so, fetch it. var adGroup = findAdGroupByName(name, campaign); if (adGroup != null) { if (adGroup.rootProductGroup()) { // If root product group exists and not delete, then skip ad group. adGroup.skip = true; } else { adGroup.createRootProductGroup(); } return adGroup; } // Build ad group. var adGroupOp = campaign.newAdGroupBuilder() .withName(name) .withStatus(status) .withMaxCpc(defaultBid) .build(); // Check for errors. if (!adGroupOp.isSuccessful()) { resultsByRow[0] = 'ERROR'; resultsByRow[1] = 'Error creating ad group: ' + adGroupOp.getErrors(); return null; } var adGroupResult = adGroupOp.getResult(); adGroupResult.createRootProductGroup(); Logger.log('Successfully created ad group [' + adGroupResult.getName() + ']'); // Build ad group ad. var adGroupAdOp = adGroupResult.newAdBuilder().build(); // Check for errors. if (!adGroupAdOp.isSuccessful()) { resultsByRow[0] = 'WARNING'; resultsByRow[1] = 'Error creating ad group ad: ' + adGroupAdOp.getErrors(); } return adGroupResult; } /** * DO NOT EDIT ANYTHING BELOW THIS LINE. * Please modify your spreadsheet URL and email addresses at the top of the file * only. */ /** * Validates the provided spreadsheet URL and email address * to make sure that they're set up properly. Throws a descriptive error message * if validation fails. * * @param {string} spreadsheeturl The URL of the spreadsheet to open. * @param {string} sheetname The name of the sheet within the spreadsheet that * should be fetched. * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL. * @throws {Error} If the spreadsheet URL or email hasn't been set */ function validateAndGetSpreadsheet(spreadsheeturl, sheetname) { if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') { throw new Error('Please specify a valid Spreadsheet URL. You can find' + ' a link to a template in the associated guide for this script.'); } if (sheetname == 'YOUR_SHEET_NAME') { throw new Error('Please specify the name of the sheet you want to use on' + ' your spreadsheet.'); } var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl); var sheet = spreadsheet.getSheetByName(sheetname); return sheet; }