This post is long overdue. In the past 5 years, we’ve had hundreds of conversations with customers, partners and within our team on the subject of product attributes, options and variations. In fact, it’s so complex an issue that it’s the topic of a stand-alone training session for every new team member that joins Webgility!
I’ve been procrastinating writing this post for a while now but a curious email from a customer this morning about synching attributes finally got my fingers rolling on the keyboard.
Before you read further, I recommend grabbing a cup of your favorite beverage because this could take a little time. I wish it didn’t but then again, I wouldn’t be writing about it if it were simple.
- Product attributes – it’s a characteristic of a product like color, size, type, etc.
- Product options – these are the values associated with an attribute. So “blue” or “black” would be options for an attribute like color. Or “small”, “medium” could be options for an attribute like size.
- Variant – a product that has attributes and each option of that attribute is represented with a different identifier or SKU. So if you have a “small blue shirt” and a “large black shirt” that have different SKUs, each of these shirts is a variant.
- Parent item – typically the “shirt” in the examples above would be the parent item. You can’t really sell a shirt by itself and it doesn’t have option values or stock….it’s simply the parent item that helps structure the variants.
- Child item – each combination of option values for a parent item produces a child item. So, “small blue shirt” and “large black shirt” are child items of a Parent item “shirt” which has attributes called size and color.
- Stock or Inventory – the number of items you have available for a particular product
Sample variant product: Let’s define a sample variant product, its attributes and values:
- Item Name – sample shirt
- Item SKU – shirt
- Attributes – size and color
- Option values for size – small, medium & large
- Option values for color – blue, black & red
- Variant SKUs (assuming I have this type of a naming convention for SKUs)
- Inventory – 10 of each of these variants in stock
>> Therefore, we have a total of 9 variant products.
What’s the big deal?
The basic concept of product options and variants seems simple. The complexity arises not from semantics but from the lack of standardization of how these are handled by various eCommerce platforms and accounting systems. So let’s take a look at a few variations (pardon the pun) of how these are managed:
1) QuickBooks Financial software – Out of the box, QuickBooks does not support product attributes. You could use “Custom Fields” to define product attributes but they are just fields, they don’t provide a relationship between parent and child items or connect a set of variant products. QuickBooks Pro/Premier supports up to 5 custom fields while Enterprise edition supports up to 15. QuickBooks uses simple lists to represent a collection of items and it uses 1 field as the unique identifier for a product: Item Name/Number. It does not have separate fields for SKU and Name. You CAN establish a parent-child relationship between products by creating “sub-items” but there are no standard ways to organize items having multiple attributes. Consequently, customers can use a number of interesting ways to represent product variations, for example:
- Each variant as a separate item with a standardized name
- Each variant as a separate item using 2 custom fields with/without any naming convention
- A parent item with each variant as a sub-item
- A parent item with a tree of attributes. The sub-sub-items are the variants
>> What this means is: depending on how you’ve organized your catalog, you could have 9 products in QuickBooks, or 10, or even 13 to represent the same set of actual 9 variant shirts that you have available for sale.
2)QuickBooks Point of Sale – The basic edition does not support product attributes, however, PRO edition supports up to 2 attributes. For the example we’re using, the 2 standard POS attributes would work very well and as the screenshot below shows, you would have 9 variant products. There is no parent item here, they are all separate products but they appear to be “connected” together because they have the SAME name. POS uses the name field to group these variant items. If you change the Item Name for any of the variants, it will be detached from the group and become a stand-alone product.
Some shopping cart platforms and how they handle product variants (in alphabetical order)
1) Amazon – To properly represent variant products in Amazon, you must create the parent item, define attributes and then create each child item separately. You would also have to define the variation theme. So for our above scenario, you would end up with 10 products (1 parent and 9 child products). The end customer would purchase the child product but the parent item would be used to represent the group of variants. There are some restrictions around which categories and item-types allow for variations. Refer to https://sellercentral.amazon.com/gp/help/help.html/ref=ag_8841_cont_8831?ie=UTF8&itemID=8841&language=en_US.
2) BigCommerce – You begin here by creating variation sets (like “color and size”), defining the values in each set (like “blue”, “red”, “small”, etc.) and then assigning that variation set to the product. Once the variation set is assigned, you can select which combination you’d like to enable and the corresponding SKU, price, etc. For our scenario above, you would have 1 product in your BigCommerce catalog along with list of all child items under the “Variation details” screen. Refer to http://www.bigcommerce.com/userguide/Products.htm#Ran82558.
3) eBay – In eBay when creating a new item, you start by defining a variation and its characteristics. Each child item is then generated automatically. For our scenario above, you would end up with 9 variant products. Note that variations are only allowed in certain categories. Refer to http://pages.ebay.com/help/sell/listing-variations.html.
4) Magento – You begin by defining attributes and grouping them into attribute sets (let’s say I create an attribute set called apparel and define color and size as attributes that are part of that set). You then create individual child products followed by a single parent item which is referred to as a “Configurable Product” in Magento. You assign the attribute set to the configurable product and then add each variant child product to the configurable product. For our scenario above, you would end up with 10 products (1 configurable product and 9 simple products)
Refer to http://www.magentocommerce.com/knowledge-base/entry/tutorial-creating-a-configurable-product.
5) X-cart – Similar to BigCommerce, you define option groups, then create separate options and define the values of these options. Once the options are created you assign them to a product and manage the SKUs, pricing, etc. for each child item under the “Product Options” screen. For our scenario above, you would have 1 product in your X-cart catalog along with a list of child items defined under the “Product Options” screen. Refer to http://help.qtmsoft.com/index.php?title=X-Cart:Product_Options.
Breaking down the problem
As you can see from the scenarios above, depending on your version of QuickBooks, your shopping cart platform and how you choose to organize your catalog, there are a variety of ways to represent the same product. This means that in order for all variant items to be perfectly in-sync across all platforms, they must be organized identically OR you have to define some sort of rule about how the catalog is organized in each platform and then configure these rules for your catalog…both of which are difficult to standardize or enforce given the number of platforms, type of products and sellers.
I get it, but isn’t that the whole point of getting a software like eCC?
Yes sir! The fact that keeping catalogs in sync is such a complex challenge is one key reason why software like eCC exists. So we absolutely should be developing ways to automating everything… and we have. The challenge however is that (a) we haven’t automated it for all platforms YET, (b) there are some platforms where automation isn’t feasible YET, and (c) even if we were to build the automation, there are some platforms and use-cases where configuring eCC for automation would be so complex that it would defeat the purpose.
So let’s address what eCC currently does, and what it doesn’t.
What features does eCC offer?
For the purposes of simplicity, eCC provides these key features which are impacted by the presence of product variants. Visit this page for detailed comparison chart of features by shopping cart: http://www.webgility.com/ecc-shopping-cart-comparison-chart.php
- Feature 1: eCC can download orders and post them into QuickBooks or QuickBooks POS so you have proper accounting of revenues and inventory – This works for ALL platforms we support and it works even if you have variant products in the order.
- Feature 2: eCC can transfer products from QuickBooks or QuickBooks POS to your store, OR from your store to QuickBooks or QuickBooks POS– This feature is limited for some shopping carts. We’ll explore this in more detail below.
- Feature 3: eCC can synchronize the price and/or quantity of products between your store and QuickBooks OR store and QuickBooks POS – This feature is limited for some shopping carts. We’ll explore this in more detail below.
Let’s review each feature in more detail…
FEATURE 1 DETAILS FOR QUICKBOOKS: eCC can post ALL your orders into QuickBooks so your accounting is correct even for orders with variants
When you receive an order from your online store and you use QuickBooks, irrespective of your store platform, eCC will look for the unique Item SKU or the Item Name and post the order into QuickBooks. As long as you have setup your shopping cart to have a unique SKU or Item Name for the variant item and you have created a unique matching item in QuickBooks, eCC will post the order properly, irrespective of how you’ve organized your catalog. For example, if we receive an order for 2 of shirt-blue-small, as long as you have a product in QuickBooks with the Item Name/Number of “shirt-blue-small”, eCC will properly match up and record the order.
If you haven’t created a matching product in QuickBooks, eCC also provides the option to map it manually to an alternative product or to automatically create the item in QuickBooks at the time of posting it. So whether you automatically match, manually map or automatically create the item, eCC ensures that the revenues are properly accounted for in QuickBooks and the inventory of the corresponding item is deducted when it is sold.
FEATURE 1 DETAILS FOR POS: eCC can post ALL your orders into QuickBooks Point of Sale so your sales tracking is correct even for orders with variants
When you receive an order from your online store and you use QuickBooks POS, irrespective of your store platform, eCC will look for the unique Item SKU or the Item Name and post the order into POS. As long as you have setup your shopping cart to have a unique SKU or Item Name for the variant item and you have created a unique matching item in POS, eCC will post the order properly, irrespective of how you’ve organized your catalog. eCC can use the Item # field, Item Name field, ALU, or UPC field to uniquely identify a product in POS. For example, if we receive an order for 2 of shirt-blue-small, as long as you have a product in POS with shirt-blue-small in Item Name or ALU field, eCC will properly match up and record the order.
If you haven’t created a matching product in QuickBooks POS, eCC also provides the option to map it manually to an alternative product or to automatically create the item in POS at the time of posting it. So whether you automatically match, manually map or automatically create the item, eCC ensures that the revenues are properly accounted for in POS and the inventory of the corresponding item is deducted when it is sold.
FEATURE 2 DETAILS: Transferring products
This paragraph is perhaps the real beginning for this post because it dives into the details on transferring products using the eCC Product module. Unfortunately, all the above information was essential to establishing an understanding of how the catalog and the platform impact the functionality of eCC.
Let’s take a look at a couple of scenarios where eCC works like a charm:
Scenario 2A: You have a simple product in Amazon and want to transfer it to QuickBooks or QuickBooks POS – eCC pulls in the item details from Amazon, you provide some additional information required to create the product in QuickBooks and you’re all set with a couple of mouse-clicks. You can also do this in bulk.
Scenario 2C: You have a variant item in QuickBooks and want to transfer it to your X-cart store – since there are many ways to organize the catalog in QuickBooks, you first have to configure eCC to specify how you have organized your products and based on the rules, eCC will figure out which item is a parent and which is a child item. In the example above, eCC will figure out that the first part of the item name is “shirt”, the separator is a dash “-“, the second part is the size attribute and the 3rd party is the color attribute. It will then create the proper item based on the formula you’ve specified. Here is a screenshot of what the configuration screen looks like:
Let’s take a look at a couple of scenarios where eCC doesn’t work quite seamlessly YET:
Scenario 2D: You want to transfer products from QuickBooks to your Amazon store – this feature is not currently supported. Creating new products in Amazon requires a strict product schema that varies by category. Amazon requires several fields of data that are not always available within QuickBooks such as Amazon Item Type, Item Dimensions, Package dimensions, weight, etc. Since the data is not available readily within QuickBooks, we’ve chosen not to implement this feature yet.
Scenario 2E: You have variant products in QuickBooks and you want to transfer them to your Magento Store – eCC can transfer the simple products from QuickBooks to Magento, however, it does not currently create the configurable parent item automatically. Also, you have to specify the values of the attributes manually after the simple products are created. This is an area of high priority for us and we’ll enhancing it to support transfer of attribute values and automatically creating Configurable parent item in the next couple months.
Scenario 2F: You have variant products in your store and want to transfer all of them into QuickBooks – this feature is not currently available in eCC for all carts. As described in various sections above, variants can be represented in a number of ways so automating variant creation from the store to QuickBooks would require standardization and/or defining an extensive schema. We’ve implemented this feature for a limited set of carts. I’d recommend getting some hands-on experience with eCC to see how this feature works for the limited carts and visiting the comparison page to see which carts are supported.
FEATURE 3 DETAILS: Synchronizing product price and quantity
Once your catalog is created in your online store and QuickBooks or POS, you have to maintain the price and quantity between these platforms. eCC can automatically sync up the price and quantity of simple products for most shopping carts. Refer to our comparison chart for details. However, in the case of variant items, keeping things in sync can get really complex. Let’s take a look at some scenarios:
Scenario 3A: You want to sync the price and quantity of your products in Amazon and QuickBooks – this is easy. eCC matches the items up, and can update the price and quantity in QuickBooks or in your store depending on which is the master catalog. The key here is that Amazon and QuickBooks have separate products to represent each variant, so as long as they match up, synching their price and quantity is easy.
Scenario 3B: You want to sync the price and quantity of your products in eBay and QuickBooks – this is similarly easy but there is a caveat: eBay does not allow you to specify the price and quantity of Auction items, so eCC only supports synching of values for Fixed Price items.
Scenario 3C: You want to sync the price and quantity of a variant item in Magento and QuickBooks – in the case of Magento, each variant item is created separately so as long as the items match up in QuickBooks, eCC is able to sync them up.
Scenario 3D: You want to sync the price and quantity of a variant item in BigCommerce and QuickBooks – this is where things get really interesting. Let’s look at price synching first. In BigCommerce, the price of a variant can be FIXED like a simple product OR it can be RELATIVE. If the price of a variant is fixed, then the synching is easy. However, if the price of the variant is relative, it means that eCC must figure out the relative difference in price between the parent and the child item and then transfer that value into the variant price. Calculating this relative difference in price means that your QuickBooks catalog has to be organized very systematically with parent child relationships. We’re back to the same issue: this feature could work well if your QuickBooks catalog is well organized, however, this is rarely the case. To build a fool-proof sync of variants that would work across the board for ALL customers, we’d have to force customers to organize their catalogs precisely or ask them to define a complex set of matching rules so eCC can work in the FIXED price and the RELATIVE price scenario.
Now let’s look at the quantity synching. With BigCommerce, you can configure whether to manage inventory at the parent item level or manage the inventory by each variation. If you choose to manage the quantity by variation, then you specify the fixed quantity of the variant item so in this case, synching is simpler and feasible.
>>As you can see from the scenarios above, synching the product price and quantity is trivial if items are organized and can be identified with a unique value and have fixed values for price and quantity. However, as you get into more complex catalogs with relative pricing of variants or un-standardized naming schema, eCC sync feature has some limitations.
Additional eCC info: How eCC handle posting orders with simple items having option values
Let’s define a sample product with options but no variants, i.e., a product that has attributes but you don’t really care to track the inventory of each variant.
- Item Name – Sample Pen
- SKU – pen
- Attributes – color, engraving
- Option values for color – blue, black
- Engraving – text field with whatever value you would like to put
So we have 1 product which is available in blue or black color, but we don’t need to track the inventory of these items separately. In this case, your order will look something like this:
eCC will grab the product options and post them into the description field of the transaction:
Again, unique item identifier so everything lines up and the description field in QuickBooks can be used to fill option values. Accounting is in sync. Similarly, the item can be transferred from QuickBooks to store or vice versa, you’ll just have to specify option values manually for the item in your online store. Price and quantity sync also works as long as the product options don’t alter the price of the item. If the price varies by option, you would end up with scenario 3D above which has limitations.
While this post provides a lot of details around the complexity of product variants and the features of eCC, there is no denying that this is a problem that must be solved. The goal of this post is to help customers understand where we are, what eCC does and things we’re working to enhance. I also hope this post discredits any blanket notion that eCC doesn’t work with variants because as we can see above, it works for a number of use-cases. There are areas such as transferring of variant products and price synching which clearly need some development effort. My team and I are committed to growing the functionality of eCC in this and all other areas which bring value to our customers. Thanks for reading and stay tuned for upcoming eCC releases and more posts.
Founder/CEO of Webgility