Ever since Microsoft Excel beat Lotus 1-2-3′s butt to become the world’s preferred spreadsheet solution, it has never looked back. It’s hard for any search marketer to think of a world without Excel. I tell rookies new to the industry to move themselves a tiny bed into cell A1 because they’re going to be practically living in Excel for the rest of their careers. Seriously though, search marketers who can master the ins and outs of this “simple on the outside, sophisticated on the inside” tool, will certainly have a leg up on their peers.

I would hope that most of you have been exposed to a majority of the following Excel tips for SEM pros, so this list is mainly for the more junior members of your team. However, I’m hoping there will be a couple nuggets included for even the most experienced search engine marketers.

NOTE – all tips contain MS Excel 2007 directions, however, most of these features are still available in earlier versions.

Conditional Format(ting). MS Excel definition – A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.

Conditional Formatting is your best weapon against the paralyzing effects of having too much data to analyze. Basically, you can highlight individual cells based on criteria you set. One of the main ways we use Excel in-house is for optimization when looking through keyword performance reports. If a keyword is, say, over our CPA goal by 150%, the keyword’s cell automatically gets filled in with red, alerting us to that issue. Conversely, if a keyword is doing well, maybe 75% of the CPA goal desired, it gets highlighted in green to let us know there’s an opportunity there. conformat

As you can see in the screen shot above, you are not just limited to filling in cells with colors. There are data bars, color sets, and icons that can help you visually judge in an instant how a particular metric is performing. Basically, any formatting options you have in excel from font, borders to shading, or other options, can be utilized.

HOW TO: In the HOME ribbon, click the CONDITIONAL FORMATTING button to open up a drop down menu of various canned rule sets. If the basic rules aren’t cutting it, click the NEW RULE link to build a custom one for your needs.

Concatenation. MS Excel definition – The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items.

concatenate

Concatenation is handy in the event that you have text in multiple fields,  and want to join the text fields together. The most common use we have for the concatenation function is when we’re generating Yahoo and MSN bulksheets from Google ones. The main difference there is the ad text description lines 1 and 2. In Google, they appear in two different cells. In Yahoo/MSN, they’re combined into one. By using concatenation, you can merge the two description lines into one cell. Another great use of the function is in building keyword lists. Many times, you’ll have a few core keywords that you just want to add modifiers to and concatenation can help you do this task very quickly.

HOW TO: On the FORMULAS ribbon, choose TEXT -> CONCATENATE. The pop-up will walk you through the process of choosing which cells you want to combine. You can also just type in =CONCATENATE() and input the cells, delimited by commas, in the parenthesis. If you want to use the new combination, make sure to COPY and PASTE VALUES so you get the actual combination, not the formula.

TIP: You can also add in your own text by using quotation marks to surround the text and using them in place of cell references. For example, in the screenshot above, if the formula had been changed from =CONCATENATE(A1,B1) to =CONCATENATE(A1,” “,B1), a space would be added between the first and last names of the joined pair.

Length formula. MS Excel definition – Returns the number of characters in a text string

I highlighted this fairly simple formula in a post last year, and I thought it would be perfect to revisit for this column. Just type in =len() and input the cell you want the formula to count and it will let you know how many characters (including spaces between words) are in that cell. The most common task we use this for is to help us adhere to the character limit guidelines of the engines. length

HOW TO: For this example, I’ll use the standard Google format of 35 characters for each description line. First setup the columns in Excel going horizontally (ie. A = headlines, Be-description line I, etc). Then, add length columns in between each and use the excel “length formula” to count the characters in the first headline column. The length formula is: =len(cell), with cell being the cell for which you want counted.

Now you will see the character count (including spares) for your first headline. The next step is to apply conditioned formatting to this cell which will highlight it if you happen to go over the character limit. From there, it’s fairly simple to see which lines you have to fix.

In my next post, I’ll cover macros, some cool printing tricks, and how to select the visible area, one of my favorite excel tips to share.

My call was answered

In my last post, I lamented the fact that the current paid search account structure of Campaigns and Ad Groups was very limiting to paid search marketers. I shared some of personal woes with this issue and my workaround of using secondary data tables to enhance my keyword performance reports. After the post, I was contacted by Ryan Reisert from eSearchVision who feels his tool solves this dilemma.

With their “catalog system”, users can create up to a dozen classifications and apply to any keyword or group of keywords. Then, from anywhere in the system, users can pivot, optimize, set rules, report, etc by filtering these classifiers. See below some screenshots on how this looks in their system:

The classification system: esearch1

Inside the tool and filtering by catalog: esearch2

It’s a pretty nifty tool. I like being able to sift through my “branded keywords” even if they’re all in different campaigns and ad groups. Although eSearchVision doesn’t completely fix the system, it does take account structure to the next level and lets us transcend the current boundaries.

Overwhelmed yet? No? Then you must be one of those data junkies who loves to live in Excel. Most search marketers do.

Stay tuned for Part II of this article, featuring more advanced tips on how to optimize your Excel knowledge for the purposes of search engine marketing.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: SEM | How To: PPC | Search Marketing Toolbox

Sponsored


About The Author: is the director of marketing research for Kenshoo, the leading provider of bid management software. You can follow him on Twitter at @mediatechguy.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • http://www.twitter.com/ogletree ogletree

    You mention the “catalog system” but don’t say how to get it.

  • Josh Dreller

    ogletree… the catalog system is part of eSearchVision’s platform. You should contact them for a demo and pricing

  • http://www.redmudmedia.com Red_Mud_Rookie

    Thank you Josh…. this has been very useful. My life is going to become easier for this as I dive back into paid search campaign management. One thing I am yet to figure out is how you hyperlink a list of URLS in one go… ideas welcome.

  • Leafgreen

    Josh, this is great if I want to build my own custom spreadsheets for my campaigns. But that seems like everyone is reinventing the wheel. Are there any existing Excel templates for PPC campaign and/or keyword management? Free or for fee?

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide