Considerations when integrating QuickBooks, Peachtree, or Sage 50 with your shopping cart.

We have listed some overarching considerations that should be made when evaluating eCommerce/Accounting integrations. These topics/questions have been adapted from our response to the Sleeter Group blog by Jim Savage regarding In Search of the Perfect QuickBooks Shopping Cart Integration. The content has been expanded to include the Peachtree/Sage 50 accounting platforms and  the answers are in the context of how CartSpan addresses the issues.

Communication with the Accounting API

Q:  How does the import work with the General Ledger, and which G/L products are supported?

A:  CartSpan behaves in a manner just as you would manually enter an order or invoice. For those instances transactions in-which the GL accounts are not determined dynamically (i.e., taxes) there are specific configurations set within CartSpan. Prior to executing any imports, CartSpan will re-verify the existence of all configured GL accounts. This occurs as the business will sometimes delete or modify existing GL account structures and sometimes overlook the integration settings.

As for product types supported, CartSpan supports all types except the ‘Group’ product type, which essentially represents a bundled configuration. CartSpan does, however, support this concept of bundling, but does so from the shopping cart side during the import process. CartSpan will import all of the ‘children’ of the bundled ‘parent’ and properly allocate price in the event that a discounted fixed-price is detected.

Q:  What is the underlying technology that is being used? .

A:  CartSpan uses the QuickBooks API and the qbXML method of executing all import transactions. This method of communication with QB supersedes the older more labor-intensive methodology of using IIF file imports. For enhanced features representing competitive advantage, CartSpan utilizes the read-only version of the QODBC (Open Database Connectivity) tool offered by FlexQuarters. This allows CartSpan to perform very fast reads directly on the database for the purpose of supporting all import/export operations. In-short, there is no constant entering/exiting of each application to accomplish the work.

Q:  Does it operate in real-time or does it run on a predetermined schedule?

A:  Like most integration applications, CartSpan runs on a schedule for both the order imports and the item price/stock updates in the cart. I see this as a general architectural necessity as achieving ‘live’ performance would require technology on the accounting side that doesn’t exist (Triggers); and even if it did, it wouldn’t likely be offered by accounting system providers. With that said, CartSpan does employ some programming techniques that give the impression of near real-time price/stock updates in the cart. As for orders, most organizations seem to operate effectively with having their orders imported on intervals of 7-15 minutes.

Q:  Can the frequency at-which it imports orders or updates inventory be changed?

A:  Yes, one can change the frequency of the order imports, but there are some caveats associated with this decision. The largest consideration is the amount of ‘processing overhead’ associated with ‘calling’ the API of the cart you have chosen. Some cart APIs are lightweight and execute very quickly (<2 to 5+ seconds) and some are computationally intensive (up to 20 seconds) in getting data back. Also of consideration, is how hard-working the integration is. By this, I mean how much value-added pre-processing does the integration perform on your data before it actually performs the import? So, if you have a ‘hard-working’ integration that maximizes the imports and the back-end API is not ‘lightweight’, you probably don’t want to be calling it every 60 seconds.

Q:  How reliable is the synchronization process with accounting?

A:  This is a very good question that truly separates a mediocre integration from a good one. The CartSpan integration utilizes custom PHP scripts or Web Services to accomplishing the work; rather than using the standard API tools offered by most carts. The first benefit of this approach is that it allows us to harvest the exact data that we need to maximize the integration. The second, equally important benefit, is that CartSpan is not held ‘hostage’ to API constraint when there are supporting changes to be made. This translates to an ultra-high level of reliability.

Q:  How does it import transactions into the accounting system?

A:  With QuickBooks, Peachtree, or Sage 50 U.S., the SDK ‘shoulders the burden’ of managing transactional integrity. Thus, the only requirement is for CartSpan (or any other integrating system) is to craft a valid XML message during the import process. If there are any errors during the import process, the SDK will reject the import and provide a message to the integrating application.

Q:  Does it maintain transaction history or data-based logs?

A:  There is not much call for retaining a transaction history as this information is already contained within the accounting system. To differentiate between hand-entered and automated import, some users will utilize an order prefix or a default sales rep for all web orders. CartSpan does, however, offer an archiving feature for all import files processed.

