Just a small group of the keywords in your account are likely to be doing the lion’s share of the work in your account. Now, thanks to our handy keyword performance script, you can identify exactly which keywords are contributing the most to your success and which ones are costing the most.

The 80/20 principle

If you’ve ever read any book on time management or even personal development, you may have come across something called the 80/20 principle.

Sometimes called the Pareto principle, the concept is named after the Italian economist Vilfredo Pareto who noticed its effects in the early 19th century.

The idea is simple: 80% of the outputs come from 20% of the inputs.

For example:

  • 20% of the pods produce 80% of the peas
  • 20% of the criminals commit 80% of crimes
  • 20% of your customers make up 80% of your sales

It’s important to remember that this is an observation, not a natural law. The numbers aren’t always going to be an 80/20 split, but they’re usually close enough.

Despite this, many natural phenomena follow a similar pattern. Everything from word frequency (even in languages we can’t decipher yet) to the diameter of craters on the moon have be shown to fit a Pareto distribution.

The weirdest part is that no one knows why.

Now, almost 200 years later, we are seeing these effects once again in the world of PPC advertising.

If you look into your Google Ads account, you’ll likely find that around 20% of your keywords are leading to about 80% of your conversions.

If you’d like to find out what that 20% is, we’ve got a script to help you do just that and more.

Our Pareto principle–inspired keyword performance script

All you have to do is install and run this Google Ads script and it will give you a spreadsheet report with all of the detail that you need to know.

If you’ve never run a script before, Google has a quick guide to show you how to set your first one up.

The spreadsheet will break down exactly what percentage of your cost that each keyword represents, as well as what percentage of your conversions.

If you are running an e-commerce account which pulls your conversion value into Google Ads, it’ll give you the keyword distributions for your revenue too.

/*******************************************************************************
*
* [KPR] Keyword Performance Report
* Author: Nathan Ifill (@nathanifill), Impression
*
* This script gives you the distribution of cost, conversions and revenue
* (if you are pulling conversion value into your account) amongst your keywords.
*
* v1.1
*
* Any suggestions? Email nathan.ifill@impression.co.uk
*
* Change History:
* v1.1
* - NI added option to select or exclude campaigns by name
*
*******************************************************************************/
// Want your report emailed to you every month? Enter your email address here.
// e.g. var yourEmailAddress = "james.jameson@impression.co.uk";

var yourEmailAddress = "";

var campaignNameContains = "";
// Use this if you only want to look at some campaigns such as campaigns with
// names containing 'Brand' or 'Remarketing'. Leave it as "" if you don't need
// it.

// e.g. var campaignNameContains = "Brand";

var campaignNameDoesNotContain = "";
// Use this if you want to exclude some campaigns such as campaigns with names
// containing 'Brand' or 'Remarketing'. Leave it as "" if you don't need it.

// e.g. var campaignNameDoesNotContain = "Remarketing";

/*******************************************************************************
//********* ONLY CERTIFIED NERDS SHOULD CHANGE THINGS BELOW THIS LINE **********
//*****************************************************************************/
var fullCleanLog = ""; // initialise fullCleanLog

var whereStatements = "";

if (campaignNameDoesNotContain != "") {
  whereStatements += " AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '"
  + campaignNameDoesNotContain + "' ";
}

var query = "SELECT Criteria, AdGroupName, CampaignName, KeywordMatchType,"
+ " Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionValue,"
+ " ConversionRate FROM KEYWORDS_PERFORMANCE_REPORT WHERE Cost > 0"
+ " AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "'"
+ whereStatements
+ " DURING LAST_MONTH"

var report = AdsApp.report(query);

