Sunday, July 29, 2012

Edit a CSV Without Changing Number Formats

This tutorial demonstrates how to prevent Excel from changing your csv data and points out some other problems that can occur when editing a csv file. You can jump to the video at the end or read on for a bit more detailed version.

Many users believe csv is just another type of Excel file, however this is not the case. Excel can open csv files and sets itself as the default editor when it is installed, but csv is nothing more than a text file with a different file extension. The most common use of the csv file format is in moving data (represented in the form of a table) from one database or program to another incompatible database or program. A csv cannot store formatting or data that is not text (such as images)

Editing a csv file in Excel can cause fields with numbers to change automatically into some undesired format. This can go unnoticed in large data sets, only to be discovered weeks or even months later in your eCommerce site or other destination database. At this point it can be difficult to track down the erroneous records and correct the information. The most common cause of surprise format changes is opening the csv directly either by double-clicking the file or using the File > Open dialog in Excel. Importing a csv into an blank Excel sheet will prevent the majority of undesired results.

The most common cause of surprise format changes is opening the csv directly either by double-clicking the file or using the File > Open dialog in Excel. Importing a csv into an blank Excel sheet will prevent the majority of undesired results. Here's how:
  1. Open a new Excel sheet, select the Data tab, then click 'From Text' in the Get External Data group.
  2. Browse to the CSV file and select 'Import'.
  3. In step 1 of the Import Wizard choose 'Delimited' as the original data type. Typically you don't need to make any other changes here but be sure your header row is actually on row 1 and change this number accordingly if it begins further down the sheet. Click 'Next'.
  4. In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.
  5. Step 3 is where you tell Excel not to change your formats. With the first column in the Data Preview selected, scroll across to the last column and select it while holding the SHIFT key (all columns should now be selected).
  6. Select 'Text' from the Column Data Format group and then 'Finish'. Click OK to insert the data into cell A1.
Note that if the source data contains forced line breaks (ALT + ENTER was used to create a new line of text) these will come in as a broken row when importing. If this happens you need to fix the source data or open the file in a text editor such as Notepad and remove the line breaks (they are represented in Notepad as a small square). If these are not fixed the import will most likely fail completely or partially when you attempt to get the csv data to it's destination.

