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

In the quest to create a truly epic pay per click campaign, our intrepid search marketer often runs the gamut of keywords, from the obvious to the creative to the downright silly.

But no matter how stupid “paper click” might sound as a keyword, there’s at least one person out there who’s going to search that exact phrase, right?

Right?

Oftentimes, depending on how enthusiastic the search marketer might be, an account can accumulate hundreds of keywords that never get any impressions at all, leaving one to wonder: is it really worth it, keeping all these zero impression keywords?

Maybe.

Generally speaking, it’s a good idea to cull any non-performing keywords, especially since a large number of them make it more difficult to manage your account. For the most part, they’re really not adding anything of value, since their click through rate is technically at zero percent.

On the other hand, a CTR of zero percent doesn’t necessarily lower your Quality Score, at least according to our Google sources. While CTR plays a major role in determining a keyword’s Quality Score, it’s not the only factor, not by a long shot. The historical performance of the keyword across Google’s entire system, the past performance of your display URL, the overall performance of your account—all of these go into the equation that churns out your Quality Score.

So while it’s safe to assume that most zero impression keywords are clogging up your account, it’s the Quality Score that ultimately decides whether a keyword is worth keeping or not. If the Quality Score is less than five, or otherwise significantly lower than the rest, your best course of action is to delete the keyword.

And if the Quality Score is good? It definitely doesn’t hurt to let it hang around a little while longer. A high quality keyword is better than nothing, right?

Right.

Ad Quality, Adwords, Google, Google AdWords, negative keywords, PPC, Quality Score

Google calls them “€œnegative”€ keywords. Yahoo prefers to use the word “excluded”. I call them “€œfantastic”.

As an SEM manager, it is very frustrating to see click charges for keywords that are irrelevant to your client’€™s product or service. Although my first reaction is to blame the engine for taking too much liberty with the synonyms of my keywords, but the only way to block future unqualified traffic is to bulk up your negative keyword list. As a result of continually improving this list, the bounce rates have experienced a steady decline in a good majority of my accounts. (more…)

negative keywords