⚡️ Comunidad orientada a crear líderes de negocio digital

Scripts Google Ads

Los scripts de Google Ads permiten a los anunciantes optimizar y gestionar sus campañas de forma eficiente. Mediante el uso de JavaScript, estos scripts pueden automatizar tareas repetitivas, ajustar pujas en tiempo real y generar informes personalizados, mejorando así el rendimiento y ahorrando tiempo.
Índice de contenidos

Definición de scripts Google Ads 🧐

Los scripts de Google Ads son fragmentos de código en JavaScript que se utilizan para automatizar la gestión y optimización de las campañas publicitarias. Imagina tener un asistente virtual que puede ajustar las pujas, pausar anuncios que no están funcionando y generar informes personalizados sin que tú tengas que mover un dedo.

Beneficios de usar scripts de Google Ads 🔥

El uso de scripts en Google Ads ofrece múltiples beneficios. Por ejemplo, puedes automatizar la tarea de ajustar las pujas basándote en el rendimiento de las palabras clave, lo que garantiza que siempre estés obteniendo el máximo retorno de inversión.

Además, puedes programar scripts para pausar anuncios que están agotando tu presupuesto sin generar resultados, o para aumentar la visibilidad de tus mejores anuncios durante las horas pico.

Cómo funcionan los scripts de Google Ads 🚀

Los scripts de Google Ads funcionan mediante la ejecución de código JavaScript en la plataforma de Google Ads. Este código puede interactuar con tus campañas para realizar tareas específicas.

Por ejemplo, puedes escribir un script que revise diariamente el rendimiento de tus anuncios y ajuste automáticamente las pujas para las palabras clave que están generando más conversiones. Todo esto se hace de manera programada, lo que significa que puedes dejar que los scripts trabajen por ti incluso cuando no estás frente a la computadora.

Cómo instalar los scripts en Google Ads 🛠️

1. Accedemos a la secuencia de comandos en Herramientas y configuración.

Paso 1 para instalar un script en google ads

2. Le damos al “+” y a nuevo scrip

paso 2 para instalar un script de google ads

Tipos de scripts Google Ads 👇🏽

Existen varios tipos de scripts que puedes usar en Google Ads, cada uno con su propósito específico. Algunos ejemplos incluyen:

Scripts de informes: Generan informes detallados sobre el rendimiento de tus campañas y los envían a tu correo electrónico.
Scripts de optimización: Ajustan automáticamente las pujas y las palabras clave para mejorar el rendimiento.
Scripts de alerta: Te notifican cuando ocurren ciertos eventos, como un gasto excesivo en un anuncio o un descenso en el rendimiento. Estos son solo algunos ejemplos, pero las posibilidades son casi infinitas.

Ventajas y desventajas de usar scripts Google Ads 🤙🏽

Ventajas de usar scripts

  1. Automatización de Tareas Repetitivas: Los scripts permiten automatizar tareas rutinarias como ajustes de pujas, generación de informes y monitoreo de rendimiento, liberando tiempo para enfocarse en estrategias de mayor impacto.
  2. Optimización en Tiempo Real: Con scripts, puedes ajustar las pujas y modificar las campañas en tiempo real basándote en el rendimiento de los anuncios, lo que puede mejorar significativamente el ROI y la eficiencia de tus campañas.
  3. Generación de Informes Personalizados: Puedes crear scripts que generen informes detallados y personalizados que se ajusten a tus necesidades específicas, enviándolos automáticamente a tu correo electrónico o almacenándolos en Google Sheets.
  4. Detección y Corrección de Errores: Los scripts pueden programarse para detectar anomalías o errores en las campañas, como anuncios que no están generando conversiones, y corregirlos automáticamente antes de que causen un impacto negativo.
  5. Escalabilidad: Los scripts facilitan la gestión de grandes volúmenes de campañas y anuncios, permitiendo a los anunciantes escalar sus esfuerzos publicitarios sin aumentar proporcionalmente la carga de trabajo manual.

