Profit loss formula to build your P&L statement

profit loss formula

The new ProfitLoss() formula function is available for you to use.

Here’s the page for a deeper understanding and guide on how to use this Profit Loss formula.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website documentation for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

Profit loss formula to build your P&L statement 1

Spreadsheet formula to get profit loss data

We’ve added another free template for you to create a profit and loss statement spreadsheet, but it’s not as automated as I wanted it to be.

Thus the GORILLA_PROFITLOSS() is born.

gorilla profit loss amazon function

Easiest example:


This simple example will automatically load a consolidated table like the following:

amazon fba profit loss statement table results
amazon fba profit loss statement table results

Rather than looking up all the individual transactions and compiling it yourself, it is done automatically so that you can pull up this data by simply changing the time period you want to look up.





You can see how easy your life becomes when you can verify your profit and loss on the fly.

Try doing this manually – It is a nightmare.

More Profit Loss Examples

You can also build a profit and loss statement per SKU.


Profit loss formula to build your P&L statement 2

When you define a SKU, the profit loss table will not include Amazon Reimbursements, Income-Other and Amazon lending.

This is because certain transactions are NOT applicable to a sku. The transaction is matched to your seller account, not SKU.

Think about storage costs. If you look at your transaction data, storage costs are always associated with your seller account.

Amazon does not charge you per SKU. It is a single dollar amount each month.

You can also see the details of what is rolled up into each of the categories.

  • Sales
  • Discounts/Promotions
  • Amazon Reimbursements
  • Shipping Income
  • Income-Other
  • Amazon Lending
  • COGS
  • Amazon Fees
  • AdvertisingTax

To look up a category, use the expanded formula like:


By entering “AMAZON FEES” and “YES” at the end, it will list all the Amazon seller fees one by one and how much was transacted for each item.

Amazon fees breakdown profit loss
Amazon fees breakdown profit loss

Same thing here where I can drill down on what is rolled up into “COGS” by looking it up in the formula.

Profit loss formula to build your P&L statement 3

There you have it.

To see other functions and examples, visit the functions page for all the features we support.

Load Amazon FBA returns and refunds data into sheets

You can now load the total number of FBA returns and the values of the returns by SKU or across your entire account.

This is on the heels of the newly released Amazon salestax function. If you have data importing requests, leave a comment below and we’ll add it to our consideration board.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website version for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

Load Amazon FBA returns and refunds data into sheets 4

Our latest FBA refunds function

We have created two separate functions for refunds. One is to get the qty of FBA returns over any period for any sku.

The other is to get the total value of the refunds.

See how to use it below and the dedicated functions and documentation pages and links to see more examples and usage.


Easiest example:


More examples on the RefundsCount() explanation page.

gorilla refunds count function

You can see how easy it is to use. The only requirement is to enter the time period.

The list of time periods is extensive and you can also use custom dates.

=GORILLA_REFUNDSCOUNT("CUSTOM", "FR", A2:A34, "2020-01-01", "2020-01-15")

Enter “custom” in the period attribute. Then fill up the other attributes following the onscreen formula popup.

In the above formula, it’s grabbing the refunded count for:

  • custom time period from 2020 Jan 1 to Jan 15
  • France market
  • for all SKUs entered in cells A2 to A34

Here’s another example of how this looks on Google sheets.

refunds count results gorilla roi

Using ranges can load all the data in one shot.

Getting the returns like this using RefundsCount() is the same concept as SalesCount(). Think of the two functions are sisters. They work the same, just different results.


Easiest example:


More examples on the RefundsTotal() explanation page.

gorilla refunds total

This is the same concept as RefundsCount. The only difference is that this pulls in the dollar value of all returns.

If you have 100 refunds for a $10 product, it will load $1,000 instead of 100.

All the same time periods are supported as well as custom time periods.

Here it is in action.

amazon fba returns refunds total results
Amazon fba returns refunds total results

To see other functions and examples, visit the functions page for all the features we support.

New: Amazon SalesTax to get sales and tax data by state and city

The new SalesTax() function is available for you to start using immediately.

Here’s the page for a deeper understanding and guide to Amazon FBA sales tax.

As always, we update documentation consistently and in multiple locations to make it easy to find.

  1. Official page of all the functions we support
  2. Google Docs version
  3. Website version for easier navigation
  4. On any Google sheet > menu > addons > Gorilla > 2.See Supported Functions and examples

New: Amazon SalesTax to get sales and tax data by state and city 5


Warning: This function is NOT for accounting.
Do NOT use this for your taxes.

Best uses of this function:

  • help you prepare how much you will owe
  • know which states bring in the most sales
  • know which cities bring in the most sales

Easiest example:


More examples on the formula functions page under SALESTAX().

gorilla sales tax functionIf you need to get an idea of how much you owe in taxes, what is being collected by Amazon and what you owe, this function will help you gain a good idea of the amount.

Again, I can’t stress enough that this is not for accounting purposes. The numbers will be off slightly (+/- 3-7%) because we import transactions daily. If a customer decides to return a product on the 23rd day, Gorilla does not redownload the transactions to reconcile for this refund.

With millions of transactions being processed, it is impossible to redownload everyone’s transactional history.

This is why for taxes, you must export the file directly from Amazon to generate the latest reconciled data.

Display Amazon SalesTax by city

The default results will group the sales by city.

It shows the sales of each City as well as the tax amount collected by Amazon or owed by you.

You can also see which city brings in the most sales for you.

This is useful if you want to target ads on Facebook or Google. Targetting towards the city with the most buyers is a good idea to increase conversion and keep ad spend low.

gorilla salestax formula google sheets
Displaying the salestax formula grouped by city

Display SalesTax by state

To group the sales and taxes by state, use the format as in the example below.


Here’s what it looks like.

Gorilla FBA sales tax formula by state
Gorilla FBA sales tax formula by state

If you are registered in multiple states, this will help you organize your cash to figure out how much you can expect to owe.

You can also see where the majority of your buyers are.

Helping with local sales tax remittance

If you file local taxes monthly, you have to enter the cities where you had sales. Trying to do it manually is the worst thing possible.

I literally had to spend hours sorting the reports, combining the data and grouping them by city and state to provide the numbers to the state.

With this function, you can eliminate the majority of the grunt work.

The numbers are not 100% accurate, but they are close enough for you to figure out how much you will owe.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included.

amazon inventory replenish template spreadsheet

Looking for Google or Excel spreadsheets as an Amazon inventory management system?

You’ve come to the right place.

By the end of this article, you’ll be able to:

  • create your own inventory management template using proper spreadsheet structure and formulas
  • load Amazon FBA inventory data into Google sheets automatically
  • design and use Google Sheets or Excel as an Amazon warehouse management tool

There is a working spreadsheet template at the end of the article you can download. It is the same version that I’ll show you how to create in this article.

Use the quick links below to jump to the section you are looking for.

Let’s begin.

What this article is NOT about

If you are not an Amazon seller and looking for information on how Amazon actually operates its warehouse or inventory, this article is not for you.

I do not talk about their warehouse locations, how they pick orders with robots, seller fees, random inventory locations, barcodes and so on.

This article is for Amazon sellers who are looking to create their own Amazon inventory system to handle their own operations.

Building the Amazon stock management spreadsheet

This is the inventory stock spreadsheet that I’ll build with you here. Here’s the link to the final preview.

inventory stock spreadsheet tracking

amazon excel warehouse inventory details tracking spreadsheet in stock

Before we dive into the technical details, some theory is required.

*Cue the eyeroll*

Yes, I know.

If you are an expert with spreadsheets and also a A-grade UI designer, then skip to the practical section titled Basics to designing an effective Amazon inventory system”

Understanding your Amazon inventory management system needs

inventory system at amazon for most sellers

Every seller needs a system to track and manage their inventory.

Ultimately on Amazon, there are two main types of sellers regarding inventory.

  1. You focus on a smaller number of SKUs
  2. Or you sell tens of thousands of different SKUs and do large volumes by combining the total sales.

The focus of this article will be on the first group of sellers.

FBA sellers who focus on a small number of SKUs.
  • Sellers who carry less than a thousand SKUs. Focuses on a smaller number of SKUs.
  • Sellers who sell big volume with a smaller number of products.
  • Sellers who don’t have 30+ employees in the warehouse fulfilling products.
  • Sellers who don’t need an expensive full-blown Warehouse Management System (WMS) that easily eats up $2,000 a month to operate.
  • Sellers who need nimble, flexible and creative solutions without being locked into enterprise software or a programming team.
  • Sellers who don’t need EDI interfaces.

Whether you sell $100k a year or $100M a year doesn’t matter when looking for the best Amazon inventory system. The most important factor to consider for your inventory system is the number of SKUs you manage.

The number of SKUs in your inventory is the number one constraint of your inventory management system.

Why smaller operations should use Google Sheets as an internal Amazon inventory management system

The more SKUs you have, the more complex your requirements become. You’ve outgrown the warehouse tracker you built.

You need a multi-channel fulfillment software or a Warehouse Management System or both.

It is a nightmare to try and handle 10,000 SKUs.

small sellers handling inventory tracking
source: Simpsons

If you have more than 5,000 SKUs, stop reading this and look up “warehouse management software”.

That is what you need – not an inventory tracking spreadsheet.

But if you don’t have thousands of SKUs, keep reading because this solution can work for you.

ShippingEasy for multichannel order management

For multi-channel orders, we use ShippingEasy for our operations to scale our FBA business. It handles all of the green processes in the chart below.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 6
source: shippingeasy

Our main use of ShippingEasy is for two things.

  1. centralize incoming orders from all different selling platforms
  2. centralize purchase orders

ShippingEasy’s inventory tracking and stock management system is on the weak side as there isn’t enough flexibility. However, for order management, ShippingEasy is the way to go.

For our inventory planning and operations, we like to:

  • monitor inventory data by SKU
  • monitor inventory data several times a day, last 7 days, last 30 days, by each month, quarter, last 7 days, last 30 days, last 90 days, compare to last year
  • create forecasts of the SKU we are interested in analyzing
  • check past inventory sales velocity over any period we want (day, week, month, between any two dates)

To date, I have yet to come across a solution that offered the flexibility that we required in terms of being able to get the date ranges and data for specific SKUs quickly.

We always ended up having to export csv files and then feed it into our spreadsheet models for every date range we tracked.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 7

It was a nightmare.

That’s why we built our Amazon inventory system on Google Sheets + Gorilla ROI making use of our sales and inventory addon.

Simplify with Google Sheets for Amazon Inventory

gorilla sheets functions for Amazon Seller

As I mentioned, unless you have thousands of SKUs in your inventory to manage, there’s a good chance Google Sheets will work for you.

I’ve helped Amazon companies implement a custom inventory system based off our Google Sheets Amazon Business Spreadsheet Package. One company even sells more than $50M in revenue with a corporate structure. But a spreadsheet still works because not everyone can get access to an enterprise software or has the knowledge to use it properly and get the info required.

Whereas, spreadsheets are so easy to create, share and can be accessed by anyone in the company.

The only difference between us and this big company?

They generate huge revenues by selling expensive products with large margins. Whether you sell 1,000 of one SKU in a month vs 10,000 of one SKU, the concept is the same except the velocity is faster.

The reason why I’ve been able to help other companies supercharge their spreadsheets is that we do more unit volume than most companies (at lower margins) and understand how to create efficient spreadsheets and trackers for people in our business.

If you believe that Google sheets or Excel spreadsheets will work for you, let’s get our hands dirty.

Basics to designing an effective Amazon inventory system

When creating spreadsheets, there are basics you must learn and apply.

Anyone can make a spreadsheet, and that's the problem.
Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 8

You wouldn’t build a house without going through your list of needs and then locking down your structure and design with an architect.

To make your spreadsheet life easier, you should apply the same concept if you don’t want to end up with a spreadsheet where it seems like the toilet is smack in the middle of your kitchen.

The inventory sheet is going to be structured into 3 sections.

  1. Data
  2. Logic
  3. Presentation

Unless a spreadsheet is structured like this, you are going to have a monster on your hands. It will grow out of control quickly.

The rules for each of the three sections.


For this inventory management tracker, the architecture looks like this.

design inventory spreadsheet structure

The DATA sheets

In this inventory/warehouse project, there are going to be two data sheets.

  1. One for product information such as the SKU, ASIN, FNSKU, that matches the Amazon information.
  2. One is for inventory data such as how many of each sku is instock, transfer, inbound and some basic sales data.

The LOGIC sheet

The middle “INVENTORY LOGIC” is the sheet that contains the formulas to combine and crunch the product data and inventory data into a useable format.

No data is manually entered into the logic sheet.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 9

The logic sheet is 100% dynamic.

Typically, you likely built spreadsheets where the data and formulas are mixed together on the same page, but it is much easier to manage when you separate the spreadsheet tabs like this.

Here’s why.

Let’s say you want to add 10 new products to your current spreadsheet, change the ASIN and add dimensions.

Now what?

  • you have to add 10 new rows or columns
  • you enter all the product info
  • you have to create formulas for the new SKUs
  • you have to update all your formulas, graphs, tables because the entire data moved and is out of whack

Expect to spend the next 1-2 days (around 10 hours) trying to clean, test and fix up the bugs.

But if you use a logic sheet which simply runs the calculations by looking up the data sheet, all you do is update the data sheet with the extra data and that’s it.

The logic sheet is set up so that it updates itself or with minimal maintenance.

This type of separation also makes it easy to scale your spreadsheet and share your spreadsheet without fear that someone will break it.


The hardest part isn’t the logic, but the presentation. This is where you have to simplify the data to what you need. Not to cram as much as you can onto a screen.

What you don’t want.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 10

This is what you envision being able to share with your team.

sales Inventory Management Template Inventory Dashboard
Source: insightlysquared

The goal of the presentation sheet should be to keep it as simple as possible. Get straight to the point and deliver the key important message.

It should be a dashboard so the business owner or executives can understand what is going on in a split second without needing anyone to explain it to them.

If you need to explain what is on the screen every time, the presentation has failed.

We’ve gone through the design and the architecture. Keep it in mind as it will help you see the picture of how everything comes together.

design inventory spreadsheet structure

Let’s start building the structure.

Building the INPUTS sheet

Every spreadsheet system should have a dedicated space for input data. Rather than plugging in an absolute value like “25” for 25 days in a formula, you can reference a cell like A4.

This way, if you need to change the number from 25 to 40, you don’t have to find every formula that has “25”. Referencing cells makes it a time process.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 11

In my inputs tab, I like to keep it very simple. I have some formulas to help reduce steps like counting the number of SKUs, the low stock threshold alert, list of marketplaces.

In our internal Amazon inventory system, there is more information like a list of date ranges that we look up, quarterly deadline dates, seller ID’s, table references and more advanced aspects that feed into our formulas.

amazon spreadsheet input ranges date ranges automated

It all depends on your needs.

Anytime you find yourself having to enter a hard value into a formula, move it to the Inputs section.

Building the product DATA sheet

product datasheet for inventory warehouse systems spreadsheet

  • Enter your product data into this tab like so.
  • Enter the product details that don’t change often.
  • Do not enter stock count, inventory status, pricing etc.
  • This is your database of product information.

You can put as many heading columns as you want. It doesn’t matter as long as you keep it simple and only enter data that does not change often.

Your SKU is unlikely to change, your ASIN is unlikely to change. Do not enter things like targets you are aiming for, sales velocity and other pieces of data that change constantly.

In our internal “master” product data tab, our headers include:

our inventory product master headers

  • SKU
  • ASIN
  • Image URL
  • Image
  • Product Name
  • UPC
  • Peak / Offpeak
  • Unit/ case
  • Category
  • Rank
  • My Price
  • Leadtime (Days)
  • Rec Order Lev in Days
  • Suppliers
  • Parent or Child

We’ve automated the data so that it gets pulled automatically, but you can enter this data manually as it doesn’t change often.

The SKU is the unique number that we use throughout the spreadsheet to find, link, sort and filter data.

Getting back to the example, if you fill it out, it should look like this:

product data setup

To get the little thumbnail images:

  • go to seller central
  • go to manage inventory
  • right click on the thumbnail and save as image
  • insert into your spreadsheet

seller central manage inventory save image

Building the inventory DATA sheet

amazon inventory stock data tracking

  • The INVENTORY_DATA tab must have the SKU and ASIN to make your life easier
  • You can fill this data by exporting your inventory reports from seller central
  • Change the header titles as needed based on the data you need

The hard part with this is that the data for the inventory status needs to be updated regularly. This likely means you have to manually enter the data.

Pro tip: use Gorilla ROI inventory and FBA addon to get the latest data automatically.

When the data is filled up, it looks like this.

import amazon inventory data

Because this inventory template is a simple version, you could combine this data inside “INVENTORY” rather than separating it out as I show here.

However, it’s good practice to see how it comes together.

There is a detailed definition and guide to getting inventory data using Gorilla ROI add-on.

  • Total Available = InStock + Inbound + Transfer
  • InStock = inventory available and ready to ship by Amazon
  • Inbound = inventory on the way to Amazon’s fulfillment center and has not been checked in
  • Transfer = inventory has been checked in and being transferred to other fulfillment centers

Building the inventory LOGIC sheet

Now we combine the data from PRODUCT_DATA and INVENTORY_DATA into this sheet.

This way, if PRODUCT_DATA or INVENTORY_DATA is updated with new data, this sheet will automatically update itself.

Let the spreadsheet do the work for you.

inventory stock tracking formulas

  • Columns A to E is the information from PRODUCT_DATA.
  • Columns F to L pull the info from INVENTORY_DATA.

The SKU is loaded by using a formula as simple as


Or you can use more advanced formulas like


The other columns for image, product name, ASIN is pulled using VLOOKUP.

VLOOKUP is also used to find and automatically display the inventory data points such as instock, inbound, transfer, last 7 days units sold etc.

You can see that the INVENTORY sheet has zero inputs. It’s just a sheet with formulas that updates itself based on your inventory data at Amazon.

amazon inventory stock data tracking imported

The VLOOKUP formulas use the SKU as the search_key to locate the correct data point to pull in.

  • The red section in the screenshot above comes from the PRODUCT_DATA sheet.
  • The purple section comes from “INVENTORY_DATA”.

(Refer to the spreadsheet template directly to see the formulas for yourself.)

Since this is the logic sheet, this is where you can go wild with all your formulas to calculate stock rates, forecasts and anything else you know.

To keep it simple, I’ve added the following calculations:

  • Av/30Day Units Sold
  • AMZN Inv Days Left
  • QTY to Ship

Av/30Day Units Sold – Average Last 30 days units sold

Rather than using the standard last 30 days, I like to normalize the data to try and account for any spikes.

That’s why I have last 7 days, last 14 days and last 30 days in my inventory data sheet.

amazon average 30 days units sold

The formula to calculate this is:

=(Last7Days x 4 + Last14Days x 2 + Last30Days) ÷ 3

It converts the last 7 days and last 14 days into a “month” equivalent.

Then the average is taken for the three values.

For an even more accurate assumption, you can use the calculation of:

=(Last7Days÷7) + (Last14Days÷14) + (Last30Days÷30) ÷ 3 x 30

What this version does is normalize the 7 day, 14 day and 30 day sales into a one day figure. It then takes the average and then multiplies it by 30 to get the total average last 30 days units sold.

There are a lot of different ways to calculate the average 30 days or normalize it, and it depends on your needs and your business.

Since we have some seasonality like any business, our internal version has more layers to it. We combine it with weighting factors based on the current month.

E.g. we sell more in December and January, so our calculation puts more weight towards the last 7 days and last 14 days calculation during these months than the last 30 days.

During off-peak season, the weighting focuses more on the last 30 days.

AMZN Inv Days Left – Amazon Inventory Days Left

Inventory days remaining data at Amazon via seller central, is always one day late because Amazon generates the report once a day.

If you have fast moving inventory that you need to keep an eye on, the daily report by Amazon is not helpful. They also use straight-line calculation which simplifies the trends and cannot account for spikes and seasonality.

amazon inventory days left

Hence the formula that I use to calculate the Amazon inventory days remaining.

=IFERROR((TotalAvailable ÷ Av30DayUnitsSold) x 30,"")

Remember that TotalAvailable = InStock + Inbound + Transfer.

The main aspect of the formula is (TotalAvailable ÷ Av30DayUnitsSold) x 30.

By using the average 30 days units sold, it compares against the total available inventory. This number is then multiplied by 30 to give you how the total days of inventory you have left at Amazon.

Here’s a quick example.

  • Let’s say you have 100 units total available.
  • Average units sold over the last 30 days is 25.
  • 100 ÷ 25 = 4
  • 4 x 30 = 120 days

The result is that there are 120 days of inventory remaining for your Amazon FBA account.

The IFERROR() function is used to make clean up the results. Rather than show an error on the spreadsheet, I like to display a blank space using ” “.

