Welcome to Accounting for Ecommerce Businesses.

Section 1 is about the Master Sku Lists.

I give you two products you can use to track this in.

  • Airtable
  • Smartsheet

Click a lesson on the left to start taking the course…

Or use the table here to navigate the course in our expanded view:

Accounting for E-Commerce Businesses (Expanded Navigation)

When I took on my first Ecommerce client and started getting into the process of cleaning up his books, I had no idea what I was getting into. I was trying to match sales on the web with products in QuickBooks Online, and it was proving to be challenging.

Understanding how a database needs to work, I realized that there was no common link between QuickBooks Online and all of his sales channels. The Skus were different, the product names were different. In some cases there were duplicates of the same product with different Sku and descriptions.

This was for SEO, and it has made him hugely successful, so what was I going to tell him? That he can’t do it because it creates an accounting nightmare? That’s exactly NOT what the job of the accountant is. We are supposed to take what the client has created and become successful with, and figure out the way to track it all.

In order for a database to work, there has to be a “key” field. This is the link that shows up once in each table, so that whenever you need to, every version of the same thing, can be linked to a single source. In this case, the master product.

I set up a project in Smartsheet.

Dumped his entire product list from QBO in there and told him that for every instance of each product being sold on a sales chanel, I need a separate line item in Smartsheet. The QuickBooks Online Sku will be the same in each case. That is the “key” or the master Sku. Then we’ll need to list the sales channel, and that product’s Sku on that sales channel. If a product appears twice on a sales channel with two different Sku’s because there are different descriptions for SEO purposes, then it will appear twice in the Master Sku list, but the QuickBooks Online Sku will be the same in both cases.

This way, later on, when I download a sale with that item, I can easily look up, based on the Sales Channel Sku, which Sku in QuickBooks Online, that item goes with. Once I know that, I can of course look up the product name in QuickBooks Online.

With the above in place, we will easily be able to map new items from a sales channel to QuickBooks Online. We will also be able to import sales data very easily.

Developing this Master Sku list turned out to be the very key to building a system for running the ecommerce business with extreme efficiency.

In this section we’ll look at how I built this in Smartsheet, but then I will show you what I think is a better tool for this. Airtable.