Summary of Best Practices:
  • If you are the one generating the csv, retrieve only the data you need (get the least amount of columns and rows to accomplish the task).
  • Import the csv as text into Excel (don't open it directly)
  • If the editing task is extensive save it as an Excel file and re-save it as a csv when finished (this will retain column widths, formatting and formulas until you're done)
  • Fix spelling and data errors, line breaks, etc. in the source database if possible.

Saturday, June 30, 2012

Are PPC Shopping Comparison Sites Worthwhile?

I see forum and blog posts all the time professing that PPC (Pay Per Click) sites such as Amazon, Nextag, Shopzilla, etc. are not cost effective. Some would say that the overall conversion rates show the cost of clicks to be more than the profit realized from the sales. From my perspective, looking in from the outside, do I think they are worth it?

Absolutely. Utilizing PPC sites can drive a substantial amount of additional traffic to your eCommerce site and increase sales considerably. But that's not where the gold is...

The gold lies in what's not selling. Everyone who clicks a link to your store wants to spend money, including 'window shoppers'. So why didn't they buy what they clicked on? Many, including the comparison sites themselves would have you just remove the under-performing products from your feed and concentrate on those that are currently generating sales. That philosophy will get you nowhere except right where you are. By focusing on that one question: "Why didn't they buy what they clicked on?", you'll not only increase sales and improve the performance of your dollars spent on the PPC sites, you'll also improve the conversion of every visitor to that product (and your site as a whole) no matter where they come from.

Although it may be a good idea to drop some products from the feed temporarily if you have a lot of products getting multiple clicks with no return, once the cause is found you shouldn't have to remove anything. Here's some tips and self-assessment questions to get you on the path to spinning that straw into gold:
  • Don't focus on generating clicks, focus on generating qualified clicks - Bringing in traffic is never a bad thing, even if that traffic isn't interested in your products at the moment - as long as you can leave them with a positive mental sticky note. But how much are you willing to pay people looking for casual shoes to come into your boating store? It might be worth adding a couple 'vague' products to a PPC feed that will bring in some window shoppers, but certainly not every product.
  • Provide accurate names and descriptions - What shows up on a shopping comparison site varies widely from site to site. Some, like Bing only show the first 50 (I think) characters of the name and no description. Amazon goes to about 70 with no description (for Product Ads From External Websites). You have to do your best to fully describe the main attributes in the product name. If you sell gloves for instance and your product name is "Insulated Gloves", that might generate a lot of clicks with no return because it's just an investigative click. "15deg Gortex Insulated Gloves, Fleece Lined" on the other hand weeds out a lot of potential useless clicks. Adding brand to the product name can be a positive for brand loyalists but whether it should be at the beginning or end of the name is a bit of a Coke/Pepsi debate. I say put it at the end so if the name gets truncated we lose the brand but not the description.
  • Do you sell low priced items with a huge shipping fee attached? - Imagine walking into Best Bin to pick up a new mouse for your computer. The price tag says $27.95 but when it's finally your turn at the cash register it's $42.95! (they have to pay the cashier somehow right?) Would you still buy it or just walk away disgusted? Would you go back to Best Bin in the future? Finding a way around this, even if it means higher prices could leave your customers without the mental black eye. Find a way to offer free shipping and you'll obtain a double positive; the user has the feeling of getting something for free and doesn't suffer the emotional let-down at the check stand.
  • Do people leave your site with a mental note to remember you or is it forgotten the moment they click out? - Think about the eCommerce sites you've visited but not made a purchase and ask yourself why you remember them and why you didn't buy. Apply that to your own site and see if your site has the same memory benefits or suffers from the same symptoms that caused you to move on.
  • Can the visitor tell instantly what other products or services you offer? - Consider the unqualified clickers that you attract. They'll undoubtedly have a wide variety of interests and tastes. Assume they got to your site by clicking the "Insulated Gloves" link and landed on the Insulated Gloves product page (of course it's not what they're looking for). Is every other interest that your store has to offer easy to find or is there too much fluff to see anything without micro-analyzing the page? They were on a mission when they came to your site, have you managed to convince them to take a minor detour?
Remember the conversion numbers shown in the reports generated by the comparison sites don't tell the whole story. Some people bookmark the site and come back, some just remember the site and come back, others block the conversion tracking code through various means. These reports can however spot trends and be your best ally in finding the weaknesses of your site and turning movers into shakers.

Sunday, June 24, 2012

We've Moved!

Well, virtually anyway. Our web site has been split from our SharePoint project management site and received a facelift in the process. What started out as a 1-2 week project turned into a five month ordeal.

When Triage was started we had everything under one roof in our SharePoint site which functioned as our public company site, blog and client project site. We now have many channels that serve to both inform the public as well as serve our clients needs even better. These channels include:
  • Our Company Website - Completely redesigned and updated to reflect all we have to offer at Triage
  • This Blog - Moved to it's own sub-domain with far better ability to handle newer web technologies and media options. (SharePoint is really limited in it's blogging abilities)
  • Our YouTube Channel - Our media was getting a bit scattered. This gives us a single source for any instructional or commentary videos.
  • Our SharePoint Project Management Site - Upgraded to SharePoint 2010, this was a bit of a trade-off that we felt was really worth doing. The new version of SharePoint has much tighter integration with MS Office as well as a new modern ribbon tool bar. Unfortunately the old SPS application templates as well as many of the "Fabulous Forty" templates don't work in the new version.
  • Our eCommerce Site - Formerly just a private test site, our Big Commerce site is now public and serves double duty as a fully functional eCommerce site as well as a test bed.
"What, no Facebook?" you ask. Nope, but I'll get on that soap box in another post :)

Hope you like what we've done and remember: Stay Organized!