If you often work on AdWords text ads, you'll like this little spreadsheet that will make the keyword matching tags automatically for you. As you know, the matching convention for keywords is:
Imagine how tiresome doing this would be, for every word, if you have hundreds of keywords (or search terms). Here's a Google spreadsheet that will do this for you: Keyword Match Tagger. You just paste your search terms (one search term per line) on the "Keyword" column, and the other columns will populate with the keyword matching tags which you can then copy to your own spreadsheet. If you pasted in keywords with duplicates, those will be highlighted in red. Since modified broad match does not consider the order of the words in a search term, the "MMB Duplicate Detector" column will rearrange the words alphabetically in a search term and highlight duplicates in red as well.
Here's how it works:
When you click the link, Google Docs will prompt you to save a copy in your Google Drive.
You can recreate this spreadsheet yourself in these steps:
Exact Match | Phrase Match | Modified Broad Match |
---|---|---|
[this is a keyword] | "this is a keyword" | +this +is +a +keyword |
Here's how it works:
When you click the link, Google Docs will prompt you to save a copy in your Google Drive.
You can recreate this spreadsheet yourself in these steps:
- Make a new Google spreadsheet;
- Create five column headings: Keyword, Exact, Phrase, Modified Broad Match, MBM Duplicate Detector;
- Below the 'Exact' heading, type or paste the formula ="["&$B2&"]" Change the cell address 'B2' to your actual keyword cell address;
- Below the 'Phrase' heading, type or paste the formula =""""&$B2&"""" ;
- Below the 'Modified Broad Match' heading, type or paste the formula
="+"&(SUBSTITUTE($B2," "," +")) ; - Below the 'MBM Duplicate Detector' heading, type or paste the formula
=IFERROR(ArrayFormula(JOIN(" ", SORT(TRANSPOSE(TRIM(SPLIT(B2," ")))))),"") Again don't forget to change the cell address 'B2' to your actual cell address. (Thanks to the user, Ahab, for his shorter answer in this Google Docs help forum thread. I just modified it by adding an error trap and removing the commas in the original formula) - Copy the cells down as many rows as you like, either by selecting the cells with formula and dragging the little blue square that shows up in the selection's lower right corner, or by selecting the top-most cell with the formula, holding Shift and clicking on the bottom-most row you'd like the formulas to be to create a selection block, and doing Ctrl+D to copy the topmost cells down to the last cells below. In this sheet I copied the formulas down to the 700th row but you may want to have less than that.
- To detect duplicate keywords in the 'Keyword' column, select all the cells that should hold keywords, then go to the menu entry 'Format > Conditional formatting', then select the 'Custom formula here' option (bottom of the dropdown), and paste this formula in the 'Value or formula' box that appears:
=COUNTIF($B$2:$B$701, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1 (Change the range '$B$2:$B$701' to your actual range address). Then, change the default format [that shows up when the formula is true] using the text formatting buttons in the 'Formatting style' section of the dropdown (I use red bold text). Then click 'Done'. - To detect duplicates in the 'MBM Duplicate Detector' column, select all cells with formula in that column and repeat step 8, changing '$B$2:$B$701' to your actual range address. If the 'Keyword' column range is '$B$2:$B$701', then your 'MBM Duplicate Detector' column range would be '$F$2:$F$701' (Thanks to user 'CommonRaven', for his answer to this StackOverflow question.)
Pics:
This spreadsheet was actually an Excel macro-enabled workbook (*.xlsm) first—with a different formula for sorting the words in a cell alphabetically:
=IFERROR(SortWithinCell(B2," ",TRUE),"")
This formula uses a custom 'SortWithinCell' function created through a Visual Basic macro—the reason for the *.xlsm file extension—since I couldn't find a working formula that doesn't use macros. In Excel, paste this Visual Basic script in the module box that shows up after doing Alt+F11, 'Insert > Module':
You'll be asked to save your file as *.xlsm. (See "Can you sort data alphabetically in one particular cell?" from Excelforum.com for more info on the function.)
Use the formula =IFERROR(SortWithinCell(B2," ",TRUE),"") under the'MBM Duplicate Detector' heading.
Excel's conditional formatting is also a bit different, since conditionally formatting duplicate values is already built in:
Note: Originally this Google spreadsheet didn't have a Modified Broad Match duplicate detector column. Looks like it has slowed down with all the conditional formatting. Ah well. ¯\_(ツ)_/¯
###
Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, DelimitingCharacter & " ", DelimitingCharacter) MyArray = Split(CelltoSortString, DelimitingCharacter) For N = 0 To UBound(MyArray) For M = 1 To UBound(MyArray) If MyArray(M) < MyArray(M - 1) Then TempValue = MyArray(M) MyArray(M) = MyArray(M - 1) MyArray(M - 1) = TempValue End If Next M Next N For N = 0 To UBound(MyArray) SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter Next N SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1) If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ") End Function
Use the formula =IFERROR(SortWithinCell(B2," ",TRUE),"") under the'MBM Duplicate Detector' heading.
Excel's conditional formatting is also a bit different, since conditionally formatting duplicate values is already built in:
###
Comments