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

Sponsored


About The Author: is Director of Advertising Services at Stone Temple Consulting. Crosby has extensive experience in search engine marketing including growing small accounts to multi-million dollar success stories at companies such as QuinStreet.com and JustAnswer.com.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Juliana Hughes

    Super informative. Thank you for your thoroughness!

  • Steven Plimmer

    That’s a good way of doing it. Or you could add the match type column in the UI before downloading then add the “raw” keyword column and use this formula:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C35,”"”",”"),”[",""),"]“,”"),”+”,”"),”-”,”")

    Where B2 is the original keyword cell. Then you fill the column with this formula.

    Save this file so you can reuse either the formula or the file (to paste data into).

    If you don’t have the match type column for some reason, this formula will do it (in a column you add for match type):
    =IFERROR(IFERROR(IF(LEFT(B2,1)=”[“,”Exact”,IF(LEFT(B2,1)=CHAR(34),”Phrase”, “Broad”)),IF(LEFT(B2,1)=CHAR(34),”Phrase”, “Broad”)),”Broad”)

    The above is if you don’t want to specify Modified Broad and if you have no negatives.

  • http://www.facebook.com/konradhagemes Konrad Hagemes

    Jep that’s cool but there is a small mistake in the code of the first two functions at the beginning:

    wrong:
    Public Function GetMatchTypeFromPowerPostKeyword

    (ByVal kwd As String) As String
    Dim mt As String
    mt = “”…
    correctly:Public Function GetMatchTypeFromPowerPostKeyword(ByVal kwd As String)
    Dim mt As String
    mt = “”…

    wrong:Public Function GetKeywordFromPowerPostKeyword
    (ByVal kwd As String) As String
    If (0 = Len(kwd)) Then…
    correctly:Public Function GetKeywordFromPowerPostKeyword(ByVal kwd As String)
    If (0 = Len(kwd)) Then…

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide