Select your language
Focus your display campaigns on high quality domains
Start Now!
When setting up a display campaign in Google Ads, you most likely will lose a lot of your budget to shitty websites. Google will serve the campaign everywhere it can, based on your target audience. But with a little help of the script and the API from Ahrefs or SE Ranking, you can focus on high quality domains.
To use this script, you'll need a subscription to the Ahrefs API or SE Ranking API.
The script pulls all yesterday's placements from your account. You can set a threshold for the number of impressions, if you want to limit the API use.
The it will run al these placements through the API to collect the domain rating and the traffic. Based on your set thresholds for these to KPI's, it will mark a domain good or bad. Both will be added to a spreadsheet and the bad ones will be added to a placement exclusions list. You'll have to define the name of this list in the config and attach the list to all your display campaigns.
The next day, the script will run again and pulls all good placements from the spreadsheet. That way, the API won't have to those domains again.
This script doesn't include PMax placements
The script is limited to run 6.000 domains per day. That's because of the URL-fetch that happens in the script. For the use of the API, three fetches are made (create a report, pull the values and then delete the report). You can only make 20.000 fetches a day, based on Google limits.
// Copyright 2024
// Free to use or alter for everyone. A mention would be nice ;-)
//
// Created By: Tibbe van Asten
//
// Last update: 11-03-2024
//
// ABOUT THE SCRIPT
// This script checks all placements and runs them through an analyser
// Bad domains can be excluded based on your set threshold.
//
////////////////////////////////////////////////////////////////////
var config = {
LOG : true,
// Add a new spreadsheet to keep track of good and bad placements.
// Adding both will make this script more efficient
SPREADSHEET_URL: "https://docs.google.com/spreadsheets/d/1XXyuZUOqUlrdyeb7euFrwg93aF26Y-QNtmzNKJdI3DU/edit",
AHREFS_API_KEY: "", //J8HdCkkEOHSRtIE-LRTpsckIxVGtxZ5c4obC6dbz
// When using SE Ranking API, create an API key. Also enter the CC
SE_RANKING_API_KEY: "e460ef9f1877d7a7ec1300eca2376980ea5b5af6",
SE_RANKING_COUNTRY: "nl",
EXCLUSIONS_LIST: "Display Exclusions",
IMPRESSION_THRESHOLD: 0,
DR_THRESHOLD: 33,
TRAFFIC_THRESHOLD: 3000
}
////////////////////////////////////////////////////////////////////
function main() {
let spreadsheet = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
let goodPlacements = collectPlacements(spreadsheet, "Good placements");
let goodTab = SpreadsheetApp.openById(spreadsheet.getId()).getSheetByName("Good placements");
let badTab = SpreadsheetApp.openById(spreadsheet.getId()).getSheetByName("Bad placements");
let today = new Date().toISOString().slice(0, 10);
let list = selectExclusionsList(config.EXCLUSIONS_LIST);
var placements = AdsApp.report(
"SELECT group_placement_view.target_url " +
"FROM group_placement_view " +
"WHERE group_placement_view.placement_type = 'WEBSITE' " +
"AND group_placement_view.target_url NOT REGEXP_MATCH '"+goodPlacements+"' " +
"AND metrics.impressions > "+config.IMPRESSION_THRESHOLD+" " +
"AND segments.date DURING YESTERDAY").rows();
if(config.LOG === true){
Logger.log(placements.totalNumEntities() + " placements found" + "\n\n");
}
var i = 0;
while(placements.hasNext() && i < 6000){
var placement = placements.next();
if(config.AHREFS_API_KEY != ""){
var dr = checkAhrefs("domain-rating", placement['group_placement_view.target_url'], today).domain_rating.domain_rating;
var traffic = checkAhrefs("metrics", placement['group_placement_view.target_url'], today).metrics.org_traffic;
}
if(config.SE_RANKING_API_KEY != ""){
var dr = checkSERankingDA(config.SE_RANKING_COUNTRY, placement['group_placement_view.target_url']).inlink_rank;
var traffic = checkSERankingTraffic(config.SE_RANKING_COUNTRY, placement['group_placement_view.target_url']).organic.traffic_sum;
}
if(dr < config.DR_THRESHOLD || traffic < config.TRAFFIC_THRESHOLD){
badTab.appendRow([placement['group_placement_view.target_url'],dr,traffic,today])
list.addExcludedPlacement(placement['group_placement_view.target_url'])
if(config.LOG === true){
Logger.log(placement['group_placement_view.target_url'] + " excluded");
}
} else {
goodTab.appendRow([placement['group_placement_view.target_url'],dr,traffic,today])
}
if(config.LOG === true){
Logger.log(placement['group_placement_view.target_url'] + " - DR: " + dr + " - Traffic: " + traffic);
Logger.log(" ");
}
i++;
} // placementIterator
} // function main()
////////////////////////////////////////////////////////////////////
function selectExclusionsList(name){
var list = AdsApp
.excludedPlacementLists()
.withCondition("shared_set.name = '"+name+"'")
.get().next();
return list;
} //selectExclusionsList
////////////////////////////////////////////////////////////////////
function collectPlacements(file, tab){
var tab = SpreadsheetApp.openById(file.getId()).getSheetByName(tab);
var placements = tab.getRange("A2:A");
var filteredPlacements = placements.getValues().filter(subArray => subArray.some(Boolean)).flat().toString().replace("[","").replace("]","").replaceAll(",","|");
if(config.LOG === true){
Logger.log("Good placements: " + filteredPlacements);
}
return filteredPlacements
} // collectPlacements
////////////////////////////////////////////////////////////////////
function checkAhrefs(metric, url, date){
const options = {
method: "GET",
headers: {
"Content-Type": "application/json",
"Authorization": "Bearer "+config.AHREFS_API_KEY
},
"muteHttpExceptions": true
};
Logger.log("https://api.ahrefs.com/v3/site-explorer/"+metric+"?date="+date+"&target="+url)
var response = UrlFetchApp.fetch("https://api.ahrefs.com/v3/site-explorer/"+metric+"?date="+date+"&target="+url, options);
const data = JSON.parse(response.getContentText());
Logger.log(data)
return data;
} // checkAhrefs
////////////////////////////////////////////////////////////////////
function checkSERankingDA(source,url){
const options = {
method: "POST",
headers: {
"Content-Type": "application/json",
"Authorization": "Token "+config.SE_RANKING_API_KEY
},
"muteHttpExceptions": true
};
const options2 = {
method: "GET",
headers: {
"Content-Type": "application/json",
"Authorization": "Token "+config.SE_RANKING_API_KEY
},
"muteHttpExceptions": true
};
const options3 = {
method: "DELETE",
headers: {
"Content-Type": "application/json",
"Authorization": "Token "+config.SE_RANKING_API_KEY
},
"muteHttpExceptions": true
};
var createReport = UrlFetchApp.fetch("https://api4.seranking.com/backlink-reports?mode=domain&target="+url, options);
const reportData = JSON.parse(createReport.getContentText());
var response = UrlFetchApp.fetch("https://api4.seranking.com/backlink-reports/"+reportData.report_id+"/overview", options2);
const data = JSON.parse(response.getContentText());
var deleteReport = UrlFetchApp.fetch("https://api4.seranking.com/backlink-reports/"+reportData.report_id, options3);
return data;
} // checkSERanking
////////////////////////////////////////////////////////////////////
function checkSERankingTraffic(source,url){
const options = {
method: "GET",
headers: {
"Content-Type": "application/json",
"Authorization": "Token "+config.SE_RANKING_API_KEY
},
"muteHttpExceptions": true
};
var response = UrlFetchApp.fetch("https://api4.seranking.com/research/"+source+"/overview/?domain="+url, options);
const data = JSON.parse(response.getContentText());
return data;
} // checkSERanking
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.
