Sign up for weekly recaps of the ever-changing search marketing landscape.
Advanced Excel For PPC: How To Work With Match Types Using VBA Code
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:
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 - email@example.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:
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:
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.
Now, add insert a new column before column D, and call it “Match Type”. In this column, in cell D3, enter the following:
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.
Good luck out there.
Some opinions expressed in this article may be those of a guest author and not necessarily Search Engine Land. Staff authors are listed here.