Google Ads Script Search Query Deduplicator 22.1 - ENGLISH

Deutsche Version: LIESMICH.md

Description

Use this Google Ads Script to identify all search terms that do not match in the intended ad group and automatically exclude them.

Overview

• Installation
• First execution & overview of the columns in the sheet
• Planning / regular execution
• Function descriptions
• Function parameters

---

Installation

I. Set up Google Ads scripts

Open your Google Ads account or MCC and navigate to "Tools and Settings > Bulk Actions > Scripts".

The Search Query Duplicator is divided into two scripts.

Script 1 - "SQ Deduplicator 22.1 Prepare Data"

The first script prepares the data for further processing and writes it to a Google Sheet.

Setup:

Create a new Google Ads script (suggested name: "SQ Deduplicator 22.1 Prepare Data") and overwrite the content with the appropriate code template:

• Single account: single_account_prepare_data.js
• MCC version: mcc_version_prepare_data.js

Script 2 - "SQ Deduplicator 22.1 Process Data"

The second script processes the unprocessed rows from the Google Sheet and writes the processing status back to the respective data row.

Setup:

Create a new Google Ads script (suggested name: "SQ Deduplicator 22.1 Process Data") and overwrite the content with the appropriate code template:

• Single account: single_account_process_data.js
• MCC version: mcc_version_process_data.js

II. Create Google Sheet from template

A Google Sheet is used for the temporary storage and further processing of the search terms. You can copy the template with the following Google Drive link: adtraffic Search Query Deduplicator Google Sheet Template

Paste the ID of your newly created Google Sheet into both scripts at the position of "{{Search Query Deduplicator Google Sheet ID}}".

III Provide BatchService Response (MCC) resp. "ocid"-Parameter (Single Account) [optional]

In order to generate direct links to the Google Ads ad groups, the "ocid" parameter for the respective Google Ads account is required.

III.a Google Ads "BatchService Response" (for MCC Version)

The "ocid" parameter can be read from the response of the BatchService at MCC level for all child accounts. To make the response available as an object in the script, proceed as follows:

1. Open the relevant MCC and navigate to "Accounts > Performance"
2. Open the browser's developer tools and select the "Network" tab
3. Reload the page and filter the entries with "batch account"
4. Click on the entry that starts with "Batch?authuser=" and select "Response" on the right
5. Copy the entire content of the response and paste it in Script 1 - "SQ Deduplicator 22.1 Prepare Data" at the porsition of "{{BatchService Response}}".

Alternatively:

Remove the placeholder "{{BatchService Response}}", leaving the following content in the line:

let batchAccountSnippet = new Object(); ---

III.b Provide Google Ads "ocid" parameter (for single accounts)

For single accounts, the "ocid" parameter can be read from the address bar.

1. Log in to the relevant Google Ads account and copy the digits after "ocid=" from your browser's address bar
2. Paste the copied digits into Script 1 - "SQ Deduplicator 22.1 Prepare Data" at the postion of "{{ocid}}".

Alternatively:

Remove the placeholder "{{ocid}}", leaving the following content in the line:

const ocid = ''; ---

[optional] IV. Define Account IDs (for MCC version)

Add the Account IDs of the accounts to be processed as comma-separated strings in both scripts at the postion of "{{Account IDs Array}}".

Example syntax: '123-456-7890','456-123-7890','654-321-7890','321-654-7890'

Alternatively:

Remove the placeholder "Account IDs Array", leaving the following content in the line:

let accountIds = [];

---

First execution & overview of the columns in the sheet

back to top

When the Script 1 - "SQ Deduplicator 22.1 Prepare Data" is executed for the first time, a new worksheet with the respective account ID as the worksheet name with eleven columns is created in the Google Sheet for each processed account:

• searchTerm
• matchType
• campaignId
• adgroupId
• campaignName
• adGroupName
• deepLink
• impressions
• clicks
• conversions
• processed

