Budget Eater Report

Analyze keyword and product performance over the past period. What is the influence of keywords and products with only 1 or 2 conversions?

Start Now!
Budget Eater Report for Google Shopping
Budget Get started!

You could apply the 20/80 rule in Search and Shopping campaigns. Put your time and attention into the 20% of keywords and products that should deliver 80% of the sales. That is a proposition that is subject to necessary discussion.

In the case of Google Shopping, SMEC (smarter ecommerce) has shown that this is not true. SMEC has written the script below, because research shows that 80% of the conversions in Shopping come from 80% of the products. These products have then only achieved 1 or 2 conversions in the last 30 days. These products are called the Long Tail products.
In addition, 70% of the costs go to products that never yield conversions.

Insight into Long Tail products and keywords

To provide insight into this phenomenon and to show how much costs go to products and keywords that do not yield conversions, use the script below. A number of tweaks and upgrades have been made from the SMEC original. In addition to the analysis for Shopping, you can do the same for Search. This involves looking at keywords that have achieved conversions between 1 and the threshold (standard 3). You can then compare this result with Shopping. You can also send the results by email.

Settings

  • LOG: Change this to 'true' to see what exactly happened. Leave on 'false' when the script is really running, because that's faster.
  • DATE_RANGE: Set the daterange for the period you want to check for impressions. LAST_30_DAYS by default.
  • ANALYSE_KEYWORDS: Analyseer zoekwoorden op dezelfde manier als producten. Zet op 'false' om niet op te nemen.
  • LONG_TAIL_CONVERSIONS_THRESHOLD: The maximum number of conversions to be named Long Tail.
  • EMAIL_REPORT: Set to 'false' when you don't want to receive an email.
  • EMAIL_RECIPIENTS: Enter the e-mail address where you want to receive the notification. You can enter multiple email addresses by separating them with a comma.
  • EMAIL_SUBJECT: The subject of the email that will be send.

Frequentie: Set the schedule of this script to once a week or once a month.

The script
// (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()
Show whole script!
The Experts
Tibbe van Asten Head of PPC @ Increase
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
How about you? JOIN US!
Sharing Knowledge
Caring

Sharing Knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Training &
Workshop
Contact us!
Adsscripts Training & Workshop