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.impressiondigital.com");
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.impressiondigital.com)";
// 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.
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.