The first six columns are self-explanatory.

The "deepLink" column contains a direct link to the ad group of the respective search term (if the "ocid" parameter is available - see Install Step III). The "impressions", "clicks" and "conversions" columns contain the respective values ​​for the queried period (by default the last 30 days).

The last column "processed" can contain six different return values ​​after executing the Script 2 - "SQ Deduplicator 22.1 Process Data":

Return values ​​from Script 2

back to top

Value	Meaning
Term skipped	The search term does not match an actively booked keyword in the account. No negative keyword was created.
Campaign Negative added	The search term corresponds to an actively booked keyword in another campaign. A negative keyword was created in the campaign of the found search term. (*)
Campaign Negative skipped	The search term corresponds to an actively booked keyword in another campaign. However, no negative keyword was created in the campaign of the found search term because a corresponding negative keyword was already booked there.
AdGroup Negative added	The search term matches an actively booked keyword in another ad group in the same campaign. A negative keyword was created in the ad group of the found search term. (*)
AdGroup Negative skipped	The search term matches an actively booked keyword in another ad group in the same campaign. However, no negative keyword was created in the ad group of the found search term because a corresponding negative keyword was already booked there.
Comparison failed	At least one duplicate was found when processing the data from the sheet, but an error occurred when matching the campaignId.

(*) Running the script in preview mode will not create negative keywords. In the script editor under "Changes" you can see in which campaigns or ad groups negative keywords would be booked.

---

Planning / regular execution

back to top

By executing the Script 1 - "SQ Deduplicator 22.1 Prepare Data" all data in the Google Sheet will be overwritten. Running the script weekly should be sufficient for most accounts. For large accounts with several hundred newly identified search queries every day, daily execution makes perfect sense.

The Script 2 - "SQ Deduplicator 22.1 Process Data" only processes rows without a value in the "processed" column. A limit for the maximum number of lines to be processed can be defined (default: 1,000 lines) so that the script does not run into a timeout when processing a very large number of search terms. Script 2 should be planned to run enough times to process all of the identified search terms.

---

Function descriptions

back to top

Script 1 - "SQ Deduplicator 22.1 Prepare Data"

main() ⇒

MCC version: Loads the accounts to be processed and starts the parallel execution of the script.

Single account version: Basically the same as the MCC function processAccount()

processAccount() ⇒

Runs the Google Sheet preparation, then loads the active search terms and initiates the export of the aggregated search terms to the Google Sheet.

prepareSheet() ⇒

Creates a new sheet for the account to be processed in the Google Sheet or deletes the data it contains if a sheet already exists for the respective account.

getActiveSearchTerms() ⇒

Retrieves all search terms that did not match with EXACT or NEAR EXACT match type and generated a minimum number of impressions (30 by default) in the given time period (30 days by default).

writeData() ⇒

Writes the prepared data to the Google Sheet.

---

Script 2 - "SQ Deduplicator 22.1 Process Data"

back to top

main() ⇒

MCC version: Loads the accounts to be processed and starts the parallel execution of the script.

Single account version: Basically the same as the MCC function processAccount()

processAccount() ⇒

Runs the Google Sheet preparation, then loads the active search terms and starts writing the aggregated search terms to the Google Sheet.

checkForDuplicates() ⇒

Based on the processed search term in all other ad groups, it searches for active keywords that exactly match the respective search term, initiates the creation of a keyword to be excluded if it is found and has the result of the processing written back to the Google Sheet.

createCampaignNegative() ⇒

Creates an exact match negative keyword at the campaign level, using the processed search term as the keyword text.

createAdGroupNegative() ⇒

Creates an exact match negative keyword at the ad group level, using the processed search term as the keyword text.

readSheet() ⇒

Reads the data (without header) from the Google Sheet into an array.

updateData() ⇒

Writes the result for each search term to the Google Sheet in the "processed" column of the respective row.

---

Function parameters

back to top