If the last 30 days units sold is 0, then it would throw an error. Using IFERROR() in your Google sheets lets you control the output.

In Excel, you can use ISERROR().

The spreadsheet inventory tracker gives a visual indicator where it turns orange when the days left in inventory is between 18 and 25.

It turns red when the days left is below 18.

conditional filter alert amazon inventory days left

You can change the conditional color rules based on your needs.

QTY to Ship – Quantity to Ship

The last column to round out the logic section of the spreadsheet is to create a column that lets you know how many units need to be shipped to meet your inventory threshold.

In my example, I have alert threshold set to 25 days as indicated in the INPUTS sheet.

amazon inventory days remaining to send threshold value in days
Amazon inventory days remaining to send threshold value in days

A value in the “QTY to Ship” appears when the AMZN Inv Days Left column is less than the threshold alert level (25 days).

inventory ship to amazon fba
inventory ship to Amazon FBA

The QTY to Ship is then calculated to display how many units you need to ship to stock up for 30 days.

QTY to Ship = Av30DayUnitsSold - TotalAvailable

It’s as simple as checking how many units are available at Amazon, then subtracting it how many were sold the last 30 days.

If you have 100 units at Amazon, and you sold 300 units in the last 30 days, then you should send in 200 units.

QTY to Ship = 300 – 100 = 200 units to ship

If you need to ship more units than 30 days, then Av30DayUnitsSold has to be changed to be Av45DayUnitsSold.

A shortcut formula to do this is:

QTY to Ship = Av30DayUnitsSold x 1.5 - TotalAvailable

Since 1.5 x 30days = 45 days, you can multiply the last 30 days units sold by 1.5 to get an estimated last 45 days sold.

This way, the QTY to ship would now be the total units to reach 45 days of inventory at Amazon.

With all this, the final result of the inventory logic sheet has a nice, clean table with the key data points we need to make decisions without getting overwhelmed.

amazon excel warehouse inventory details tracking spreadsheet in stock

Advanced option for better accuracy

For higher accuracy and more robust inventory system, you can set it up by peak vs off-peak.

Assuming you are good with spreadsheets, you can set up formulas to detect whether the current month is a peak or offpeak month. If it is a peak month, make it calculate using the Last45Days units sold.

If it is offpeak, use the standard formula above.

This way, you can control how much inventory stock is sitting at Amazon warehouses. No need to pay extra for storage fees.

The cost of holding inventory at your own warehouse or even a 3PL will likely be cheaper than sitting in Amazon’s warehouse during peak seasons like Christmas.

Building the dashboard PRESENTATION

So far, we’ve created all the sheets except for the presentation dashboard as shown in the architecture.

design inventory spreadsheet structure

  • INPUTS hold numbers and other references that we want to change as needed.
  • We got the product data inside PRODUCT_DATA.
  • Inventory data is coming from INVENTORY_DATA.
  • INVENTORY pulls in the needed data from INPUTS, PRODUCT_DATA and INVENTORY_DATA to give a nice usable table.

You could end it with the INVENTORY sheet. But I want to simplify it further by having a dashboard display a table with the 15 lowest inventory stock.

A simplified version of the INVENTORY table will help you quickly see which products are out of stock or about to go out of stock and act accordingly.

Imagine if you have a 1,000 SKU list or you manage multiple accounts.

Do you want to go through a massive table every day and find the SKUs manually that need to be prepped and shipped?

Or would you rather have a neat and summarized data table ready for you?

This type of simplified table helps you:

  1. identify what needs to be done
  2. drill down to the next stage by looking up the data
amazon inventory management system dashboard presentation
Final Amazon inventory management system dashboard presentation

This final table is easy to build. The majority of the grunt work is in making it look pretty.

The main formula that I personally use to create dashboards is by using QUERY().

It’s an advanced formula, but once you understand the concept and usage, it is a lifesaver. It saves so much time, can create tables and data on the fly.

All I need to use is one QUERY formula as shown below to auto-generate a filtered table of results.


using query dashboard inventory at amazon

The formula is saying:

  • Use the data from INVENTORY sheet, from column A to column Y.
  • Then grab the info and numbers from column C, A, N, M, O
  • Make sure the numbers in column N are not null (not an error or non-existent)
  • Display all the results in ascending order based on column N
  • Display only 15 results

This QUERY formula is a hard worker. Creating a table by using equals formulas and VLOOKUPS should be left for the logic sheets. Presentations should be built with QUERY where possible.

The possibilities are endless once you understand how to structure spreadsheets and make use of data.

You can create charts to track inventory. This is a chart of ours that tracks when a SKU needs to be reordered, and how many days of inventory remains at Amazon and our warehouse.

amazon inventory tracker

Automate with our Amazon inventory add-on for Google Sheets

If you have a system to track inventory at Amazon and your physical warehouse location, the next logical step is to automate and load the inventory data directly without having to log into your account and export reports.

That’s where Gorilla ROI comes in. It is a Google Sheets addon that pulls your FBA data and loads it directly into Google Sheets.

If you have more than 20 SKUs and you are copying and pasting data manually into your models, you know what a hair-pulling experience it is.

If you are growing, your forecasting or inventory management system needs flexible and dynamic data, without requiring you or someone in your team to manually feed it information.

Here’s how easy it is to get started with our addon for inventory, sales, product, reviews, fees data and more.

  1. Authorize Gorilla ROI to your Amazon seller account
  2. Install the addon on your Google account
  3. Activate the addon from the Google Sheets menu
  4. Wait for data to be imported (6-24hrs depending on your size)
  5. Pull inventory data with a formula as easy as =GORILLA_INVENTORY(“MYSKU”)

As you type in =GORILLA_INVENTORY and you will see the popup menu appear.

Follow the syntax and example and you can pull the required data you need.

seller central integration for inventory data

Being able to access your data without any code or help from programmers or other people means that you can create your own flexible and internal inventory application. Why not look like a genius in front of your peers?

We also support tracking daily inventory levels. You can filter by total availability, instock, inbound or transfer.

This way, you can see when an item went out of stock for better inventory forecasting and you can focus on making better decisions with your inventory.

When you combine the inventory data along with flexible custom sales data, you can create a powerful internal management system to solve your  warehouse tracking issues.

Click the button to start creating your own Amazon inventory management system today with our add-on.

Amazon inventory management system using Google Sheets or Excel. Spreadsheet template included. 12

Download the Amazon inventory management spreadsheet template

Click the button below to make a copy of the spreadsheet to your Google account.

If you use excel, you can save it to your account first, and then download it as an XLSX file by going to menu > file > Download > Microsoft Excel (.xlsx)

amazon inventory system spreadsheet template download link

Additional Resources

Amazon FBA sales tax for sellers. What is collected and what you owe.

amazon fba sales tax

The screenshot above is what you’ll be able to do and make your seller FBA tax calculation easier.

By then end of this Amazon FBA sales tax article, I’m going to show you:

  • how you can import your Amazon sales tax data with ease
  • calculate the sales tax amount that has been collected and what you owe
  • how much sales tax has been collected by Amazon marketplace facilitator
  • how much you have collected and owe the state
  • how Amazon pays the sales tax
  • your role in remitting taxes

Let’s begin.

FBA sales tax for Amazon sellers

Now that the Wayfair vs South Dakota dust is settling, it’s obvious that sales tax on Amazon sales is here to stay and you should be on top of it so that you don’t get a surprise tax bill in the 5 or 6 figures.

Thank goodness, it is much easier with Amazon handling the collection of sales tax and remitting it to the state on your behalf as the marketplace facilitator – something they should have done a long time ago.

Amazon has not implemented the marketplace facilitator system across all 50 states yet, but the trending is going in that direction.

Check to see if your state is listed in Amazon’s marketplace facilitator page. Only the states on the list have sales automatically collected and remitted.

An easier way is to go to your tax settings.

Click the drop down box on the right that says “add states” and then it will open a list of states with check boxes.

The unchecked states are the states that Amazon does not collect sales tax on your behalf.

tax settings amazon

E.g. New Hampshire is not on the list. This means that for all sales to NH, you’ll have to get the tax info and remit to the state yourself.

Amazon FBA sales tax is going to be the norm across every state your products are sold and shipped to.

The point is that your listings for FBA sales need to have the correct tax code so that you are not under-receiving sales tax. If you are not collecting the correct amount, you will have to pay the difference out of your own pocket.


Amazon tax code – select the right one

The full list of Amazon product tax codes is long, overwhelming and confusing.

If you are a seller, go to this page in your account to see the full list and find the matching product tax code for the item that you sell.

If you don’t have access to a seller account, click here for the full-screen capture of all the product tax codes.

This screenshot is a snippet of the entire list.

amazon product tax code preview
Amazon product tax code preview

The quickest and easiest way is to select A_GEN_TAX for all of your products. This will classify your product as a general consumable and there are no additional sales tax considerations. This tax rate will cover you safely.

It’s also the safest rate because it’s going to be the highest tax rate. Some products like food, computers and baby goods have lower tax rates.

This means that if you set products in this category to A_GEN_TAX, then you will be collecting more tax than required.

And no, you don’t get to keep it. You must pay it back, but the downside is that other sellers who have the correct product tax code will have a lower “total price” at checkout than you.

With individual product tax codes set up, Amazon will know when and when not to collect the sales tax and the correct amount.

  • If you have thousands of items to list, this is a lot of work.
  • If you don’t sell in any special categories, set it to A_GEN_TAX.

It’s a one time process when you create the listing, so it is worth the effort to set up correctly.

seller central tax code setting

Product tax codes on handmade items or no barcode products?

If you have a product without a barcode or there is not a special product tax code, then select A_GEN_TAX.

Let’s say you make vases, or ceramics or handmade soaps, then all of this goes under A_GEN_TAX.

A tricky part may be a product that you think is part of the baby category. If you make and sell baby diaper bags, or some accessory related to babies that is not related to anything medicinal, food, cosmetic, goes inside the mouth or body, then it has a very high chance of just being a regular product A_GEN_TAX.

After all, what is the difference between a diaper bag and a regular shoulder bag except for additional pockets?

Does Amazon collect sales tax for sellers

Yes and no.

Amazon will collect sales tax on all products sold to the states shown on your tax settings page.

If a state is not listed, Amazon does not collect sales on your behalf. It is up to you to collect sales tax and remit it to the state. (But this is another topic…)

If you sell in Florida, as of today, Amazon does not have the system set up to collect on your behalf.

