Substitute Broad Match Modifier Excel: Broad match modifiers are a popular Paid Search keyword match type, giving you the extended targeting of broad match without the scattergun targeting associated with pure broad match.
Creating campaigns in Excel is my go-to choice for many reasons for Google AdWords and Bing Ads. One reason is that I can take my keywords that I’ve built and replicate them with the broad match modifier to replicate any campaign. The problem is how to do this within Excel. Adding a (+) symbol could turn into a formula, so it becomes confusing. The way to create bmm keywords in excel is with the substitute formula. Here is the method to develop broad match modifier keywords in excel.
For the most part, advanced proficiency in Excel is not a requirement for advanced proficiency in paid search. That said, Excel offers a number of tools that can make a PPC account manager more efficient in his or her daily work. Below are three Excel functions with specific use cases for PPC that make them valuable tools in your account management arsenal.
Substitute Broad Match Modifier Excel
If you need to update your broad match type keywords into broad match modifier keywords, leverage the SUBSTITUTE function and AMPERSAND character. You can replace all space characters with “ +” and insert a “+” at the beginning of the string.
How to Create Broad Match Modifier Keywords in Excel for Google Ads and Bing Ads Upload
We need to write out the keywords that we want to target. Let’s say we want to build out our exact match keywords first. For this example, I wrote ten or so keywords that I would like to target. To create this as a broad match modifier, I go to the substitute formula to put in the “+” symbols in it.
BMM Formula Explanation
The formula efficiently works like this. The substitute part allows us to add the “+” mark right in front of the cell reference which in this case is A2. The next part of the formula leaves a space after cell reference A2 to create the space for the keyword. The next part of the formula again adds a “+,” but if you notice carefully, I left a space in front of that symbol. This area is incredibly vital for BMM keywords for your campaigns in Google and Bing. Having that space after each word allows the BMM keywords to efficiently served.
Things to remember
- You may not wish to apply the broad match modifier to every word in your keyword. Some of them are targeted enough to not require the modifier.
- Bulk uploads are useful but should always be carefully sense checked to ensure there is nothing that shouldn’t be there.
- Depending on the way your product is defined, you may get similar results by using phrase match keywords.
- Even though broad match modified keywords are more specific than pure broad match, they are still broad match keywords. Remember to stay on top of your negatives.
Do you like using cool Excel formulas when managing your PPC accounts? Here’s another on CPA based bulk bidding.
SUBSTITUTE for Modified Broad Match Keywords
The Substitute function in Excel performs a find and replace operation on the selected cell.
Useful in a number of situations, I most often use this formula to add “+” to the front of each word in a bulk list of keywords. When building out keywords in an AdWords Editor Excel template, this formula instantly converts any number of keywords to modified broad match.
− Text: the cell(s) on which you want to perform the operation
− Old_text: the characters you want to replace
− New_text: the characters you want to put in place of old_text
− Instance_num: which occurrence of old_text you want to replace (if omitted, all instances are replaced)
How to Use It
When creating keyword lists in Excel, the following variation of the Substitute function can be used to convert keywords into modified broad match:
=”+”&SUBSTITUTE(A1,” “,” +”)
The initial “+” places the “+” in front of the first word of the keyword. The Substitute function then replaces each instance of a space with a space followed by “+”.
LEN and SUBSTITUTE to Count Words
Although it seems like there should be a simple formula to count the number of words in Excel, the most efficient way to do this is with a combination of the LEN and SUBSTITUTE functions.
I most frequently use this when doing keyword research for competitive terms. While Google’s Keyword Planner has some great recommendations, you must spend time sifting through the less useful one- and two-word recommendations to find suitable keywords. After downloading suggestions from the Keyword Planner into Excel, I use this function to calculate the number of words in each keyword. I then filter for longer-tail terms (e.g., 3+ words) to quickly find the most valuable recommendations.
− LEN – Text: the cell that contains the characters you want to count
− SUBSTITUTE – See above
How to Use It
The idea here is that a space designates a break between words. By counting the number of spaces, we can essentially count the number of words. In a blank column, use the below formula to count the number of words in the selected cell(s):
The first expression counts the number of characters in the selected cell. The SUBSTITUTE function in the second expression replaces all instances of a space (“ “) with nothing (“”), so we can then count the number of characters without spaces in the selected cell. Taking the difference of the two expressions yields the number of spaces. The final +1 is for the first word, which has no space before it.
SUBTOTAL & Filters for Quick SQR Analysis
Excel filters are a powerful way to quickly drill down into search queries that contain certain words, do not contain certain words, etc. Evaluating performance for certain types of search queries can inform negative keywords, new ad group breakouts, and other optimizations. SUBTOTAL can be used to view performance metrics only for the queries you’re filtering on.
Traditional SUM formulas calculate on the full range of data and will not update as you add and remove filters. The SUBTOTAL function can be used to perform a number of computations, including COUNT, SUM, AVERAGE, and more. For SQR analysis, I most often use SUM. This allows me to quickly compare performance for search queries containing different modifiers (e.g., “modern” versus “stylish”).
− Function_Num: the number that corresponds to the computation you want to perform; for SUM, this is 9
How to Make Your SEO & PPC Spreadsheets Rock
As a marketing professional, you no doubt utilize Microsoft Excel in your regular daily work, especially for tracking analytics and campaign activities. Depending upon how long you’ve been using Excel or who taught you how to do things, you may or may not be making the most out of this powerful tool. Whether you are frustrated because certain tasks are too cumbersome or time consuming or you don’t even think about it because you just do what you’ve always done, I’ll bet you can be more efficient.
So, if you’re interested in doing all of the things you’re doing in Excel today but in less time and with less opportunity for manual error, let these 10 tips be your guide. Note that in each example, cell values such as A1 are used as samples only. You should enter the cell identifiers specific to your situation as appropriate.
The problem? While the basic Microsoft Office Find and Replace features work, they take more time than you would like.
The solution? The SUBSTITUTE formula. By inserting this easy formula, you can speed up the time it takes you (or Excel) to find and replace select values. As an added bonus, you can do this for multiple values at the same time and even designate the order they should be done in. This formula is:
The problem? When keywords are imported from tools or titles are created via concatenation, every word can end up in all lowercase letters but you know that you need title case.
The solution? The PROPER formula. With just a few keystrokes, you can have all of your data display in title case. This formula is:
- Extra Spaces
The problem? Extra spaces can be added to keywords or other data when imported into your spreadsheet.
The solution? The TRIM formula. This function lets you identify which cells for Excel to scan and eliminate all unnecessary spaces, before or after phrases or in between words. This formula is:
- Combining Values into One Cell
The problem? Keyword research often finds you wanting to combine values from multiple cells into one cell and you don’t want to have to do that manually.
The solution? The CONCATENATE formula—short version. You may have seen the standard formula that lets you concatenate (which simply means to combine) information from two cells into one but there’s a shorter, more concise version that makes this function even better. This formula is:
- Match Type
The problem? Being able to manipulate your match type value can be time consuming.
The solution? Multiple match type formulas. Excel gives you the power to search by exact match, phrase match or broad match modifier. Each of these functions leverages the power of multiple formulas together, including TRIM, SUBSTITUTE and CONCATENATE. These formulas are:
For exact match: =”[“&TRIM(A1)&”]”
For phrase match: =””””&TRIM(A1)&””””
For broad match modifier: =”+”&SUBSTITUTE(TRIM(A1),””,”+)
The problem? Excel’s SUM function is great but you find it tedious to manually enter =SUM(A1:A9).
The solution? The Quick SUM formula. With only two key clicks, you can create the sum formula seen above that would take you 11 key clicks. This lets you quickly add up all values in a column in the cells above the one in which you enter the function. This function is accessed by:
Step 1: place the cursor below the column of numbers you want to sum (or to the left of the row of numbers you want to sum).
Step 2: hold down the Alt key and then press the equals = sign while still holding Alt.
Step 3: press Enter.
- Scrolling Up and Down
The problem? Your spreadsheets are likely thousands of rows long. If you are on row 2034 and want to view something on row 8, you need an easy way to get there.
The solution? The Quick Scroll keyboard function. This is not so much of a formula as it is a handy function in Excel. If you want to select cells from your starting and ending place, you can use the same keystrokes but also add SHIFT. These shortcuts are:
To go up: CTRL + ↑
To go down: CTRL + ↓
- Designating Percentages
The problem? Entering numbers or importing them is enough, you don’t want to go through multiple steps and dialog boxes just to add a % sign, too.
The solution? The Number Formatting shortcut. When you consider that the % sign is found on the 5 key, this makes a lot of sense. This shortcut is:
CTRL SHFT 5
- Designating Currency
The problem? Entering numbers or importing them is enough, you don’t want to go through multiple steps and dialog boxes just to add a $ sign, too.
The solution? The Number Formatting shortcut. When you consider that the $ sign is found on the 4 key, this makes a lot of sense. This shortcut is:
CTRL SHFT 4
- Applying a Formula to Multiple Cells
The problem? In a great many cases, the formula you assign to a particular cell is the one you will want assigned to all cells in that column—and you don’t want to do this manually.
The solution? The Repeat Formula shortcut. You only need to enter your formula in the first cell of a column and then you can copy it out from there. To do this:
Hover cursor in lower right of first cell, wait for + sign to appear
Double click to copy
These are by no means all of the functions you can take advantage of in Excel to make your day easier but they will no doubt get you off to a great start. Your time is valuable and the need for your data to be accurate is great. Take advantage of all the help that your tools offer to simplify, save time and improve the quality of your work.
PPC Keyword Match Type Manipulation
The Function Trifecta!
This is one of the most useful functions and one of my all-time favorites. I can’t begin to explain how much time that this saves over the course of a workweek. If you’ve ever performed keyword research and left trailing or double spaces in cells, then this function will solve all problems. The TRIM function simplly removes leading and trailing spaces as well as any consecutive spaces that may appear within text.
I’m personally not a huge fan of the long form version of the CONCATENATE function and rarely, if ever, use it. I’ve found that the abbreviated ampersand form is a much better fit for my workflow. Both the CONCATENATE function and ampersand combine values either inputted manually or from different cells into one cell. It’s important to note that you must manually insert a space between values. This can be accomplished by inserting a space within quotes.
Find and replace is ok, but those who really want speed up their day use the SUBSTITUTE function to swap values. The SUBSTITUTE function takes a value that you define and replaces it with another value that you define. The true beauty is that you can nest multiple SUBSTITUTE functions to perform many swaps simultaneously or in preferred orders.
=SUBSTITUTE(A1,”original value“,”replacement value“)
Easy Match Type Manipulation
Let’s break down how to quickly convert any keyword into any match type. We won’t explain how each match type behaves, but will illustrate how to quickly achieve any match type change that is needed.
Always use the TRIM function:
First, always wrap keyword text in a TRIM function. If the TRIM function is not used there is a risk of adding a trailing spaces or nonsense values into keyword lists. This can be an especially annoying problem when adding hundreds or thousands of keywords into an account.
For those wondering how to view formulas within multiple cells, try playing around with CTRL + ~. It’s a bunch of fun and quickly reveals what’s taking place in complicated or unfamiliar spreadsheets.
Creating an exact match version is perhaps the simplest conversion to accomplish. Simply CONCATENATE each side of the cell with opening and closing brackets. Both the standard usage of CONCATENATE and the ampersand form are shown below.
The trick with creating a phrase match version of a keyword is getting the quotations to correctly display. Since Excel by default uses quotes to indicate text, four consecutive quotes must be used in order to display correctly.
Broad Match Modifier
This is one of my favorites. Truth be told, I get excited anytime that I have the chance to use SUBSTITUTE. In this case, use CONCATENATE to prepend a plus sign then use SUBSTITUTE to replace a space with a space and plus sign.
People also ask
What is a broad match modifier?
Contents. Broad match modifier (BMM): This match type is a relatively new entrant and lies in between broad and phrase match. It gives more control than broad match and more freedom than phrase match. It works by adding a ‘+’ sign in front of words in a keyword phrase when the match type is selected as broad.
How do you add a broad match modifier?
Sign in to your Google Ads account.
From the page menu on the left, click Keywords.
Hover over the keyword you wish to edit, then click the pencil icon .
Add the plus sign (+) prefix to any word you want to make into broad match modifiers.
What is the difference between broad match and phrase match?
Phrase match offers some of the versatility of broad match, but like modified broad match, introduces a higher level of control. Your ad will only appear when a user queries your key phrase using your keywords in the exact order you enter them, but there might be other words either before or after that phrase.
How do I change the keyword to match keyword in broad match?
Select the keywords to edit.
In the edit panel, select a new Match type in the drop-down menu.
Should I use broad or exact match?
It’s also a good idea to vary your keyword match types. Don’t use all broad match keywords, or your ad will display for too many people who aren’t interested. Likewise, if you only use exact match, your ads might not show up often enough to get you good results.
Are exact match keywords cheaper?
Exact match will have lower cost
This best practice only ensures that by doing so one can target keywords more accurately and will help bring down the cost. … In other words, it is cheaper to target a search query from exact match keyword when compared to a phrase match keyword.
What is exact match?
With exact match, you can show your ad to customers who are searching for your exact keyword, or close variants of your exact keyword. Close variants include searches for keywords with the same meaning as the exact keywords, regardless of spelling or grammar similarities between the query and the keyword.
Should negative keywords be broad match?
Negative broad keywords won’t match on synonyms or similar words like they can for regular keywords. Negative keywords, even on broad match, won’t match on close variations or plurals. For single-word negative keywords, there is no difference between broad and phrase match.
What is Phrase Match example?
Phrase match is a keyword matching option whereby Google matches your ad only against keywords that include a phrase you designate. … For example, your ad could appear for the query red tennis shoes but not for shoes for tennis, tennis shoe, or tennis sneakers.
How many primary match types are there?
Google AdWords currently has three primary “match types” for setting up specific keywords for each of your ads.
What are different types of keywords?
There are 9 types of keywords: short tail, longtail, short-term, long-term, product defining, customer defining, geo-targeting, and intent targeting. All of these keywords have their special strength that can multiply your SEO efforts when used in different situations.
Which is the default keyword match type?
First, start off with broad match keywords to maximize the potential to show your ads on relevant searches. Broad match is the default type of keyword, so if you’ve already added keywords, chances are you don’t have to do anything special to make them broad match. Learn more about adding keywords.
Which keyword match type has the highest relevance but the lowest reach?
Exact match keywords provide the lowest reach but highest relevance. Brackets around keywords sets them to exact match. Only search terms that exactly match the keywords in brackets will cause your ad to show.
How many keywords should you target?
Targeting about 5 keywords (each with a monthly search volume of 100+) to start with is recommended for most small businesses. For some businesses, that might not seem a lot, but targeting 5 keywords doesn’t mean that your website will only rank for 5 keywords and get traffic from 5 keywords.
Why is it a good idea to separate your keyword match types into different ad groups?
Grouping your campaigns by match type makes life a lot easier when it comes to adding search terms. Since each non-exact campaign has an equivalent exact match campaign then it’s usually a great initial place to look when considering where to add the search term to your account.
What is the opposite of exact?
exact(adj) marked by strict and particular and complete accordance with fact. “an exact mind”; “an exact copy”; “hit the exact center of the target” Antonyms: wrong, free, inexact, approximate, loose, rough, odd, incorrect, round, approximative, liberal.
Does exact match include plurals?
As Google got smarter, they altered Exact Match keywords to account for close variants, including misspellings, singular forms, plural forms, acronyms, stemmings, abbreviations, and accents. This cut down on the need to include dozens of variants in a single ad group considerably.
What is negative phrase match?
A keyword setting that allows you to exclude your ad for searches that include the exact keyword phrase. Searches may include additional words, and the ad won’t show as long as the keywords are included in the search in the same order.
What type of keywords should be added as negative keywords?
If your ads are being shown for searches using terms that are unrelated to you, add them to your negative keyword list. You might already have an idea of the search queries that you don’t want to trigger your ads.
What percentage of clicks do PPC ads get?
While the average click-through rate for PPC ads is only 2 percent, the average click-through rate for the top paid result on the screen is almost 8 percent (Accuracast). More than half of all searches are longer than four words (Wordstream).
Which bid strategy allows you to pay after an ad is clicked?
Maximize Clicks: This is an automated bid strategy. It’s the simplest way to bid for clicks. All you have to do is set an average daily budget, and the Google Ads system automatically manages your bids to bring you the most clicks possible within your budget.
How do I find my LSI keywords?
Search engine LSI keywords suggestion
Just type your main keyword in Google search and note down all the words displayed by it. That’s it you now have a list of related keywords. If the list has very few keywords then you can repeat the same process with the words displayed by Google.
What are long tail keywords?
Long-tail keywords are longer and more specific keyword phrases that visitors are more likely to use when they’re closer to a point-of-purchase or when they’re using voice search. They’re a little bit counter-intuitive, at first, but they can be hugely valuable if you know how to use them.
How many keywords is too many?
Select keywords that are pertinent to the topic of each webpage. Each keyword should be strategically selected and placed. Keep in mind not to exceed 20 keywords per page regardless of whether the ideal keyword density matches up with the amount of content.