CSE Formulas are a lost science to the vast majority of Excel Users.  CSE stands for Control+Shift+Enter.  CSE formulas allow you to perform array calculations.  Let’s see how it works:

To calculate to total product sum of each row, you will need to write 4 short formulas (see image) or 1 long one = (A2*B2)+(A3*B3) )+(A4*B4) )+(A5*B5) )+(A6*B6)

To utilize the power of CSE formulas and simplify your calculations you would enter the following formula in Cell C4 and get the same result:  =sum(A2:B6*B2:B6).  Before you press enter, Remember, you must press (CSE) Control+Shit+Enter.  This is the only way to convert your formula to calculate Arrays.  You will notice that your formula converted from =sum(A2:A6*B2:B6) to ={sum(A2:A6*B2:B6)}. This may sound like its not worth the time, but consider more complex formulas over 1,000s of cells!

Now, like a child in school you are asking yourself: “How am I going to use this in the real (SEM) world?”  Lets take a look…

Say you have 2,300 new keywords in a massive list targeted to provide physical therapy to people with back issues, but it’s a dirty list and you want to exclude any keywords related to surgery.

• Column A should have your keywords
• Column B should have your negative keyword list
• Column D will be where your CSE formula goes

Use the following formula in Cell D2: =IF(SUM(NOT(ISERROR(FIND($B$2:$B$6,A2)))*1)>0,TRUE,FALSE) & CTRL+SHIFT+ENTER
The only variable you need to change in this formula is $B$6: The 6 represents the last row on which you negative keyword list ends.  In this example, it ends on Row 6.  If it ends on row 50, then the formula would change to $B$50 or ={IF(SUM(NOT(ISERROR(FIND($B$2:$B$50,A2)))*1)>0,TRUE,FALSE)}

.

.

Copy the formula down to the last row in Column A.  Turn on Filter and select True from Column D or sort by Column D.  You can now delete those rows which you do not want and continue with your clean keyword list.

You can also use this formula to build an account structure. Instead of using negative keywords, add a list of like minded words to help you group into campaigns and adgroups.

Excel, Keywords, negative keywords, SEM

Alright, so you have your keyword list built out. Hundreds, maybe even thousands, of keywords all ready and waiting to be typed in and searched by your beloved audience. You’re ready to get people on your site. You’re excited.

But here’s the snag: unless you’re running campaigns made entirely of exact match keywords, you’re going to need to throw in a few negatives to keep the undesirables out. And by a “throw in a few,” I mean “build a comprehensive list.”

Why? Well, first off, a lot of people underestimate the power of the negative keyword. But if you think about it, negatives refine the search process just as much as any other keyword. They all have the same purpose, though the methods are different: keywords bring certain people in and negative keywords keep certain people out. Like a guest list and a bouncer, respectively. You need both to throw a successful shindig.

Yeah, just roll with me on the analogies here.

Like this one: basically, negatives are the silly putty that plug the holes left by broad and phrase match keywords. Without them, your search funnel will be leaking all over the place. Which just leads to wasted money, in the end.

So what’s the first step? Keeping in mind that there are two different levels of negative keywords, the ad group level and the campaign level. And trust me, they’re not the same.

Ad group level negatives are best used to keep your ad groups completely separate from each other, so that search queries don’t trigger a broad match in one ad group when there’s an exact match in another ad group, one that happens to be much better suited for them. In other words, they keep your keywords in the ad groups they’re supposed to be in.

So really, all you need to do at the ad group level is cross-examine your main keywords. For example, if you have Dog Toys and Squeaky Dog Toys ad groups, then you should probably make “squeaky” a negative keyword in the Dog Toys ad group. That way, anybody who types in “squeaky dog toys” gets ad creative from the right ad group.

That’s really it for ad group level negatives. Campaign level negatives, in the other hand, are a bit trickier.

The best method for those is to take a good look at your keyword list and think long and hard about what kind of searchers you want to exclude. In some cases, these might be fairly obvious (but not always), like a realtor excluding “courses,” “picture,” and “jobs.” The people typing those in are probably not the people a real estate agent is looking for.

But in other cases, negative keywords aren’t quite so straightforward. Sometimes, you can think of them right up front, which saves you a little time and money. But sometimes, you don’t catch them until later.

The good news is, any Google search can turn up a few good lists of negative keywords, both broad and for specific sectors. Most companies also have their own negative keyword lists that you can use.

Do not, however, just copy and paste them in; you’ll run the high risk of blindly excluding people that you actually really want. Instead, you want to go through and pick and choose which ones would be best for your campaign. Use the lists as a baseline and don’t give up on brainstorming some of your own.

The last and most important step? Keep updating your negative keyword list. Don’t give up once you’ve set up the account. The best thing you can do is consistently run search query reports and pick through the things people type in to make sure that they’re all in harmony with what you want. Over time, you’ll find more and more irrelevant and non-converting keywords to add to your pile.

And that is the spirit of optimization.

Keywords, negative keywords

Engine Ready’s President and CEO, Jamie Smith, and Vice President Brian Lewis have been co-hosting a segment on Webmaster Radio called Best Search Strategies. A very concise name, don’t you think?

Along with co-host Jim Ledger, Jamie and Brian delve into topics like keyword monitoring, Google product extensions, the latest studies and observations from all of us on the Engine Ready team, our Conversion Critic software, and much more fun stuff. Their latest segment, in which they discuss competitive intelligence and keyword strategies, can be found here: Advanced Competitive Intelligence Tools.

Each (approximately) 30-minute show is aired monthly, and can be streamed online or downloaded as a podcast. They’ve been doing this since July of last year, so there are quite a few interesting shows in the backlog if you want to catch up. And their next broadcast is due any minute now, so make sure you stay tuned here: Best Search Strategies.

Conversion Critic, Engine Ready, Industry Events, Keywords, Webmaster Radio