Amazon also does not collect sales tax on FBM orders. Only FBA orders.

If you fulfill orders via FBM or even through your ecommerce stores, you will need to keep record of the taxes collected and remit it yourself.

Does Amazon pay sales tax for sellers

This is another yes and no answer and comes up a lot.

Because Amazon only pays on your behalf if they have collected on your behalf. This is why you need to check your tax settings to see which states are being collected on your behalf.

If Amazon does not collect sales tax for Florida, then you will be required to pay to the state of Florida the sales tax you have collected.

If you go to your transaction report, there is a column that says “tax collection model” and “marketplace withheld tax”.

“Tax collection model” tells you whether Amazon has collected it or not. If Amazon has collected sales tax for you, it has the value “MarketplaceFacilitator”.

The column “marketplace withheld tax” will then show you the amount that Amazon has collected and pays to the state for sellers.

Here’s a screenshot of the report and showing the taxes collected and paid/unpaid which I’ve highlighted yellow.

marketplace facilitator tax collected for sellers
marketplace facilitator tax collected for sellers

Estimated tax to be collected Amazon?

If you follow the screenshot above, and filter for all the orders where it does not say MarketplaceFacilitator in your transaction report, you can easily find out the total amount of sales tax that was not collected.

Since Amazon will pay sales tax that it has collected for your account, you only have to calculate the amount that you owe.

Much simpler.

Here’s how to get your sales tax data from seller central and filter the data.

  1. Select the entire header row of the table.
  2. Click on the menu > data > filter
  3. Click on the drop down icon and then deselect “MarketplaceFacilitator” and click OK.

Amazon FBA sales tax for sellers. What is collected and what you owe. 13

You will have a list of all the orders where sales tax was not collected.

How to get FBA tax data automatically

If this sounds like too much work – it is.

There is an automatic way that I’ll show you in this section.

However, if you need numbers for accounting purposes and it must be correct to the cent, there is no other way to do it than manually.

No software will be as accurate as generating the transaction report directly from seller central and then going through it yourself as I showed you above.

But what if you want the numbers to prepare yourself and accuracy isn’t so important and having the cash set aside?

This case is different as you are looking for data to show you the health of the business and so you don’t get caught off guard.

Knowing roughly that you’ll owe the state $500 in taxes, and being prepared for it is better than having no idea.

Enter Gorilla ROI

Import sales tax info using Gorilla ROI





Assuming you have:

  1. installed the addon and followed the getting started instructions to connect your seller central account
  2. wait around 24hrs for your data to get imported and calculated

In any Google sheet, start to enter the formula =GORILLA_SALES and you will get this popup showing you how to use the formula.

sales tax gorilla roi
Importing sales tax information with Gorilla ROI – FBA only.

This formula allows you to pull total revenue and the taxes collected by state and city.

If you do something as simple as =GORILLA_SALESTAX(“LAST MONTH”), the result looks something like this.

gorilla salestax formula google sheets

This single formula is the default display and

  • combines your sales
  • organizes it into state and city
  • shows whether the tax was collected
  • shows total sales per city and state
  • shows the total tax collected

The above formula without any inputs, other than the time period displays it by default as “city” setting.

If you take it one step further and group it by “state“, it will look like this.

Gorilla FBA sales tax formula by state
Gorilla Amazon FBA sales tax formula by state

Notice how all the values are now consolidated into states.

This way, you can quickly see how much sales you did per state, and the level of taxes.

WARNING: This is NOT an accounting tool.

Reports are not reconciled for every refund or canceled order. The data is imported at the end of the day. If a customer returns your product later on, we do not go back into history and reconcile transactions continuously. It is not possible.

What are some ways to use this sale tax data?

Please heed the warning above. Depending on your refund rate, you will see differences.

E.g. if your average monthly refund percentage is 4%, then expect the total sales to be off by about 4%. That is because we import your transaction data daily. We do not generate the data when you request it through your sheets. That is only possible for you to do manually through seller central.

  • You can see which states have the most sales and you can adjust inventory closer to that region if you use a 3PL.
  • You can get a very good estimate of how much sales tax you will owe to the state you are registered in.
  • You can use it in your profit and loss statement to get net income as net income is after taxes.
  • Make sure that enough tax money is set aside so you don’t get a nasty surprise at the end of the month/quarter/year.

See how automating the data collection process can improve your Amazon FBA business

We have much more data that you can pull using Gorilla ROI addon for Sheets. It’s not just sales and tax data you see here.

You can load your seller data such as:

  • Product information
  • Your product BSR
  • Inventory across marketplaces – instock, inbound, transfer
  • Sales data across any period – last month, 2020 Q1, your fiscal year
  • Reviews
  • Charges and fees for a P&L

You can supercharge any of your existing spreadsheets by data process automated.

How to create an Amazon sales tracker with Google Sheets

dashboard sales fba spreadsheet

By the end of this article, you’ll know how to

  • create your own Amazon sales tracking tool
  • pull Amazon sales data by SKU or ASIN
  • track sales by day, week, month or any other period you need
  • feed and automate your sales dashboard
  • copy a free Amazon sales tracker spreadsheet to your account

all within Google Sheets. You’ll look like a genius in front of your team.

Use the Gorilla ROI sheets addon to start automating your processes.

The basics to creating an effective Amazon sales tracker

In order to track sales data, you need – sales data.

If you are doing it manually, save yourself the headache and hassle of manually downloading reports by automating the work.

The reason why trackers and spreadsheets you first built enthusiastically ultimately fizzle out, is because the work required to update with data is a major hurdle.

How to create an Amazon sales tracker with Google Sheets 14

As your product portfolio grows, your spreadsheets and various trackers grow with it.

What also grows is the time it takes to update. It grows exponentially.

I bet you can find a wasteland of old excel or spreadsheets that you haven’t opened in years in your folders or drives.

In order to scale, where you can grow without increasing your workload to get data, we recommend using Gorilla ROI.

How to create an Amazon sales tracker with Google Sheets 15

To get the Amazon sales data you need, use our free Gorilla Sheets addon which is free for any 3 SKUs.

Enter your SKU, not your ASIN.

The methods described in this article assumes you have:

  1. already installed the Gorilla Sheets addon
  2. connected to your Amazon seller account
  3. data has fully loaded (1-24 hours depending on your size)

Now, let the fun begin.

Laying the foundation

build strong spreadsheet foundation

Whether you do this with manual data or with auto updating data, structure your spreadsheets so that each tab is dedicated for a single purpose of data, logic or presentation.




Organizing “Data”

The sheet that keeps track of your data is compiled and stored in one place. Not all over the place in 10 different tabs.

How to create an Amazon sales tracker with Google Sheets 16

How often have you had to search every sheet and cell and update it with the newest data point?

If you keep all your data together in one big place, you can use this central location as the “database” of your spreadsheet.

This way, if you update the data in this single location, all your other sheets will be updated automatically.

Organizing “Logic”

How to create an Amazon sales tracker with Google Sheets 17

Likewise, don’t mix the data with the logic (formulas) on the same sheet, otherwise, it becomes extremely messy to separate.

Make it so that one sheet has all the data, and another has all the formulas and logic so that the sheet with the formulas do not have to be updated again. Make it so that the tab with the formulas will be 100% hands-off. It just updates automatically based on what the data sheet says as you update it with Amazon sales.

Organizing “Presentation”

Presentation is self-explanatory and there is a section of Do’s and Don’ts in the upcoming section.

Use the presentation sheet to create charts, tables, graphs and so on. It does not need any data or formulas. It uses the output from the formulas.

Again, the three basic and best practices to make your spreadsheet life simple.

1. Data sheets - stores data only
2. Logic sheets - only contains formulas and gets its data from the "Data Sheet"
3. Presentation - uses the output from the logic sheet to create charts, tables and visual presentation

Understanding how to build sales spreadsheets better

If you aren’t sure of what I mean so far, think of it like your house.

How to create an Amazon sales tracker with Google Sheets 18

You have the following rooms:

  • bedroom = sleeping, relaxing
  • kitchen = cooking
  • dining room = eating
  • TV room = watch TV
  • and so on

Organize your spreadsheet like a house.

What everyone does with sales spreadsheets is to create one where everything is everywhere and all mixed up.

The kitchen is used for cooking, sleeping, playing with pets, shaving your beard.

The dining room is used as a swimming pool, toilet, project room, garage.

As you see, it is a mess. Your spreadsheets are likely the equivalent.

A home can have more than one room for each purpose like 5 bedrooms, 2 kitchens, 3 bathrooms.

In your spreadsheets:

  • one tab could be for daily sales data
  • one tab be for monthly sales data
  • one tab be for return data
  • one tab for product information data
  • one tab to calculate the logic for monthly sales projections
  • one tab to present projections

In this example, 4 are used for data.

1 is for logic.

1 is for presentation.

Let one tab serve one purpose (data, logic or presentation). This way, as your spreadsheet grows, it will not become a mess.

A real sales tracking spreadsheet example

Here’s an example of a potential sales tracking spreadsheet for your Amazon sales. This is how we organize our sales tracker that you can save at the bottom of this article.

amazon sales tracker
Amazon Sales Tracker – Google Sheets

Dashboard = Presentation = Updated dynamically and what we look at. Updates based on sales data.

We use VLOOKUP, QUERY(), SPARKLINE() to create awesome looking dashboards for things like sales tracking, inventory monitoring, sales forecasting, cost analysis and more.

amazon best sellers sales performance
Amazon seller sales performance tracker

SALES_STATS = Data = the sales performance of each SKU over various time periods we track like

  • Last 7 days
  • Last month
  • This month
  • Last year
  • Last week

sales tracker time period

We actually follow over 20 different sales periods to track how we are doing.

SALES_MONTHLY = Data = sales performance of each SKU by month.

amazon sales tracker monthlyProduct_details = Data = contains product information like the entire SKU list, ASIN, FNSKU, UPC etc. Get this using =GORILLA_PRODUCT().

How to create an Amazon sales tracker with Google Sheets 19

If you have existing spreadsheets, try reorganizing it to separate the data and keep it clean before it gets even more out of hand.

Note: When creating a simple spreadsheet like this, a logic sheet is not required because Gorilla ROI handles data + logic.

Manual process to get daily sales into your sales sheet

There are multiple different ways of getting your Amazon sales data.

Here’s the manual (and painful) process.

