the blog of
Shawn Plep

Creating Better CSV Files With Excel

Excel is a great tool for a number of reasons. But when it’s used to create CSV files, it usually throws something at me that’s hard to parse.

I often need to use a script to parse CSVs (datafeeds, database exports, or just spreadsheets of data) and grab data, or reformat the CSV, but a problem I’ve run into many times is the fact the Excel exports to CSV and does not allow me to enclose the fields in quotes, double-quotes, or…anything. And it separate the fields by commas. And that’s just how it is. (No options!)

So, instead of doing regex acrobatics or parsing contortions, there’s a way to make Excel export in a much easier-to-parse format.

To do this, you must change a Windows XP setting. Click Start->Settings->Control Panel->Regional and Language Options. Next to the Language, click “Customize”. Next, chnage the “List Separator” (under the Numbers tab) to something very unique. I use a tilde (”~”) symbol, due to the fact that I rarely find that within the data I’m working with. But you can use something else such as a pipe symbol.

Then, export your data to CSV. Your fields will have a more unique separator which you can easily distinguish. If you want, you can easily do a search-and-replace of that symbol as well. It’s saved me a lot of time!

How to Use an Affiliate Datafeed (Effectively)

In the world of affiliate marketing, there are a whole lot of tools available. Merchants want you to sell their stuff for them. And even if you (as an affiliate) are unsuccessful, they still benefit due to the inbound links and citations you provide to them.

So, if you’re an affiliate, you need to understand how to properly use the tools provided to you so you can benefit from them as well (and actually rank, and sell stuff). The most important (and common) tool you can find is the datafeed.

A datafeed is usually a CSV file containing the products from a particular merchant. The fields consist of the product name, description, price, link, and maybe a few others. Merchants on affiliate networks such as Shareasale will typically have datafeeds.

Here’s how you should use a datafeed, with SEO in mind…