Q:  Does it support multiple users?

A:  CartSpan is installed on a single machine. Generally, there is no need to run on multiple machines as an imported order is available for anyone to operate upon within the multi-user accounting system. CartSpan users typically identify a single high-availability machine within the office upon which to install. The programming complexity and tools required to enable multi-user capability would begin to push maintenance costs beyond what user are willing to pay.

Q:  How does it handle errors during the import process?

A:  In addition to the previous comments that address verification of the configuration, CartSpan offers an interface that gracefully handles errors in execution and their subsequent resolution.  CartSpan will record the error message and stage the offending CSV file in an exceptions directory. The user may then ‘view’ the specific messages associated with the file and correct them within the CartSpan interface. Many of the errors that present themselves in competitive offerings, such as customers not being present, are completely avoided as CartSpan automatically creates these new records to support the import. Additionally, CartSpan will send an email to a designated administrator, notifying he/she of the specific error encountered.

Q:  Is is possible to re-import orders after errors have occurred?

A:  Not only can a modified CSV file be re-run, CartSpan allows a retroactive date to be specified (if desired) to retrieve past orders. CartSpan will ignore any orders that have been imported previously.

Supported Accounting Transactions

Q: What options are available for importing my Web order?

A: CartSpan is configurable to import the Web orders as either an Order or an Invoice. If the customer is paying with a credit card, an optional ‘Receipt’ or ‘Payment’ can be received. based upon the payment methods selected. CartSpan is configurable to apply a Receipt (or Payment) based upon user-specified order status codes. For instance, if the customer wants to ‘recognize’ a PayPal payment method immediately (rather than 2-3 day conventional clearing), that is his/her choice.

Q: Can it automatically create my Items if they don’t already exist?

A: Yes. CartSpan can be configured to automatically create new items in accounting as a ‘Stock Item’ with the default configuration established within the accounting system.  This is an ‘optional’ capability. There will most likely be some additional information required as import has no way of knowing ultimately which G/L account codes you wish to apply to the new item. For this reason, CartSpan can be configured to send an email to the system administrator indicating the new items that were created.

Q: How does it handle payment references for the purpose of reconciling merchant accounts?

A: For carts that support the acquisition of pay-method detail, CartSpan can import the ‘payment method’ and transaction codes into appropriate reference fields.  A formatted date mnemonic can also be configured to support report grouping. Additionally, specific payment methods can be targeted to individual cash accounts for reconciliation.

Sales Tax Management

Q: Do the integration import sales tax appropriately?

A: CartSpan supports the cross-referencing of Ship-To ‘State’ or ‘Zip Code’ with tax codes configured in the accounting system. Any import, for which no cross-reference can be found, will default to a standard configured tax code. CartSpan supports the cross-referencing of ‘Group Tax’ items that reference multiple sub-tax-authorities. As an example, a user could have 50 tax codes with each representing a different state. Each of those tax codes could have associated ‘sub authorities’ that break-out that total amount. Hopefully, businesses won’t be overburdened beyond this level of requirement as governmental tax policies evolve in the future.

Q: Does the integration product import the sales tax as a line-item or into the designated field on the form?

A: For QuickBooks, CartSpan imports tax items into the body of the order or invoice. While aesthetically more attractive in the bottom of the sales form, there is, technically-speaking, a good reason as to why it’s best imported into the body.

The QuickBooks SDK was built under the assumption that tax would be calculated during the import based upon the items ordered and their associated taxable status’. This is suitable for an SDK application that is creating orders, but is exactly the opposite of what is required in the import of a web-order. With the import of the web-order, we start with the tax (handed to us from the cart) and must make that value reconcile on the sales form. If the user has perfectly configured the accounting system to match tax calculation in the cart, the values would work if conventional import mechanisms were used. But this is not likely to happen, often resulting in being at-least a penny off; enough to produce that annoying ‘Not Paid’ status. CartSpan, like other integrations, will ‘back-into’ the appropriate values to avoid this situation, and importing into the ‘body’ is the only way of guaranteeing transaction integrity.

For Peachtree and Sage 50, CartSpan imports tax directly into the tax field on the sales order given a higher level of control during the import process.

