Join our ecommerce accounting newsletter   

Shopify to QuickBooks integration

Getting Shopify Sales Detail into QuickBooks Online: Best Practices to Strike the Right Balance

It’s a given that in order to understand the business and to be able to make good business decisions, the business owner, CFO, or financial advisor needs data that is accurate, timely, and available to be leveraged in key reports.

But when dealing with disparate systems that are designed for one purpose, (like Shopify is for selling goods quickly and delightfully), but not for another (like accounting for those sales in a way that accountants and bookkeepers can leverage to inform good business decisions), it’s important to understand how each system can work together, to serve the needs of the business, and to strike the right balance between too much detail, and not enough.  

Shopify business owners often think they need every single sale to be transferred into the accounting system, so they can run sales reports there.  This is, for all but the lowest-volume businesses, a bad decision to make because sending thousands of transactions into QuickBooks Online (QBO) for example, will bloat the system quickly and force the business owner to upgrade to the highest level of QBO, and could even cause the business to exceed the limits allowed in terms of numbers of customers.   Also – too much detail can cause the forest to be lost for the trees.

It is far better to let each system do what it does best, and to bridge the gap via smart integration.

To make this clearer, an analysis of the specific JOB(S) that each system needs to do for the business, is the best place to start.

Shopify’s main “job” is to enable customers to buy products quickly and in a delightful way.  To do this, the system allows the business owner to create lightning fast checkouts for customers, and enables sales on all the important social platforms (think Facebook, Instagram, TikTok, etc) as well as facilitating in person and business website sales as well.  In the back-end, there are core components to choose from including the ability to create Shopping Carts and awesome Checkout experiences for customers, back-end Commerce functions like the ability to create product catalogs and even widgets that help enable complicated sales like cross-border selling, and subscription sales.  Sales reporting, marketing insights, security and compliance are also features that Shopify offers.

Contrast Shopify’s intense focus on enabling business owners to sell, with QuickBooks Online’s General Ledger’s (GL) main “job.”  The GL’s main job is to show a summary of the results of operations at any one time for the business.  An example is to show how much sales tax is owed, how much cash is in each bank account, how much revenue was made in any given time period, for each type of revenue-generating activity, and of course, how much profit is left over once all the expenses have been taken into account.  In other words, summary numbers that over time, show how the business is doing.  A properly-configured GL with the right data coming into it, can ALSO show summary sales trend data that is very helpful to surface insights and help run the business.

So why do so many people think that the only way to get what they need out of the GL, is to send every single sales transaction into the accounting system?  Asked another way – how much detail do they really need to transfer into the accounting system (aka the General Ledger), so they can get the reports they want, out of the accounting system?

Let’s break down what most people are really looking for, when they ask for Shopify to be “completely integrated” with their accounting system.   

Usually, they are looking for the following reports to be readily available so they can be tracked over time to identify trends and help uncover insights: 

  1. Sales by Product Type (category), or, by Item
  2. COGS by product type 
  3. Sales by time period
  4. Sales by selling channel
  5. Sales tax due 

The best way to get this into the accounting system is to make summary journal entries that take into account the detail needed so reports can be pulled easily. It’s even better if an automation tool is used to grab the data from Shopify and create and book the journal entries with no human intervention needed.  

In order to do this, Shopify needs to be set up in such a way that your Product Catalog is detailed enough to include a Product Type for each item you sell, as well as how much it costs you to purchase the item.  Ensuring both are tied to each item

Here are the things you’ll need to do in Shopify:

  1. Ensure each product is tied to the right Product Category in Shopify
  2. Ensure each product has a Cost Per Item entered in.  (This allows you to run margin analysis reporting and see how much you are actually making by Item, or by Category.)
  3. Ensure each product has a vendor entered in – this allows you to run reports by Vendor in Shopify.

Then be sure to choose an integration tool, like Bookkeep, that is configurable and powerful enough to bring the data in by Product Type, or by Item.

To see how easy this is to enable in Bookkeep, check out these articles: 

Once enabled, Bookkeep will automatically pull the sales data and categorize by either Product Type (Category) or by Item Name, within the Sales GL accounts.  A separate sub-account for each tracked Category of Item Name must be created in QBO (as example) for this to work seamlessly.  But once set up, it’s perfect because the two systems (Shopify and QBO) can do what they each do best, and the business benefits from having the right level of detail in each system.

Start a trial or book a demo.