Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to find if any ads or keywords have landing pages with the wrong domain.
/**
*
* Domain Name Checker
*
* This script will scan through your keyword and ad URLs, checking the domain
* names for anything out of place, and output any discrepancies it finds into a
* Google Sheet.
*
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
////////////////////////////////////////////////////////////////////////////////
// Options
var domainName = "brainlabsdigital.com";
// The domain you expect to be in all your keyword and ad URLs.
// Can be a whole URL (www.brainlabsdigital.com) or a partial URL
// (brainlabsdigital.com) to cover multiple subdomains.
var isWholeDomainName = true;
// If the domain name you gave is a whole URL, set this to true. Otherwise,
// leave it as false.
var targetSheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// Replace this with the URL of a blank Google Sheet; this is where the script
// will output its results
var campaignNameContains = [];
// Use this if you only want to look at some campaigns.
// For example ["Generic"] would only look at campaigns with 'generic' in the
// name, while ["Generic", "Competitor"] would only look at campaigns with
// either 'generic' or 'competitor' in the name.
// Leave as [] to include all campaigns.
var campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns.
// For example ["Brand"] would ignore any campaigns with 'brand' in the name,
// while ["Brand", "Key Terms"] would ignore any campaigns with 'brand' or
// 'key terms' in the name.
// Leave as [] to not exclude any campaigns.
var ignorePausedCampaigns = true;
// Set this to true to only look at currently active campaigns.
// Set to false to include campaigns that had impressions but are currently paused.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions
function main() {
// Escape any special characters in the given domain name.
prepareDomainName();
Logger.log("Prepared domain name for checking.");
// Fetch the URLs of keywords and ads attached to valid campaigns, filtering
// out those with the correct domain name.
var urlData = getUrlData();
Logger.log("Fetched all URLs.");
var numberOfBadUrls = Object.keys(urlData).length;
if (numberOfBadUrls === 0) {
Logger.log("No incorrect URLs found.");
} else {
// Output the bad URLs and their keywords and ads to the target sheet.
outputToSheet(urlData);
Logger.log("Output " + numberOfBadUrls + " incorrect URLs to sheet.");
}
Logger.log("Finished.");
}
// Escape any special characters in the given domain name.
function prepareDomainName() {
domainName = domainName.replace(/[-\/\\^$*+?.()|[\]{}]/g, '\\$&');
};
// This function returns an object containing the URLs and details of keywords
// and ads attached to valid campaigns.
function getUrlData() {
var urlData = new Object();
var expectedPattern = /./;
var whereStatements = ["Status = 'ENABLED'",
"AdGroupStatus = 'ENABLED'"
];
if (ignorePausedCampaigns) {
whereStatements.push("CampaignStatus IN ['ENABLED']");
} else {
whereStatements.push("CampaignStatus IN ['ENABLED','PAUSED']");
}
if (isWholeDomainName) {
expectedPattern = new RegExp("^https?://" + domainName);
} else {
expectedPattern = new RegExp("^https?://([^/]*?\\.)*" + domainName);
}
if (campaignNameContains.length == 0) {
campaignNameContains.push(false);
}
for (var i = 0; i < campaignNameDoesNotContain.length; i++) {
whereStatements.push("CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '"
+ campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "'");
}
for (var i = 0; i < campaignNameContains.length; i++) {
if (campaignNameContains[i] === false) {
var finalWhereStatements = whereStatements;
} else {
var finalWhereStatements = whereStatements.concat(
["CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains[i] + "'"]
);
}
var keywordReport = AdWordsApp.report(
"SELECT CampaignName, AdGroupName, Criteria, FinalMobileUrls, FinalUrls " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE FinalUrls != '--' AND " + finalWhereStatements.join(" AND "));
var rows = keywordReport.rows();
while (rows.hasNext()) {
var row = rows.next();
var urls = jsonToArray(row['FinalMobileUrls']).concat(
jsonToArray(row['FinalUrls'])
);
for (var j in urls) {
var url = urls[j].toLowerCase();
if (url.match(expectedPattern) === null) {
var rowData = {"CampaignName": row['CampaignName'],
"AdGroupName": row['AdGroupName'],
"Keyword": row['Criteria']
};
if (!urlData.hasOwnProperty(url)) {
urlData[url] = {"keywords": {}, "ads": {}};
}
urlData[url]["keywords"][row['Id']] = rowData;
}
}
}
var adReport = AdWordsApp.report(
"SELECT CampaignName, AdGroupName, HeadlinePart1, HeadlinePart2, " +
"CreativeFinalMobileUrls, CreativeFinalUrls " +
"FROM AD_PERFORMANCE_REPORT " +
"WHERE CreativeFinalUrls != '--' AND "
+ finalWhereStatements.join(" AND "));
var rows = adReport.rows();
while (rows.hasNext()) {
var row = rows.next();
var urls = jsonToArray(row['CreativeFinalMobileUrls']).concat(
jsonToArray(row['CreativeFinalUrls'])
);
for (var j in urls) {
var url = urls[j].toLowerCase();
if (url.match(expectedPattern) === null) {
var rowData = {"CampaignName": row['CampaignName'],
"AdGroupName": row['AdGroupName'],
"Headline": row['HeadlinePart1'] + " - "
+ row['HeadlinePart2']
};
if (!urlData.hasOwnProperty(url)) {
urlData[url] = {"keywords": {}, "ads": {}};
}
urlData[url]["ads"][rowData['Headline']] = rowData;
}
}
}
whereStatements.push("CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '"
+ campaignNameContains[i] + "'");
}
return urlData;
}
// This function outputs details about any invalid URLs to the given Google
// Sheet.
function outputToSheet(urlData) {
var ss = checkSpreadsheet(targetSheetUrl, "the spreadsheet");
var keywordsSheet = ss.getSheetByName("Results - Keywords");
var adsSheet = ss.getSheetByName("Results - Ads");
if (keywordsSheet == null) {
keywordsSheet = ss.insertSheet("Results - Keywords");
}
if (adsSheet == null) {
adsSheet = ss.insertSheet("Results - Ads");
}
keywordsSheet.clear();
adsSheet.clear();
var keywordsRange = [["Bad URL", "Keyword", "Ad Group", "Campaign"]];
var adsRange = [["Bad URL", "Ad Headline", "Ad Group", "Campaign"]];
for (var url in urlData) {
for (var j in urlData[url]["keywords"]) {
var data = urlData[url]["keywords"][j];
keywordsRange.push([url,
data["Keyword"],
data["AdGroupName"],
data["CampaignName"]]);
}
for (var j in urlData[url]["ads"]) {
var data = urlData[url]["ads"][j];
adsRange.push([url,
data["Headline"],
data["AdGroupName"],
data["CampaignName"]]);
}
}
keywordsSheet.getRange(1,1,keywordsRange.length,4).setValues(keywordsRange);
adsSheet.getRange(1,1,adsRange.length,4).setValues(adsRange);
}
// A small helper function for processing AdWords report fields.
function jsonToArray(str) {
return str == "--" ? [] : JSON.parse(str);
}
// Check the spreadsheet URL has been entered, and that it works
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL.");
}
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
// Checks if you can edit the spreadsheet
var sheet = spreadsheet.getSheets()[0];
var sheetName = sheet.getName();
sheet.setName(sheetName);
return spreadsheet;
} catch (e) {
throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.