How to create an Amazon sales tracker with Google Sheets 20

  • Go to seller central
  • Go to reports > business reports > detail page sales and traffic by child item
  • Change the dates on the calendar to what you need
  • Export the data
  • Open up the csv or excel spreadsheet and create a pivot table
  • Extract the SKU and sales data
  • Copy and paste to your main spreadsheet in the data tab
  • Repeat each day (argh!)

It’s a painful and tedious process but it works. Keep doing it on a daily basis to track your Amazon sales.

But what if you want to track something other than daily sales? What then? I’ll get to that later.

Automatic method to get daily Amazon sales data

  • Enter your list of SKUs in column A (let’s say you have 200 SKUs)
  • In cell B1, you type in =GORILLA_SALESCOUNTHIST(A1:A200)
  • Done

This formula will update itself every day and pull in the last 30 days of your daily sales for each of your 200 SKUs.

SalesCountHist() pulls daily sales
SalesCountHist() pulls daily sales

Get monthly sales into the spreadsheet tracker

Getting monthly sales numbers is the same process.

If you do it manually, you have to adjust your dates to the start and end of each month, export the data and then compile it again into a monthly column.

To do it automatically, use the formula =GORILLA_SALESCOUNT().

  • Create a header row in the format YYYY-MM (don’t forget the dash)
  • Use the formula =GORILLA_SALESCOUNT(D1, “US”, A2:A100, “ALL”)
    • This will pull the monthly sales for the YYYY-MM that is entered into cell D1
    • for the US market
    • bulk load for SKUs in A2 to A100
    • for all sales status
Monthly amazon sales by sku
Monthly amazon sales by sku

By making the sales formulas dynamic, anytime the inputs change, the sales stats change with it.

In this case, when the month ticks over and it becomes 2020-03 the headers are updated which the formulas will detected and then also start pulling data for 2020-03.

This way, the tracker is fully automated and all you have to do is focus on the numbers and making decisions of what to send and order.

Sales data presentation

If you have organized your data and logic, you can now turn your attention to the presentation.

Here’s an example of an ugly presentation that everyone ends up creating.

ugly spreadsheet structure and information

How to create an Amazon sales tracker with Google Sheets 21

How to create an Amazon sales tracker with Google Sheets 22

You get the point.

Coming back to the basics, this is because the data, logic, and presentation are combined into one place.

While design is subjective, here are some tips to make dashboards look like this:

PPC analysis and kpi dashboard
PPC analysis and kpi dashboard
dashboard sales fba spreadsheet
dashboard sales fba spreadsheet
Seasonal sales and inventory forecast calculator
Seasonal sales and inventory forecast calculator

12 Simple do’s and don’ts of spreadsheet presentation

How to create an Amazon sales tracker with Google Sheets 23

  • Don’t overuse different colors
  • Do use 3-5 colors max. Keep the color palette consistent.
  • Don’t color your cell unless there is a clear reason and it is so obvious everyone else will understanding the meaning.
  • Do insert notes and comments in the cell to deliver the message.
  • Don’t make the spreadsheet something only YOU can understand.
  • Do understand the objective of what others need to see and future proof it.
  • Don’t use the standard strong colors in the color bar
  • Do use softer, neutral or pastel version of the colors
  • Don’t overcrowd and stuff the sheet with as much data and numbers as possible
  • Do focus on simplifying it and then simplifying it further. Display your core KPIs and the message you want to convey only.
  • Don’t keep every column and row the same default size.
  • Do expand and resize rows and columns to give added room and emphasis.
  • Don’t use numbers to express everything.
  • Do use icons and symbols to break up walls of numbers for visual understanding.
  • Don’t be afraid to use grayish tones for text.
  • Do emphasize the important data.
  • Don’t put everything into a huge table.
  • Do use multiple tables to break down the display into bite-sized tables.
  • Don’t get too crazy by mixing multiple design elements to make it look “pretty”.
  • Do keep the number of visual data presentation to max 2 per sheet. Stick to consistent use of bar charts or one other type.
  • Don’t use pie charts. They are the worst.
  • Do use bar charts or line charts instead. Please avoid pie charts… please?
  • Don’t use 3D effects.
  • Do use simple and clean 2D visuals.

Advanced techniques to boost your visual  spreadsheet presentation

Keeping things as simple and to the point is the best.

To date,  it’s hard to find a better way to visual data than a simple bar chart (horizontal and vertical) or a line chart.

With simplified tables and bar/line charts, you can blow people away with advanced sales dashboards because of the effectiveness and simplicity.

With just 3 advanced spreadsheet functions, you can turn your current spreadsheets from “meh” to “wow”.


Google Sheets VLOOKUP doc.

Sample Usage

VLOOKUP("ASIN B088325DS", A2:B26, 2, FALSE)


VLOOKUP(search_key, range, index, [is_sorted])

If you structure your spreadsheets as I recommend into

  • sales
  • logic
  • presentation,

you need a way to find the data without doing things manually.

Use vlookup to find information for a particular ASIN like my inventory template.

VLOOKUP searches for the SKU, in the data sheet that has all the data, and pulls in the matching values for “last 7 days”.

sales velocity vlookup
sales velocity vlookup

This type of table is what I call the “logic”. There is no data. It pulls and updates based on the formulas.

It’s also not the “presentation” as this table is all I need to know my inventory status. It’s a logic sheet that is good enough to be the presentation.

Use VLOOKUP everywhere you can to eliminate manual data entry.


Google Sheets SPARKLINE doc.

Sample Usage




SPARKLINE(A1:A5, {"charttype","column"; "axis", true; "axiscolor", "red"})

As they say, a picture is worth a thousand words.

What type of sales data is easier to read and understand?

This table?

amazon daily sales units sold

Or this?

sparkline amazon sales chart visual

By using sparkline charts, you can create mini charts that fit inside a cell.

In my example, I’ve combined sparkline charts for each sku/ASIN inside a simplified table.

Nothing fancy. Just clean, simple and straight to the point.


Google Sheets QUERY doc.

This is one of the most powerful advanced functions that Microsoft Excel does not have.

This functions lets you truly use your spreadsheet like a database + application.

Sample Usage

QUERY(A2:E6,"select avg(A) pivot B")


This is how we use it for our sales spreadsheet dashboard.

QUERY is used mostly for the “presentation” stage. It finds the data, and then dumps it onto the screen.

spreadsheet query dashboard
spreadsheet query dashboard
=QUERY('AZUSA-SALES'!$A3:$BB,"select G, A, AZ, L, M, Q, S ORDER BY L DESC", 1)

My query formula is getting the data from columns G,A,AZ,L,M,Q and S from the tab “AZUSA-SALES”.

It then tells the query formula to sort it in descending order based on the data in column L of the AZUSA-SALES table.

The result is what you see in the screenshot above.

How to create an Amazon sales tracker with Google Sheets 24

Why is this so awesome?

If you have a big sheet of seemingly random and messy data, you can ask QUERY to do all the hard work to find, sort and display the data for you.

You could create multiple tables based on your needs.

  • A table analyzing conversion rates
  • A table tracking sales (as shown above)
  • A table tracking the child products of a particular parent
  • A table tracking the BSK rank
  • and so on

Free Amazon Sales Tracker with Google Sheets

The template can be copied with your Google account.

How to create an Amazon sales tracker with Google Sheets 25amazon google sheets spreadsheet template integrationIf you are looking for a way to supercharge your existing spreadsheets to eliminate the manual data work, or build 100% flexible internal tools, check out Gorilla ROI.

More tutorials

Amazon Review Checker with Google Sheets

I’ll show you how to create an Amazon review checker using Google Sheets and our free addon.

Keeping track of Amazon reviews is a vital way for sellers to know what buyers think of your product. This way you can see trends or any issues along the way.

Monitoring reviews also helps in improving your product as you can implement changes that your customers are complaining about.

In this article, I’ll show you how to load the average product rating, review details and the total review count for your products using this automated method.

This way, you can use Gorilla ROI as an Amazon review checker or Amazon review tracker.

Note that Gorilla ROI does NOT work as a research tool to track competitor reviews.

How to retrieve your average Amazon Product rating

amazon review checker

Track each product rating to see how your product is performing, which product is in danger of being delisted, and the general trend of how customers are reacting to your product.

Keeping track of your negative reviews is critical as it determines the life of the product on Amazon.

Here’s how to use the formula to get the rating for a single product.


In this one, I picked our sample “ASIN12123” in the USA market. It returns a value of 4.5 which means the product has an average of 4.5 star rating.

gorilla ratings in action 1 sku

The best way to use this is via arrays like the example below.

gorilla ratings array in action

By requesting in bulk, it will load the ratings quicker in Google sheets.

Tracking the ratings of your products is a good way to keep an eye on it. Low ratings will most likely penalize you as a seller or your product might get delisted for being low quality.

Rather than have to go through your listings to get the product rating, you can see it all from a single screen in your spreadsheet.

How to track you Amazon reviews

Amazon Review Checker with Google Sheets 26

This function will load your Amazon reviews, but also provides detailed information about the review.

=GORILLA_REVIEW("ASIN123123","us", 5)

The formula will load the Amazon review for

  • ASIN123123
  • in the US market
  • last 5 reviews on the site

You can’t use arrays with this function because it pulls many data points and automatically loads it in a table format.

If you edit the formula above to be something like below where the 5 is replaced with 30, the function will now pull in your last 30 Amazon reviews.

=GORILLA_REVIEW("ASIN123123","US", 30)

Back to the original formula of 5 reviews. See the screenshot below.

The function returns in a tabular format:

  • ASIN or SKU
  • the review rating (1 to 5)
  • the date of the review
  • review title
  • reviewer name
  • link to the review
  • full text of the review

gorilla reviews in action

This is why ranges such as A1:A100 are not supported because the result comes back as a table. Can’t have a table of results overlapping another table of results and so on.

Get the review count of your product

review count guide

This function is most useful when you want to see the total number of reviews a product has received.

We organize the reviews by the child ASIN, not the parent.

If you have 5 ASINs under a parent, and if one child ASIN is generating 80% of the reviews, it doesn’t make sense to combine and show the total number of reviews for all your child products.

This function opens up many ways you can create your own Amazon review tracker.

If you want to get the total number of review counts use this formula.


review count total in action

You can use a single ASIN/SKU with this function and you can also use array. See the example below.

gorilla review count array in action

Using the array, Gorilla ROI will pull in review count data for the SKU/ASIN from A2 to A5.

Example Amazon Review Tracker Spreadsheet

Here’s an example of the type of custom and flexible dashboard you can build to check and track your reviews.

Google spreadsheet preview

amazon reviews and price checker
amazon reviews and price checker

Here’s another version where we created this review tracker to be proactive with managing our listings and customer support.

pull in amazon reviews
click to enlarge Amazon review spreadsheet

These spreadsheets and more are offered for free to Gorilla ROI members.

Free Amazon FBA spreadsheets

Amazon Review Checker with Google Sheets 27

Looking for other free tools?

Go here for free Amazon FBA spreadsheets that you can use to help run and analyze your FBA operations.

Combine it with Gorilla ROI addon that you can get started with for free and see how powerful it is to automate the data retrieval aspect of your business.


Best software for FBA sellers and how we use it in our FBA business

What you’ll learn

  • A review of the best software for FBA sellers that we use in our operations
  • Which FBA tools that are a must-have for us
  • How our team uses these tools for Amazon

Best software for FBA sellers

This is going to be interesting.

I know people are always interested in knowing how others run their business, so I’m going to share the best software for FBA sellers and other vital tools we use to run our Amazon business.

It’s not just limited to how we run the Amazon side of things, but other things like customer service, project management, internal operations and aspects outside of Amazon.

I originally sent this out in an email, but I’ve added more examples and explanations to help you out.

This list is not complete or the proper way of doing things. It’s just what works for us. If you have some cool implementations on how you do it, leave your thoughts in the comment section below.

Tools used to run Amazon

Best software for FBA sellers and how we use it in our FBA business 28

Surprisingly we don’t use many FBA software tools for Amazon.

Scouting and research tools – AMZ SCOUT, Amazon Storefront, Retail stores

If you’ve been reading my newsletters for a while, you know how I feel about scouting software for FBA sellers.

I’m not a fan and I only use it to get rough numbers on potential products and ideas that I have in my head.

I never use it for sourcing actual products.

The main issue is that anytime you run the tool on a product, it gets stored on a database for a million other sellers to look up.

I do not run the tool on any of our products for the same reason. No need to share our product and data with the masses.

However, I do use AMZ Scout over Jungle Scout. Purely for the reason that more sellers use Jungle Scout vs AMZ Scout.

To get accurate ideas and data on what is working and what isn’t, using the Amazon Storefront strategy is one of the best options. Amazon lists in order of best to worst selling items in your storefront.

All you have to do is look up competitor storefronts and you can immediately know which one is their top seller.

This way, it’s easy to expand your own product line and you have the most accurate data right in front of you.

The other option is to visit a big box retail store. Our top sellers have come like this. If a big retailer like Target, Walmart, Bed Bath Beyond, Home Depot or Lowes is carrying it, it means the market is big.

From there, we narrow it down a little and find a specific product that looks to be doing well. We then find ways of improving it and putting a unique spin on it.

Knowing that China sellers are copycats, they just want to private label something. i.e. ODM products.

Since we want to be unique and sell it in the long run, we create our own version to own the mold and prevent China sellers from selling the same thing. The more expensive and harder it is for a seller to copy our product, the bigger the barrier to entry.

One of our best selling products used to sell 100 units a day. That has now dropped to 20-30 because of so many copy cats. It was an easy product to copy and they got away with it.

Since then, we’ve decided that if a product is too easy to copy, we don’t want to sell it.

Listing software – NONE

We still use CSV files or manually create listings from seller central as we don’t deal with hundreds of SKU’s.

Customer follow ups – FeedbackWhiz

I used to use FeedbackFive, but ditched them because FeedbackWhiz offered the same features for email follow ups at half the price.

So far so good. They have other features like sales numbers and order information, but these numbers are inaccurate.

I’m looking at my dashboard on FeedbackWhiz and it’s telling me I have sales of $42k for the 2nd week of May. It should be $49k which is what Amazon and GorillaROI shows.

Promotion, launches, product reviews – Facebook, ManyChat

Used to do promotions heavily back in the day, but stopped for a long time.

But then it became near impossible to get new products going or competing in tough categories.

Amazon is cracking down anyways and I’d rather let the shady China sellers get banned. Same with giveaways.

But launches are still necessary. Rebates can work, but it’s ultra-expensive.

The goal for us is to try and get the cost of a review below $20 after accounting for everything.

Most of our products retail for less than $20. Spending in excess of that is a bad ROI. Our ROI is super bad.

Let’s say I give away 100 products.

  • COGS is $200 ($2 each)
  • Ad spend is $400
  • I received 3 reviews from this campaign

Total spent to get a review is $600.

3 reviews = $200 spent per review = horrific.

Calculate it like this to figure out what you can spend and where the best channels are for you.

So far, it’s definitely within the Amazon ecosystem of Vine or Early Review Program.

We do use other strategies but because of all the naysayers and self-policing sellers, I’ll leave it out of here. If you want to know, drop a comment or email me.

Keyword generators – KWFinder, Storybase, keywordshitter

Lots of free and good tools out there. The one that we pay for is storybase as we use it a lot for SEO and other content.

Keywordshitter is awesome. Check it out if you haven’t heard of it or used it before.

Data and analytics – Google Sheets, Gorilla ROI

Yes, it’s a plug for our tool, but only because it does everything that we need.

  • Reviews
  • Inventory planning and forecasting
  • Hijacking
  • Sales reports
  • Team sharing
  • Custom reports

The latest spreadsheet we created internally is a PPC spend analyzer and optimizer. Not the typical style that you see with other software.

This spreadsheet is specific to our internal KPI. By being able to pull sales totals over various periods and combining it with the PPC spend, it has already become a very powerful tool in understanding our current performance and where we need improvements.

We used to use InventoryLab to get the fees and charges, but their numbers are off and Gorilla ROI pulls in all the fees and charges, much more accurately and is customizable.

By creating custom spreadsheets with Google, it’s become like an “internal app” that we can create and edit on the fly without having to know any code.

There are different spreadsheets for specific employees and departments so that everyone understands what their roles and KPI’s are.


We have our own designer that creates all the marketing images, packaging, mockups, and digital stuff.

No such thing as FBA software or expensive agencies.

Just a quality designer that we found proactively using upwork and

Rather than posting a job and waiting for somebody to come by, we have found our best designers and a team we work with by manually going through hundreds of portfolios and contacting the designer directly if we felt they matched our design style.

I used to do it all myself and I’m capable, but not great.

Since we started outsourcing this area I can focus on doing the things that scale and to do tasks that bring in revenue – not tasks that save money.

All in all, we keep things simple on the Amazon side.

Ecommerce outside of Amazon

Best software for FBA sellers and how we use it in our FBA business 29
This is where things get different.

Shopping cart – Prestashop , now WooCommerce

I have used Shopify in the past. We used to sell high-end ceramics so we spent a good deal of time and money on the Shopify site. The pro is that it’s easy to set up and get running, but if you do any decent amount of sales, it becomes expensive fast and severely handcuffing.

Shopify makes you pay for every-single-little-thing.

So for our current store, I chose Prestashop but switched over to wordpress and woocommerce in 2019 with highly customized functions.

It’s free, all the things you pay for in Shopify are included. It works out of the box. Plenty of templates you can purchase and customize.

It’s great and functions very well, but I’ve changed my strategy of converting our site towards content-heavy, landing style pages, and I need something very flexible and more of a CMS than a shopping cart.

Hence the move to wordpress and woocommerce.

I’ve already applied it to one other site and it fits what I’m trying to do, so I’m in the process of moving our main money site to this new format.

It should supercharge conversions and boost our SEO as we get content heavy and keyword rich.

[update] After switching all sites to woocommerce and focusing on making each listing page a long sales page with tons of content (Amazon does it just like this) SEO has received a boost, more keywords are ranking and traffic is increasing.

With this new format, I’m also able to create single Amazon niche sites on the fly. I can literally go from nothing to a working and functioning woocommerce site in less than a week with a 100% custom design.

Email marketing – Sendinblue

I used to use Drip. Now I use Sendinblue as it’s simple, affordable and has all the tools you find in more expensive software like automation, but targeted for smaller sized businesses.

If the e-commerce site grows substantially, I’ll need to switch back to Drip or something more powerful, but for now Sendinblue works perfect for us.

It was a hassle getting things set up, but now that it’s running, no need to fix what’s working.

Customer service – Hotjar,, Freshdesk

Hotjar has a free plan and is just awesome. It can save videos of how visitors interact with your website. It gives you a lot of insight into which parts of your site need to change.

Recently I got a no brainer lifetime deal for through

It is the little round chat widgets you see in the bottom corner of sites nowadays which helps to capture leads.

Appsumo is one of my go-to places for best deals. Especially for software as it is all lifetime one-time payment based.

Freshdesk is our help desk and ticketing system.

People send emails, it gets registered into the system for someone in our team to answer. Rather than sending contact forms, questions and emails to a person’s inbox, it is better to centralize such communication into a single location.

Payment Processor and Gateway – Stripe

No introduction needed.

Gone are the days where you had to use old services that stopped innovating and helping sellers like

Stripe is just easy. No contractual obligations. Fees are decent. If you do volume, you get a discount. They really changed the landscape for online selling.

Integrates very nicely with woocommerce.

If you have multiple stores, you can connect them all to one Stripe account which makes things so easy.

Webhosting – Ramnode VPS, Cloudflare

I’ve tried a LOT of different webhosting over the past decade of running online businesses.

From supercheap ($2/mo), to cheap like bluehost and godaddy (from $5/mo), to better and more expensive dedicated VPS solutions ($50-200/mo), cloud servers (several hundred) and so on.

At the moment, I’ve found the best webhost for our ecommerce needs. That’s Ramnode.

I pay $10 for some awesome specs because our website isn’t huge and slammed with traffic.

For any small to mid size site, you can’t beat these guys.

Very reliable, very affordable, very good speeds and each of my sites is set up separately.

The downside is that for most people who don’t have developers, you have to pay extra for CPanel. A lot of people nowadays want a WordPress managed server, which I find to be a waste of money.

Then again, I have my own team of devs, so it’s a no brainer for us to custom set up servers based on our needs.

Communication and Operations

This is where the heart of our operations lie. It’s a simple and cost-effective system we have set up.

We mostly use 3-5 tools that handle everything. But the heart and soul is Zapier.

Zapier, Trello, Slack, Gmail, Google Docs

If you don’t know zapier, the software allows you to relay data from one software or tool to another. Think of it as a traffic director.

It’s the same as how Gorilla ROI operates. We connect Amazon seller central with Google Sheets.

Although we only support 1 integration (Amazon to Google Sheets), with Zapier, it supports hundreds of different software that you can hook up to send data back and forth.

All without code.

Here’s an example of how we use it.

  • I have a customer that likes to text me his wholesale orders
  • From my phone, I forward the text to a specific gmail like “
  • Zapier recognizes this email based on the subject and from address.
  • Zapier is triggered to forward the message to our Trello board for wholesale orders.
  • Somebody in the orders team will see the Trello card that is created and fulfill the order.

Another example related to Amazon is:

  • Anytime an Amazon customer messages us directly, it is directed to a single and central email account.
  • We have this email set up with Google suite.
  • Zapier recognizes the from address and subject.
  • Zapier forwards it to a slack channel that somebody monitors to follow up.
  • This way, every message is answered or handled within 24 hours.

An implementation method with Gorilla ROI is:

  • When a Google spreadsheet using Gorilla ROI is updated
  • Zapier is triggered that a change was made on this spreadsheet
  • Zapier can then forward the spreadsheet to team members, or alert somebody or do hundreds of other things.

You can create multiple levels of different tasks too.

After the spreadsheet is updated, I could have it automatically forwarded to gmail, and then uploaded to Trello, send a notification to slack and so on.

It’s all based on the idea of “If This happens, Then do This”.

Can’t live without it.

Has automated so many of the little manual tasks that needlessly eats time. We use it in ways that you haven’t even thought of that I can’t share.

If you start using it, you’ll discover ways to optimize your workflow with the software and the best FBA seller software that works for your business.

For now, hope you got some insight and ideas about improving your own operations.

If you have some cool implementations, please share!

5 ways to speed up your FBA Amazon business

FBA Amazon business

With so many processes that you perform in your FBA Amazon business, here are 5 ways to speed up and improve business overall using Gorilla ROI in your workflow.

The goal is to automate, eliminate or simplify the existing processes that occur repetitively throughout your FBA Amazon business.

For some of our past ideas, you should check out:

1. Tracking daily units sold

Time and money-wasting process:

  1. Seller central
  2. reports
  3. set the date period
  4. download report for the past 2 weeks
  5. update the date period
  6. download report for another previous period
  7. combine csv reports into one file
  8. create pivot table
  9. get the results

New efficient process:

  1. enter a single formula to load historical daily sales

One of the most popular ways of using Gorilla ROI is to track your daily sales via the units sold or the dollar value.

It’s such a basic requirement that Amazon does not provide within Seller Central. There’s no way to look at all your ASINs immediately know the total number of units sold on a per-day basis.

With Gorilla ROI, you can do something like this with a single spreadsheet formula. Dates on the side, each ASIN up top.

FBA sales hist daily tracking

Normally you have to download multiple reports because Amazon won’t let you download all the data in a single file.

Then you have to combine all the csv reports and create a pivot table to get the same thing Gorilla ROI does for you in 3 seconds.

2. Improve your Amazon FBA business sales performance dashboard

Time and money-wasting process:

  1. log in to seller central
  2. go to business reports
  3. go to sales reports
  4. update the custom date range for each period you want to look at
  5. download the report
  6. do the same thing for another period
  7. open up both files in excel and review

New efficient process:

  1. Use the freely included sales template to get immediate sales reports over multiple time period. Use formula SALESCOUNT, SALESTOTAL, SALESCOUNTHIST, SALESTOTALHIST.

Amazon gives you basic sales numbers for the last 7, 14 and 30 days. It’s good for a feel-good glance but doesn’t provide you with any detailed information about your FBA business.

If you want to get deeper into the numbers, you have to go into your sales and traffic reports and then update the date range each and every time.

5 ways to speed up your FBA Amazon business 30

Then you have to download the file each time you want to compare it with another period.

This is where your custom sales dashboards in Google Sheets comes in handy to give you a better picture and trend of how your sales are doing.

Currently, you’re used to looking at the most basic sales summary from seller central.

5 ways to speed up your FBA Amazon business 31

But what this doesn’t show is how you are doing compared to previous and different time periods. It also doesn’t show which of your SKUs are doing well.

Turn the above box into something like the following where you can see how you did in the same day last year, this year and even quickly see which products are performing well.

FBA sales performance dashboard
FBA sales performance dashboard | Click to enlarge

Sharing a spreadsheet with people is also much easier and safer than making seller central accounts for everyone or sharing accounts among employees.

Other free Amazon FBA spreadsheets are available for download.

If you use sign up to GorillaROI, you get a suite of free ready to go spreadsheets like the one above as part of the membership.

3. Simplify inventory management system

Time and money-wasting process:

  1. Go to seller central
  2. Go to manage inventory to check alerts, inbound status, transfers
  3. Go to inventory planning to review days in stock, restock inventory recommendations
  4. Inform fulfillment team via email, manual spreadsheet, chat or phone
  5. High potential of going out of stock

New efficient process:

  1. Auto import inventory data and units sold in a Google sheet
  2. Track your numbers and prevent going out of stock from a single spreadsheet without requiring 10’s of clicks to get simple data
  3. Share the spreadsheet and delegate to the fulfillment team to maintain inventory levels

Amazon provides inventory replenishment recommendations but their numbers and pages are all over the place.

You have to go to the manage inventory page to see the number of units that are currently available, inbound and being transferred.

Then you have to go to the inventory planning page to get a summary, followed by visiting the restock inventory page to get an estimate of what products to send into the fulfillment centers.

So much work for a single data point.

That’s not the end though.

To find the velocity of sales per SKU, you have to download reports from different time ranges and combine them to get a value.

This is a lot of work and eats up a lot of time. If you are doing this daily, or even 2-3 times a week, save yourself hours of work by simplifying and automating the process.

One of our most used spreadsheet templates is the inventory management sheet that helps you keep product in stock as well as planning. It gives you a complete picture of your inventory and sales by SKU or ASIN.

This means you have all the important pieces of information loaded in a single spreadsheet that updates itself automatically.

This is an example of what you can do. It’s so easy to know what needs to be sent, and how many units.

inventory sheet screen

You can automatically load this type of information and create your own custom dashboard, tables or simply feed the data into your existing spreadsheets.

A detailed tutorial to pull inventory data is below.

How to load FBA inventory data into Google Sheets

4. Record daily historical inventory to learn your seasonality

Time and money-wasting process:

  1. Go to seller central
  2. Go to manage inventory
  3. Click the arrow to expand the status of the product inventory
  4. Manually copy the data into your spreadsheet
  5. Do it for every sku you sell
  6. (miss a day and you’ve got gaps in your data already)

New efficient process:

  1. Enter a single formula using INVENTORYHIST()

One thing I’ve learned related to historical inventory is that sellers want to know when they went out of stock to learn about their seasonality.

Because every product is different, keeping track of inventory throughout the past by day, weeks, months helps the sellers to get more insight into their FBA Amazon business to plan for inventory.

The hard part is that it’s a massive chore to write down the dates and number of units remaining in stock for every sku, each and every day.

If you have 5 skus, then it’s possible. If you have 100, it’s a nightmare. Not worth the effort and errors.

By loading historical daily inventory numbers using our Gorilla_InventoryHist() function, you can load your historical inventory units and prevent your operations from going out of stock.

An example is the screenshot below where I can filter for inventory in “transfer” process from this week for any sku I’ve selected.

5 ways to speed up your FBA Amazon business 32No clicking around searching for information. No compiling spreadsheets.

5. Review management and respond faster to complaints

Time and money-wasting process:

  1. Go to seller central
  2. Go to the product page
  3. Click around for the reviews
  4. Sort for negatives
  5. Repeat for all products

New efficient process:

  1. Use software that curates it all in one place using REVIEW formulas.

There are tons of Amazon review management and emailing software.

With Gorilla ROI, we’ve taken a different approach where you can pull reviews for any of your child ASINs directly into a spreadsheet.

This makes it easy to share and discuss. You can copy the data into an email and send it to the customer service team to review, you can pull a list of the last 50 reviews or drill down to a single SKU.

Rather than hunting to find the data, create your own dashboard to monitor customer service and reactions.

There is a ready to use review management spreadsheets available as soon as you sign up.

pull in amazon reviews

Bonus: Simplify data sharing across teams

One thing that we’ve changed is how data is handled across our business.

5 ways to speed up your FBA Amazon business 33

We no longer create new accounts for employees. Accounts are only created for people whose job requires it.

  • Me – Full access
  • Operations assistant manager – 90% access
  • Operations assistant – 70% access
  • Bookkeeper – 50% access
  • Automation only account – 90% access

That’s about it.

This way, data is protected and people are not logging in and out without reason. The goal is to limit people touching the account as much as possible. Only for those that need to.

We changed to this because after reviewing our systems, even the people who accessed seller central used it for their own single purpose.

  • Founders only visited the sales report
  • Fulfillment only inventory pages
  • Bookkeeper only downloads the finance csv’s

Even the bulk of the work performed by myself and our operations team centered on reviewing listing details and other pages weren’t visited as frequently.

These lesser-visited pages added more work and crowded mind space of another thing to check at intervals.

Now, our system is simple and looks like this:

  • A single master spreadsheet controlled by the operations team pulls in all required data
  • Sales data is then imported into a separate sales performance spreadsheet. Nothing else.
  • Inventory data is imported into a spreadsheet that the warehouse team can access.
  • Bookkeeper still has to log in and export the full files, but a lot of the financial data is pulled and reviewed via our finances spreadsheet.
  • Data we need to share with customers or suppliers and loaded into a basic non-sensitive format.

By doing this, we “silo” the data. It keeps our Amazon data protected and our teams actually end up with more data in a single spreadsheet on their computers.

All they do is refresh to pull in the latest data and get straight into making decisions.

No more meetings about what has been done, what needs to be done, who is doing what etc.

5 ways to speed up your FBA Amazon business 34

Summing up

Amazon has shown you the possibilities of utilizing software and systems to create a healthy and scalable business.

It’s no different in your own operations. The more you can automate and simplify your processes, the easier it is to scale.

Rather than bog yourself down in day-to-day activities, review the way you handle tasks step by step and work to eliminate 50-75% of those steps.

You’ll be surprised by how much fat you can cut out.

All the savings will materialize in your bottom line.

If you aren’t using Gorilla ROI or other software to operate efficiently, determine your processes first, then determine what your needs are and find a solution to help you implement the steps.

Track and import Amazon buy box price for FBA

Looking for a solution to track and import Amazon buy box FBA pricing, offers, and sellers?

Now you can import all sorts of Amazon buy box pricing data directly into Google Sheets with Gorilla ROI.

First, read the getting started guide to familiarize yourself with the setup and installation.