function main() {
  var rows = report.rows();
  var keywordSubtotal = 0;
  var costSubtotal = 0;
  var convSubtotal = 0;
  var revSubtotal = 0;
  var keywordArray = [];
  var costData = [];
  var convData = [];
  var revData = [];
  var skipRev = false; // flag to say whether to add revenue data or not

  Logger.log("Keyword Performance Report - www.impression.co.uk");
  Logger.log("-------------------------------------------------");
  cleanLog("");

  while (rows.hasNext()) {

    var row = rows.next();

    var criteria = row["Criteria"];
    var adGroupName = row["AdGroupName"];
    var campaignName = row["CampaignName"];
    var keywordMatchType = row["KeywordMatchType"];
    var impressions = +row["Impressions"];
    var clicks = +row["Clicks"];
    var ctr = row["Ctr"];
    var averageCpc = +row["AverageCpc"];
    var cost = +row["Cost"];
    var conversions = +row["Conversions"];
    var conversionValue = +row["ConversionValue"];
    var conversionRate = row["ConversionRate"];

    // Sets conversion value to 0 if NaN
    if (isNaN(conversionValue)) {
      conversionValue = 0;
    }

    // Sets ROAS if cost > 0
    if (cost > 0) {
      var roas = conversionValue / cost;
    } else {
      var roas = 0;
    }

    if (conversions > 0) {
      var costPerConversion = (cost / conversions).toFixed(2);
    } else {
      var costPerConversion = 0;
    }

    keywordArray.push({
      "criteria": criteria,
      "ad group name": adGroupName,
      "campaign name": campaignName,
      "keyword match type": keywordMatchType,
      "impressions": impressions,
      "clicks": clicks,
      "ctr": ctr,
      "average cpc": averageCpc,
      "cost": cost,
      "conversions": conversions,
      "cost per conversion": costPerConversion,
      "conversion value": conversionValue,
      "conversion rate": conversionRate,
      "roas": roas
    });

    keywordSubtotal++;
    costSubtotal += cost;
    convSubtotal += conversions;
    revSubtotal += conversionValue;
  }

  if (revSubtotal == 0) {
    skipRev = true;
  }

  var costArray = keywordArray.sort(function (a,b) {

    if ((b["cost"] - a["cost"]) < 0) {
      return -1;
    } else if ((b["cost"] - a["cost"]) > 0) {
      return 1;
    } else {
      return 0;
    }
  });

  var cumulativeCost = 0;
  var maxCostDiff = 0;
  var keywordPercentage = 0;
  var costPercentage = 0;
  var convPercentage = 0;
  var revPercentage = 0;

  costArray.forEach(function(item, index) {
    cumulativeCost += item.cost;

    item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
    item["cost percentage"] = (100 * (item["cost"] / costSubtotal)).toFixed(2);
    item["cum cost percentage"] = (100 * (cumulativeCost / costSubtotal)).toFixed(2);
    item["cost diff"] = (item["cum cost percentage"] - item["keyword percentage"]).toFixed(2);
    // sets maxCostDiff to item["cost diff"] if it's greater

    if (+item["cost diff"] > +maxCostDiff) {
      maxCostDiff = item["cost diff"];
      keywordPercentage = item["keyword percentage"];
      costPercentage = item["cum cost percentage"];
    };

    costData.push([item["criteria"],
    item["ad group name"],
    item["campaign name"],
    item["keyword match type"],
    item["cost percentage"] / 100,
    item["cum cost percentage"] / 100,
    item["impressions"],
    item["clicks"],
    item["ctr"],
    item["average cpc"],
    item["cost"],
    item["conversions"],
    item["cost per conversion"],
    item["conversion value"],
    item["conversion rate"],
    item["roas"]]);
  });

  cleanLog(keywordPercentage + "% of your keywords are spending " + costPercentage + "% of your media spend.");
  var expensiveKeywords = costArray.filter(function(item) { return +item["keyword percentage"] <= +keywordPercentage && +item["cost percentage"] <= +costPercentage});

  var convArray = keywordArray.sort(function (a,b) {
    if ((b["conversions"] - a["conversions"]) < 0) {
      return -1;
    } else if ((b["conversions"] - a["conversions"]) > 0) {
      return 1;
    }  else {
      return 0;
    }
  });

  var cumulativeConv = 0;
  var maxConvDiff = 0;
  keywordPercentage = 0;
  convPercentage = 0;

  convArray.forEach(function(item, index) {
    cumulativeConv += item.conversions;

    item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
    item["conv percentage"] = (100 * (item["conversions"] / convSubtotal)).toFixed(2);
    item["cum conv percentage"] = (100 * (cumulativeConv / convSubtotal)).toFixed(2);
    item["conv diff"] = (item["cum conv percentage"] - item["keyword percentage"]).toFixed(2);
    // sets maxCostDiff to item["cost diff"] if it's greater

    if (+item["conv diff"] > +maxConvDiff) {
      maxConvDiff = item["conv diff"];
      keywordPercentage = item["keyword percentage"];
      convPercentage = item["cum conv percentage"];
    };

    convData.push([item["criteria"],
    item["ad group name"],
    item["campaign name"],
    item["keyword match type"],
    item["conv percentage"] / 100,
    item["cum conv percentage"] / 100,
    item["impressions"],
    item["clicks"],
    item["ctr"],
    item["average cpc"],
    item["cost"],
    item["conversions"],
    item["cost per conversion"],
    item["conversion value"],
    item["conversion rate"],
    item["roas"]]);
  });

  cleanLog(keywordPercentage + "% of your keywords are driving " + convPercentage + "% of your conversions.");
  var highConvertingKeywords = convArray.filter(
    function(item) {
      return +item["keyword percentage"] <= +keywordPercentage && +item["conv percentage"] <= +convPercentage
    });

    if (!skipRev) {
      var revArray = keywordArray.sort(function (a,b) {
        if ((b["conversion value"] - a["conversion value"]) < 0) {
          return -1;
        } else if ((b["conversion value"] - a["conversion value"]) > 0) {
          return 1;
        }  else {
          return 0;
        }
      });

      // REVENUE BITS

      var cumulativeRev = 0;
      var maxRevDiff = 0;
      keywordPercentage = 0;
      revPercentage = 0;

      revArray.forEach(function(item, index) {
        cumulativeRev += item["conversion value"];

        item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
        item["rev percentage"] = (100 * (item["conversion value"] / revSubtotal)).toFixed(2);
        item["cum rev percentage"] = (100 * (cumulativeRev / revSubtotal)).toFixed(2);
        item["rev diff"] = (item["cum rev percentage"] - item["keyword percentage"]).toFixed(2);
        // sets maxCostDiff to item["cost diff"] if it's greater

        if (+item["rev diff"] > +maxRevDiff) {
          maxRevDiff = item["rev diff"];
          keywordPercentage = item["keyword percentage"];
          revPercentage = item["cum rev percentage"];
        };
        // If rev percentage isn't a num, set it to '--'
        isNaN(item["rev percentage"])? item["rev percentage"] = '--' : false;
        isNaN(item["cum rev percentage"])? item["cum rev percentage"] = '--' : false;

        revData.push([item["criteria"],
        item["ad group name"],
        item["campaign name"],
        item["keyword match type"],
        item["rev percentage"] / 100,
        item["cum rev percentage"] / 100,
        item["impressions"],
        item["clicks"],
        item["ctr"],
        item["average cpc"],
        item["cost"],
        item["conversions"],
        item["cost per conversion"],
        item["conversion value"],
        item["conversion rate"],
        item["roas"]]);
      });

      cleanLog(keywordPercentage + "% of your keywords are driving " + revPercentage + "% of your revenue.");
      var highRevKeywords = revArray.filter(
        function(item) {
          return +item["keyword percentage"] <= +keywordPercentage && +item["rev percentage"] <= +revPercentage
        });
      }
      // SPREADSHEET BITS

      // Headers for sheets
      var costReportHeaders = [ "Keyword", "Ad Group", "Campaign",
      "Match type", "% of Cost", "Cum. % of Cost", "Impr.", "Clicks", "CTR",
      "Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value", "Conv. rate",
      "Conv. value / cost"];

      var convReportHeaders = [ "Keyword", "Ad Group", "Campaign",
      "Match type", "% of Conversions", "Cum. % of Conversions", "Impr.", "Clicks",
      "CTR",   "Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value",
      "Conv. rate", "Conv. value / cost"];

      if (!skipRev) {
        var revReportHeaders = [ "Keyword", "Ad Group", "Campaign",
        "Match type", "% of Revenue", "Cum. % of Revenue", "Impr.", "Clicks", "CTR",
        "Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value", "Conv. rate",
        "Conv. value / cost"];
      }

      var title = "Keyword Performance Report: Cost, Conversions & Revenue";

      if (skipRev) {
        title = "Keyword Performance Report: Cost & Conversions";
      }

      var timeZone = AdsApp.currentAccount().getTimeZone();
      var today = new Date(); // initialises a date object to today
      var d = new Date(today.setMonth(today.getMonth()-1));
      // sets the date to a month ago
      var lastMonth = Utilities.formatDate(d, timeZone, 'MMMM'); // December
      var shortMonth = Utilities.formatDate(d, timeZone, 'MMM'); // Dec
      var theYear = parseFloat(Utilities.formatDate(d, timeZone, 'yyyy'));

      title += " - " + lastMonth + " " + theYear + " (www.impression.co.uk)";
      // Appends the date to the title name

      title = AdsApp.currentAccount().getName() + " " + title;

      // Make the spreadsheet
      var ss = SpreadsheetApp.create(title);

      // Give URL for spreadsheet in logs
      cleanLog("");
      cleanLog("The full report is available here: " + ss.getUrl());
      Logger.log("");

      if (yourEmailAddress != '') {
        try {
          var subject = "[KPR] " + AdsApp.currentAccount().getName()
          + " Keyword Performance Report "
          + " - " + shortMonth + " " + theYear;
          var body = title + fullCleanLog;
          MailApp.sendEmail(yourEmailAddress, subject, body);
          Logger.log("Email sent to " + yourEmailAddress + ".");
        } catch (e) {

          Logger.log("Unable to send keyword report email. Please check the email "
          + "address provided is valid.");
        }
      }

      // Name the sheets
      var costSheet = ss.getSheets()[0].setName("Cost Distribution");
      var convSheet = ss.insertSheet("Conversions Distribution");

      if (!skipRev) {var revSheet = ss.insertSheet("Revenue Distribution");}

      // Cost sheet bits
      costSheet.deleteColumns(2, costSheet.getMaxColumns() - 1);
      costSheet.deleteRows(2, costSheet.getMaxRows() - 1);
      costSheet.getRange(1,1,1,costReportHeaders.length).setValues([costReportHeaders]);

      // Conv sheet bits
      convSheet.deleteColumns(2, convSheet.getMaxColumns() - 1);
      convSheet.deleteRows(2, convSheet.getMaxRows() - 1);
      convSheet.getRange(1,1,1,convReportHeaders.length).setValues([convReportHeaders]);

      if (!skipRev) {
        // Rev sheet bits
        revSheet.deleteColumns(2, revSheet.getMaxColumns() - 1);
        revSheet.deleteRows(2, revSheet.getMaxRows() - 1);
        revSheet.getRange(1,1,1,revReportHeaders.length).setValues([revReportHeaders]);
      }

      // Freeze keyword column:
      costSheet.setFrozenColumns(1);
      convSheet.setFrozenColumns(1);
      if (!skipRev) {
        revSheet.setFrozenColumns(1);
      }

      // Put the data in
      costSheet.getRange(2,1,costData.length,costData[0].length).setValues(costData);
      convSheet.getRange(2,1,convData.length,convData[0].length).setValues(convData);

      if (!skipRev) {
        revSheet.getRange(2,1,revData.length,revData[0].length).setValues(revData);
      }

      // Set formats for columns
      var costRangeList = costSheet.getRangeList(['J2:P']);
      var costPrcRangeList = costSheet.getRangeList(['E2:F', 'O2:O']);
      costRangeList.setNumberFormat('0.00');
      costPrcRangeList.setNumberFormat('0.00%');

      var convRangeList = convSheet.getRangeList(['J2:P']);
      var convPrcRangeList = convSheet.getRangeList(['E2:F', 'O2:O']);
      convRangeList.setNumberFormat('0.00');
      convPrcRangeList.setNumberFormat('0.00%');

      if (!skipRev) {
        var revRangeList = revSheet.getRangeList(['J2:P']);
        var revPrcRangeList = revSheet.getRangeList(['E2:F', 'O2:O']);
        revRangeList.setNumberFormat('0.00');
        revPrcRangeList.setNumberFormat('0.00%');
      }

      // Set column widths
      costSheet.setColumnWidths(2, costReportHeaders.length - 1, 175);
      convSheet.setColumnWidths(2, convReportHeaders.length - 1, 175);

      if (!skipRev) {
        revSheet.setColumnWidths(2, revReportHeaders.length - 1, 175);
      }

      // Set first column to be auto sized
      costSheet.autoResizeColumn(1);
      convSheet.autoResizeColumn(1);

      if (!skipRev) {
        revSheet.autoResizeColumn(1);
      }

      // Add filter
      costSheet.getDataRange().createFilter();
      convSheet.getDataRange().createFilter();
      if (!skipRev) {
        revSheet.getDataRange().createFilter();
      }
      Logger.log(" ");
      Logger.log("2 multiplied by 10 plus 1. Romeo done.");
      Logger.log("https://youtu.be/g7VhofoV3qs?t=236");
    }

    function cleanLog(input) {
      Logger.log(input);
      fullCleanLog += "\n" + input;
    }

