Budget Eater Report

// (c) Smarter Ecommerce GmbH. All rights reserved.
// License: MIT
//
// Budget Eater Script         . * ･ ｡ﾟ☆━੧[ ✪ ᗜ ✪ ]⊃
//
// Created by: smec Team [smarter-ecommerce.com]
// Extended by: Tibbe van Asten (adsscripts.com)
//
// Created: 27-04-2018
// Last update: 05-01-2021 - Added Search campaigns & email
//
// Overview: You can use this script to evaluate your Long Tail and your
// non-converters from the last 30 days. The script will give you an impression
// of the correlations between costs, conversions, and the Long Tail within
// your account.
//
//
// Twitter: @smec, @adsscripts-com
// Email: scripts@smarter-ecommerce.com
//
////////////////////////////////////////////////////////////////////

var config = {

  LOG : true,

  DATE_RANGE : "LAST_30_DAYS",
  ANALYSE_KEYWORDS : true,

  // Sets the number of conversions threshold for evaluating the Long Tail.
  LONG_TAIL_CONVERSIONS_THRESHOLD : 3,

  // Set email to receive this report in your inbox
  EMAIL_REPORT : true,
  EMAIL_RECIPIENTS : "yourname@company.com",
  EMAIL_SUBJECT : "Budget Eater Report"

}

////////////////////////////////////////////////////////////////////

function main() {

  var emailContent = "";
  
  // Analyse at product-level
  Logger.log("...Starting products analysis...");
  emailContent = analyseCampaigns("Shopping campaigns", "OfferId, Cost, Conversions, ConversionValue, Impressions", "SHOPPING_PERFORMANCE_REPORT", "products", emailContent);

  // Analyse at query-level
  if(config.ANALYSE_KEYWORDS === true){
    Logger.log("...Starting keyword analysis...");
    emailContent = analyseCampaigns("Search campaigns", "Criteria, Cost, Conversions, ConversionValue, Impressions", "KEYWORDS_PERFORMANCE_REPORT", "keywords", emailContent)
  }
  
  // Print results in log
  if(config.LOG === true){
    Logger.log(" ");
    Logger.log(emailContent);
  }

  // Email results
  if(config.EMAIL_REPORT === true){
    emailReport(emailContent.replace(/\n/g,"<br />"));
  }

} // function main()

////////////////////////////////////////////////////////////////////

function analyseCampaigns(type, select, report, name, emailContent){

    // Select cost and conversions for all products during the last 30 days.
    var report = AdsApp.report(
      "SELECT " + select + " " +
      "FROM " + report + " " +
      "DURING "+ config.DATE_RANGE
    );

    var result = {
        nonConverters: { count: 0, cost: 0 },
        longtail: { count: 0, cost: 0, conversions: 0, conversionValue: 0 },
        converters: { count: 0, cost: 0, conversions: 0, conversionValue: 0 }
    };

    // Iterate through all rows and categorize each product by the number of its conversions.
    var rows = report.rows();

    while (rows.hasNext()) {
      var row = rows.next();
      var conversions = parseFloat(row['Conversions'], 10);
      var cost = parseFloat(row['Cost'], 10);
      var conversionValue = parseFloat(row['ConversionValue'], 10);

      if (conversions < 1) {
          result.nonConverters.count += 1;
          result.nonConverters.cost += cost;
      } else if (conversions < config.LONG_TAIL_CONVERSIONS_THRESHOLD) {
          result.longtail.count += 1;
          result.longtail.conversions += conversions;
          result.longtail.cost += cost;
          result.longtail.conversionValue += conversionValue;
      } else {
          result.converters.count += 1;
          result.converters.cost += cost;
          result.converters.conversions += conversions;
          result.converters.conversionValue += conversionValue;
      }
    }
  
      Logger.log(result)

    var totalCost = result.nonConverters.cost + result.longtail.cost + result.converters.cost;
    var totalConversions = result.longtail.conversions + result.converters.conversions;
    var totalConversionValue = result.longtail.conversionValue + result.converters.conversionValue;

    emailContent += "------------------------------------\n";
    emailContent += type + " (" + config.DATE_RANGE + ")\n";
    emailContent += "------------------------------------\n";
    emailContent += "Number of " + name +" without conversions: " + result.nonConverters.count + "\n";
    emailContent += "Number of " + name +" in Long Tail: " + result.longtail.count + "\n";
    emailContent += "Number of " + name +" having at least " + config.LONG_TAIL_CONVERSIONS_THRESHOLD + " conversions: " + result.converters.count + "\n\n";
    emailContent += "Total cost: " + round(totalCost) + "\n";
    emailContent += "Cost for Non-Converters: " + round(result.nonConverters.cost) +
        " (" + round(percentage(result.nonConverters.cost, totalCost)) + "% from total cost)\n\n";
    emailContent += "Total conversion value: " + round(totalConversionValue) + "\n";
    emailContent += "Total conversions: " + round(totalConversions) + "\n";
    emailContent += "Conversions from Long Tail: " + round(result.longtail.conversions) +
        " (" + round(percentage(result.longtail.conversions, totalConversions)) + "% from total conversions)\n\n";
    emailContent += "Total ROI: " + round(totalConversionValue / totalCost) + "\n";
    emailContent += "ROI for Long Tail: " + round(result.longtail.conversionValue / result.longtail.cost) + "\n";
    emailContent += "\n\n";

    return emailContent;

} // function analyseCampaigns()

////////////////////////////////////////////////////////////////////

function round(number) {

  // Rounds the given number to 2 decimals
  return Math.round(number * 100) / 100;

} // function round()

////////////////////////////////////////////////////////////////////

function percentage(value, total) {

  // Returns the percentage of a value for the given total value
  return value / total * 100;

} // function percentage()

////////////////////////////////////////////////////////////////////

function emailReport(emailContent){

  MailApp.sendEmail({
    to: config.EMAIL_RECIPIENTS,
    subject: config.EMAIL_SUBJECT,
    htmlBody: emailContent
  });

} // emailReport()