How To: Excel at Excel for SEM Applications, Part 1

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 […]

Chat with SearchBot

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 this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Josh Dreller
Contributor
Josh Dreller has been a search marketer since 2003 with a focus on SEM technology. As a media technologist fluent in the use of leading industry systems, Josh stays abreast of cutting edge digital marketing and measurement tools to maximize the effect of digital media on business goals. He has a deep passion to monitor the constantly evolving intersection between marketing and technology. Josh is currently the Director of Content Marketing at Kenshoo.

Get the must-read newsletter for search marketers.