Desventajas de usar scripts

  1. Requieren Conocimientos de Programación: Para crear y modificar scripts eficientemente, se necesitan conocimientos de JavaScript. Esto puede ser una barrera para los anunciantes que no tienen experiencia en programación.
  2. Posibilidad de Errores: Si un script no está bien escrito o contiene errores, puede causar problemas en las campañas, como ajustes de pujas incorrectos o pausas no deseadas de anuncios, afectando negativamente el rendimiento.
  3. Dependencia de Automatización: Confiar excesivamente en scripts puede llevar a pasar por alto el análisis estratégico y la creatividad humana, que son cruciales para el éxito de las campañas publicitarias.
  4. Mantenimiento y Actualización: Los scripts necesitan ser revisados y actualizados regularmente para adaptarse a cambios en las campañas y a las nuevas funcionalidades de Google Ads. Esto requiere tiempo y recursos continuos.
  5. Limitaciones en Funcionalidades: Aunque los scripts son muy útiles, tienen limitaciones en cuanto a las acciones que pueden realizar y la frecuencia con la que pueden ejecutarse. Algunas tareas complejas pueden no ser totalmente automatizables con scripts.

Scripts Google Ads para Pmax (Performance Max)

Performance Max Script de Mike Rhodes

script gasto google ads pmax

Pmax no nos permite saber el gasto que tiene en cada red, es por eso por lo que este script nos permite ver los costes asignados a:

  • Shopping
  • Display
  • Vídeo
  • Search

Además de lo que ve en la captura de pantalla, tenemos más tablas donde puede ver cifras de gasto, ROAS y más para cada campaña.

Script Google Ads – Pmax (Performance Max)

Performance Max Script de Mike Rhodes

script gasto google ads pmax

Ya que no podemos saber a que redes de Performance Max destina la inversión Google Ads, es por eso que este script es el más útil de todos.

Este, mediante un Google Sheet nos permite ver el gasto que se realiza en las diferentes redes de pmax:

  • Shopping
  • Display
  • Video
  • Search
				
					
// want the paid version? head to www.mikerhodes.com.au
// or buy it here: https://mikerhodes.circle.so/checkout/latest-script
// it does everything the free script does, plus much more
// and includes free updates, support, some training & more

// v28 (free - does not include support)
// make a copy of this template sheet first & copy your URL into the ss variable on line 11. template: https://docs.google.com/spreadsheets/d/1aGOBOLNUjEIwwlYuS0D3fcIimRnzGdLSJxVd9rHOQ0E/copy

function main() {
  let ss = SpreadsheetApp.openByUrl(''); 
  let zombieDays = 366   // how many days data do you want to use to identify zombie (0 click) products?
  let prodDays = 181     // how many days data do you want to use to identify 'long range' products?  



// don’t change any code below this line ——————————————————————————————————————————————————————————————————————————————



  // define query elements. wrap with spaces for safety
  let impr        = ' metrics.impressions ';
  let clicks      = ' metrics.clicks ';
  let cost        = ' metrics.cost_micros ';
  let conv        = ' metrics.conversions '; 
  let value       = ' metrics.conversions_value '; 
  let allConv     = ' metrics.all_conversions '; 
  let allValue    = ' metrics.all_conversions_value '; 
  let views       = ' metrics.video_views ';
  let cpv         = ' metrics.average_cpv ';
  let segDate     = ' segments.date ';  
  let prodTitle   = ' segments.product_title ';
  let prodID      = ' segments.product_item_id ';
  let prodC0      = ' segments.product_custom_attribute0 ';
  let prodC1      = ' segments.product_custom_attribute1 ';
  let prodC2      = ' segments.product_custom_attribute2 ';
  let prodC3      = ' segments.product_custom_attribute3 ';
  let prodC4      = ' segments.product_custom_attribute4 '; 
  let campName    = ' campaign.name ';
  let chType      = ' campaign.advertising_channel_type ';
  let adgName     = ' ad_group.name ';
  let adStatus    = ' ad_group_ad.status ';
  let adPerf      = ' ad_group_ad_asset_view.performance_label ';
  let adType      = ' ad_group_ad_asset_view.field_type ';
  let aIdAsset    = ' asset.resource_name ';  
  let aId         = ' asset.id ';
  let assetType   = ' asset.type ';
  let aFinalUrl   = ' asset.final_urls ';
  let assetName   = ' asset.name ';
  let assetText   = ' asset.text_asset.text ';
  let assetSource = ' asset.source ' ; 
  let adUrl       = ' asset.image_asset.full_size.url ';
  let ytTitle     = ' asset.youtube_video_asset.youtube_video_title ';
  let ytId        = ' asset.youtube_video_asset.youtube_video_id ';
  let agId        = ' asset_group.id ';    
  let assetFtype  = ' asset_group_asset.field_type ';
  let adPmaxPerf  = ' asset_group_asset.performance_label ';  
  let agStrength  = ' asset_group.ad_strength ';
  let agStatus    = ' asset_group.status ';
  let asgName     = ' asset_group.name ';
  let lgType      = ' asset_group_listing_group_filter.type ';  
  let aIdCamp     = ' segments.asset_interaction_target.asset ';
  let interAsset  = ' segments.asset_interaction_target.interaction_on_this_asset ';
  let pMaxOnly    =	' AND campaign.advertising_channel_type = "PERFORMANCE_MAX" '; 
  let searchOnly  =	' AND campaign.advertising_channel_type = "SEARCH" ';   
  let agFilter    =	' AND asset_group_listing_group_filter.type != "SUBDIVISION" ';   
  let adgEnabled  = ' AND ad_group.status = "ENABLED" AND campaign.status = "ENABLED" AND ad_group_ad.status = "ENABLED" ';
  let asgEnabled  = ' asset_group.status = "ENABLED" AND campaign.status = "ENABLED" ';           
  let notInter    = ' AND segments.asset_interaction_target.interaction_on_this_asset != "TRUE" ';
  let inter       = ' AND segments.asset_interaction_target.interaction_on_this_asset = "TRUE" ';
  let date07      = ' segments.date DURING LAST_7_DAYS ';  
  let date30      = ' segments.date DURING LAST_30_DAYS ';  
  let order       = ' ORDER BY campaign.name '; 
  let orderImpr   = ' ORDER BY metrics.impressions DESC '; 
  
  
  //  Date stuff
  let MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  let now = new Date();
  let from = new Date(now.getTime() - zombieDays * MILLIS_PER_DAY);        // xx days in the past - default 366
  let prod180 = new Date(now.getTime() - prodDays * MILLIS_PER_DAY);       // xx days in the past - default 181    
  let to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);                   // yesterday
  let timeZone = AdsApp.currentAccount().getTimeZone(); 
  let zombieRange = ' segments.date BETWEEN "' + Utilities.formatDate(from, timeZone, 'yyyy-MM-dd') + '" AND "' + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + '"' 
  let prodDate = ' segments.date BETWEEN "' + Utilities.formatDate(prod180, timeZone, 'yyyy-MM-dd') + '" AND "' + Utilities.formatDate(to, timeZone, 'yyyy-MM-dd') + '"'
  
  // build queries                     
  let cd = [segDate, campName, cost, conv, value, views, cpv, impr, clicks, chType] // campaign by day
  let campQuery = 'SELECT ' + cd.join(',') + 
      ' FROM campaign ' +
      ' WHERE ' + date30 + pMaxOnly + order ; 
  
  let dv = [segDate, campName, aIdCamp, cost, conv, value, views, cpv, impr, chType, interAsset] // inter by day
  let dvQuery = 'SELECT ' + dv.join(',') + 
      ' FROM campaign ' +
      ' WHERE ' + date30 + pMaxOnly + notInter + order ; 
  
  let p = [campName, prodTitle, cost, conv, value, impr, chType,prodID,prodC0,prodC1,prodC2,prodC3,prodC4] // product totals 
  let pQuery = 'SELECT ' + p.join(',')  + 
      ' FROM shopping_performance_view  ' + 
      ' WHERE ' + date30 + pMaxOnly + order ; 
  let p180Query = 'SELECT ' + p.join(',')  + 
      ' FROM shopping_performance_view  ' + 
      ' WHERE ' + prodDate + pMaxOnly + order ;   

  let ag = [segDate, campName, asgName, agStrength, agStatus, lgType, impr, clicks, cost, conv, value] // asset group by day
  let agQuery = 'SELECT ' + ag.join(',')  + 
      ' FROM asset_group_product_group_view ' +
      ' WHERE ' + date30 + agFilter ;

  let assets = [aId, aFinalUrl, assetSource, assetType, ytTitle, ytId, assetText, aIdAsset, assetName] // names, IDs, URLs for all ad assets in account
  let assetQuery = 'SELECT ' + assets.join(',')  + 
      ' FROM asset ' ;
  
  let ads = [campName, asgName, agId, aIdAsset, assetFtype, adPmaxPerf, agStrength, agStatus, assetSource] // pMax ads
  let adsQuery = 'SELECT ' + ads.join(',') +
      ' FROM asset_group_asset ' ;
  
  let zombies = [prodID, clicks, impr, prodTitle] // zombie (0click) products - last xx days, set xx days at top of script
  let zQuery = 'SELECT ' + zombies.join(',') +
      ' FROM shopping_performance_view ' +
      ' WHERE metrics.clicks < 1 AND ' + zombieRange + orderImpr ;  


  // call report function to pull data & push it to the named tabs in the sheet
  runReport(campQuery,  ss.getSheetByName('r_camp'));  
  runReport(dvQuery,    ss.getSheetByName('r_dv'));     
  runReport(pQuery,     ss.getSheetByName('r_prod_t')); 
  runReport(p180Query,  ss.getSheetByName('r_prod_t_180'));   
  runReport(agQuery,    ss.getSheetByName('r_ag'));   
  runReport(assetQuery, ss.getSheetByName('r_allads'));
  runReport(adsQuery,   ss.getSheetByName('r_ads'));   
  runReport(zQuery,     ss.getSheetByName('zombies')); 
  
} // end main



