Logo Lira
  • Home
  • Services
    • eCommerce PPC Management
    • Shopify PPC Management
    • eCommerce PPC Audit
    • Google Shopping Management
    • Performance Max Management
    • Microsoft Shopping Management
    • Amazon PPC Services
    • Real Estate PPC Management
    • SaaS PPC Management
    • Lead Generation
    • Bing Ads Management
  • Portfolio
  • Blog
  • Resources
    • Library
      • eCommerce
      • SaaS
      • Real Estate
    • Downloads
    • E-commerce Glossary
    • ACOS to ROAS Calculator
    • Shopify Google Shopping Feed
  • About Us
    • Team
    • Google Premier Agency
  • Contacts
Get in touch
Logo Lira
  • Home
  • Services
    • eCommerce PPC Management
    • Shopify PPC Management
    • eCommerce PPC Audit
    • Google Shopping Management
    • Performance Max Management
    • Microsoft Shopping Management
    • Amazon PPC Services
    • Real Estate PPC Management
    • SaaS PPC Management
    • Lead Generation
    • Bing Ads Management
  • Portfolio
  • Blog
  • Resources
    • Library
      • eCommerce
      • SaaS
      • Real Estate
    • Downloads
    • E-commerce Glossary
    • ACOS to ROAS Calculator
    • Shopify Google Shopping Feed
  • About Us
    • Team
    • Google Premier Agency
  • Contacts
Get in touch
  • Home
  • Services
    • eCommerce PPC Management
    • Shopify PPC Management
    • eCommerce PPC Audit
    • Google Shopping Management
    • Performance Max Management
    • Microsoft Shopping Management
    • Amazon PPC Services
    • Real Estate PPC Management
    • SaaS PPC Management
    • Lead Generation
    • Bing Ads Management
  • Portfolio
  • Blog
  • Resources
    • Library
      • eCommerce
      • SaaS
      • Real Estate
    • Downloads
    • E-commerce Glossary
    • ACOS to ROAS Calculator
    • Shopify Google Shopping Feed
  • About Us
    • Team
    • Google Premier Agency
  • Contacts
  • Home
  • Services
    • eCommerce PPC Management
    • Shopify PPC Management
    • eCommerce PPC Audit
    • Google Shopping Management
    • Performance Max Management
    • Microsoft Shopping Management
    • Amazon PPC Services
    • Real Estate PPC Management
    • SaaS PPC Management
    • Lead Generation
    • Bing Ads Management
  • Portfolio
  • Blog
  • Resources
    • Library
      • eCommerce
      • SaaS
      • Real Estate
    • Downloads
    • E-commerce Glossary
    • ACOS to ROAS Calculator
    • Shopify Google Shopping Feed
  • About Us
    • Team
    • Google Premier Agency
  • Contacts
Google Ads

Google Ads Script for Cleaning GDN Placements by Placement`s Titles

May 16, 2017 by Artem Akulov

There is a huge amount of bad placements in Google Display Network (GDN). But at the same time, GDN is a good source for PPC lead generation. If you don’t use it, you don’t get all leads and conversions you could. However, if you use GDN and do not clean your placement`s list, you waste your money for sites about games, recipes, films, fun, etc.

A simple but very routine solution could be to manually clean all placements on a weekly basis. It can include the following steps:

  1. Get placement report for last week or day (depends on how much traffic you get from GDN).
  2. Click in each placement with clicks or more than 3 impressions to check the placement.
  3. If placement is about one of the common “bad” fields that often wastes money (sites about games, recipes, films, funny videos, etc), copy those placements to the Negative placement list in the Shared library.

This is not rocket science, but it`s hard, takes time and we have to do it if we do not want to waste money.
That’s why we decided to develop a Google Ads script that makes the same process. There are ready-to-use AdWords scripts below.

The Main Idea

The main idea is that bad placements get the most traffic from organic searches. And for SEO it is highly important to set the right words in the Title HTML tag.

For example:

If a webmaster wants to promote a site for search terms about games or fun, he (or she) has to include those words in the site`s title tag.

So all we need is to compare placement`s title tags with a predefined list of bad word templates. It`s enough to remove placement if the title tag consists of the word “game” or “GTA”, etc in any place.

That`s why our script parses placements titles, compares them with bad words templates and adds bad placements to the Negative list in the Shared library.

Script Mechanism

For collecting data about placements and for cleaning we use separate scripts (because we need time delay for parsing placement`s titles). Titles are parsed via Google Spreadsheet.

The parsing process takes time (depending on placement count), that`s why we decided to make 2 versions of the script:

  1. The impression script version analyzes placements with more than 3 impressions but without clicks and conversions (for yesterday’s placement report) and works on a daily basis.
  2. Click script version analyzes placements with clicks but without conversions (for 7 placement reports) and works weekly.

Actually, we could analyze only placements with clicks. But in this way, we remove bad placement only when it already spend our money. So we make an additional script version which analyzes and cleans placements with impressions. And we remove bad placements before they spend money.

Final Script Process:

Run Report script (send data to Google SpreadSheet) → Google SpreadSheet parses placement titles → Run Cleaning script (compare parsed titles and bad words template, set also in Google SpreadSheet).

Script Schedule

As I mentioned, for title parsing we use Google SpreadSheet. And parsing takes time (usually 50-100 placement titles per hour). If you have a lot of GDN traffic, we recommend scheduling script in a next way:

For Impression Version

  • First Run – Start report script manually (after 3 a.m., also recommended before 3 p.m.)
  • Set for cleaning script schedule at 2 a.m. daily
  • Set for report script schedule at 3 a.m. daily

In this way, time delay between sending data to Google SpreadSheet = time on parsing process and cleaning is 23 hours.

For Click Version

  • Set for report script schedule at 3 a.m. on Monday weekly
  • Set for cleaning script schedule at 3 a.m. Tuesday weekly

Here is an example for scripts schedule:

Schedule Example For Adwords Scripts

Google SpreadSheet for Parsing and Store Bad Words Templates

The spreadSheet consists of 3 sheets:

  • Source
  • BadWords
  • RawDataReport
Google Spreadsheet for parsing placement titles

“Source” Sheet

Column A — placement`s domain names

Column B — parsed titles (by Google Spreadsheet formulas)

“BadWords” Sheet

Bad Words Templates

We added common templates for bad words. You can add additional templates to the list. Just add new templates in column A. You also can remove some templates (for example, if you work with a woman audience and want to show ads on recipe sites).

“RawDataReport” Sheet

Here first report script sends data from placement report in AdWords (filtered by impressions or clicks as set).

Warning. Do not change “Source” and “RawDataReport” sheets.

Impressions Report Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [[email protected]]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1IawBXlLeBsYBi9A45HQadTlACEgtMhY4jG7nw773hII/edit?usp=sharing

//Set link to your copy of Google Spreadsheet above
var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/1r0WMGA-vJaa8iH7gEd9IjPiKOwovepyQ7QI5fSItNM4/edit?usp=sharing’;

var RawDataReport_Name = “RawDataReport”; //Don’t change the name of the sheet if you use standart docs template
var RawDataReportSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(RawDataReport_Name);

function main() {
createReports();
}
//Get placements with 4 and more impressions without clicks for yesterday.
//For Clicks it`s recommended to set a copy of current script.
//Second script works on weekly basis
function createReports() {
var RawDataReport = AdWordsApp.report(“SELECT Domain, Clicks, Impressions ” + “FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ” + “WHERE Impressions > 3 ” + “AND Clicks < 1 ” +”AND Conversions < 1 ” + “DURING YESTERDAY”);
RawDataReport.exportToSheet(RawDataReportSheet);
}

Impression report script on GitHub

Clicks Report Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [[email protected]]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1gECAF4iatFWDEnwKaZdnLLBCoW2-O6uOuZMOpQ94JaI/edit?usp=sharing

//Set link to your copy of Google Spreadsheet above
var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/14zZoMrMV1K41zMqRjla9jfIBOOUX7IRawYGIqlp3mwY/edit?usp=sharing’;

var RawDataReport_Name = “RawDataReport”; //Don’t change the name of the sheet if you use standart docs template
var RawDataReportSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(RawDataReport_Name);

function main() {
createReports();
}
//Get placements with 1 and more clicks over last 7 days.
//It`s recommended to schedule script weekly
function createReports() {
var RawDataReport = AdWordsApp.report(“SELECT Domain, Clicks ” + “FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ” + “WHERE Clicks > 0 ” + “AND Conversions < 1 ” + “DURING LAST_7_DAYS”);
RawDataReport.exportToSheet(RawDataReportSheet);
}

Clicks report script on GitHub

Cleaning Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [[email protected]]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

//Set Negative Placement list in Shared library for script`s results.
//If list in not exist script create a list with that name
var EXCLUDED_PLACEMENT_LIST_NAME = ‘PlacementCleanerByTitleList’;

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1IawBXlLeBsYBi9A45HQadTlACEgtMhY4jG7nw773hII/edit?usp=sharing

var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/1r0WMGA-vJaa8iH7gEd9IjPiKOwovepyQ7QI5fSItNM4/edit?usp=sharing’;
//Get Data with Placement`s Titles
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var urlTitles = getSheetData(ss, 0);
//Massive on negative templates which will be compared with parsed GDN placement`s Titles.
var badWords = getSheetData(ss, 1);

