Google Ads Negative Keywords – Free script to automate analysis

“Lost money on irrelevant clicks will never be paid back again.“  Unknown.

Negative keywords are a crucial component of any Google Ads campaign. If you are using different match types (beyond exact), you will bid on search terms that are irrelevant for your business model. This way, you will constantly lose money while reaching an audience that is not interested in your products or services.

Google Ads Negative Keywords are keywords that you specifically do not want to target; they help you to target a more relevant audience, increase CTR, and decrease average CPC. However, it can take considerable time to build a working list of negatives. In this article, we will explain what negative keywords are, how to find them, and how to add them to your Google Ads account. At the end of this article, as a Free bonus we will share the script we use and that already saved us more than 1000 hours of working time.

How Google Ads negative keywords work? 

Negative keywords let you block ads for irrelevant phrases that are otherwise triggered by broad or match phrase keywords. For example, you might run campaigns for a jewelry store that does not sell diamonds. You are targeting the keyword +buy +ring. This keyword in the BMM match type may trigger the following search terms:

buy an engagement ring

buy a ring with diamonds

buy a wedding ring

Adding diamonds, as a negative keyword will block all search terms that contain diamonds.

Types of Google Ads negative keywords:

  1. Broad is the default match type which allows you to block traffic from all search terms where every word, in any order, of your negative keyword appears in the search. Example – diamond ring without brackets or quotation marks.
  2. Using exact match type you will block traffic only from specific search phrase. Example – [diamond ring] in brackets.
  3.  Phrase match will block traffic from specific search phrase and any additional words which user may type. Example – “diamond ring” in quotation marks.

How to find and add Negative Keywords:

In Google Ads you can add negatives on three levels:

  1. On the Campaign level
  2. On the Ad group level
  3. On the Shared library level

It is more convenient to use the Shared library level because you can use the same list for all of your campaigns right away. So, instead of adding seven or more lists for each of your campaigns you get away with only having one.

To add negatives on a Shared library level, click on “Shared library” > “Campaign negative keywords” > “+ LIST”:

On this screenshot you can see multiple lists because we are promoting different services in this Google Ads account. So, a negative keyword for Campaign A may be a good and relevant keyword for Campaign B.

To add negatives on Campaign level, choose the required campaign, navigate to the keyword level, click on “Negative keywords”, and add the keywords on the required level:

I never add any negatives on the Campaign level within each campaign, because I am doing it on a Shared Library level; however, I am using the Ad group level for two reasons:

  1. To implement cross negatives when creating separate campaigns in different match types.
  2. To individually block specific search terms for specific For example, for the last five years, we have been promoting a wine store:

Currently, only wine, produced in 2016 is in stock, so we had to block all other years for this Ad group:

Google Ads Universal Negative Keywords:

When you are performing a keyword research you will receive a huge list, which will include both relevant and irrelevant keywords. To simplify the work you can mark all keywords that contain universal negatives and delete them.

Also, you can add universal negatives on a Shared library level from the very start. You can find many lists on the web, just Google them and go through the list carefully before uploading it, as it may contain relevant keywords for your industry.

How to Analyze Google Ads Search Report:

Step 1: Download your search terms report. 

Before uploading, you can set a filter to exclude all search terms that lead to conversions and search terms that were triggered by exact match keywords:

Step 2: Download the list of your Google Ads target keywords.

Step 3: Combine it all in Excel.

Your final file should have the following structure:

Keyword | Search Term | Metrics from search report

Step 4: Set up and launch macros.

To launch macros you need to open Visual Basic (Alt+F11)

If you don’t have Visual Basic in your developer’s menu, you can unlock it by following the process: File > Options > Customize Ribbon > Main Tabs or by shortcut (Alt+F11)

When you open Visual Basic, you will see the editor. Insert a new module:

And paste the following code into it:

Sub SearchTerms()
'Updateby20160628 by UAWC. Ivan Gurin
Dim x
Dim arr() As String
Dim xValue As String
Dim aValue As String
Dim Splitted() As String
Dim InputRng As Range, OutRng As Range, Keywords As Range
xTitleId = "UAWC Agency SearchTerms Analyzer Yandex"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("SearchTerms + Impressions + Clicks + Cost :", xTitleId, InputRng.Address, Type:=8)
Set Keywords = Application.Selection
Set Keywords = Application.InputBox("Keywords :", xTitleId, Keywords.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set dicKeywords = CreateObject("Scripting.Dictionary")
dicKeywords.CompareMode = vbTextCompare 'case insensitive
Set dicFinal = CreateObject("Scripting.Dictionary")
dicFinal.CompareMode = vbTextCompare 'case insensitive
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For j = 1 To Keywords.Columns.Count
For i = 2 To Keywords.Rows.Count
xValue = Keywords.Cells(i, j).Value
Dim indexOfDash As Integer
indexOfDash = InStr(1, xValue, "-")
If indexOfDash > 0 Then
Dim newxValue As String
newxValue = Left(xValue, indexOfDash - 2)
Else: newxValue = xValue
End If
For Each x In Split(RuOnly(newxValue), " ")
If x <> "" And Not dicKeywords.Exists(x) Then
dicKeywords(x) = ""
End If
For i = 2 To InputRng.Rows.Count
aValue = InputRng.Cells(i, 1).Value
For Each x In Split(RuOnly(aValue), " ")
If x <> "" And Not dicKeywords.Exists(x) Then
If dicFinal.Exists(x) Then
Set dic2 = dicFinal.Item(x)
dic2.Item("Impressions") = dic2.Item("Impressions") + InputRng.Cells(i, 2).Value
dic2.Item("Clicks") = dic2.Item("Clicks") + InputRng.Cells(i, 3).Value
dic2.Item("Cost") = dic2.Item("Cost") + InputRng.Cells(i, 4).Value
Set dic2 = CreateObject("Scripting.Dictionary")
dic2.CompareMode = vbTextCompare
dic2.Add "Impressions", InputRng.Cells(i, 2).Value
dic2.Add "Clicks", InputRng.Cells(i, 3).Value
dic2.Add "Cost", InputRng.Cells(i, 4).Value
dicFinal.Add x, dic2
End If
End If
Set OutRng = OutRng.Offset(0, 1)
OutRng.Value = "Impressions"
Set OutRng = OutRng.Offset(0, 1)
OutRng.Value = "Clicks"
Set OutRng = OutRng.Offset(0, 1)
OutRng.Value = "Cost"
Set OutRng = OutRng.Offset(1, -3)
For Each Key In dicFinal
OutRng.Value = Key
Set OutRng = OutRng.Offset(0, 1)
For Each kk In dicFinal(Key).keys
OutRng.Value = dicFinal(Key).Item(kk)
Set OutRng = OutRng.Offset(0, 1)
Set OutRng = OutRng.Offset(1, -4)
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "Naslajdaytes'"
End Sub
Function RuOnly(strSource As String) As String
Dim i As Integer
Dim strResult As String
For i = 1 To Len(strSource)
Select Case Asc(Mid(strSource, i, 1))
Case 32, 48 To 57, 60 To 90, 97 To 122, 128 To 255:  '32 - probel, 48 to 57 - cyfry, 60 to 90, 97 to 122- english Letters, 128 to 255 - russian
strResult = strResult & Mid(strSource, i, 1)
End Select
RuOnly = strResult
End Function

You will see something like this:

Launch the script by pressing the green button Play (or F5). You will see a pop up window asking you to choose columns, which contain search terms and metrics. In my case these are columns B-F.

Then, in the next pop up window, choose the column with your keywords:

And finally, choose the column in which you want to output your results:

In the end, you will get a list of potential negatives:

Step 5: Work with the potential list of Google Ads Negative Keywords.

You will want to just go through the list and mark keywords that are not relevant, or if your list is very long, you can filter your search terms by cost to exclude traffic that is wasting the largest share of your budget.

Leave a Reply