Pull Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets

written by GORILLA ROI

If you are looking to track your Amazon Best Sellers Rank (BSR) or sales rank via Google Sheets, follow this tutorial.

This method is to track your own products that you sell and hold in your inventory.

This method does NOT work for tracking competitor products or for research. Only for products in your seller central account.

Load your Best Seller Rank now

If you want to follow along and start pulling your best sellers rank data for your products inside Google Sheets, read the getting started guide to start the right way and to get familiar 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-6 hours depending on your size)

Let’s begin.

Amazon’s Best Sellers Rank or BSR explained

If you are not familiar with what Amazon’s sales rank or BSR is, it is the rank of the product for the category.

Amazon best sellers rank
Amazon best sellers rank | click to enlarge

You can see that this Amazon product ranking is 315 in the grocery and gourmet food category.

The grocery and gourmet food category is considered the “top” category because it is at the highest level.

This same product is ranked 5th under candy mints.

Candy mints is the “child” category as it is the detailed category and at the deepest position.

A single product is ranked for each category, but the one people are referring to when they talk about BSR is the rank of the topmost broad category.

I won’t get into the details fo what affects your Amazon ranking because there are many factors involved. But a good chunk of it depends on your recent sales, historical sales, and conversion rates in comparison to other products in the category.

How to download your Amazon sales rank

If you’ve installed the Gorilla ROI addon for Google Sheets, you can use our functions to pull your product sales rank.

There are two Gorilla functions that will help get historical sales ranking for any period of time. This is an easy way to create your own Amazon rank tracker spreadsheet.

=GORILLA_RANKING()
=GORILLA_RANKHIST()

Let’s look at how the two methods work, what it is used for, and what data you can download with it.

How to get BSR of a product

Here’s how to use Gorilla_Ranking() to get the Amazon best seller rank of your product.

=GORILLA_RANKING(sku, [marketplace], [queryType], [returnStyle])
  • SKU – required
  • Marketplace – optional. Use to filter based on marketplace like US, CA, DE, AE etc.
  • queryType – optional. Control whether to pull the rank based on the top category or child category.
  • returnStyle – optional. Control whether to pull just the BSR number, or category or both.

You can get the Amazon product ranking for the broadest top-level category or deepest child category.

99% of the time, the BSR you are interested in is the top level category. The lower the number, the better and the more popular that product is.

Take a look at the results for the formula below.

=GORILLA_RANKING("ASIN123123","US","child","r_cat")

ranking r_cat

  1. The test product ASIN entered is “ASIN123123”
  2. I want Gorilla ROI to display the rank in the child category for only the US market
  3. “r_cat” means I want to see the rank + category

The child category for this ASIN is “brushes” and the product is ranked 293 for “brushes”.

If I change the variable from “child” to “top”, it looks like this.

=GORILLA_RANKING("B00123123","US","top","r_cat")

The product’s top level category is health and beauty with a sales rank of 22,634.

Here’s another example where you can use ranges to pull BSR in bulk. Even if you have 1000 SKUs, you can use this method.

=GORILLA_RANKING(A5:A15, "US", "child","r_full")
Pull Bulk BSR into Google Sheets
Pull Bulk BSR into Google Sheets
  1. SKUs are entered into A5:A15.
  2. Pulling rank for US marketplace
  3. Pull the child category
  4. Display using the full category tree

Notice that there is a SKU/ASIN that does not have a rank. It can mean a few things like it’s new, Amazon ranking algorithm has not picked it up yet or has been deleted from your account and is no longer supported.

How to get historical BSR for FBA

Another feature that we have is to pull a daily list of historical BSR (Best Sellers Rank) over any time period for a single or range of SKUs.

This method only works for a single marketplace at a time. So if you are trying to pull BSR for all EU marketplaces, it will not work. You have to define each marketplace at a time.

show bsr category

Here’s the syntax:

=GORILLA_RANKHIST(period, sku, [marketplace], [queryType], [start_date]. [end_date])
  • Period – required. We offer over 50 preset time periods to use like “last month”, “last 7 days”, YYYYQ1, YYYY-MM.
  • SKU – required
  • Marketplace – optional. Use to filter based on marketplace like US, CA, DE, AE etc.
  • queryType – optional. Control whether to pull the rank based on the top category or child category.
  • start_date – optional. Use YYYY-MM-DD format.
  • end_date – optional. Use YYYY-MM-DD format.

Here’s an example and the result.

=GORILLA_RANKHIST("last 7 days", B1, "US","top")
historical bsr table
historical BSR table
  1. Getting the rank from the “last 7 days”
  2. Cell B1 is where I have my SKU
  3. US marketplace
  4. Top category only

The next example has a slight change to the preset time period and downloading the child category.

=GORILLA_RANKHIST("last month", B1, "US","child")

sales rank history fba month

Getting bulk historical FBA best sellers rank is just as easy.

I have 5 SKUs in cell A36 to A40. Using a closed range formula, I can do something like this.

=GORILLA_RANKHIST("last 7 days", A36:A40, "US","top")
bulk historical bsr fba products
bulk historical BSR FBA products | click to enlarge

The result is an automatic table of the sales rank over the last 7 days for each of the SKUs.

With this one line, you can generate a table of BSR values in seconds

All that with convenient autogenerated headings, plus categories and exact dates and ranking numbers.

Using Gorilla ROI, you are given so much flexibility and freedom to get the data you want and use it any way you need.

Download a free BSR rank tracking spreadsheet

Now that you see the power and flexibility of using Gorilla ROI ranking and rankhist functions here’s a free Amazon rank tracking template you can download or copy straight to your Google sheets account.

The formulas and functions have been entered for you and after you’ve installed the Gorilla addon and activate it.

Give it 1 day to collect the data as the rank is gathered at the end of each day. Then refresh the sheet the next day and it will load it with your data.

free best sellers rank tracking spreadsheet
Free best sellers rank tracking spreadsheet | Click to load and copy to your account

These functions are just two of the many functions Gorilla ROI supports. You can check out the functions list page to see what creative spreadsheets you can make with Gorila ROI.

References

What is Gorilla ROI?

Gorilla ROI automatically pulls Amazon data into Google sheets for you to make sense of the data without the inconvenience of manually downloading, sorting and updating spreadsheets.

Learn how you can centralize your data and use it to increase your ROI.

Leave a Reply

Your email address will not be published. Required fields are marked *