AdWords Negative Keywords – Save money and time with an Excel script.

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

Negative keywords are a crucial component of any AdWords 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.

Adwords 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 AdWords 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 AdWords  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 AdWords 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 AdWords 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 AdWords 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:

AdWords 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.

You can copy our universal negatives from this link.

How to Analyze AdWords 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 AdWords 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
        Next
    Next
Next


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
        Else
            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
        Next
Next

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)
    Next
    Set OutRng = OutRng.Offset(1, -4)
Next


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
    Next
    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 Adwords 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

Your email address will not be published. Required fields are marked *