Google Ad Script to exclude those placements that have more than 100 impressions & less than 10% of view rate
Rahul
var CAMPAIGN_NAME_CONTAINS = ''; // Leave empty to target all campaigns
var MIN_IMPRESSIONS = 100;
var MIN_VIEW_RATE = 0.10; // 10%
// Calculate date range for last 6 months
var endDate = new Date();
var startDate = new Date();
startDate.setDate(endDate.getDate() - 180);
var DATE_RANGE = formatDate(startDate) + "' AND '" + formatDate(endDate);
var query = "SELECT campaign.id, campaign.name, campaign.status, group_placement_view.placement, " +
"group_placement_view.placement_type, metrics.impressions, metrics.video_views " +
"FROM group_placement_view " +
"WHERE campaign.advertising_channel_type = 'VIDEO' " +
"AND metrics.impressions >= " + MIN_IMPRESSIONS + " " +
"AND segments.date BETWEEN '" + DATE_RANGE + "' " +
"AND campaign.status IN ('ENABLED', 'PAUSED')";
if (CAMPAIGN_NAME_CONTAINS) {
query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS + "%'";
}
var report = AdsApp.search(query);
while (report.hasNext()) {
var row = report.next();
var campaignId = row['campaign.id'];
var campaignName = row['campaign.name'];
var campaignStatus = row['campaign.status'];
var placement = row['group_placement_view.placement'];
var placementType = row['group_placement_view.placement_type'];
var impressions = row['metrics.impressions'];
var videoViews = row['metrics.video_views'];
var viewRate = videoViews / impressions;
if (placementType === 'YOUTUBE_CHANNEL' && viewRate < MIN_VIEW_RATE) {
var campaign = AdsApp.videoCampaigns().withIds([campaignId]).get().next();
try {
campaign.excludePlacement(placement);
Logger.log('Excluded channel ' + placement + ' from campaign ' + campaignName +
' (Status: ' + campaignStatus + ') due to low view rate: ' +
(viewRate * 100).toFixed(2) + '%');
} catch (e) {
Logger.log('Failed to exclude channel ' + placement + ' from campaign ' + campaignName +
' (Status: ' + campaignStatus + '). Error: ' + e.toString());
}
}
}
}
// Helper function to format date as YYYY-MM-DD
function formatDate(date) {
var year = date.getFullYear();
var month = ('0' + (date.getMonth() + 1)).slice(-2);
var day = ('0' + date.getDate()).slice(-2);
return year + '-' + month + '-' + day;
}
Google Ads Scripts Forum Advisor
Hi,
Thank you for reaching out to the Google Ads Scripts support team.
I would like to inform you that the "AdsApp.search()" method you are using will only support search and display type of campaigns. I would suggest that you create a script by referring to the documents “AdsApp.VideoPlacement”, “AdsApp.VideoPlacementSelector”, and “columns” and you can exclude a placement list by referring to the sample code in the “document”.
I hope this helps. Kindly get back to us if you still face any issues.
Thanks,
 |
Google Ads Scripts Team |
RULK (Rahul Kumar)
HI Team,
I have modified the script as per your suggestion, could you please check why is it not fetching the data in log while it is executed without any error.
Here is the code-
function main() {
var CAMPAIGN_NAME_CONTAINS = ''; // Leave empty to target all campaigns
var MIN_IMPRESSIONS = 100;
var MIN_VIEW_RATE = 0.10; // 10%
// Calculate date range for last 6 months
var endDate = new Date();
var startDate = new Date();
startDate.setDate(endDate.getDate() - 180);
// Format dates as required by the API
var formattedStartDate = formatDate(startDate);
var formattedEndDate = formatDate(endDate);
var campaignIterator = AdsApp.videoCampaigns()
.withCondition("CampaignStatus IN ['ENABLED', 'PAUSED']");
if (CAMPAIGN_NAME_CONTAINS) {
campaignIterator = campaignIterator
.withCondition("CampaignName CONTAINS '" + CAMPAIGN_NAME_CONTAINS + "'");
}
campaignIterator = campaignIterator.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var videoTargeting = campaign.videoTargeting();
var placementIterator = videoTargeting.youTubeChannels() // Changed from placements() to youTubeChannels()
.forDateRange(formattedStartDate, formattedEndDate)
.withCondition("Impressions >= " + MIN_IMPRESSIONS)
.get();
while (placementIterator.hasNext()) {
var placement = placementIterator.next();
var stats = placement.getStatsFor(formattedStartDate, formattedEndDate);
var impressions = stats.getImpressions();
var views = stats.getViews();
var viewRate = views / impressions;
if (viewRate < MIN_VIEW_RATE) {
try {
videoTargeting.excludeYouTubeChannel(placement.getChannelId()); // Changed to excludeYouTubeChannel
Logger.log('Excluded channel ' + placement.getChannelName() + ' (ID: ' + placement.getChannelId() + ') from campaign ' + campaign.getName() +
' (Status: ' + campaign.getStatus() + ') due to low view rate: ' +
(viewRate * 100).toFixed(2) + '%');
} catch (e) {
Logger.log('Failed to exclude channel ' + placement.getChannelName() + ' (ID: ' + placement.getChannelId() + ') from campaign ' + campaign.getName() +
' (Status: ' + campaign.getStatus() + '). Error: ' + e.toString());
}
}
}
}
}
// Helper function to format date as required by the API
function formatDate(date) {
return {
year: date.getFullYear(),
month: date.getMonth() + 1,
day: date.getDate()
};
}
Output Ref- 
|
Best regards
Rahul (RULK)
|
From: Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com>
Sent: Wednesday, June 26, 2024 5:10 PM
To: adwords...@googlegroups.com
Subject: RE: google ad script to exclude those placements that have more than 100 impressions & less than 10% of view rate
Caution: This email originated from outside of the Novo Nordisk email system. Use caution when clicking on links or opening attachments.
--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit
https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/adwords-scripts/ZCWOFtgM00M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
adwords-scrip...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/adwords-scripts/3McK9000000000000000000000000000000000000000000000SFOR3600NWeWYKbsQcKXTwTyE_aQMw%40sfdc.net.
Sigurd Fabrin
Google Ads Scripts Forum Advisor
Hi
@Sigurd - Thanks for the insights.
I would suggest that you follow the Sigurd’s suggestion and try executing the script. If you still face any issues, kindly get back to us with the below details.
- Google Ads account ID/CID
- Name of the affected script
- Shareable spreadsheet link if you are using in your script
RULK (Rahul Kumar)
Hi Team,
Here is the details:
CID - 960-878-5592
Script Name: Exclude placement
|
Best regards
Rahul (RULK)
|
Google Ads Scripts Forum Advisor
After verifying the account (960-878-5592), it is observed that video campaigns are paused and impressions are showing zero. That is the reason the script is not fetching any data.