function main() {
var excludePlacementArray = [];
for (var i = 0; i < urlTitles.length; i++) {
if (containsAny(urlTitles[i][1].toLowerCase(), badWords)) {
excludePlacementArray[excludePlacementArray.length] = urlTitles[i][0].toString();
}
}
Logger.log(excludePlacementArray);//In Log you will see a result list of new excluded placements
addNegativeKeywordToList(excludePlacementArray);
}
function getSheetData(ss, sheetIndex) {
var sheet = ss.getSheets()[sheetIndex];
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
return range.getValues();
}
function containsAny(str, substrings) {
for (var i = 0; i != substrings.length; i++) {
var substring = substrings[i];
if (str.indexOf(substring) != – 1 && str.indexOf(‘mobileapp::’) == -1) {
return substring;
}
}
return null;
}
function addNegativeKeywordToList(negativePlacements) {
var excludedPlacementListIterator =
AdWordsApp.excludedPlacementLists().withCondition(“Name = ‘” + EXCLUDED_PLACEMENT_LIST_NAME + “‘”).get();

if (excludedPlacementListIterator.totalNumEntities() == 1) {
var excludedPlacementList = excludedPlacementListIterator.next().addExcludedPlacements(negativePlacements);
} else {
AdWordsApp.newExcludedPlacementListBuilder()
.withName(EXCLUDED_PLACEMENT_LIST_NAME)
.build().getResult().addExcludedPlacements(negativePlacements);
}
}

Cleaning script on GitHub

Script creates new negative placements list (if it does not exist) and adds new bad placements to it. You need to implement this result negative placements list to all GDN campaigns you need.

Script adds excluded placements to log.

Additional Links

  • Google SpreadSheet for Impressions report (ready to use) [Eng version]
  • Google SpreadSheet for Clicks report (ready to use) [Eng version]
  • GitHub with AdWords Scripts

Conclusion

Those scripts are not a full substitution of human work and human brain, but they make placement cleaning work much easier. So specialists can spend more time on experiments and analytics.

Maximize your business’s potential with our expert PPC lead generation services, and start converting more leads into loyal customers today. Contact us to get started.

google ads
Guide: Call Reporting and Call Conversions for Google AdsGuide: Call Reporting and Call Conversions for Google AdsFebruary 9, 2017
International Real Estate Advertising : Specifics & RecommendationsMarch 13, 2019International Real Estate Advertising : Specifics & Recommendations
0 0 votes
Article Rating
Subscribe
Login
Notify of

0 Comments
Inline Feedbacks
View all comments
Subscribe to Lira Blog Updates
PPC insights for experts and newbies
* = required field
Categories
Analytics case study E-commerce Google Ads Microsoft Ads (Bing Ads) PPC Real Estate PPC Shopify Uncategorized Yandex.Direct
Featured author image: Google Ads Script for Cleaning GDN Placements by Placement`s Titles

Artem Akulov

Hi! I'm the Founder & CEO at Lira Agency. Welcome to our blog! Ask questions and share your insights in comments.

Recent Posts
  • Effective Strategies to Boost Your E-commerce Sales and Drive Revenue Growth March 7, 2023
  • Bing Shopping VS Google Shopping March 2, 2023
  • Key E-Commerce Statistics You Need to Know for 2023 and Beyond February 17, 2023

Logo Lira

Making strong companies all over the world even stronger with thoughtful PPC management

[email protected]

Google Premier Partner Lira Agency

Facebook Business Partner Lira Agency

Privacy Policy | Terms & Conditions

Copyright © 2023 Lira. All Rights Reserved

#integrio_button_64170465cea9f .wgl_button_link { color: rgba(253,253,253,1); }#integrio_button_64170465cea9f .wgl_button_link:hover { color: rgba(253,253,253,1); }#integrio_button_64170465cea9f .wgl_button_link { border-color: rgba(33,107,34,1); background-color: rgba(33,107,34,1); }#integrio_button_64170465cea9f .wgl_button_link:hover { border-color: rgba(66,150,43,1); background-color: rgba(66,150,43,1); }#integrio_button_64170465cea9f.effect_3d .link_wrapper { color: rgba(33,107,34,1); }#integrio_button_64170465d15be .wgl_button_link { color: rgba(255,255,255,1); }#integrio_button_64170465d15be .wgl_button_link:hover { color: rgba(255,255,255,1); }#integrio_button_64170465d15be .wgl_button_link { border-color: rgba(33,107,34,1); background-color: rgba(33,107,34,1); }#integrio_button_64170465d15be .wgl_button_link:hover { border-color: rgba(66,150,43,1); background-color: rgba(66,150,43,1); }#integrio_button_64170465d15be.effect_3d .link_wrapper { color: rgba(33,107,34,1); }#integrio_soc_icon_wrap_64170465de9ec a{ background: #216b22; border-color: #216b22; }#integrio_soc_icon_wrap_64170465de9ec a:hover{ background: #3c8e25; border-color: #3c8e25; }#integrio_soc_icon_wrap_64170465de9ec a{ color: #ffffff; }#integrio_soc_icon_wrap_64170465de9ec a:hover{ color: #ffffff; }
We use cookies to store your preferences and visits in order to provide the most relevant experience. By clicking "Accept All", you accept ALL cookies. Visit "Cookie Settings" to make changes.
Cookie SettingsAccept All
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT
wpDiscuz