I was approached by one of my e-commerce clients who was spending 5 hours per week manually entering sales information into their accounting software. This was time that could definitely be spent elsewhere, though being accounting it was extremely important that the data was entered accurately.
The project required that the client's accounting software, QuickBooks Online, had sales receipts automatically created every time a sale was made on their WooCommerce-based website. While there are a handful of SAAS options on the market that handle this specific integration, prices start from around $40USD per month which can be expensive if your store is not pulling in hundreds of orders a month.
Project Requirements
As their website serves an international audience, the client had a variety of specific requirements. As such, the automated solution needed to:
- Deal with multiple currencies & exchange rates
- Account for multiple payment solutions & their fees
- Work with the WooCommerce Product Bundles plugin
- Notify the client if there were any errors when processing
I built a solution which would periodically check for new WooCommerce orders and pull the order info into QuickBooks Online as a sales receipt.
Multiple Currencies of the World
The WooCommerce store uses Aelia Currency Switcher to sell in multiple currencies such as AUD, EUR & USD, which needed to be reflected in the created sales receipts in QuickBooks Online. I was able to automate bringing in the exchange rate for every order accurately, which allowed for accurate reporting in the accounting software.
Working With Product Bundles
Many stores like to offer discounts to their users by bundling similar products together and offer a discount, ultimately increasing the average order value. In the client's case, they were using WooCommerce Product Bundles plugin, and the discount needed to be accounted on a product basis rather than an order basis.
For example, if a bundle contains 3 t-shirts which normally cost $20 each and there's a $10 discount on the bundle (making the total price $50), this $10 discount needed to be distributed equally across all items in the bundle. As a result, the price for each t-shirt would be entered in QuickBooks Online as $16.66.
This was able to be achieved by individually checking the prices of each product within the bundle, calculating a total discounted amount, then subtracting the average off each item. Using the example above, this would be a $3.33 discount off each t-shirt.
Accounting For Fees From Payment Processors
One thing that's inevitable when dealing with online payments is dealing with the fees of payment processors. In the client's case, creating expenses in QuickBooks Online for fees incurred by PayPal and Stripe needed to be automated at the same time sales were imported. This was done by querying each order's payment method and then, depending on the payment processor and currency, querying the processor to pull the fee and create the expense.
This allowed for accurate reporting of fees spent on each processor for each order rather than doing a batch amount at the end of the month.
Notifications For Unexpected Errors
When dealing with automation it's important to set some checks in place to ensure that the data you're transforming matches up with the source data. If this isn't done, it could mean bundles of incorrect data may get processed automatically, resulting in endless headaches trying to undo the damage.
To get around this, I created a check system to ensure that every sales receipt created in QuickBooks Online matched the order total, currency, products, and more, to the order in WooCommerce.
In the case where a mismatch occurred, an email was immediately sent to the client to notify them specifcally where it occured. This allowed the client to immediately fix the source of the issue, saving many headaches if it had slipped by unnoticed.
Conclusion
This has saved the client over 20 hours a month of manual work per month, and as the store grows, will save many more. Human error is also reduced as every line item is checked to ensure no mistakes are made. And, in the case of any mismatches, the client is notified to address it immediately.
This has freed up time for the client, and has allowed them to focus on other areas of the business while the accounting is taken care of in the background.