When working with AdWords Keyword reports downloaded from the Web interface, you may come across Keywords with their Match Types signified by special punctuation, rather than a separate column indicating Match Type.

In this post, we will use some Excel VBA code that will help us work back and forth between this format and the regular, two column format recognized by AdWords Editor. As an example, we can add all of our existing broad match keywords as exact match so we can refine our bidding control on them. Let’s get started.

This posting format originated as a means of uploading keywords in bulk through the web interface without having to provide 2 columns for keyword and match type. So, rather than having to input something like:

red bike    broad
blue bike   phrase
fast bike   phrase
tiny bike   exact

We can instead simply encode the match type using special punctuation, like this:

red bike
"blue bike"
"fast bike"
[tiny bike]

In this style, Broad Match keywords are left as-is (no special punctuation). Note that this is the default when simply pasting a list of keywords. Be careful with Broad Match!). Broad Match Modified keywords get a “+” in front of each modified keyword (I usually tag all of them by default, but your usage may vary. Phrase Match keywords get surrounded by double-quotes. Exact Match Keywords get surrounded by brackets.

Basically, like this:

Broad Match
+Broad +Match +Modified
"Phrase Match"
[Exact Match]

Here is the current google help on the topic: http://support.google.com/adwords/bin/answer.py?hl=en&answer=2497836&from=6100&rd=1

To see for yourself, login to your AdWords account, navigate to the Keywords tab (hopefully, with some keywords of various match types), and click the download button.

Once you download and open in Excel, you should see something like this:

From here, if you wanted to work with those keywords and make a bulk sheet to upload to AdWords Editor, you would have to translate from that Power Posting format to regular Bulk Sheet Format. Of course, if you want to copy/paste into the web interface, you can do so, but you might find that somewhat limiting. For example, you can’t paste into multiple Campaigns or AdGroups, delete things, etc.

Let’s see if we can translate those characters into something meaningful without having to do a bunch of manual find-and-replace operations.

Here is some Excel VBA code that will do the trick:

  Option Explicit

  'COPYRIGHT: 2011 Stone Temple Consulting 
(http://www.stonetemple.com)
  'AUTHOR: Crosby Grant - cgrant@stonetemple.com
  'LICENSE: Provided under Creative Commons BY-NC license.
  'Details here: http://creativecommons.org/licenses/by-nc/3.0/
  'Essentially: You may share or remix, 
but you must attribute the original author
(e.g.: include this entire block of text) 
and you may not use this work for commercial purposes 
(e.g.: sell it or its output).
  'WARRANTY: This code is distributed as-is, with no warranty.
  'USAGE INFORMATION: 
http://searchengineland.com/ppc-shop-tools-the-permutator-99135
  'CONTRIBUTIONS:
Code and design contributions graciously accepted.
Please contact the author directly.

  Public Function GetMatchTypeFromPowerPostKeyword

(ByVal kwd As String) As String
    Dim mt As String
    mt = ""

    If (0 = Len(kwd)) Then
      GetMatchTypeFromPowerPostKeyword = ""
      Exit Function
    Else
      If ("-" = Left(kwd, 1)) Then
        If (1 < Len(kwd)) Then
          mt = "Negative "
          kwd = Mid(kwd, 2, Len(kwd) - 1)
        End If
      End If
      If ("[" = Left(kwd, 1) And "]" = Right(kwd, 1)) Then
        kwd = Mid(kwd, 2, Len(kwd) - 2)
        GetMatchTypeFromPowerPostKeyword = mt + "Exact"
        Exit Function
      ElseIf ("""" = Left(kwd, 1) And """" = Right(kwd, 1)) Then
        kwd = Mid(kwd, 2, Len(kwd) - 2)
        GetMatchTypeFromPowerPostKeyword = mt + "Phrase"
        Exit Function
      Else
        GetMatchTypeFromPowerPostKeyword = mt + "Broad"
        Exit Function
      End If
    End If
  End Function

  Public Function GetKeywordFromPowerPostKeyword
(ByVal kwd As String) As String
    If (0 = Len(kwd)) Then
      GetKeywordFromPowerPostKeyword = ""
      Exit Function
    Else
      If ("-" = Left(kwd, 1)) Then
        If (1 < Len(kwd)) Then
          kwd = Mid(kwd, 2, Len(kwd) - 1)
        End If
      End If
      If ("[" = Left(kwd, 1) And "]" = Right(kwd, 1)) Then
        kwd = Mid(kwd, 2, Len(kwd) - 2)
        GetKeywordFromPowerPostKeyword = kwd
        Exit Function
      ElseIf ("""" = Left(kwd, 1) And """" = Right(kwd, 1)) Then
        kwd = Mid(kwd, 2, Len(kwd) - 2)
        GetKeywordFromPowerPostKeyword = kwd
        Exit Function
      Else
        GetKeywordFromPowerPostKeyword = kwd
        Exit Function
      End If
    End If
  End Function

  Public Function GetPowerPostKeyword
(ByVal kwd As String, ByVal mt As String)
    mt = Replace(LCase(mt), " match", "")
    Select Case mt
      Case "broad"
        GetPowerPostKeyword = kwd
      Case "negative broad"
        GetPowerPostKeyword = "-" & kwd
      Case "phrase"
        GetPowerPostKeyword = """" & kwd & """"
      Case "negative phrase"
        GetPowerPostKeyword = "-""" & kwd & """"
      Case "exact"
        GetPowerPostKeyword = "[" & kwd & "]"
      Case "negative exact"
        GetPowerPostKeyword = "-[" & kwd & "]"
    End Select
  End Function

If you are unfamiliar with working with VBA, here is the short version of how to make use of this:

• Open your worksheet in Excel.
• ALT-F11 (opens the VBA coding window)
• Copy/Paste the above code into the editor
• ALT-F11 (switches back to Excel)
• Save-As a Macro-Enabled Workbook
• Use the functions like any other Excel Function

Here is a nice resource to help get you started: http://msdn.microsoft.com/en-us/library/office/ee814737.aspx

Let’s get to that keyword download sheet then, shall we?

First, let’s rename the Keyword column to “Keyword.Old”. Then, insert a new column before column C, and call it “Keyword”. In this column, in cell C3, type the following formula, and then fill-down all the rows in your report.

=GetKeywordFromPowerPostKeyword($B3)

Your report should look similar to this:

Now, add insert a new column before column D, and call it “Match Type”. In this column, in cell D3, enter the following:

=GetMatchTypeFromPowerPostKeyword($B3)

Your report should now look something like this:

From here, let’s say we want to add all of our existing broad match keywords as exact match so we can refine our bidding control on them. In Excel, add a Filter to your report (ALT-A-T). Then filter the Match Type column to only show Broad.

Now, you have a few options from here, but one simple method would be to type “Exact” into the Match Type column, and copy that down for all the rows you are seeing, then copy to the clipboard.

Next, open up AdWords Editor and Add/Update Multiple Keywords (CTRL-SHIFT-K). Editor will automatically ignore the “Keyword.Old” column (and any other columns related to metrics, etc.).

Good luck out there.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Advanced | Channel: SEM | How To | How To: PPC | Search Marketing Toolbox