When you are running an ecommerce business, there is a lot that you need to track about your products. your inventory management system (if you have one) has all of the information in it, but how accessible is that information? Can you quickly get a report that shows you each sales channel a particular product is sold on?
Even if you can get this, I am sure it isn’t easy. With Airtable is is very easy.
The reason I like Airtable better than Smartsheet for this, is that with Smartsheet you will have one list. Here, as you can see from the image above (and in the video), there are multiple tables set up. This is where Airtable gets really powerful. I can track detailed information about different elements, and then link them (bring them together) in the Master Product List.
For example, my sales channels are listed in a separate table. This way I can track the login info, and other info about each sales channel, without cluttering up a single table.
Notice I also have a separate table for the suppliers. This will allow me to track their contact information. Also, from a bookkeeping standpoint, now I have a list of the payees whom we buy inventory from. That’s one less question I need to ask my clent!
As you can see, the key here is to track information in separate tables.
The general rule here is that if an item has to be repeated in a table, then it probably needs it’s own table. You will probably have ideas for more things to add. Add them! And let me know about it, because I love to learn how others have used what I have taught them. It helps me.
Here are the tables I want you to start with:
- Design Notes
- Master Product List
- Sales Channels
I do this in every “base” that I ever create in Airtable. It’s where I track ideas and notes – things I want to do, or follow up on. Links to resources that might help me and so on. Since I am developing this course with this base, I have a link to the files where I am developing this course. I also have a link to the template I am selling separately in my shopping cart for Amazon Import. I also have a note that my Sample data is set up.
This is just a list of the products and their master Sku. This is the Sku that is used in QBO. So when you add a new product, you add it here first, then you add it as a linked item in the Master Product List.
Master Product List
This is the list that links the products in the sales channels to the products in QuickBooks Online. You’ll need to set up your sales channels first, and presumably you’ve already got some products set up in the previous table. Then you can build this table. This one has the “meat.”
Initially you’ll set this up as a grid. Then we’ll create some groupings so that you can see the reports I was referring to earlier – (eg) grouping the list by product, so you can quickly see which sales channels a product is sold on.
The video will show you how to create all of this. It’s important in the case of the Master Product List, that you set up the same columns, in the same order. This will come up later when you want to use my Amazon Settlement Report Template. You will be exporting this to populate a table there, so the order matters.
Here are the columns, and they should be kept in this order, especially if you’re planning on using my Amazon Settlement Report Template later on.
- Product Name (Sales Channel)
- Sku (Sales Channel)
- Sales Channel
- Master Sku (QBO)
- Product Name (QBO)
- Product Name Length (QBO) (must be under 100)
- Sales Description
- MPN (Mfg Part No)
- Cost per unit
- Price per unit
- Product URL
- Attachments (you can include photos of the product here)
Once again you may want to add things, and please do!
These are the places where you sell your products online. They should include your website, Amazon, Ebay, Walmart, Groupon – any place where someone can buy your products from you. These are repeated on the master product list, so they get their own table. This also makes it easy to track additional information such as logins, and URLs for these channels.
This is simply a vendor list containing the names and contact information for the companies you buy your inventory from. You can also associate your products with the suppliers. Airtable gives you an option for a “linked” field (column) to allow multiple items. This is useful here, in case you buy the same product for more than one supplier. Now your buyer(s) can use this as an important and useful reference tool. Heck you can create another table for orders in here, just to track that (although you have QuickBooks Online for that).
As I said earlier, you will (hopefully) have more ideas about what can be added and tracked here. I say hopefully, because that means you’re engaged and really thinking about this, and that is always the best way to learn about something. Get engaged, and think about how you can use this to make your life easier.