// query & export report data to named sheet
function runReport(q,sh) {
  const report = AdsApp.report(q);
  report.exportToSheet(sh);  
}
				
			

Labelizar de Floris de Schrijver

script pmax labelizer

Este script nos dará datos de nuestros productos según diferentes segmentos en función de su rentabilidad y rendimiento que hayan obtenido

  • Index: productos que cumplen con nuestros requisitos de ROAS
  • Over – index: productos que superan nuestro objetivo.
  • Near-index: productos con buen rendimiento que no están teniendo un alto número de impresiones. Nuestro objetivo es potenciarlo.
  • Under-index: productos con un alto coste que no están teniendo ventas.
  • No-index: productos que no están apareciendo.
				
					// Flowboost Labelizer 1.1 by Floris de Schrijver
// custom requests? floris@flowboost.co
// V1.1 - enter your data as variables 
// make copy of the spreadsheet
// add the spreadsheet URL as supplemental feed to Google merchant center (check cel H1 for custom_label_1, label or product type)
// ------- make sure you register at https://library.flowboost.co for other free scripts, automation tips, how-to and much more (to come)
// ------- updates will be uploaded in the library first 
// -- v3.1 is live since july 2023 > advanced settings, better segmentation en graphs
// ------- Newsletters to check > 
// -- PPC Edge newsletter > https://www.ppcmastery.com/blog/ 
// -------
// let it run daily, smooth sailing
// be aware: products with no impressions will not be added, include all other items in the no-index campaign or ad group
// -------
// Enter your data here >
var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1AHPMy__XMFBtufgsc-RQWcktAjX2K_GZyy3nTUqprWI/copy"; // make a copy of the spreadsheet, don’t change any tabs in the sheet
var breakevenRoas = 6.5; // set it 10-20% lower than the account target
var AverageCvr = 5; // conversion rate on average in shopping in past daysAgo
var impressionThreshold = 50; // less than 50 means a ‘zombie’ or ‘no-index’
var daysAgo = 90; // days you like to look back at
// Advanced settings only available in premium labelizer script (get it in the library)
 // - conversion lag in days
 // - segment on 1 or multiple campaign(s) only
 // - capitalization bug
 // - 4 graphs and over time improvement
 // - bucket for proven bad products
 // - creates buckets based relative- instead of absolute difference in ROAS
 // - bucket based on tCPA or tROAS
