Export Your Master Sku List Into Your Amazon Import Template

In this lesson I am going to show you how to get your master Sku list into my Amazon Settlement Report Template. I will show you how to do this of course both in Airtable and Smartsheet. The template is sold separately – Amazon Settlement Report Template.

It’s worth noting here, that the same template, with a little tweaking can be used for any sales channel, but in most cases, you will want to use Webgility to sync your sales. If you don’t want to spend the money on Webgility, then for the one time cost of my template and this course, you can import all of your sales this way.

This is not a one time process. When I have my Master Sku List setup, I give my client access so they can add products any time. Some do this often. Any time I am about to import an Amazon settlement report, I need to update my master sku list to the template. Otherwise the template will not be able to find any new products.

Here’s your workflow (once the initial setup is complete).

  • Client or you adds products to the master sku list.
  • An alert is set up to notify the appropriate party that new products are added.
  • The new products are added to QBO (I tried doing this with Zapier. You can’t add new products).
  • At each Amazon settlement date you update the master list to the template.
  • Prepare and run your Amazon Settlement Report Import.

The process of exporting from either product (Airtable or Smartsheet) is very easy. Then, if you kept things in the right order (see prior lessons), it is a simple copy and paste values into the template.

I probably don’t even need to do a video on this, but since I always do a video, I am doing a video on this.

From Airtable:

From Smartsheet:

Once you have your master list exported, you open the template to the Product List – Master tab, and paste values. Do not just paste, or you will ruin my beautiful formatting!

 

Once this is done you are ready to move on to the next step.

Section 2 – How to Use The Amazon Settlement Template

Amazon is probably the one place where every single ecommerce business owner has to have a presence. 

It’s simple supply and demand. There is a tremendous demand there. It’s the first place I go as a consumer whenever I need something. 

Accordingly I wanted to give you my process for how I handle accounting for Amazon sales.

Webgility isn’t 100% reliable, or you have to pay a lot of money to have a plan that allows you to distinguish orders that shipped vs those that didn’t.

Amazon only funds the orders that shipped, so if webgility is pushing all of the sales in, it will be a nightmare to reconcile that with what actually funded to your account.

My process here walks you through how to get your bi-weekly Amazon Settlement report organized very quickly in terms of Gross Sales, fees, reserves, and net deposit.

Once you have everything set up, you will be able to get that report in two clicks.

Then it’s another 5 minutes to import the sales and complete the sales receipt in QuickBooks Online.

This involves a template that I’ve created in Excel which is sold separately.

My top ecommerce client tells me I should be charging twice what I am.

My Amazon Settlement Template takes what would otherwise be hours and turns it into minutes. That is not an exaggeration.

This section of the course takes you step by step through the entire process of setting up the template, and using it to import your Amazon sales into QuickBooks Online.

https://nerdenterprises.com/product/amazon-settlement-report/

 

Build Your Master Sku List with Smartsheet

Smartsheet is a great place to set this up (and much less expensive than airtable if you need a lot of collaborators).

The key difference between how you set this up in Airtable vs Smartsheet is that in Smartsheet you will have one table for everything. Essentially it is your Master Product List (from Airtable) without your linked fields (columns).

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)
  • Suppliers
  • Cost per unit
  • Price per unit
  • Product URL
  • Notes
  • Attachments (you can include photos of the product here)

Once again you may want to add things, and please do!

In Smartsheet, since you will not be linking to things, you may want to use the “Drop Down” column type for things like Sales Channel, & Suppliers. This is especially important for the sales channels.

You will likely want to use “Filters” in Smartsheet to create reports. This will work much better if you have 100% consistency in naming things. Dropdowns eliminate the chance of typos.

The video here will show you how to set all of this up in Smartsheet.

Build Your Master Sku List with Airtable

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?

Like this?

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
  • Products
  • Master Product List
  • Sales Channels
  • Suppliers

Design Notes

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.

Products

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)
  • Suppliers
  • Cost per unit
  • Price per unit
  • Product URL
  • Notes
  • Attachments (you can include photos of the product here)

Once again you may want to add things, and please do!

Sales Channels

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.

Suppliers

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.