Skip to content

Instantly share code, notes, and snippets.

@BrainlabsDigital
Created July 4, 2017 09:49
  • Star 2 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save BrainlabsDigital/6b052a0ba742eaa790ba815833b13653 to your computer and use it in GitHub Desktop.
Script to find and label duplicated ads
/**
*
* Duplicate Ad Detector
* This script will find ads with identical text and URLs, and label
* one to keep and the rest to pause according to performance.
*
* Version: 1.0
* Google AdWords Script maintained by brainlabsdigital.com
*
**/
var metric = "Ctr";
//Select the metric which will determine which duplicate ad will be kept.
// Choose from "Ctr", "Clicks", "Impressions", "Cost", "Conversions", "AverageCpc"
var campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns.
// For example ["Display"] would ignore any campaigns with 'Display' in the name,
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or
// 'Shopping' in the name.
// Leave as [] to not exclude any campaigns.
var campaignNameContains = [];
// Use this if you only want to look at some campaigns.
// For example ["Brand"] would only look at campaigns with 'Brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic'
// in the name.
// Leave as [] to include all campaigns.
var ignorePausedCampaigns = true;
// Set this to true to only look at currently active campaigns.
// Set to false to also include campaigns that are currently paused.
var checkUrl = true;
// Set this to true to include the URL as part of the ad text - ads are only
// treated as duplicates if they have the same ad copy and final URL.
// Set this to false to treat ads as duplicates if they have the same copy
// but different landing pages.
var caseSensitive = false;
// If this is set to true, then only ads with the same capitalisation will
// count as duplicates.
// If this is false, ad text comparison will be case insensitive.
var keepLabel = "Duplicate Ad: Enable";
//Label one ad from each duplicate group
var pauseLabel = "Duplicate Ad: Pause";
//Label all ads which don't have the best statistic from selected
function main() {
var campaignIds = getCampaignIds();
//Create labels
var keepLabelId = getOrCreateLabelId(keepLabel);
var pauseLabelId = getOrCreateLabelId(pauseLabel);
//Metric validation
var allowedMetrics = ["Ctr", "Clicks", "Impressions", "Cost", "Conversions", "AverageCpc"];
metric = validateMetricName(metric, allowedMetrics);
// Record the ads
var adsByAdGroup = getAds(campaignIds, keepLabelId, pauseLabelId, metric);
// Find and label the duplicates
findDuplicates(adsByAdGroup, keepLabel, pauseLabel);
Logger.log("Finished.");
}
// Get the IDs of campaigns which match the given options
function getCampaignIds() {
var whereStatement = "WHERE ";
var whereStatementsArray = [];
var campaignIds = [];
if (ignorePausedCampaigns) {
whereStatement += "CampaignStatus = ENABLED ";
} else {
whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] ";
}
for (var i=0; i<campaignNameDoesNotContain.length; i++) {
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' ";
}
if (campaignNameContains.length == 0) {
whereStatementsArray = [whereStatement];
} else {
for (var i=0; i<campaignNameContains.length; i++) {
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" ');
}
}
for (var i=0; i<whereStatementsArray.length; i++) {
var campaignReport = AdWordsApp.report(
"SELECT CampaignId " +
"FROM CAMPAIGN_PERFORMANCE_REPORT " +
whereStatementsArray[i] +
"DURING LAST_30_DAYS");
var rows = campaignReport.rows();
while (rows.hasNext()) {
var row = rows.next();
campaignIds.push(row['CampaignId']);
}
}
if (campaignIds.length == 0) {
throw("No campaigns found with the given settings.");
}
Logger.log(campaignIds.length + " campaigns found");
return campaignIds;
}
// Create the label if it doesn't exist, and return its ID.
// (Returns a dummy ID if the label does not exist and this is a preview run,
// because we can't create or apply the label)
function getOrCreateLabelId(labelName) {
var labels = AdWordsApp.labels().withCondition("Name = '" + labelName + "'").get();
if (!labels.hasNext()) {
AdWordsApp.createLabel(labelName);
labels = AdWordsApp.labels().withCondition("Name = '" + labelName + "'").get();
}
if (AdWordsApp.getExecutionInfo().isPreview() && !labels.hasNext()) {
var labelId = 0;
} else {
var labelId = labels.next().getId();
}
return labelId;
}
// Verify that a metric name is valid, and return it with the correct capitalisation.
function validateMetricName(metric, allowedMetrics) {
var allowedMetrics_lowerCase = allowedMetrics.map(function (str){return str.toLowerCase()});
var metricIndex = allowedMetrics_lowerCase.indexOf(metric.toLowerCase().replace(" ","").trim());
if(metricIndex === -1){
throw "Metric '" + metric + "' not recognised, please set to one from '" + allowedMetrics.join("', '") + "'.";
return;
}
return allowedMetrics[metricIndex];
}
// Get the text and the relevant stat for all text ads and ETAs
// in the given campaigns.
// Returns the ads, grouped by their ad group ID.
function getAds(campaignIds, keepLabelId, pauseLabelId, metric) {
//Construct AWQL report query and generate report
var query = 'SELECT Id, AdGroupId, Headline, Description1, Description2, DisplayUrl, CreativeFinalUrls, HeadlinePart1, HeadlinePart2, Description, Path1, Path2, ' + metric + ' ' +
'FROM AD_PERFORMANCE_REPORT ' +
"WHERE AdType IN [TEXT_AD, EXPANDED_TEXT_AD] " +
"AND Status = ENABLED " +
"AND Labels CONTAINS_NONE [" + keepLabelId + "," + pauseLabelId +"] " +
"AND CampaignId IN [" + campaignIds.join(",") + "] " +
"DURING LAST_30_DAYS";
var report = AdWordsApp.report(query);
//Poll report rows
var adsByAdGroup = {};
var rows = report.rows();
while(rows.hasNext()){
var row = rows.next();
var metricStat = parseFloat(row[metric].replace(/,/g, ""));
if(metric.toLowerCase() === "AverageCpc".toLowerCase()){
if(metricStat > 0){
metricStat = 1 / metricStat;
}
}
var stats = {metric: metricStat};
var groupId = row['AdGroupId'];
if(typeof(adsByAdGroup[groupId]) === "undefined"){
adsByAdGroup[groupId] = [];
}
var ad = {};
if (checkUrl) {
var finalUrl = row['CreativeFinalUrls'].toLowerCase();
} else {
var finalUrl = "";
}
if (row['AdType'] == "Text ad") {
ad['Text'] = row['Headline'] + "#" + row['Description1'] + "#" + row['Description2'] + "#" + row['DisplayUrl'] + "#" + finalUrl;
} else {
ad['Text'] = row['HeadlinePart1'] + "#" + row['HeadlinePart2'] + "#" + row['Description'] + "#" + row['Path1'] + "#" + row['Path2'] + "#" + finalUrl;
}
if (!caseSensitive) {
ad['Text'] = ad['Text'].toLowerCase();
}
ad["AdId"] = row['Id'];
ad["AdGroupId"] = row['AdGroupId'];
ad["Id"] = [row['AdGroupId'], row['Id']];
ad["Stats"] = stats;
adsByAdGroup[groupId].push(ad);
}
return adsByAdGroup;
}
// Finds duplicate ads
function findDuplicates(adsByAdGroup, keepLabel, pauseLabel) {
var ids = Object.keys(adsByAdGroup);
Logger.log("Found ads in " + ids.length + " ad groups");
for (var i=0; i<ids.length; i += 1000) {
if ((i+1)%10000 == 0) {
Logger.log("Checking ad group " + (i+1) + " of " + ids.length);
}
var duplicateAds = {};
for(var j=i; j<i+100 && j<ids.length; j++){
var id = ids[j];
var currentGroupsAds = adsByAdGroup[id];
var adArray = [];
for(var ad in currentGroupsAds){
adArray.push(currentGroupsAds[ad]["Text"]);
}
for(var ad in currentGroupsAds){
var adText = currentGroupsAds[ad]["Text"];
var firstIndex = adArray.indexOf(adText);
var lastIndex = adArray.lastIndexOf(adText);
//push the dupes into dupe groups
if(firstIndex !== lastIndex){
if(typeof(duplicateAds[id]) === "undefined") {
duplicateAds[id]={};
}
if(typeof(duplicateAds[id][adText]) === "undefined") {
duplicateAds[id][adText]=[];
}
duplicateAds[id][adText].push(currentGroupsAds[ad]);
}
}
}
pickBestAdAndLabel(duplicateAds, keepLabel, pauseLabel);
}
}
// Finds the best ad, according to the user defined metric
// then labels accordingly
function pickBestAdAndLabel(duplicateAds, keepLabel, pauseLabel) {
var idsForPauseLabel = [];
var idsForKeepLabel = [];
for (var id in duplicateAds) {
for (var adText in duplicateAds[id]) {
//cycle through each group to pick best of the bunch
var maxmetric = -1;
var bestAd = [];
for (var ad in duplicateAds[id][adText]) {
if (parseFloat(duplicateAds[id][adText][ad]["Stats"]["metric"]) > maxmetric) {
maxmetric = duplicateAds[id][adText][ad]["Stats"]["metric"];
bestAd[0] = duplicateAds[id][adText][ad];
}
}
var indexOfBest = duplicateAds[id][adText].indexOf(bestAd[0]);
duplicateAds[id][adText].splice(indexOfBest, 1);
idsForKeepLabel.push(bestAd[0]["Id"]);
for (ad in duplicateAds[id][adText]) {
idsForPauseLabel.push(duplicateAds[id][adText][ad]["Id"]);
}
}
}
//label all groups with pause/unpause labels
if (idsForKeepLabel.length + idsForPauseLabel.length > 0) {
applyLabelsToAds(idsForKeepLabel, keepLabel);
applyLabelsToAds(idsForPauseLabel, pauseLabel);
}
}
// Applies a label to all ads with the given ids
function applyLabelsToAds(ids, labelName) {
for (var i=0; i<ids.length; i += 5000) {
var iterator = AdWordsApp.ads()
.withIds(ids.slice(i, i+5000))
.get();
while (iterator.hasNext()){
var ad = iterator.next();
ad.applyLabel(labelName);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment