Jims eCommerce Connection
Print This Post

In Search of the Perfect QuickBooks Shopping Cart Integration

May 14, 2012 | By | 30 Replies More

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!

Tags: , , ,

Category: Ecommerce, Jim's eCommerce Connection, QuickBooks Add-ons, Technology/Trends

About the Author ()

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.

Comments (30)

Trackback URL | Comments RSS Feed

  1. 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

    • Joy says:

      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.

  2. Diane Gilson says:

    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…

    • Parag says:

      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

    • John says:

      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.

  3. Jim Savage says:

    Parag,

    Well stated, thanks for the input. I do look forward to hearing about future updates, especially as it pertains to sales tax.

  4. Jim Savage says:

    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.

  5. Diane Gilson says:

    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.

  6. 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

  7. 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

  8. Lynn says:

    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?

      • Lynn says:

        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.

    • Tim Gibson says:

      The Teapplix product claims to build assemblies in QuickBooks, but we have not used that product. We formerly used assemblies to represent quantity purchases of an item. Buy 5 at a time and get a discounted flat price of X. We converted to the use of QuickBooks item groups specifically because we had to build assemblies on each order, manually, after importing orders. We also wanted our sales and inventory information to be based on inventory items, not assembly items. Connectors that use a mapping table to convert ecommerce sku’s to QuickBooks sku’s seem to have the most flexibility.

  9. Ellen Lewis says:

    Does anyone have experience using webgility’s product with Quickbooks Point of Sale. I use QuickBooks Point of Sale in my retail brick and mortar stores and want to open an ecommerce site (Shopify), and I need a product that will sync my Quickbooks POS inventory with my online site to show what is actually in inventory. Thanks, Ellen

    • Tricia says:

      Ellen,

      I would recommend PDGsoft.com.. It fully integrates with QB POS. As far as I know, it is the only shopping cart solution with that capability. I’ve implemented it for a few clients, but have used it in depth with one client for 4 years now. Good Luck. Ask for Damien and tell him I recommended you. He is by far the most experienced tech support guy on the team.

  10. Hi Jim, for handling sales tax you mention “I have not yet seen an integration that gets this right.” What in your mind is the correct way to handle the sales tax?

    • Jim Savage says:

      The best way to handle this would be to have the tax calculated from the shopping cart itself using Avalara or something similar, then let the amount be passed into QB as is with the sales tax item and amount. QB should not be calculating tax in this scenario, it should just accept what is coming from the shopping cart.

      The challenge is getting the middleware (T-Hub, Webgility) to pass through the sales tax correctly into QB. So far, this has been a challenge, but I expect that may be changing soon. Stay tuned, check out my blog that just came out on the Marketplace Fairness Act.

      • Thanks Jim. I suspected the best way to do this was the pull the ‘actual’ tax amount from the source (e-commerce platform, PayPal, etc). I assume the issue for the middleware is technical then? Meaning they have a hard time applying the tax amount to the appropriate account in QB?

        I’ve seen some blogs that talk about adding the tax as a line item in the sales receipt, but not in the ‘tax’ location at the bottom where it normally belongs.

        • Jim Savage says:

          Steve,I know that Webgility and T-Hub have plans to be able to pass through the correct sales tax amounts and item codes. I cannot say when that will happen, but I look forward to that happening. To me, it doesn’t matter if it is a line item or at the bootom of the transaction as long as it is recorded correctly. Thanks for the comments…

    • Hi Steve,

      The CartSpan integration (competitor to eCC and T-Hub) for QuickBooks handles tax allocation perfectly; in as much as the information provided. What comes in from the cart can be cross-referenced according to state or zip code. This is imperfect if customers are using a tax-calc add-on like Avalara or TaxCloud to generate these values. In which case, my clients typically import these to a common tax id by the same name and settle-up later with these services that manage the remittances for them.

      If you are not overly complex and manage your tax calculations by state, CartSpan supports this cross-reference and will even allocate the appropriate amounts to any sub-authorities associated with the applicable tax code…to the penny.

  11. Tom says:

    I want to add e-commerce to my existing business. I have been looking for a hosted solution that calculates the sales tax, and sends the data to Quick Books Premier Manuf. & Wholesale. I have checked dozens of systems. Network Solutions, which purchased Monster Shopping Cart, and is now owned by Web.com, claims to do so, and offers a CMS (content management system) that looks manageable.

    Does anyone have experience with this system? Any comments?

    Does anyone have a better solution for a “hosted” environment for sales tax accountability and Quick Books Premier (inventory) integration.

    Am I asking for too much?

  12. Barclay says:

    I installed QuickBooks on the same computer as the company file, but I still can’t open the company file in multi-user mode.

Leave a Reply