Load 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.

The method described is to track your own products that you sell and hold in your inventory in seller central.

This method does NOT work for tracking competitor products or for research.

Only for products in your seller central account.

If you are looking for Amazon seller software for research and to track competitor Best Sellers Rank, the space is filled with tools for this.

Search for “Amazon Best Sellers Rank Tool” and you’ll be given a lot of options.

Our focus however has always been on internal products and operations. Such data is more reliable and is important for you to maintain and grow your business.

Rather than make estimates of what competitors sales are based on the BSR, we give you the data for your own BSR and sales so that it matches up exactly. No guessing.

This way, you can use your BSR and units sold to estimate how much a competitor is selling.

On a different note, if you need to know how many units to sell to break even on new products, check out our break even analysis formula and spreadsheet calculator.

First install the addon to get your Best Seller Rank

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.

The addon is free to use for any 3 SKUs (don’t enter the ASIN).

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

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

The methods described in this article to import your Amazon BSR 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. In this case, it is Grocery and Gourmet Food.

I won’t get into the details of what affects your Amazon ranking because there are many factors involved and it’s a black box. Nobody knows Amazon’s algorithm to rank higher and faster.

But a good chunk of it depends on your:

  • recent sales
  • historical sales
  • conversion rates in comparison to other products in the category

How to download your Amazon best sellers 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 your Amazon product

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

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

=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")

Amazon ranking category in the US market and the child rank for the category

  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")

Load Amazon Best Sellers Rank (BSR) or Sales Rank into Google Sheets 3The 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 auto-generated 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.

What if you want competitor BSR numbers?

Gorilla ROI is not a research or competitor tracking tool.

There are so many tools out there that track competitor BSR that it’s not an area we need to fill. Our focus is on internal operations and products so that you can control and stay ahead with your business and not be overwhelmed playing catch up all the time.

By knowing your own Best Seller Rank, you can detect when something is wrong, you can better understand your product sales trends when you match the BSR to your units sold.

How do others calculate units sold per month using Best Sellers Rank?

With the research focused tools like Junglescout, Sellics, SellerApp, etc offer BSR tracking.

You enter the ASINs you want to track and then it will update daily.

By scraping and utilizing all the data when a user subscribes to their services, the research tools are able to collect and combine the data into their system.

By maintaining a big database, they are able to guess how many units an item has sold based on the product BSR.

It’s not accurate, but it’s good for an approximation. Products in very tight niches have inaccurate data.

Guesstimating the BSR works better for very competitive industries where the data can be cross checked against other products and sellers.

This is one of the reasons why we suggest sellers not to use research and scouting tools on their own products.

Keep your data to yourself. Don’t feed it into scouting databases for millions of other people to see. You only make it easier for others.

Best Seller Rank is not organic rank

The Best Seller Rank is a number that compares your unit sales to competitors.

If you sold 100 units of keyrings, and your BSR is 3,000, this means that you are ranking 3000 in terms of number of units sold.

An organic rank in the Amazon results is where your listing shows up based on the keyword entered.

Organic rank is related to SEO, but it certainly plays a part with BSR. If your product is showing up in the top 10 results for “keyrings”, your BSR will be much higher than if your product is showing up on the 3rd or 4th page of results.

Shoppers simply don’t scroll to the next pages as often. They simply enter a different keyword to find what they are looking for.

Organic ranking is a whole different strategy and is based primarily on:

  • sales
  • conversions

This is why so many black hat sellers manipulate and cheat the system to try and sell more. Whether it be through fake reviews or launch giveaways.

Download a free Amazon 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.

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

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

Resource

Did you also know that you can find profitable niches to sell on Amazon using ZonGuru’s Niche Finder which utilizes powerful keyword data to unlock your next product niche idea. After typing in a keyword, you will instantly see a curated list of profitable niches along with relevant data such as competition, buyer demand, revenue, reviews, search volume, Niche Score, and much more.

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 *

15 − one =