How to Perform a Negative Keyword Scrub

Next up in our Ad Words Reporting 101 series: How to Perform a Negative Keyword Scrub! Running an analysis to determine which terms to set as negatives is an absolutely crucial part of running paid search campaigns: it’s as important to tell Google what NOT to bid on as it is to tell it what to bid on. So let’s get started.

Why?

REMOVE LOSING KEYWORDS. JDM looks at search terms that are under-performing and evaluates them for relevancy to the product being advertised. If a search term is irrelevant, then it is flagged and made a negative keyword. This means the ad will not display for those keywords anymore.

OPTIMIZE. Ultimately, as expensive searches are made into negative keywords, the account focuses spend on relevant and efficient searches.


How?

1. Download a search term report off Google Ads

2. Flag potential negative keywords

3. Identify and Build Negative Keywords

Skills

1. Excel concatenate function

2. Excel remove duplicates

End product:

image1.png

1. Download a search term report off Google Ads

Filters:

  • Dates: last 30 days up to yesterday

  • Impressions > 0

  • Campaign name contains “google-search”

  • Campaign Name: does not contain “alpha”

Columns:

  • Impressions

  • Clicks

  • CTR

  • Cost

  • Avg. pos.

  • Conversions

  • Cost/Conv.

  • CVR

2. Flag potential negative keywords

INSERT 6 COLUMNS. Insert the following 6 columns to the left of the report: Spend Flag, Impressions Flag, Clicks Flag, CPA Flag, Conversions Flag, and Compiled Flag.

  • SPEND FLAG. Write “Flag” in this column for the terms that meet the following criteria: sort by spend and look at all keywords down to $25 in spend, or 200 rows (whichever comes first).

  • IMPRESSIONS FLAG. Write “Flag” in this column for the terms that meet the following criteria: sort by impressions and look at all keyword down to 5 impressions, or 200 rows (whichever comes first).

  • CLICKS FLAG. Write “Flag” in this column for the terms that meet the following criteria: sort by clicks and look at all keywords down to 3 clicks, or 200 rows (whichever comes first).

  • CPA FLAG. Write “Flag” in this column for the terms that meet the following criteria: CPA > $250.

  • CONVERSIONS FLAG. Write “Flag” in this column for the terms that meet the following criteria: conversions=0 & cost>$200

  • COMPILED FLAG. Put the following excel function in cell F2 and fill down to concatenate all the flags: =E2&D2&C2&B2&A2

3. Identify and Build Negative Keywords

SNIFF TEST. Copy all the search terms that have been flagged to a new tab. Remove duplicates in the list. Now, look at all the remaining search terms and highlight the ones that look irrelevant to the company’s product. Once you’re done, delete all those that you did not highlight. You are left with the search terms to make into negative keywords.

BUILD THE NEG KEYWORDS. For each search term that does not fit the business, create a negative keyword. If you’d like to create a negative keyword for “free gap year programs” because the program isn’t free, you could set “free gap year programs” as the negative keyword for it. Phrase match is used in this example but that will not always be the case; you will need to specify the match type:

    • Phrase Match (you will use this most of the time): “gap year”

    • Exact Match (for when you don’t know what to use): [gap year]

    • Broad Match Modified: +gap +year

    • Broad Match: gap year

Claire Forgan