// Start script don't edit anything below here
function main(){
var products = getFilteredShoppingProducts(daysAgo);
products.sort(function(a,b){return a[0] > b[0];});
products = products.slice(0, 999999);
pushToSpreadsheet(products);
}
function getFilteredShoppingProducts(daysAgo){
var today = new Date();
var daysAgo = new Date(today.getFullYear(), today.getMonth(), today.getDate() - daysAgo);
var dateFrom = Utilities.formatDate(daysAgo, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
var dateTo = Utilities.formatDate(today, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd'); 
var query = 
 "SELECT OfferId, Impressions, Clicks, Ctr, Cost, Conversions, ConversionValue " +
 "FROM SHOPPING_PERFORMANCE_REPORT " +
 "DURING "+ dateFrom +","+ dateTo; 
var products = [];
var count = 0;
var report = AdWordsApp.report(query);
var rows = report.rows();
while (rows.hasNext()){
var row = rows.next();
var offer_id = row['OfferId'];
var impressions = row['Impressions'].toString();
var clicks = row['Clicks'].toString();
var cost = row['Cost'].toString();
var conversions = row['Conversions'].toString();
var conversionValue = row['ConversionValue'].toString();
var convValuePerCost = (conversionValue.replace(",", "") / cost.replace(",", "")).toString();
if (isNaN(convValuePerCost)){
 convValuePerCost = 0;
} 
var isProductType = '';
if (clicks > ( 300 / AverageCvr ) && convValuePerCost >= breakevenRoas + 1){
 isProductType = 'over-index';
} else if (clicks >= ( 100 / AverageCvr ) && convValuePerCost >= breakevenRoas ){
 isProductType = 'index';
} else if (convValuePerCost >= breakevenRoas - 1){
 isProductType = 'near-index'; 
} else if (impressions < impressionThreshold){
 isProductType = 'no-index';
} else {
 isProductType = 'under-index';
}
products.push([offer_id, impressions, clicks, cost, conversions, conversionValue, convValuePerCost, isProductType]);
count+= 1;
}
Logger.log(count);
return products;
}
function pushToSpreadsheet(data){
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName('Flowbelizer');
var lastRow = sheet.getMaxRows();
sheet.getRange('A2:H'+lastRow).clearContent();
var start_row=2;
var endRow=start_row+data.length-1;
var range = sheet.getRange('A'+start_row+':'+'H'+endRow);
if (data.length>0){range.setValues(data);}
return;
}
				
			

Link Checker Script de Tibbe Van Asten

Este script sirve a nivel MCC para detectar anomalías como por ejemplo caídas de URL que afecten a nuestras campañas.

Este, comprueba automáticamente si las URL que utilizan tus anuncios o extensiones son accesibles. Si la página no está disponible temporalmente o el script encuentra un error 404, se documenta y puede enviarte un correo electrónico.

				
					// Copyright 2016, 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 Link Checker
 *
 * @overview The Link Checker script iterates through the ads, keywords, and
 *     sitelinks in your account and makes sure their URLs do not produce "Page
 *     not found" or other types of error responses. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/link-checker
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 3.1
 *
 * @changelog
 * - version 4.0
 *   - Refactored for readability and efficiency - particularly for sitelinks.
 * - version 3.1
 *   - Split into info, config, and code.
 * - version 3.0
 *   - Updated to use new Google Ads scripts features.
 * - version 2.2
 *   - Added support for failure strings and custom validation functions.
 * - version 2.1
 *   - Added expansion of conditional ValueTrack parameters (e.g. ifmobile).
 *   - Added expanded text ad and other ad format support.
 * - version 2.0.3
 *   - Added validation for external spreadsheet setup.
 * - version 2.0.2
 *   - Allow the custom tracking label to include spaces.
 * - version 2.0.1
 *   - Catch and output all UrlFetchApp exceptions.
 * - version 2.0
				
			

Script Nivel de calidad de la cuenta

El nivel de calidad es una de las métricas que nunca debemos perder de vista en una cuenta de Google Ads. Sin embargo, el hecho de que sea a nivel de palabras clave puede dificultar ver la causa y efecto de las optimizaciones y evoluciones a lo largo del tiempo. Para ello, se recomienda este script, ya que permite seguir la evolución del nivel de calidad de toda la cuenta tomando las palabras clave activas y ponderándolas por el número de impresiones. Esto permite ver el resultado del trabajo de optimización en la evolución del nivel de calidad de la cuenta:

				
					/***************************************
* Store Account Level Quality Score in Google Spreadsheet.
* Version 1.1
* ChangeLog v1.1
*  - Changed ACCOUNT_NAME to SHEET_NAME and updated the default value.
*  - Removed getSpreadsheet function
*
* Created By: Russ Savage
* Based on script originally found at: http://goo.gl/rTHbF
* FreeAdWordsScripts.com
*********************************/
function main() {
  var SPREADSHEET_URL = "Your Spreadsheet Url Goes Here";
  var SHEET_NAME = 'Sheet1';
  var today = new Date();
  var date_str = [today.getFullYear(),(today.getMonth() + 1),today.getDate()].join("-");
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var qs_sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  var kw_iter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .forDateRange("LAST_30_DAYS")
    .withCondition("Impressions > 0")
    .orderBy("Impressions DESC")
    .withLimit(50000)
    .get();

  var tot_imps_weighted_qs = 0;
  var tot_imps = 0;
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var kw_stats = kw.getStatsFor("LAST_30_DAYS");
    var imps = kw_stats.getImpressions();
    var qs = kw.getQualityScore();
    tot_imps_weighted_qs += (qs * imps);
    tot_imps += imps;
  }
    
  var acct_qs = tot_imps_weighted_qs / tot_imps;
  
  qs_sheet.appendRow([date_str,acct_qs]);
}
				
			

Detector de anomalías en una cuenta

Script Detector de anomalías en una cuenta

El script Detector de anomalías de la cuenta alerta al anunciante cada vez que una cuenta de Google Ads se comporta de forma muy diferente a lo que hacía anteriormente.

Cuando se encuentra un problema, la secuencia de comandos enviará al usuario un correo electrónico de alerta.

				
					// Copyright 2017, 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 Account Anomaly Detector
 *
 * @fileoverview The Account Anomaly Detector alerts the advertiser whenever an
 * advertiser account is suddenly behaving too differently from what's
 * historically observed. See
 * https://developers.google.com/google-ads/scripts/docs/solutions/account-anomaly-detector
 * for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.2
 *   - Fixed time conversion issue.
 *   - Moved email code.
 * - version 2.1
 *   - Split into info, config, and code.
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.1.1
 *   - Fixed bug in handling of reports with 0 rows.
 * - version 1.1
 *   - Added conversions to tracked statistics.
 * - version 1.0.3
 *   - Improved code readability and comments.
 * - version 1.0.2
 *   - Added validation for external spreadsheet setup.
 *   - Updated to use report version v201609.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * Configuration to be used for the Account Anomaly Detector.
 */
CONFIG = {
  // URL of the default spreadsheet template. This should be a copy of
  // https://docs.google.com/spreadsheets/u/1/d/1_KjU9UI8EyWQCIE6xlxnUVaomEaHGJW-dgfkA15JhZY/copy
  'spreadsheet_url': 'YOUR_SPREADSHEET_URL',

  // More reporting options can be found at
  // https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp#report_2
  'reporting_options': {
    // Comment out the following line to default to the latest reporting version.
    'apiVersion': 'v10'
  }
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const REPORTING_OPTIONS = CONFIG.reporting_options;

const FIELDS = ['segments.hour', 'segments.day_of_week', 'metrics.clicks',
        'metrics.impressions', 'metrics.conversions', 'metrics.cost_micros'];
const DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
            'Saturday', 'Sunday'];

/**
 * This script detects the anomalies(if any) in the account and alerts the
 * customer in form of a mail and spreadsheet.
 */

function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());

  const impressionsThreshold = parseField(spreadsheet.
      getRangeByName('impressions').getValue());
  const clicksThreshold = parseField(spreadsheet.getRangeByName('clicks').
      getValue());
  const conversionsThreshold =
      parseField(spreadsheet.getRangeByName('conversions').getValue());
  const costThreshold = parseField(spreadsheet.getRangeByName('cost').getValue());
  const weeksStr = spreadsheet.getRangeByName('weeks').getValue();
  const weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')),10);
  const email = spreadsheet.getRangeByName('email').getValue();

  const now = new Date();

  // Basic reporting statistics are usually available with no more than a 3-hour
  // delay.
  const upTo = new Date(now.getTime() - 3 * 3600 * 1000);
  const upToHour = parseInt(getDateStringInTimeZone('H', upTo),10);

  if (upToHour == 1) {
    // first run for the day, kill existing alerts
    spreadsheet.getRangeByName('clicks_alert').clearContent();
    spreadsheet.getRangeByName('impressions_alert').clearContent();
    spreadsheet.getRangeByName('conversions_alert').clearContent();
    spreadsheet.getRangeByName('cost_alert').clearContent();
  }

  const dateRangeToCheck = getDateStringInPast(0, upTo);
  const dateRangeToEnd = getDateStringInPast(1, upTo);
  const dateRangeToStart = getDateStringInPast(1 + weeks * 7, upTo);
  const fields = FIELDS.join(",");
  const dayOfWeekIndex = parseInt(getDateStringInTimeZone('u', now), 10);
  const dayOfWeek = DAYS[dayOfWeekIndex].toUpperCase();
  const todayQuery = `SELECT ${fields} FROM customer ` +
      `WHERE segments.date BETWEEN "${dateRangeToCheck}" ` +
      `AND "${dateRangeToCheck}"`;
  const pastQuery = `SELECT ${fields} FROM customer ` +
      `WHERE segments.day_of_week=` +
      `"${dayOfWeek}" ` +
      `AND segments.date BETWEEN "${dateRangeToStart}" ` +
      `AND "${dateRangeToEnd}"`;
  const todayStats = getReportStats(todayQuery, upToHour, 1);
  const pastStats = getReportStats(pastQuery, upToHour, weeks);

  const statsExist = todayStats && pastStats;
  const formattedHour = `${upToHour}:00`;

  const alertText = [];
  if (statsExist) {
    if (impressionsThreshold &&
        todayStats.impressions < pastStats.impressions * impressionsThreshold) {
      const ImpressionsAlert = `    Impressions are too low: ` +
          `${todayStats.impressions} impressions by ${formattedHour},` +
          ` expecting at least ` +
          `${parseInt(pastStats.impressions * impressionsThreshold,10)}`;
      writeAlert(spreadsheet, 'impressions_alert', alertText, ImpressionsAlert,
          upToHour);
    }
    if (clicksThreshold &&
        todayStats.clicks < pastStats.clicks * clicksThreshold) {
      const clickAlert = `    Clicks are too low: ` +
          `${todayStats.clicks} clicks by ${formattedHour},` +
          ` expecting at least ` +
          `${(pastStats.clicks * clicksThreshold).toFixed(1)}`;
      writeAlert(spreadsheet, 'clicks_alert', alertText, clickAlert, upToHour);
    }
    if (conversionsThreshold &&
        todayStats.conversions < pastStats.conversions * conversionsThreshold) {
      const conversionsAlert =
          `    Conversions are too low: ` +
          `${todayStats.conversions} conversions by ${formattedHour},` +
          ` expecting at least ` +
          `${(pastStats.conversions * conversionsThreshold).toFixed(1)}`;
      writeAlert(
        spreadsheet, 'conversions_alert', alertText, conversionsAlert,
        upToHour);
    }
    if (costThreshold &&
        todayStats.cost > pastStats.cost * costThreshold) {
      const costAlert = `    Cost is too high: ` +
          `${todayStats.cost} ${AdsApp.currentAccount().getCurrencyCode()} ` +
          `by ${formattedHour}, expecting at most ` +
          `${(pastStats.cost * costThreshold).toFixed(2)}`;
      writeAlert(spreadsheet, 'cost_alert', alertText, costAlert, upToHour);
    }
  }

  if (alertText.length > 0 && email && email.length > 0) {
    MailApp.sendEmail(email,
        `Google Ads Account ${AdsApp.currentAccount().getCustomerId()}` +
        ` misbehaved.`,
        `Your account ${AdsApp.currentAccount().getCustomerId()}` +
        ` is not performing as expected today: \n\n${alertText.join('\n')}` +
        `\n\nLog into Google Ads and take a look.\n\nAlerts dashboard: ` +
        `${SPREADSHEET_URL}`);
  }

  writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats, pastStats,
      AdsApp.currentAccount().getCustomerId());
}