• Change the content. As you pull in a datafeed, you should always make some alterations. For one thing, I like to “clean up” the data – because I often find weird mistakes, strange abbreviations, or undesirable formatting. Secondly, I like to replace words and/or phrases with different ones. I do this across the board, just because it will make my data slightly different than what everyone else has.
• Add to the content. You can get as fancy as you want with this, but the fancier the better. At the very basic level, you can make what’s already there more “wordy” – just another variation on replacing words (as noted above). But the better option is to add totally new additional content. If you can scrape content and add a snippet of info to your product entry, your site will have the datafeed content plus the scraped additional content. One example of this (that I do) is a t-shirt site I own. The products all come from the merchant’s datafeed, but in addition to that data I write an additional few sentences or paragraphs about the shirt. If applicable, I embed a related YouTube video. Get creative and you can probably think of tons of ideas like this, for your datafeed.
• Re-format the content. Again this is basic, but can only help – especially when compounded with the other changes. If the datafeed uses bullet points, take out that formatting and use CSS or just dashes and linebreaks. If the description comes before the price, put the price after the title but before the description. If there is no “sale” price…add one! Put a strikethrough on it and put it in red. Pull out the first hundred characters of the description and make it into a short description, and re-order the sentences – and use that on your site’s landing page to link to the product. Get the idea?
• Re-do the images. For images, you should download them (either by actually downloading, or if not possible by caching). You should re-name them with the product name. You should re-create the thumbnails in a different size (even if only slightly different.
• Do not link to the merchant. (If you can help it.) Mod_redirect is your friend. I have been saying for a while now that learning htaccess is one of the most important things a web developer can learn, and this applies to aff. marketing as well. Maybe more. You can avoid linking to the merchant through various means, and if you can’t do it super-clean via htaccess directives you can do it through other means as well (with your favorite scripting language). The end result needs to simply be that all links and all assets either are being served from your site or they look like they are.

I probably don’t have to say it, but if you have scripting skills and can at least find your way around a server, you’re ahead of the game. To do any of the above manually will take a long time and become tedious quite quickly. I recommend learning PHP, becoming familiar with mod_rewrite, and learning how relational databases work. (A little regex can’t hurt either!)

There are other aspects to using datafeeds, but this list covers the big issues and will get you around most of the SEO issues that plague an affiliate. And surprisingly, most affiliates don’t even implement one of the changes on my list! So if you do what I suggest, you may end up in the top 5% of affiliates – thus giving you an advantage in your site’s optimization and avoiding  having exactly the same content as everyone else (including the merchant).

Datafeeds are a starting point, best used as a basis to build upon. To use them effectively, you must enact the fundamental rule of SEO which is to provide value (by adding it, in this case).

My Theory of Perfect Simplicity

Many “popular” things are not popular or widely-used because they’re better (in the sense that they “do” more or are superior in their design). They’re better in the sense that they’re accessible to the greatest number of people, or they do one thing extremely well. Popularity – lasting popularity – is a result of something having that “thing” I think of as “perfect simplicity”.

When I do just about anything in life, whether it’s for work or for pleasure, I relish opportunities when I can do something simply but well. There are many opportunities to do this in the field of programming. A program, well-written, can be a beautiful thing. Perhaps only other programmers can understand that statement, but I think the truth is comprehensible to just about anyone when they think of what a program is: it’s a set of directions for getting from one place to another.

If I can give those directions very clearly and accurately, the program will work as it should. If I can be succinct and pithy in my “wording”, the program will work quickly and run efficiently. Accomplishing those things is perfect simplicity. (Unfortunately, I am not usually able to do things that way in a typical work environment, but sometimes it happens.)

A great arena in which perfect simplicity can be seen is music. There are a few bands I never grow tired of listening to, but they’re not all “great” musicians. Steve Vai is great guitarist. Buckethead is a great guitarist. But those aren’t the musicians I listen to over and over again. However, I can throw in the Ramones and enjoy their music fairly consistently. Why is that? Part of the reason is that the songs are so simple, and so perfectly executed. The songs are almost all extremely catchy. Because of that, the Ramones are a best-selling band to this day. Technical musicians like Vai don’t sell many CDs, however, in comparison.

So what does this all mean, to you and I? It means we need to demonstrate perfect simplicity in what we do, if we hope to be successful. A way to do that is to specialize in one aspect of your interests, and become good (really good) at something. I personally enjoy a great deal of things: playing guitar, writing, gardening, building things, drawing, programming, playing drums, working out, etc. However, I can’t ever hope to do all or most of those things at an expert level. So I pick one thing (web programming) and do it as best I can.

We can all do that, and I think we’ll see greater success as we maintain focus on one thing in our lives. This perfect simplicity is what we gain from focus and practice, and it breeds success.

Thanks For Clearing Up that Metatags Thing, Google

I’ve thought this for some time (based on writings by some SEO guys I respect) but until now it’s been an educated guess that meta tags (specifically, the keyword tag) exhibit no influence upon Google’s ranking of a page. So I must say thanks, Google, for making it clear to all of us who care. Meta tags are not an all-important piece of SEO.

The headline of a recent Webmaster Central blog entry should say it all: “Google does not use the keywords meta tag in web ranking“. But if you want to know more, you can read or watch Matt Cutts elaborate further.

Now, another minor point: it might not be a total waste of time to at least fill in a few keywords. I say this because of the Bing webmaster blog taking a somewhat neutral position on meta keywords. It makes me think that perhaps Bing “pays attention” to the meta keywords, at best. But I don’t see it as any sort of actual ranking factor whatsoever.

Update: I found this excellent and balanced perspective on the meta tags issue by John Andrews. I recommend it highly.

My Thoughts on Google Caffeine

Edit: When this post was written, Caffeine wasn’t live (for my datacenter area, at least). As of this morning (9/21/09) I noticed that I am seeing Caffeine’s results for all my searches.

I was recently asked what my thoughts were on the latest Google update, known as “Caffeine”. More than a simple algorithm update, it seems Caffeine is actually an upgrade to what powers Google’s search. It would be analogous to placing a newer, better engine inside a car.

You can try Google, powered by Caffeine at http://www2.sandbox.google.com.

When I brew a cup of Community Dark Roast to get my caffeine fix, the caffeine usually gets me going in the morning and speeds things up. I don’t know if that’s why Google picked their name, but searches do seem to be faster. (I tested a few searches between Caffeine and “regular” Google and found searches were often twice as fast. Of course, we’re talking times like 0.52 seconds vs 0.21 seconds. But still.)

The number of results found is unpredictable – sometimes “old” Google has more than Caffeine, sometimes less. Why? I would expect there’s a difference in indexing what is supposed to be more relevant to the query, and I suppose that’s why the reported number of the results in the index differs. This shouldn’t actually affect my search experience too much, since I usually don’t delve into the SERPS much more than page 2. But it shows that there’s a significant change in how things work behind the scenes.

Another difference I noticed was the snippet was different on many results. I like the new ones more. For instance, for the search “square foot gardening” I noticed a few of the results had a date preceding them, while the Caffeine version only contains the text I care about. However, this could be a negative – specifically in the case of blog posts: including the post’s date in the snippet could actually be helpful to a searcher. So I have a mixed opinion on this aspect of Caffeine.

I wanted to see how much better (or worse) Caffeine handled current news. So I picked a few events and headlines from Drudge Report and tried various searches. Sometimes Caffeine was  better, sometimes it was about the same, and sometimes it was worse (showing older or less-relevant items). Overall, I don’t see enough of a difference to cite any real advantages or disadvantages between the two.

You wouldn’t believe how many random/weird/varied searches I attempted, just to see what Caffeine would spit out. One was “baptist church”. Both versions give Landover Baptist Church as one of the top results – and I don’t think that’s really the best result to serve someone performing that search (as much as I love the site and think it’s clever). Also (on that same search) “old” Google delivers some image and video results – while Caffeine does not. In my opinion, the video and image results are a good thing and add to the searcher’s experience.

With some other searches, I definitely saw the Caffeine results as being less-relevant and inferior in quality. But for the most part, this is a subjective judgment.

So, what do I think of Caffeine? My first impression is simply that it’s faster, but no more relevant. I’m sure we’ll have to wait until it’s officially put into use (assuming the engineers are still adjusting and tweaking it) before we can really see what it’s all about. But for now? I’m going to stick with searching via google.com, and will occasionally try Caffeine out of curiosity and to see if the results differ vastly when I’m concerned about the effect my SEO efforts may be having on a particular site.

If you want to easily compare the current incarnation of Google’s search engine with Caffeine, Compare Caffeine is your best bet

Use CSS to Show the Current Nav Point

Here’s a quick but useful CSS technique. Learn it, and love it – this technique will let you do with CSS what other people have to do with a scripting language.  (Remember: the less scripts you run on a page, the faster your site will load. Make the computer do less.)

First, let’s start with some example nav code:

<ul>
<li><a class="home" href="/">Home</a></li>
<li><a class="about" href="/about/">About us</a></li>
<li><a class="contact" href="/contact/">Contact us</a></li>
</ul>

The important thing to note is that each nav item has it’s own distinct class. After you’ve given each nav item a class, move on to your body tag.

For the body tag on each page, give it an id that matches the class name you gave that page in the nav. (So, the body tag for “About Us” would have an id of “about” and so on.)

Last, make a CSS rule to make the magic happen…

<code>#home .home, #about .about, #about .about, #contact .contact
 {
 text-decoration: underline;
 }

Obviously, you would make the CSS rule do whatever it is you actually need to happen – but hopefully you get the idea. When the nav class and the body id coincide for any item, that rule takes effect.

My 9 Best Coding Tips

Over the last few years, I’ve learned a lot about programming and coding sites. Some of this knowledge came from books, but the majority of the really good things I’ve learned came from simply “doing it” – trying things, experimenting, and just learning what works best and what doesn’t work. In other words, experience really is the best teacher.

So here are nine “tips” – short but helpful things that can make your life easier when you develop a web app, website, or script. I will list these in no particular order – hopefully at least on thing in this list will be of use.

Create a wireframe, sitemap, or content matrix before you ever start coding. Unless you’re hacking something out really quick – plan your programs. Seriously. I see many people just sit down and start coding, but in my opinion they waste far more time doing things that way. It’s far better to plan out your program first, work out the flow of events and logic, and then start coding. And you don’t need to use Omnigraffle or any tools to do this: I map out database structures, navigations, and wireframes on yellow legal pads. So plan your projects in the way that works for you best – but do it.

Use a good text editor or IDE. I personally prefer Crimson Editor. If you’re on a Mac, Textmate is supposed to be good. But I would not recommend trying to code in Dreamweaver as I’ve seen some people attempt. You really need to get into the code – and I recommend this for HTML, CSS, as well as scripting.

Group all “like” files. Have a directory for images, one for CSS, etc. Don’t throw it all in your main directory and expect to keep things in order. Grouping files that belong together will make maintenance much simpler.

Name all include files with a leading underscore. Someone wrote this on their blog (I don’t remember who) and it’s helped me keep things organized for some time. I often have a file such as “__header.php”, “__footer.php”, or “__nav.php” and I can always tell which files are includes for other “main” files or template files by merely glancing at them. Also, by using the underscore character to name them, they will appear at the top of my directory listing by default.

Indent your code with tabs, instead of spaces. It keeps the structure of your code consistent, and it takes less characters to make one tab than to make four spaces. I know it might seem insignificant, but I like to make my files as small as possible.

Use comments, “stubs” and pseudocode when developing – liberally. There’s no reason that you can’t delete/replace this stuff later if you don’t need it. But initially, I find it useful to “jump right in” and actually create the file(s) for my program and make myself notes where things are missing or where I haven’t yet implemented something.

Don’t use inline styles. This is a general rule that I’ve found should rarely be broken. Learn how to control all your pages with a stylesheet. You’ll see the beauty and simplicity of it if you’ve ever had to hunt through multiple pages for stray inline styles when you needed to make a change.

When you begin to get unproductive, go for a walk. I find that there are three activities where I can really think creatively and productively: mowing the lawn, taking a shower, and going for a walk. Seriously, many of my best ideas come to me in the shower! But taking a walk usually clears my head, as well. The essential ingredient here is the “zoning out” factor paired with no interruptions.

If you can’t think of a way to solve a problem, present the situation to a non-programmer. This might sound like it’s the wrong way to approach a coding problem, but it works very well. (Of course, you have to strip the problem down to its essentials and present it in non-technical terms.) But if you have someone you can bounce problems off of and they have no pre-conceived ideas about how you’re “supposed to do it”, you might be surprised at the insights you’ll get from them.

There you have it: nine very specific ideas to help programmers. Whether you code in Perl, Ruby, or PHP or if you’re making small web sites or huge web applications one of these ideas is sure to crop up in the near future. I hope you find at least one of these tips helpful when that time comes!

SEO Tool Under Development: SaturationChecker

I constantly have ideas, but I rarely have time. (Not an issue – if you don’t need to sleep. But I need my beauty rest each night.) Sometimes, I get a bit of bonus time when I can actually work on one of my ideas.

That recently happened with my idea for SaturationChecker. It’s a simple script, but what it accomplishes can save the SEO-minded site owner a great deal of trouble and help them improve their site.

Here’s how it works:

1. User submits the URL for the site they wish to check
2. SaturationChecker (SC) then attempts to find either  an XML sitemap, or an HTML sitemap file, or an RSS feed. (In that order.)
3. If a source for URLs is located, SC then checks each URL successively and writes it to the database. Each URL is recorded along with its status, and whether or not the URL was found in Google’s index.
4. The unindexed URLs are then available to the user, so they can be targeted for promotion or perhaps simply rotated to the top of the XML sitemap file.

It’s up to the user to find a way to get the unindexed URLs “found” by Google; what SC does is simply shows where the deficiencies lie in a site’s index saturation. (So, if a site had 100 pages, yet only 50 of them were in Google, that site would have only a 50% index saturation.)

This tool was developed for my own use, and as such it’s a bit rough around the edges. (It works for me.) I do wish to polish it and then make it available for others’ use at some point in the future.

Some additions I will be adding will be:

• The capability to track if a URL is added to the index, then dropped again. (This may indicate the URL was found, and added, but then dropped due to an on-page problem or simply a better page coming along from a competing site.)
• A percentage report, as given in the example above. Very simple to code, but I haven’t added it as of yet.
• Prettiness. The thing has no style, and if I release this for public consumption I want it to look nice!

Since I haven’t released this yet, consider this post as “pseudo-code” for you coders out there to make your own. It’s a fairly simple script – I began it one night in my hotel during this year’s SEOmoz seminar and had the basic functions, and finished the rest of it today. (And I happen to be a coding perfectionist.)

Additionally, I’m open to new ideas – so feel free to contact me if you have any suggestions and I’ll see what I can do.

Things I’ve Come to Believe

I have a few philosophies that I’ve either adopted (by learning from others) or that I came to believe due to things I’ve experienced in life. Here are a few.

- I don’t debate with people. If someone wishes to know something, they’ll ask me and I’ll tell them. It doesn’t offend me if they then choose to not accept what I tell them, or if they wish to research it further. However, if someone asks me something because they only wish to argue, they’re small-minded and foolish – and are merely wasting my time. When someone wastes my time in this manner, it does offend me.

- I must enjoy my work. A person’s life is actually quite a short event. That being the case, I intend to live as much of it as possible. Since I must make a living by working, it makes the most sense for me to do something I find challenging as well as enjoyable.

- When I say I will do something, I must follow through on my word. I’ve broken this a few times, and I can’t think of a time when I didn’t regret it. It’s always best to keep one’s word – and I aim to live this way.

- The things I do should help others, not hurt others. I believe our lives are meant to be lived this way. This is not a religious belief, but it should be a part of any good belief (religious or otherwise).

- I should do my best. When I have to work at any task, I simply should put forth my best effort. There are many reasons to do this, and no reason not to do this. Anything less is either lazy, irresponsible, or inconsiderate.

- My happiness is not more important than the happiness of others. While I do wish to be happy, and will make efforts to be as happy as I can be, I am not “more important” than other people and have no right to deny them happiness if my wishes conflict with the state of their lives.

- Humility is better than a proud attitude.

- Be kind to others. People are never won over by negative actions (anger, debate, war, etc), but often are won over with kindness.

- Learning is the most valuable use of spare time and should be done on a normal basis. I like to do mindless things from time to time (watch a movie, or play a video game for instance) but I try to always have an interesting book on hand as well, so I can keep growing mentally.

- I can be the best in my field (and should be). No matter what “the other guy’s” supposed ability, level of intelligence, personality type, or level of education is – it doesn’t stop me from being just as good if not better.

- Nothing is impossible. I mean this for good things as well as bad things. It’s simply a way to remember that anything can happen in life, sometimes when we never imagined it was coming our way.

How to Conceal Your WordPress Version

There may be reasons you don’t wish to disclose that you’re using WordPress, or you may simply wish to disguise which version you’re using. I hope your reasoning isn’t for “security”, because this isn’t truly effective as a security measure. But whatever your reason, here’s how to do it.

First, go to your /wp-content/themes folder, and open the folder for your current theme. There should be a file named “functions.php”. (If this file doesn’t exist, simply create the file.) Open this file, and add the following line to it:
remove_action(’wp_head’, ‘wp_generator’);

That directive tells WordPress to remove the action of displaying the generator (which shows your WordPress version). It’s that simple!

You can do a lot of neat things within the functions.php file, as well – including adding new functions and removing other actions. For instance, you can remove some of the other (possibly useless) header info that WordPress outputs by default:
remove_action(’wp_head’, ‘rsd_link’);
remove_action(’wp_head’, ‘wlwmanifest_link’);

Think of the functions.php file as a way to implement a certain amount of plugin-like functionality, without having to actually install a plugin. If you know how to code in PHP the possibilities are vast.