Script 1 - "SQ Deduplicator 22.1 Prepare Data"

global ⇒

Parameter	Type	Description
sheetFileId	String	The ID of the Google Sheet to use.
batchAccountSnippet (MCC Version only)	Object	The content of the "Batch Service Response". (Required for automatic determination of the ocid parameter.)

main() ⇒

MCC version below.
Single account version see processAccount()

Parameter	Type	Description
accountIds	Array	Contains the account IDs of the Google Ads accounts to be processed.

processAccount() ⇒

MCC version below.
Runs as main() in single account version

Parameter	Type	Description
accountId	String	The account ID of the processed account.
ocid	String	The ocid parameter matching the processed account. (Required for creating Google Ads deep links.)
sheetStatus	Integer	2 = new spreadsheet was created. 1 = content was removed from existing spreadsheet.
activeSearchTerms	Array	Return values ​​from getActiveSearchTerms()

prepareSheet() ⇒

Parameter	Type	Description
sheetFileId	String	The ID of the Google Sheet to use.
accountId	String	The account ID of the processed account.
ss	Spreadsheet Object	The Google Sheet to use
templateSheet	Sheet Object	The empty sheet template within the Google Sheets used. (Used for creating new sheets for processed accounts.)

getActiveSearchTerms() ⇒

Parameter	Type	Description
ocid	String	The ocid parameter matching the processed account. (Required for creating Google Ads deep links.)
activeSearchTerms	Array	Return values for processAccount()
query	String	GAQL query to perform search for active search terms using AdsApp.search()
search	AdsApp.SearchRowIterator	see AdsApp.SearchRowIterator
googleAdsDeeplink	String	The Google Ads deep link to the ad group of the respective search term

writeData() ⇒

Parameter	Type	Description
data	Array	Return values from getActiveSearchTerms().
sheetFileId	String	The ID of the Google Sheet to use.
sheetName	String	The account ID of the processed account.
sheet	Spreadsheet Object	The Google Sheet used.

---

Script 2 - "SQ Deduplicator 22.1 Process Data"

back to top

global ⇒

Parameter	Type	Description
sheetFileId	String	The ID of the Google Sheet to use.

main() ⇒

MCC version below.
Single account version see processAccount()

Parameter	Type	Description
accountIds	Array	Contains the account IDs of the Google Ads accounts to be processed.

processAccount() ⇒

MCC version below.
Runs as main() in single account version

Parameter	Type	Description
accountId	String	The account ID of the processed account.
sheet	Spreadsheet Object	The Google Sheet used.
existingValues	Array	All data rows (without header) of the processed account spreadsheet.
filteredValues	Array	Filtered rows from existingValues ​​with no values ​​in column "processed".
count	Integer	Auxiliary variable for counting the rows already processed
status	String	Return value from checkForDuplicates(). (See Return values ​​from Script 2)

checkForDuplicates() ⇒

Parameter	Type	Description
rowData	Array	Input values ​​from processAccount(). (The row to process from filteredValues.)
accountId	String	The account ID of the processed account.
query	String	GAQL query to search for already booked keywords in other ad groups using AdsApp.search()
search	AdsApp.SearchRowIterator	See AdsApp.SearchRowIterator

createCampaignNegative() ⇒

Parameter	Type	Description
term	String	Keyword text to create the negative keyword.
campaign	Integer	ID of the campaign in which to create the negative keyword.

createAdGroupNegative() ⇒

Parameter	Type	Description
term	String	Keyword text to create the negative keyword.
adgroup	Integer	ID of the ad group in which to create the negative keyword.

readSheet() ⇒

Parameter	Type	Description
sheet	Spreadsheet Object	The Google Sheet used.

updateData() ⇒

Parameter	Type	Description
sheet	Spreadsheet Object	The Google Sheet used.
index	Integer	Relative row number of the processed search term in the spreadsheet
status	String	Return value from checkForDuplicates(). (See Return values ​​from Script 2)