/**
 * Converts the value passed as number into a float value.
 *
 * @param {number} value that needs to be converted .
 * @return {number} A value that is of type float.
 */
function toFloat(value) {
  value = value.toString().replace(/,/g, '');
  return parseFloat(value);
}

/**
 * Converts the value passed to a float value.
 *
 * @param {number} value that needs to be converted .
 * @return {number} A value that is of type float.
 */
function parseField(value) {
  if (value == 'No alert') {
    return null;
  } else {
    return toFloat(value);
  }
}

/**
 * Converts the metrics.cost_micros by dividing it by a million to match the
 * output with version v1.1.1 of the file.
 * @param {number} value that needs to be converted.
 * @return {string} A value that is of type float.
 */
function toFloatFromMicros(value){
  value = parseFloat(value);
  return (value/1000000).toFixed(2);
}

/**
 * Runs a Google Ads report query for a number of weeks and return the average
 * values for the stats.
 *
 * @param {string} query The formatted report query.
 * @param {number} hours The limit hour of day for considering the report rows.
 * @param {number} weeks The number of weeks for the past stats.
 * @return {Object} An object containing the average values for the stats.
 */
function getReportStats(query, hours, weeks) {
  const reportRows = [];
  const report = AdsApp.search(query, REPORTING_OPTIONS);
  for(const row of report){
     reportRows.push(row);
  }
  return accumulateRows(reportRows, hours, weeks);
}

