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

There is a fantastic formula in Excel which I find myself using quite often when creating ads for Geo-Targeted campaigns.  Its called: Substitute

SUBSTITUTE(text,old_text,new_text)

To demonstrate how it works, here is an example.  Keep in mind that your campaign structure will most likely be different than how it is outlined below.  You will need to take this strategy and contour it to fit your needs.  The concept is what is most important here.

*Columns F & G would contain the Display & Destination URL

To instantly create unique ads specific to the State and City follow these instructions:

1. Write the creative and use “XXXX” to signify the State and “YYYY” to signify the city.
2. Select Cells C1 through E10 >> CTRL + D
   1. This copies the creative down for each adgroup.
3. In Cell H1 write the following formula Result: “New York Cleaning Srvc.”
   1. =Substitute(C1,”XXXX”,A1)
   2. Result: “New York Cleaning Srvc.”
4. In Cell I1 write the following formula
   1. =D1
   2. Result: “Home, Office, or Vehicle.”
5. In Cell J1 write the following formula
   1. =Substitute(E1,”YYYY”,B1)
   2. Result: “Servicing the Manhattan area.”
6. Select Cells H1 through J10 >> CTRL + D
   1. This copies the formulas down for each adgroup.
7. Select Columns H,I,J >> Right Click >> Copy
8. Select Column C >> Right Click >> Paste Special >> Paste Values >> OK
   1. This converts the formulas you wrote to text.

Here is the result:

Positives: It may be hard to see the value in using this method to create ads for 10 adgroups, but when you have campaigns which work on Towns within Cities within States, you can have hundreds of Adgroups.  At that point you will see the value in creating ads in this method as this only takes a few minutes to complete.

Negatives: Once you create ads for all of your adgroups, you will find that you have exceeded the character limits in a few locations.  These will need to be manually adjusted to make them fit.  Try removing a period: “area.” To “area”, abbreviating a word: “North” to “N”, or rewrite the entire line of text.

ad creative, Adwords, Excel, geotargeting, local business center, Local Search, ppc advertising

Operation Camouflage: Redux

There is a little talked about feature in Google AdWords listed under the Tools section called IP Exclusion.  This feature allows you to block your ads on Google from being seen by a specific computer.  You are allowed a maximum of 20 IP’s per campaign.

Many SEM analysts focus more on Search Phrases, Demographics, Referring URLs, etc… But what about the visitors IP address?  Provided that the visitor has a Static IP, we can assume that each visit under the same IP address is a unique visitor.  One known exception would be any visitor which has AOL.com as their Host Domain.

If you are using a Website Analytics product which allows for custom reports to be exported (including the visitors IP address), you are in luck.  If you are only using Google Analytics, you are out of luck as they do not capture this information – although there is another way…

Export the largest data sample you can and open it in Excel.  Select the IP address column and perform a Pivot Table. (See Instructions)

·          Save the file to your Hard Drive (Any Name)

·          Select the IP Address Column

·          Select the <Insert> tab

·          Under the <Tables> ribbon you will see PivotTable

·          Select <OK>

·          A New Sheet will open and on the right hand side of the screen you will see “IP” with a check Box

·          Select the Check Box

·          Drag the “IP” and Checkbox while holding the left mouse button down into the Values Field directly below.

·          Now Select the 1^st value under “Count of IP” in the cells to the left

·          Right Click >> Sort >> Sort Largest to Smallest

What this action has done is show you the number of times a unique IP address has visited your site.  Typically the 1^st IP address is your own, but what about the others?  You can blindly add the top 20 IP address to your AdWords campaign immediately if you would like, but I highly suggest you investigate further… Who are these visitors?  Are they Organic visits or PPC? Are they returning customers who have bought in the past or are they a possible competitor who has clicked your ad 20 times off of your most expensive keyword?  There are many questions a data analyst should ask before ultimatly using this information in Google.  The better the filtering capabilities of your analytics, the greater the chance that you are isolating your competitors and avoid blocking your best customers.

There are many benefits to blocking your ad from competitors.  If you are both competing for the top position, you will be less inclined to increase your bid if you see yourself in the 1^st position (meanwhile the rest of the world sees you in the 2^nd).  You will also decrease the number of impressions which will ultimatly help your CTR.  And most obviously, you will limit the number of poor quality/fraudulent clicks.

Worst case scenario is that 20 people in the world may not see your ads but the best case is that you will save money on bad clicks, keep competition down, and increase your CTR. 

If you do not have an analytics package which allows you to see the IP address, there is a black-hat method of discovering your competitions IP address.  This method is by no means perfect, although I would venture to say that this stratagy has a 90% chance of being effective.  But you will need to ask me directly to find out.  This one is private ;)

Analytics, Excel, Google AdWords, IP Exclusion

Problem: A PPC account needs to be built ASAP and activated.
Method: You build your account in Excel or the AdWords editor as opposed to the AdWords Interface.
Difficulty: You have many ad groups and don’t have the time to custom write countless creatives.
Solution: Setup the architecture of your campaigns in such a way that you can easily leverage the power of Excel to do the dirty work. (more…)

ad creative, Adwords, Excel, Google, PPC