Skip to content

Latest commit

 

History

History
372 lines (278 loc) · 16.9 KB

README.md

File metadata and controls

372 lines (278 loc) · 16.9 KB
Raw

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

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)