Q: How does it assign sales tax codes during the process of creating new customer records?

A: CartSpan currently utilizes the default sales tax codes defined for the creation new customer records. If the default information for new customers needs to be adjusted, CartSpan has messaging functionality to indicate to system administrators that a new customer record has been created.

Item-Handling and Cross-Referencing with the Accounting System

Q: Does it support bundle configuration and sub items?

A: Yes,  in the context of bundled products coming from the shopping cart. See the very first section regarding ‘G/L products supported’.

Q: How is the cross-referencing of Item Numbers or Item IDs achieved?

A: CartSpan item mapping is accomplished on the Shopping Cart level. All modern carts provide a free-form field on the item record that can be used to cross-reference the Item ID in accounting. Names for these fields are often ‘model’, ‘SKU’, ‘product number’, etc.  CartSpan ships with pre-defined references to these fields, but has powerful mapping capabilities to point to an alternate field if required..

Q: How does it handle product variants and configurable options coming from the cart?

A: Shopping Carts handle the issue of product options (variants) differently but CartSpan manages them in the same  basic fashion. CartSpan will concisely concatenate the options selected for configurable items and append them to the base description of the item. If the item description becomes too long, CartSpan provides logic for intelligently abbreviating the selected options so that the description fits cleanly on the order line.

Customer Management

Q: Does it add new customers to the accounting system automatically?

A: Yes. And not only does CartSpan automatically create new customer records, but you can opt to have CartSpan update the customer billing information as it changes in the cart.

Q: How does it handle creation of the Customer ID during new account creation?

A: Yes, CartSpan supports Customer ID creation in 4 ways: 1) Accept the ID assigned to the customer in the cart, 2) Utilize the customers phone number, 3) Create a dynamically formatted ID from customer billing or shipping information, or 4) Manually cross-reference the ID from the cart with legacy customer IDs (memorized). Additionally, CartSpan can be configured to an email to an Administrator indicating that new customer records have been created. This is useful when there is additional information that needs to be associated with new customers.

Q: How does it avoid creating duplicate Customer records?

A: This is one of the ‘stickiest’ issues when moving from ‘Bricks & Mortar’ to eCommerce is the handling of Customer IDs. Inherently, CartSpan knows if a customer already exists based upon the ID formatting mechanism selected. Problems generally arises when the user selects a method that does not guarantee uniqueness. For example, if creating Customer IDs dynamically from billing information using ‘Address1’ in the rule, the customer will be recognized as new if he/she moves across town. To guarantee uniqueness: 1) utilize a very detailed rule for dynamic creation, 2) Accept the ID from the cart, or 3) Manually cross-reference new customers.

Q: Can I preserve my legacy Customer IDs and match them with those codes coming from the store?

A: Yes, CartSpan has a very effective mechanism for assigning and remembering these associations. Furthermore, the process can behave in an automated fashion if there are no new customers. If there are new customers, CartSpan can send an email to an administrator indicating that there are new orders to import and customers to assign.

Configuration and Extensibility

Q: My business doesn’t treat the order fields in a conventional manner. Can I redirect information coming from the cart to alternate fields on the order?

A: Yes. Dynamic field mapping is one of the most attractive features of CartSpan. We have customers that do some unconventional things with dates and other information.

Q: Does the integration have a high level of control over the API on the shopping cart side?

A: Outside of fully-hosted systems, CartSpan writes it’s own API integrations; opting to not use those published by the eCommerce platform developers themselves. Because of this, we have been able to respond very quickly to changing user needs. In some cases, we have accommodated some very popular community add-ons within the context of the standard product. Meaning, if you use them, they are detected; if not, standard data applies.

Q: I have multiple stores. Can I import orders from all of them? And, will it cost me extra?

A: For those carts that support management of multiple stores within the same back-end database, CartSpan can be configured to retrieve orders from any (or all) store(s) into a single company in the accounting system. CartSpan has one customer running Magento with 46 individual stores setup for each field sales representative. Orders from all 46 stores are imported into a single company in the accounting system.

For those companies that have additional stores (not residing in the same database), we offer a work-around at a nominal expense that permits you to run against the same CartSpan license. This offering has the potential to save you a significant amount of investment.