/**
 * Accumulate stats for a group of rows up to the hour specified.
 *
 * @param {!Object} rows The result of query.
 * @param {number} hours The limit hour of day for considering the report rows.
 * @param {number} weeks The number of weeks for the past stats.
 * @return {!Object} Stats aggregated up to the hour specified.
 */
function accumulateRows(rows, hours, weeks) {
  let result = {clicks: 0, impressions: 0, conversions: 0, cost: 0};

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const hour = row['segments']['hour'];
    if (hour < hours) {
      result = addRow(row, result, 1 / weeks);
    }
  }
  return result;
}

/**
 * Adds two stats rows together and returns the result.
 *
 * @param {!Object} row An individual row on which average operations is performed for every property.
 * @param {!Object} previous object initialized as 0 for every property.
 * @param {number} coefficient To get the Average of the properties.
 * @return {!Object} The addition of two stats rows.
 */
function addRow(row, previous, coefficient) {
  coefficient = coefficient || 1;
  row = row || {Clicks: 0, Impressions: 0, Conversions: 0, Cost: 0};
  previous = previous || {clicks: 0, impressions: 0, conversions: 0, cost: 0};
  return {
      clicks: parseInt(row['metrics']['clicks'],10) * coefficient + previous.clicks,
      impressions:
          parseInt(row['metrics']['impressions'],10) * coefficient + previous.impressions,
      conversions:
          parseInt(row['metrics']['conversions'],10) * coefficient + previous.conversions,
      cost: toFloatFromMicros(row['metrics']['costMicros']) * coefficient + previous.cost
    };
}

