In Search of the Perfect QuickBooks Shopping Cart Integration
In my previous blog article Ecommerce, Shopping Carts and QuickBooks I talked about how to choose a shopping cart. Now let’s take a deeper dive into some of the areas that should be considered before choosing a shopping cart or a software program that integrates a shopping cart with a back end accounting system. As part of the Sleeter Group concept of chunkification , ecommerce is one of the major business components (chunks) of a business. Making each component fit together is part of the concept of Lego Mastery and provides great opportunities for helping your client put all the pieces together.
Based on having helping dozens of clients with their ecommerce businesses, I have come to the conclusion that there is no perfect solution. Some shopping carts have their own QuickBooks integration and some use a “middleware” product to accomplish it. Each has its own advantages and some come close to a complete solution. In my opinion, there is no perfect complete solution for ecommerce. I will attempt to define the components of an ideal shopping cart integration and see if there are any software vendors that will still be standing after we get through the list. Disclaimer: At the end of this, you may have more questions than when you started.
Sync with Back End
How does the sync work with the General Ledger, and which GL products are supported? What methodology is being used? There are still some software packages out there that claim to integrate with QuickBooks that are nothing more than an IIF file import. Make sure the QB “integration” is not using this method unless you enjoy extra unnecessary work. For those that don’t know what IIF is, it is a very rudimentary way of importing data into QB that requires manual intervention – see this article on IIF for more details.
Is it a live import or does it run on a schedule? Can you change the update frequency? How reliable is the Sync engine it uses to communicate between the shopping cart database and QuickBooks, or other back end systems? Does it use the QuickBooks SDK (Software Development Kit) or the Intuit Web Connector? The Web Connector has been plagued with issues and is not widely supported by Intuit.
How does it deal with transaction handling? Does it use a SQL type database to keep a history/transaction log? Can it handle more than one user at a time? How does it deal with errors in data (products that don’t exist in QB, invalid sales tax codes, etc.)? Can you re-run transactions if the data issues have been corrected?
QuickBooks Transactions
Does it enter sales receipts and/or invoices or sales orders? Can you specify which transaction types are entered for certain transactions? For example, if the web store allows for a payment type of “Purchase Order,” will it create an Invoice? Similarly, if the order is a completed sale including a credit card or PayPal payment, can you specify that it should enter a Sales Receipt in QuickBooks? Does it handle credit memos and refunds or order cancellations? How does it handle payments? Will it import merchant transactions and coordinate the batching of daily sales with Intuit Merchant Services or authorize.net?
Sales Tax
This issue, in my opinion, will define which shopping carts will be around 5 years from now. As state governments decide how to get their hands on the interstate sales tax revenue they are losing out on currently, software systems that deal with sales tax will require a major overhaul to stay compliant. Some shopping carts now have the ability to use a sales tax compliance partner such as Avalara (see the list at http://www.avalara.com/products/integrations). Even with this, we are still faced with bringing that data into QuickBooks. If you don’t subscribe to the Avalara sales tax product for QuickBooks, then the shopping cart data will need to come into QuickBooks with the correct sales tax items and codes to ensure the sales tax returns can be prepared from QuickBooks data. I have not yet seen an integration that gets this right. I would love to be proven wrong, but after researching many products, and trading notes among other Sleeter Group associates (including Doug Sleeter, who is passionate about this issue), there is no one system that takes the order and passes all of the sales tax information correctly into QuickBooks.
Other issues with sales tax: Does it bring the sales tax item in the body of the sales receipt/invoice or does it appear at the bottom of the sales form in QuickBooks? How does it assign sales tax code and sales tax items to a new customer record, created by a new user who purchases on the store?
QuickBooks Items
Can it handle sub items? Are the items in the shopping cart able to be mapped to an item that is sub-item (child) or will it only map to the Item itself (parent)? How does it deal with product variants (i.e. size, color)?
Customer: Job
Does it add new customers to QuickBooks? If so, how does it format the customer name? Can it be customized in how it creates the new customer record? For example, can I ask it to create a new customer with the customer name formatted with “Last, First, Company”, or “email, Zip, City”, or whatever I want? Does it handle orders from “Jobs” or just “Customers”? How does it avoid creating duplicate Customer records? Does it allow me to specify the fields it should use to match a new customer in the store with an existing customer in QuickBooks so it doesn’t add a new customer if it’s already there? Does it have a way to merge customer records? How flexible is the customer maintenance section of the shopping cart? Ideally, it should have something similar to Add/Edit Multiple List entries, similar to how QuickBooks Accountant Edition does.
API (Application Programming Interface)
Does the shopping cart have a read/write API for developers who want to create custom applications to pull and push data including customer lists, sales transactions, back order status, shipping information, package tracking information, etc.?
Challenge
As I stated at the beginning, this article will probably raise more questions than it answers, but I would like to use this as a jumping off point to come up with the ultimate end-to-end solution that I can recommend to the Sleeter Group to endorse as the ecommerce Awesome Add-On leading up to the Accounting Solutions Conference in October. Please use this forum to answer some of the questions raised or raise some questions of your own. Consultants, check with your clients to see what their biggest challenges are in this area. Software developers, let us know how you are addressing these issues. Please pass this article on to help us identify one or more solutions that we can recognize with the coveted Sleeter Group Awesome Add-on award!
Category: Jim's eCommerce Connection, QuickBooks Add-ons
About the Author (Author Profile)
Jim Savage has been a QuickBooks Pro Advisor since 1999. He has since signed on with the Intuit Solution Provider program, which specializes in marketing to the mid-market channel. In addition, he is an Advanced Certified Pro Advisor and his company, Savage and Associates, is an Advanced Certified Pro Advisor and certified in QuickBooks Enterprise and QuickBase in the ISP channel. Prior to becoming a Pro Advisor, Jim was an IT director for a major health insurance carrier and has been involved in large scale IT projects throughout his career. He is now specializing in EDI and E Commerce clients and integration with QuickBooks. Since he has referred to himself as "The QuickBooks Guy" since he started, his website is named appropriately, www.thequickbooksguy.com.Subscribe
If you enjoyed this article, subscribe to receive more just like it.
You’re absolutely right Jim, there isn’t a PERFECT end to end solution out there. I started Webgility to develop such a solution and 5 years and thousands of customers later, I believe our QuickBooks integration software eCC comes very close but our product roadmap isn’t getting any smaller! I think its primarily because we’re aiming for a moving target. Businesses are evolving, eCommerce platforms are evolving, and QuickBooks is evolving…not to mention that the APIs associated with all the platforms are constantly changing.
To address each of the key areas you’ve mentioned, we’ve developed a comprehensive yet simple wizard in our software to help customers record transactions in QB based on variety of business rules, we provide several options for handling taxes at a state level (county and city level taxes are still in the works), we’re able to publish and keep products and variations in sync, and provide formatting options for recording customers. And most importantly, we’re able to handle all of these features with over 30 of the most popular platforms in the market and we integrate with all versions of QuickBooks.
I look forward to learning more from the Sleeter community about their challenges and any other feedback they have on this blog post. In the mean time, we’ll continue working hard towards attaining perfection!
Thanks!
Parag
Webgility
We use webgility at our work – and we are not all that excited about it – and yet we realize we have nothing to compare it to. We have really had issues with their customer service..once you get them, they are great – but it takes forever to get them (unless you have a quick, easy question).
We did an upgrade to our website to add translation – and their software does not integrate with it…so, now we are stuck having to fork over more money…
We are shopping again for someone else because it seems we don’t use the right features to match up with them – and their slow customer response has left us scratching our heads…looking elsewhere.
Any recommendations re: products that interact with PayPal? I have a client who sells on eBay, then all financial results route through PayPal. We find it extremely complex as there are (in addition to regular, numerous individual sales transactions – only some of which include sales tax), multitudes of shipping charges, offsets to shipping charges, eBay fees, PayPal fees, customer refunds, disputes, purchases of other goods by the client, etc. We’ve conquered reconciling, but only after a lot of hair-pulling! I’d be grateful if anyone has any ideas…
Our software product eCC pulls transaction data from eBay including sales and expenses. PayPal fees are also included in the eBay expenses. You can get a free trial at http://www.webgility.com and we’d be happy to work with you to automate anything that you feel is missing in the process.
Cheers.
Parag
I have used a hosted solution called simpleport to import my paypal transactions for years. It works well and the customer support is good. Paypal has been an issue for me for some time since many of my clients do 100% of their business online. Hope this helps.
Parag,
Well stated, thanks for the input. I do look forward to hearing about future updates, especially as it pertains to sales tax.
Diane,
Reconciliation is one of the biggest pain points in the whole process. I do usually recommend using a Clearing Account to bring transactions into then transferring into the bank account. I documented the whole process for Amazon on the the Webgility support forum.
Thank you Jim and Parag,
I’ll definitely check out webgility on behalf of my client. And Jim, I’ll check out the Webgility support forum.
Hi Jim,
I author an eCommerce integration product, CartSpan, that has served the Peachtree accounting (now Sage 50 U.S.) market for approximately 2 years. CartSpan is now available for QuickBooks users as a competitive option to the Webgility eCC and Atandra T-Hub products.
CartSpan is a highly configurable integration that currently supports about 15 different popular eCommerce platforms, including integration with Amazon seller accounts at no additional cost.
Most of the requirements that you addressed are supported by CartSpan, including multiple options for managing Customer ID creation and import. CartSpan currently supports tax code import by ShipTo state and plans are underway for supporting a lower level of cross-reference by Zip Code. I have customers that currently use Avalara and will import cart-calculated taxes under that ID. As the tax debate evolves, I expect the business logic for importing these transaction will as well.
Best regards,
Scott Wheeler, Owner
Noverhead Software, LLC
513-708-1317
Scott,
That is good to know, if people are looking for a Peachtree add on, I will point them your way.
Jim
Hi Jim,
CartSpan also supports QuickBooks.
Thanks,
Scott
Hi Jim,
Here is a quick update to indicate that CartSpan now offers advanced support for sub-items in QuickBooks. A more extensive explanation regarding handling of this great QB feature can be found at Use CartSpan eCommerce integration for QuickBooks to leverage grouped product functionality in your shopping cart. services.
Thanks,
Scott
In addition to sub-items I use QuickBooks Premier Manufacturing because of its capability “to assemble” items from several other items I stock. Does anyone know of an ecommerce “integration” tool that works with the QuickBooks Manufacturing “build assemblies” capability?
Lynn, I wouldn’t expect an eCommerce solution to actually “build” assemblies. That isn’t the typical use of something like that. Without knowing what your particular situation is, if you are kitting things at the time an order is taken, I would think that you would rather work with Group items, instead of Assembly items?
Thanks. I agree that an ecommerce solution wouldn’t build assemblies, but they would need to recognize the items that are the result of assemblies. I have been told that some ecommerce solutions have trouble working with sub-items. I’ve also been told some have trouble working with assembled items. I’ve used assemblies for over 5 years and they have worked well for my business. My real question is what are the options integrate my business’s ecommerce sales with my quickbooks so my business can grow.
sub-items and assemblies are unrelated concepts. sub-items is just a nomenclature issue, a way of organizing names. And, group items are yet again a different issue (and some eCommerce systems might not work with group items).
I don’t know the details of your business so I can’t give a specific answer. If you are actually building assembled items, in a normal situation the eCommerce system wouldn’t be dealing with that. The eCommerce system will tell you how many items have been ordered, it is up to you to decide IF you can build the items, and WHEN to build the items, and then to build them so you can fulfill the order. But, then, my background is in a traditional manufacturing environment. In any case, the eCommerce system says “you have these orders”, then you use QB to do the planning and building. There are tools that work with QB to help with the planning side of things.