Once the addon is installed and you’ve entered your correct credentials then you can move on to preparing your spreadsheet.

The methods described in this article assumes you have:

  1. already installed the Gorilla Sheets addon
  2. connected to your Amazon seller account
  3. data has fully loaded (1-6hours depending on your size)

All done? Let’s get started.

Intro to Amazon’s Buy Box Price

What’s the Amazon FBA buy box and why is it important for FBA sellers? It’s important as a seller to know who’s product is being presented to the buyer. If you resell branded products, multiple people could be offering the same product.

The buy box is also called Featured Offer which you can read more here.

win the buy box

The box with the price and “buy now” button for this toddler itch relief product looks so unassuming, but more than 82% of sales go through the Amazon buy box.

When a buyer clicks the “buy now” button, it is sold by one of the many sellers. This is where the term “win the buy box” comes from.

You win the buy box based on price and performance-based metrics. But you and your product need to be eligible FIRST.

  • In Seller Central, go to your Manage Inventory page and then click Preferences.
  • Select Buy Box Eligible, and at the bottom of the page, click Save Changes.
  • The Buy Box Eligible column will display the status for each of your ASINs. Here’s Seller Central requirement for product eligibility:

To see other sellers and who is competing with the buy box, you’ll see this small text at the bottom of the buy box area.

1 new link

When you click on “1 new” link under the “Compare with similar items” link you’ll see the “offers for this product”.

The other listing on this page did not win the buy box most likely because the offer is $4 more expensive.

other offers

Note that the link under “compare with similar items” is not always “1 new” as it could be “2 new” or “3 new” depending on the number of competitors for that product.

4 new items

How to Win the Buy Box

amazon buy box

If you are a reseller, it’s hard to consistently be the winner of the buy box as you are competing for the same space.

It’s not uncommon to get the buy box at the start of they day then lose it at the end of the day.

You won’t get featured 100% of the time in a day because the Amazon algorithm dictates who wins.  We don’t suggest that sellers should focus  on winning the buy box. Keep focus on customer needs instead.

You’ll win if you keep your focus on what you can control – pricing, product/service quality, inventory, and customer satisfaction.

Amazon’s customer focus mantra means if you want to have the featured product placement then you need to pass or exceed these metrics.

  1. Price your items competitively
  2. Offer faster shipping and free shipping
  3. Provide great customer service
  4. Keep stock available

Keep in mind that if you have higher performance metrics you can win the buy box even if your price is higher. It’s Amazon rewarding sellers that sell the best product and customer service or the highest value for the price.

Although you’re competing with other third-party sellers and Amazon itself for some products, the algorithm is fair in my experience.

Now that you know what the buy box is, let’s move on to importing your buy box and other FBA pricing data into Google Sheets.

Import buy box pricing, offers and seller data to Google Sheets

Gorilla ROI can be used to auto import Amazon Seller data. There’s so much data you can get through us without knowing how to code.

I’ll be focusing on showing you how to get your product prices in this article.

For products that you have in your account (not other seller products), you can get data points such as:

  • Your current price
  • The buy box price
  • A list of all the buy box offers
  • All the buy box competition sellers
  • Lowest price offered on the listing
  • Details of the lowest offers

Some might sound redundant, but they all differ in terms of what you see in the results. I’ll show you in the examples and screenshots below.

The Gorilla Sheets functions used to load this type of pricing and buy box data into Google Sheets are:

  1. Gorilla_MyPrice()
  2. Gorilla_LowestPrice()
  3. Gorilla_LowestOffer()
  4. Gorilla_BuyBoxPrice()
  5. Gorilla_BuyBoxOffers()

This is a quick example of what you can do using our own seller account as an example.


amazon buy box price fba seller tracking

Using the formulas above will load buy box and other price data like this directly into Google Sheets for any or all of your ASINs.

Import your FBA selling price


Using MyPrice() will import your selling price for the product. If you have a sales price, it will load the actual price being displayed on the buy box.

get the lowest seller price with this function

The following formula should be used this way.

=GORILLA_MYPRICE(sku, [marketplace], [condition], [subcondition], [priceType])

The formula requires a SKU or ASIN. In its most basic form, you can do something like:


This will load your selling price for the ASIN B0172ABCJHD.

To use more of the variables and have more control over the type of price you want to load, you can use a formula like this to pull the price info.


myprice amazon buy box listing price

In this example the variables I’ve used pull up data based on:

  1. My “listingprice” for all ASINs in A4:A100 – required
  2. US market – optional
  3. New condition – optional
  4. New subcondition – optional
  5. Listing price is the actual price the product is being sold for – optional

Optional variables in the formula are used as filters. This allows you to get very granular.

For example, if you sell used condition products, you can change the new condition to “used” to pull the buybox pricing for used condition only.

Here’s the complete list for quick reference.

Condition – New, Used, Collectible, Refurbished, Club

SubCondition – Mint, Very Good, Good, Acceptable, Poor, Club, OEM, Warranty, Refurbished Warranty, Refurbished, Open Box, Other

priceType – LandedPrice, ListingPrice, ShippingPrice, RegularPrice

If you want to get the listing price of a certain ASIN/SKU that is in used but mint condition, then you have to type this:

=GORILLA_MYPRICE("ASN123123", "us", "used", "mint", "listingprice")

Because there are a lot of options at your fingertips, it’s important that the correct variables are selected.

Some things to watch for:

  1. Enter the variables that match your product
  2. Enter the correct marketplace

Do NOT do this when using the formulas


Do not use a single cell reference and copy the formula down 1,000 times.

Use closed ranges like A2:A1000. This allows you to get 1000 data points with 1 call. Not 1000 individual data calls.

If you use formulas like the ones above by referencing one cell at a time, Google will block your sheets for 24 hours as you will go over their data request limit.

Load lowest price in the Amazon buy box


Lowest price does not mean it is winning the buy box. The price could be the lowest, but it could be an FBM product with high shipping.

=GORILLA_LOWESTPRICE(sku, [marketplace], [condition], [returnStyle], [priceType])

The required variable is the SKU. All other variables are optional.

amazon lowest price

Here’s a simple example.


lowest offer bulk pricing

This example shows the lowest price for all the ASINs listed from A4:A100 in the US marketplace.

If I wanted to pull the lowest price for Canada or UK, I would change the marketplace from US to CA or UK.

To get more details of the lowest price being offered, enter a formula like the following:

Amazon FBA lowest price details
Amazon FBA lowest price details | Click to Enlarge

If you use the “fullwithheader” variable, it will load the data in a table format.

In this example, we are the manufacturer and so there are no other sellers or offers.

By default, if you use the formula with only the ASIN as the input, you will get the price. By entering additional variables, you can fine-tune your spreadsheets to display additional buy box data.

Get the lowest offered product


This is different to the function you just saw with lowestPRICE().

This is to list up the lowestOFFER and information about the sellers who are offering the same product.

get lowest offer listing for product

This function will load all the offers for the listing from any seller on a listing that you sell on.

It’s useful if you resell a branded product and want to pull all the offers being offered.

lowest offer buy box
Lowest offers | Click to enlarge

The information you get are:

  • Item Condition
  • Item Subcondition
  • Fulfillment Channel
  • Ships Domestically
  • Shipping Time
  • Seller Positive Feedback
  • Seller Feedback Count
  • Listing Price
  • Shipping Price
  • Landed Price
  • Multiple offers at lowest price

If you enter a single SKU/ASIN while using LowestOffer, you can use the variable “fullwithheader” to display it as a table format if there are multiple sellers.

If you enter a bulk SKU/ASIN list, then it will only list the lowest offered price.


Note: The lowest offer does not always win the buy box.

lowest offer bulk pricings

Here’s another example to try:

  1. Lowest offered listing price in the US market
  2. For new items
  3. For the price type “listing price”

display lowest offer automatically

Pull Amazon buy box offers


This is an awesome function to get a list of all the sellers and offerings on the listing and competing for the buy box.

There are similarities with LOWESTOFFER(), but you’ll see in the examples below, that the data it returns is different.

amazon buy box offers

Note: This function does not pull information for SKU’s that you do not sell. It only works for SKUs and ASINs in your inventory.

Note: You can’t use ranges with this function.


In this example of BuyBoxOffers(), the variables I am using means:

  1. I want to look up the details for ASIN B3729923
  2. US marketplace
  3. “True” means I want to display full data with automatic table and headers

buybox offers full with header

The screenshot cuts off at column E but since I used “true” then the sheet will display full data with automatic headers.

Here’s a full size image. Click to enlarge.

buyboxoffer data points fba

This function returns the following data points:

  1. Is My Offer
  2. Item Condition
  3. Item Subcondition
  4. Is Amz. Fulfilled
  5. Is Buy Box Winner
  6. Is Featured Merchant
  7. Seller Positive Feedback
  8. Seller Feedback Count
  9. Shipping Avail. Date
  10. Listing Price
  11. Shipping Price
  12. Landed Price
  13. Ships From

Get the winning buy box price

This is the data point a lot of sellers using Gorilla ROI want to know. What the wining buy box price is.


Using this function can help you keep track of the price action on a listing, whether anyone is fiddling with your price, whether it’s a race to the bottom.

Get amazon buy box price

The function BuyBoxPrice will help you get the current buy box price by typing this single line.

=GORILLA_BUYBOXPRICE(sku, [marketplace], [condition], [returnStyle], [priceType])

This time you can use bulk ASIN or SKU as in an array or range.

The formula above shows that

  1. I want to list the landed price for the SKUs in A3 to A8
  2. In the US market
  3. For new condition products
  4. Listing price only

buybox price show price only

Optional parameters include “new” and “used”.

You can display the data without a table header using “full” or type in  “fullwithheader” and it will provide a header row.

buy box price table data
buy box price table data | click to enlarge

The data points returned when using BUYBOXPRICE() are:

  • Item Condition
  • Fulfillment Channel
  • Offers Count
  • Man. Sug.
  • Price Sug.
  • Lowest Price
  • Listing Price
  • Shipping Price
  • Landed Price

Summing Up

If you need to pull any data and FBA information related to Amazon buy box prices, listings, sellers and offers, Gorilla ROI does it automatically for you straight into Google Sheets.

No coding knowledge required. Just install the Gorilla Sheets Addon and data will start to be downloaded.

This is just one small aspect of how Gorilla ROI can automate and optimize your business operations so that you can focus on running your business, instead of being run by your business.

There are so many other important and valuable things to do for business owners. Wasting time trying to copy, paste and track pricing data is not an area a growing company should be spending time on.