/**
 * Produces a formatted string representing a date in the past of a given date.
 *
 * @param {number} numDays The number of days in the past.
 * @param {Date} date A date object. Defaults to the current date.
 * @return {string} A formatted string in the past of the given date.
 */
function getDateStringInPast(numDays, date) {
  date = date || new Date();
  const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  const past = new Date(date.getTime() - numDays * MILLIS_PER_DAY);
  return getDateStringInTimeZone('yyyy-MM-dd', past);
}


/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {Date} [date] A date object. Defaults to the current date.
 * @param {string} [timeZone] A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * 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.
 * @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) {
  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.`);
  }
  const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  const email = spreadsheet.getRangeByName('email').getValue();
  if ('foo@example.com' == email) {
    throw new Error(`Please either set a custom email address in the` +
        ` spreadsheet, or set the email field in the spreadsheet to blank` +
        ` to send no email.`);
  }
  return spreadsheet;
}

/**
 * Writes the alert time in the spreadsheet and push the alert message to the
 * list of messages.
 *
 * @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
 * @param {string} rangeName The named range in the spreadsheet.
 * @param {Array<string>} alertText The list of alert messages.
 * @param {string} alertMessage The alert message.
 * @param {number} hour The limit hour used to get the stats.
 */
function writeAlert(spreadsheet, rangeName, alertText, alertMessage, hour) {
  const range = spreadsheet.getRangeByName(rangeName);
  if (!range.getValue() || range.getValue().length == 0) {
    alertText.push(alertMessage);
    range.setValue(`Alerting ${hour}:00`);
  }
}

/**
 * Writes the data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
 * @param {Date} now The date corresponding to the running time of the script.
 * @param {boolean} statsExist A boolean that indicates the existence of stats.
 * @param {Object} todayStats The stats for today.
 * @param {Object} pastStats The past stats for the period defined in the
 * spreadsheet.
 * @param {string} accountId The account ID.
 */
function writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats,
                                pastStats, accountId) {
  spreadsheet.getRangeByName('date').setValue(now);
  spreadsheet.getRangeByName('account_id').setValue(accountId);
  spreadsheet.getRangeByName('timestamp').setValue(
    getDateStringInTimeZone('E HH:mm:ss z', now));

  if (statsExist) {
    const dataRows = [
      [todayStats.impressions, pastStats.impressions.toFixed(0)],
      [todayStats.clicks, pastStats.clicks.toFixed(1)],
      [todayStats.conversions, pastStats.conversions.toFixed(1)],
      [todayStats.cost, pastStats.cost.toFixed(2)]
    ];
    spreadsheet.getRangeByName('data').setValues(dataRows);
  }
}