At Optimum7, we perform a lot of custom programming work for a wide variety of eCommerce solutions. We have had the privilege to work with open source systems like Magento and packaged solutions like Shopify and Volusion. Our ability to custom build CMSs and develop functionality deemed “impossible” by many development companies – in some cases, even by the creators of the eCommerce platforms themselves – has earned us quite a reputation. We’ve even earned the title of Volusion Expert.
In other words, we know eCommerce and we know custom programming.
We have a creed when it comes to eCommerce stores: “Your store is only as good as your database structure and your data.”
Most prospective customers, and even long time clients, look perplexed and confused every time we say this to them.
Much like:
We explain that our ability to create the customization they want done on their site while staying within their budget is heavily contingent on their current database structure and data, so typically we either get a:
Or a….
The bottom line is that very few clients are aware that their eCommerce store’s database is just not good. At all.
We’ve put together this piece in honor of all eCommerce consultants, designers, developers, and marketing professionals who struggle to help their clients understand the importance of a clean, organized database – sometimes it just helps to vent. We also hope that this piece will inspire eCommerce storeowners to evaluate the structure, organization, and cleanliness of their databases and do some much-needed house keeping.
Special Characters
I don’t mean the good kind of special. I’m talking about the not-so-fun “all rights reserved” and “copyright” symbols, quotations marks and ampersands – just to name a few. While they might make sense visually and contextually, a product feed that doesn’t have these special characters predefined means your system won’t be able to process them. If that is the case, they will quickly become a NIGHTMARE for anyone who chooses to work on product feeds/databases in Excel and import them back into the system. This is why:
- Every system deals with “special characters” differently. In other words, a Magento installation might be okay with using quotation marks for a field in the feed, while Volusion will not. If you import data that contains these characters into Volusion, you (and your customers) are going to find a lot of weird, senseless symbols on the front end of your store.
- If you don’t remove special characters prior to a migration or integration project, the project may end up costing you more than you anticipated. Like I said before, not every system handles special characters the same way. So if you’re migrating from Volusion to Shopify or Magento, you will likely find yourself spending more time cleaning the data than you will on the actual migration. While a few PHP queries can solve the issue for a small or medium sized database that doesn’t contain a lot of these characters, a larger database (or an extremely messy small One may turn something that should take 20 hours into something that takes 60 or even 80 hours just to dig through and clean the data!
Special characters exist for a reason and, at times, are quite useful. Some are just plain fun: ♣♣♣♣. Despite this, it’s important that you remember that any time a store’s data needs to be exported from one system and imported to another, too many special characters in your database fields will most likely result in a difficult, even painful experience for the person or people who must work with it.
SKUs Beginning with ZERO
Okay, so this may not mean that your database necessarily sucks, but c’mon, a ZERO in front of the SKU? Really? You have a store, you HAVE to work with a spreadsheet program. We all know that Excel has this funny little way of getting rid of zeros at the beginning of a string. This means that product 0123 will become 123 when you open your export in Excel. While this may not seem like such an issue, it can actually cause you to reimport data for a product that doesn’t actually exist. It won’t take long for the person working with the database to notice that something is wrong. Discovering which product(s) is missing a zero can take quite a bit longer. And yes, you can make that column a text only field and copy/paste from text, but it’s irrelevant. Why would we want do all those extra steps? Who cares if you’re trying to match your UPC? Create another field for your UPCs! It’s just not efficient.
Duplicate SKUs
One duplicate SKU, shame on you. Two duplicate SKUs, hey, it happens. 125,343 duplicate SKUs?! Then, your database sucks and you should seriously consider firing whoever is “working” on maintaining your eCommerce data feeds. The truth is that there are a GREAT deal of contingencies when dealing with online store feeds. It’s not enough for the person or group in charge to understand the technology they’re using; they must also (more importantly) understand the product, the business model, and the structure of the store that the product feed is working from. Issues like the one mentioned above, where SKUs start with Zero, often result in duplicate SKU issues. For example:
- When you are working with a product table in Volusion, the point of reference is always the SKU. SO if you want to update product 0123, you export it into Excel, which turns 0123 turns to 123. You update your data upload it to Volusion. And what happens? Volusion creates a new product with SKU 123 and doesn’t touch 0123. Now you’ve got a duplicate SKU, which is a serious problem in Volusion because you now have different pages for the same product being purchased by your customers, your inventory count becomes a mess, and within a day or two your entire online business starts to look like a very large disorganized closet. Now imagine you did that with a few thousand SKUs. This is how a minor detail can turn into a major problem.
Complicated or No Parent/Child Product Relationships
Having a clean product feed with no special characters, no duplicate SKUs, and SKUs that do not start with zero is a fantastic start, but unless you are selling items that have absolutely no customization, a clean feed is not enough. Products with a lot of options tend to have conditions. If products have conditions, your feed and system needs to communicate in a very precise way so the end result (what the user sees) is simple, easy to use, and easy to buy. I know this is getting a little complicated here, but stay with me.
Let’s say you sell T-shirts. We’ll call this product AWESOME-T.
– AWESOME-T comes in:
o Black
- Small
- Large
- Extra Large
o Red
- Small
- Medium
o White
- Small
- Medium
- Large
- Extra Large
With just this set of options, you’ll need to make it easy for customers to play around with their options, which means drop down menus will need to change according to the size options each t-shirt color has. For example: If you pick black, only small, large, and extra large should appear in the dropdown. In contrast, if you pick red, only small and medium should be displayed. So how many SKU’s do you need to have in your store to display this product properly?
….
….
….
….
Well, there are 9 color/size combinations, so, duh, 9! Right?
NO, you actually need 10. Here is why:
– Your first SKU will be your parent SKU, AWESOME-T
– Every product variation SHOULD have an extension that follows a similar format as:
o AWESOME-T-BLCK-SML (Black/Small)
o AWESOME-T-BLCK-LRG (Black/Large)
o You get the point…
With that said, if your data feed doesn’t have this kind of obvious relationship (or at least fields that can be matched to figure out the relationship between these products), it becomes an incredibly bad nightmare for the person who must match these products with their options. This too, may increase the amount of time spent on the feed (and therefore the cost of the project) before anyone can actually work with it and do what it is you asked them to do.
Now…
Ladies and gentlemen,
If you really want to know whether your database has truly reached Indiana Pacers status (I’m from Miami, so basketball fans will get the joke J), you’ll need to pass one final test.
If everything we’ve already discussed are a very real sign that there is something wrong with your product feeds, this will just make many of us cry.
::DRUMROLL::
NO CSV EXPORT OPTION…NO WAY TO EXPORT DATA PERIOD
Not having a way to generate a simple CSV export, or any kind of spreadsheet friendly export is just about the WORST of the WORST when it comes to working with eCommerce databases. It’s like telling a runner, “Hey, you need to run a marathon,” while chopping their feet off and expecting them to run in record time. At this point there are just a few options:
- If there is some kind of XML feed available, you’re in luck. It can be fairly easy to turn an XML file into a table-friendly feed that is simpler to work with.
- You can work with MySQL database files and match them, hoping the data is clean. (We’re still free to dream, right?)
The biggest downfall of not having an innate import/export functionality in your system is the inability to look at your data and work with it in a “user friendly” (or I’m-not-a-programmer-please-let-me-do-this) way. If you have a system that doesn’t allow a basic CSV import/export functionality, please do yourself a favor and move on from it. Chances are that if it lacks this most basic functionality, it lacks even more basic functionalities that you will need to run an online store that keeps people buying.
Now you might just be feeling a little like:
Don’t sweat it. As Napoleon once put it: “Impossible is a word to be found only in the dictionary of fools.” At Optimum7, we’ve been successfully dealt with some of the most disorganized, faulty, broken, and complex databases out there. Our clients count on us to not only to tell them what they need to know, but to find solutions to these problems and ultimately bring their store to life.
So, if you’d like to consult with us regarding your database, are in doubt as to whether you should keep your current system or which one you should switch to, or would simply like a second pair of eyes with the expertise to check the work your current company is doing, don’t hesitate to contact us. We know your business is at stake during these kinds of transitions, which is why our clients’ bottom line always comes first.