The script is set to give you statistics for last month – whichever month that was. Schedule it to run on the first of the month and it’ll automatically create a new report for you so that you can see how well you’re doing over time.

Depending on the size of your account, it should run for up to a minute before giving you your data.

If you add your email address to the script, it’ll email you a link to the spreadsheet once the script has run.

You can also choose to only include campaigns with a certain word in their name (e.g. “Brand” or “UK”) in the report to see specific stats for a subset of your campaigns.

Alternatively, if there are certain campaigns that you’d like to exclude from the figures, you can do that too.

What to expect

You might be surprised to find out just how skewed your account is!

The script logs will give you some of the stats as highlights, but you’ll also get these sent to you if you have entered your email address.

An example of what the logs might look like on your account.

The report itself is fairly straightforward with columns showing a variety of performance metrics for each keyword, including impressions, clicks, CTR, Avg. CPC, cost, cost per conversion and conversion rate.

In addition to this, you’ll also get a breakdown of what percentage of cost, conversions and revenue each keyword is responsible for, as well as the cumulative percentage.

The script only analyses keywords which spent money last month, so if you were expecting more, this may be the reason for the discrepancy.

Each sheet is sorted by cost, conversions and revenue in descending order, depending on which metric is being analysed. That means that the keywords that are either contributing the most to your account or spending the most of your budget will always be at the top.

Try it out on your account today to see how your keywords are performing.

Increase your spend efficiency

So, a small handful of keywords are responsible for a large chunk of your conversions and your advertising cost.

Now what do you do with this information? That’s where we come in.

Even if things are ticking along nicely in your account at the moment, there’s always room for improvement. With a few quick tweaks off of the back of this data, you can increase the spend efficiency in your account and get even better results from your current budget.

While disproportionate performance is to be expected, it could also be an indication that it’s time to restructure your account, adjust your bidding strategies and apply budget on a priority basis.

If you’d like to find out more about how you can get the most out of your account, contact us today and we can guide you through it.

Nathan Ifill

PPC Executive

PPC Account Executive at Impression. Putting the ‘ting’ into ‘digital marketing’. Follow Nathan on Twitter: @nathanifill

Leave a Reply

Your email address will not be published. Required fields are marked *