How to pull lowest price and offerings from your Amazon listing

What you’ll learn:

  • How to pull your lowest price
  • How to get the lowest price offered on your SKU’s
  • How to see the lowest offerings any of your products

Getting the lowest listed price on your product

This works the best if you are reselling items and competing with other sellers for the buy box.

Without leaving Google Sheets, you can pull the price being offered on the listing using 2 methods we provide at Gorilla ROI.

=GORILLA_MYPRICE( SKU )
=GORILLA_LOWESTOFFER( SKU )

GORILLA_MYPRICE will display your price only.

GORILLA_LOWESTOFFER also includes prices from other sellers.

If you did something like:

=GORILLA_MYPRICE("839-JDK-93K")

where the SKU (839-JDK-93K) has 5 other sellers, then it would show the price that you are selling for. If you have a sale price set up, this function will display your current sale price that will show up in the buy box.

=GORILLA_LOWESTOFFER("839-JDK-93K")

This function will return the lowest price that is currently being offered. This is not the buy box price. Another offer could be lower but not be in the buy box based on Amazon’s algorithm.

To view all the sellers listed on this SKU, enter more variables to define what you want to see.

=GORILLA_LOWESTOFFER("839-JDK-93K", "default", "fullwithheader")

This is the easiest example to display all sellers where;

  • the SKU “839-JDK-93K”
  • default marketplace (US, UK or whatever your main marketplace is)
  • will display all the headings in a table format

gorilla lowest offer

If there are multiple sellers for the SKU, this formula will create a row for each seller and display the full stats of each seller.

“Default” refers to your main marketplace.

“Fullwithheader” creates another row before the results with the table headers to make it easy to identify.

Combine MYPRICE and LOWESTOFFER

A good tip is to use both MYPRICE and LOWESTOFFER together in a spreadsheet side by side.

Let’s say you load myprice for all your SKU’s in column A.

You then load the lowestoffer for all your SKU’s in column B.

If you can see and compare the price from column A and B, you know:

  • you are not the cheapest
  • or your private label listing may have been hijacked
  • who the other sellers are and how many you are competing against

Spreadsheet Template Available for Members

free template for MyPrice and Lowest Offer
free template for MyPrice and Lowest Offer

Click the image above to see a preview of how it looks.

This spreadsheet is available to all Gorilla ROI members.

It’s available at Your Account > Basic Spreadsheets > BASIC] AMZN Reviews and Prices.

How I created this spreadsheet

The spreadsheet uses a mix of:

  • GORILLA_MYPRICE
  • GORILLA_LOWESTPRICE
  • GORILLA_REVIEWS
  • GORILLA_RATINGS
  • GORILLA_SALESCOUNT
  • GORILLA_CATEGORY

GORILLA_MYPRICE is used to bring in my price that I am selling for.

GORILLA_LOWESTPRICE pulls in the lowest price from the listing.

GORILLA_REVIEWS gets the user reviews for the product.

GORILLA_RATINGS displays the average review rating.

GORILLA_SALESCOUNT is used to get the last 30 days of units sold for a better dashboard.

GORILLA_CATEGORY grabs the product category for easier filtering.

All the data is then loaded automatically to create an all round dashboard that provides more context than the standard pages that you see on Amazon’s backend.

Short video explainer of the spreadsheet

To get a quick idea of how the spreadsheet is set up and work, watch the video below.

Software and tools we use to grow our FBA business

What you’ll learn

  • How we optimize our operations
  • Which tools that are a must have for us
  • How our team uses these tools for Amazon

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 software and 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

Surprisingly we don’t use many 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.

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 for 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 – NONE

Used to do promotions back in the day, but stopped and no longer use any services.

Product launches are a waste of time for us. Amazon is cracking down anyways and I’d rather let the shady China sellers get banned. Same with giveaways.

There are proper ways of doing it, but for what we sell, I’ve found that I can get the same results or more by spending more on PPC or reducing the price initially and then ramping it slowly up.

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.

Designs

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

No software or expensive agencies.

Just a quality designer that we found proactively using upwork and dribbble.com

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


This is where things get different.

Shopping cart – Prestashop moving to 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.

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.

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 ecommerce 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, Continual.ly, Freshdesk

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

Recently I got a no brainer lifetime deal for Continual.ly through Appsumo.com

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 Authorize.net.

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.

Very reliable, very affordable, very good speeds and each of my sites are 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.

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 “[email protected]
  • 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 a customer messages us directly, it is directed to a a single 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 which 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 it 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 up 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.

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

If you have some cool implementations, please share!

4 ways to conquer Amazon with Gorilla ROI

What you’ll learn:

  • How to detect hijackers
  • How to send daily reports automatically
  • How to monitor reviews and save $$$
  • How to forecast 3 months of inventory

Tip #1: Hijacker detection system

Normally, we don’t get hijackers on our listings.

If we do, it’s usually the weak kind. The type of seller who folds their card as soon as we send them a message to get off.

This week is different.

This is the type of seller you don’t want to deal with directly.

You’re probably aware that China sellers have a network of people and services they can use to attack and damage your business.

This seller isn’t attaching himself to directly to our product and selling fakes – which is a relief. Instead, he is creating variations under our listing and trying to leech off our stellar reviews.

Luckily, I was able to detect something was strange within a couple of hours and went into action.

Here’s a basic spreadsheet I have set up for this exact reason.

basic spreadsheet to detect hijackers
basic spreadsheet to detect hijackers

The only thing this spreadsheet does is to let me know whether my title and image has changed without my consent.

When malicious hijackers targets your listing, they will always

  1. change your image or
  2. change your title

Since we don’t have thousands of SKU’s to deal with, I scroll down and found that some of our images have changed. In the example above, if my helmet image suddenly changed into an electric dog collar, I immediately know something has been changed.

Next I quickly check if the title is different or not.

I set up a simple conditional formatting where I manually entered my original title in column C. Then in column D, I have Gorilla ROI load my titles as it appears on Amazon.

Column E is a simple formula to check if it is the same or not.

=C2=D2

If it is the same, the value returned is “true”. If it returns “false” the cell changes to red for immediate attention.

How to create this spreadsheet yourself

Load the product thumbnail with

=image(GORILLA_PRODUCT("SKUorASIN", "smallimage_url"))

Enter the correct title manually into column C.

Then in column D, use the formula:

=GORILLA_PRODUCT("SKUorASIN", "title")

Column E is a simple formula to check whether the titles match each other.

=C2=D2

Add some conditional formatting and you’ve got yourself a simple, flexible and customizable hijacker monitoring system.

Tip #2: Send yourself daily reports

If you create a spreadsheet like the one above but don’t want to open it every hour, there’s a feature in the addon where you can send yourself a PDF export of the spreadsheet to your Google email.

Go to menu > Add-ons > Gorilla Sheets > Send Spreadsheet as PDF

send spreadsheet as PDF
send spreadsheet as PDF

Make sure to enter your Google email used to install the add-on.

It will NOT send to any other email than yourself to prevent the function being used in a spammy way.

Save the settings, and get your daily report automatically and stay on top of your business.

Another automation tip is to create a filter in your gmail so that this daily email is then forwarded to your team.

Tip #3: Monitor reviews and save $$$

I don’t know anyone who enjoys 1-star reviews.

And let’s face it.

Amazon customers are a different breed in terms of pickiness and entitlement. So staying on top of reviews and being proactive to rectify situations and to please customers can go a long way.

We’ve found that even commenting on negative reviews with a helpful response does wonders to inspire confidence for other buyers. The great thing with leaving responses to reviews is that you get the last word.

Customers don’t come back to their reviews and Amazon does not notify them of a response.

Use the simple formula below to create a basic review monitoring spreadsheet.

=GORILLA_REVIEW("SKUorASIN" ,"US", 3)

This formula is telling GorillaROI to pull the Amazon review for your SKU or ASIN, in the US marketplace and then display the latest 3. You can enter whatever number you want at the end. Change it to 10 and it will display the last 10 to your spreadsheet.

Apply some basic conditional formatting for instant magic.

pull and monitor Amazon reviews
pull and monitor Amazon reviews

If cost-cutting and saving money is your thing, this spreadsheet means you can probably cancel the review pulling service that costs at least $100/mo.

Tip #4: Forecast inventory and prevent stock out

Going out of stock is another way of losing money that sellers don’t think about.

If a product goes out stock:

  • it loses sales velocity and a competing product will take its place
  • you have to increase your PPC bid in order to regain your original sales rank and position
  • you may have to spend more to get it delivered quicker
  • you lose revenue

If it’s one of your best sellers, it quickly becomes a lose-lose-lose situation.

To forecast your future inventory requirements and sales, you can look up your sales figures from the same time last month, or last quarter or last year and then project it forward.

Here are two basic methods of a 3 month simple Amazon inventory forecasting.

Method 1.

Get the total units sold throughout the last 3 months.

=GORILLA_SALESCOUNT("LAST 3 MONTHS", "US", "SKU", "ALL")

Multiply the number by 3 to get a linear forecast based on the past 3 months. Compare with your current numbers and order as needed.

Method 2.

  1. Get the last 7 days of units sold and divide by 7 to get the average daily units sold.
    =GORILLA_SALESCOUNT("LAST 7 DAYS", "US", "SKU", "ALL") / 7
  2. Get the last 30 days of units sold and divide by 30 to get the average daily units sold.
    =GORILLA_SALESCOUNT("LAST 30 DAYS", "US", "SKU", "ALL") / 30
  3. Get the last 90 days of units sold and divide by 90 to get the average daily units sold.
    =GORILLA_SALESCOUNT("LAST 3 MONTHS", "US", "SKU", "ALL") / 90
  4. Calculate the average of steps 1, 2 and 3 to get a smooth daily number of units sold.
    =AVERAGE( numbers from 1,2,3)
  5. Multiply the number from step 4 by 90 to get a forecasted 3 month projection.
    = number from step 4 x 90

There is so much more you can do with your seller data once you import it into Google Sheets.

A more advance version is to forecast by seasonality. That is something our forecasting calculator (free preview) does for you if own the Business PRO Package.

sales forecast dashboard
seasonally adjusted forecasting calculator – click to enlarge

If you know spreadsheets, let your creativity roam.

  • Use the data to create beautiful dashboards
  • Create automatic sales report generation
  • Monitor and track sales by month and see your growth
  • Utilize advanced spreadsheet formulas to perform spreadsheet magic everywhere you go.

Best practices when creating spreadsheets (for the non-techie)

What you’ll learn:

  • What you should do to create organized spreadsheets
  • The architecture of building an organized spreadsheet
  • The different functions you can use to eliminate work
  • Where you can get free spreadsheets for FBA

When was the last time you went to a restaurant and was seated in the middle of the kitchen?

Never.

The same concept should be applied when creating spreadsheets and organizing data, yet the most common thing people do is “eat in the kitchen” at a restaurant.

Anyone can do spreadsheets, but not many people can do them right.

And I’m going to share a simple way to organize and create spreadsheets that will make it easier to manage, update, scale and share.

The old way of having all the data, calculations, results and everything else jumbled up in the same sheet should be a thing of the past.

Let’s get right into it.

The restaurant technique to creating spreadsheets

Setting up a spreadsheet is no different than how a restaurant is set up.

A restaurant can be simply divided into two sections.

    1. The kitchen (back end or data side)
    2. The dining area (front end or display/user side)

All the magic happens in the kitchen (back end), and it’s either a hot mess or a well-oiled machine. In any case, it’s hectic back there – but that’s ok because it’s where all the raw data and calculations come to life. Not for people to see.

The dining area (front end) is what people see. It should be clean, well laid out and easy to navigate. No one likes cluttered, dirty, dusty tables, plates and menus.

Keep this concept in mind when creating your spreadsheets to analyze data – especially Amazon FBA data as there is a ton of information in there.

Why separation is vital

Take a look at the screenshot below of a basic spreadsheet included with Gorilla Sheets Addon.

You’ll see multiple sheets (or tabs) to separate the backend and frontend.
spreadsheet backend frontend

In the inputs tab, information used to plug into formulas are entered here. In our case, things like the number of products, our seller ID and today’s date are stored in a central location.

The formulas then reference the required data point from this tab. Now I don’t have to manually enter the data point into every formula.

Imagine the mess and headache if you enter the product ASIN into 20 different formulas and then have to update it later.

Here’s another example.

spreadsheet frontend backend 1

In this spreadsheet, I have a total of 4 sheets within the spreadsheet.

  • DASHBOARD – front end
  • INVENTORY – front end
  • product_details – back end
  • Inputs – back end

For this spreadsheet, I enter our Amazon FBA product details like SKU, ASIN, UPC, dimensions, weight, units per case and other data. No product information is ever entered outside of this tab.

Inputs sheet is the same as mentioned above where it holds generic but important data like today’s date, seller ID, marketplace ID, counts the number of SKU’s entered into product_details.

The front end dashboard and inventory sheet then pull information from the back end sheets to create a simple and nice looking dashboard.

Something like this Amazon FBA sales dashboard.

dashboard sales fba spreadsheet
dashboard sales fba spreadsheet

Or how about this Amazon fees and services dashboard that organizes what Amazon charges us.

Amazon fees and charges of services
Amazon fees and charges of services

The last thing I want to do is update the formulas every time I import new data.

As you can see, this simple practice will 10x your productivity and make your life easier.

  1. It separates the raw data with the rest of the spreadsheet. Changing the raw data will auto update the rest of the spreadsheet. Not in 10 different places scattered throughout the spreadsheet.
  2. It makes it easy to manage and scale a growing spreadsheet. The data is stored in a consistent and centralized place in your and not scattered everywhere.
  3. Other people will be able to work on the spreadsheet without messing things up.
  4. Advanced tip: you can break up the data even further if you have multiple data sources. Feed it into its own tab, and then combine a “master” data sheet by combining the data from the other tabs. You can use QUERY() or VLOOKUP() or FILTER() to consolidate the data into a master tab.

Another example of how it works

To take it one step further, I like to organize my spreadsheets like I’m running a restaurant with multiple line cooks. Each line cook does one part of the big picture. One person does meat, one person does fish, one person does appetizers and so on.

In this way, I set up my spreadsheet where each tab specializes in one area and I split it up into a back end and front end.

If I have a spreadsheet where I have information related to:

  • sales
  • inventory
  • reviews
  • product info

I create backend sheets called:

  • sales_data
  • inventory_data
  • reviews_data
  • productinfo_data

In my old inventory spreadsheet, I used to house all the inventory data, formulas and final information into the single “inventory” sheet. As more information was added, it became a nightmare to fix and change.

Simple maintenance went from a few minutes to 30 minutes to 1 hour. Then I stopped using it because it was too much of a headache. If I changed something or added more data, it either broke everything or I lost track of all the different cells that needed updating.

Spreadsheets are supposed to help, not to become a hindrance.

Now, I separate inventory by loading all the raw inventory data into inventory_data. This data sheet normally has thousands of data points like

  • units in stock
  • units in transit
  • units sold last 7, 14, 30 days
  • units sold 1 year ago, same day last week, same day last year etc

Then on the “inventory” sheet, I can pick and pull what data I need to display by using vlookups, index, or ther forumulas and loading them into the frontend sheet to make it look clean and nice.

Here’s a screenshot of the “inventory_data”.

inventory data screen
inventory_data housing the raw data – click to enlarge

And this data sheet is then used to create my nice and clean “inventory” sheet to track the important data and stats for myself and our team.

You can see what a difference it makes.

inventory sheet screen
clean and simplified inventory snapshot for our team

By separating and organizing how data is stored and referenced, you can now focus on analyzing the data.

To update the data, all it takes is for us to either bulk copy and paste in one go, or to use GorillaROI addon to auto load and update the data.

How to get data into the “Front end” sheet?

You can pull the data from your raw data section by using plain ol’ Google spreadsheet formulas.

Vlookup() is a classic staple, which I won’t go into here as there are literally thousands of examples on how to use it.

Index() and Match() are under-rated options but very powerful methods. Good examples and explanations in this link.

Importrange() is one of my favorites because it’s so easy and loads quickly.

You can load entire sheets from a different file into your current spreadsheet. If I’m working with a monster spreadsheet, I import multiple different spreadsheets into a single sheet. Here’s the page from Google on how to use it.

Query() is an advanced function that I use to pull and display data directly.

If you look at any of the dashboards for the spreadsheets included with GorillaROI, it’s what I use to quickly build nice looking tables without having to mess with too many vlookups.

There’s nothing complicated about these formulas or this method of creating spreadsheets.

But it does require more “prep” and thought and that’s the biggest hurdle when it comes to making spreadsheets. It’s so easy to start plugging data and calculations anywhere rather than taking 15 minutes to understand what you are trying to do.

Once done right, you’ll be asking why you’ve been doing it any other way.

Free spreadsheets for Amazon sellers

If you don’t want to deal with all the stuff I’m talking about here and just want some templates to edit and work off, I have put a list of free fba Amazon spreadsheet templates for you already.

Follow the link above and you can download spreadsheets for:

  • Detail child item sales analysis
  • Amazon PPC Customer Search keyword analysis dashboard
  • Amazon PPC Targeting Keyword analysis dashboard
  • Seller sales performance stats
  • Product listings information template
  • Sale & traffic analysis dashboard

Combine it with the free Addon to see how powerful it can be.

How to protect your FBA business from China and copycats – Part 2

Here’s part 1 of this series.

None of this is groundbreaking stuff. But we’ve found that creating even one extra step is enough to deter competitors from trying to copy our products.

What we do to protect our business from China and copycats

protect your FBA business with these strategies
protect your FBA business with these strategies

Our framework for new products and business is to:

  • get signed legal documents in Chinese and English like a NNN, supplier agreements, vendor scorecards
  • spend more upfront to create barriers of entry
  • over-engineer
  • find backup factories we can switch to
  • file design and utility patents in the US and China
  • create culture related items that China won’t catch onto quickly
  • leverage established brands

You’d think we would learn our lessons after receiving a bad shipment with 100% defective units.

Not so.

We made the same mistake 2 more times for a total of 30,000 defective units.

Many suppliers are great to deal with. The quality and standards have improved greatly over the years, but you still come across enough bad apples to stay cautious

I’ll take you in more detail through each of the steps outlined above.

Legal documents

One of the areas we neglected was getting legal documents signed.

“Guangxi” (building relationships and goodwill) was how we did it with our initial suppliers. No official documents, no details of what each party would be responsible if something were to happen.

Came back to bite us as the first supplier blamed us for all the defective units.

We were at fault for sure…. for trusting their word.

Get a Chinese English NNN signed

chinese english nnn agreement template
chinese english nnn agreement template

As we manufacture unique products with our designs and mechanisms, we now implement a legal process where we get manufacturers to sign a Chinese and English NNN (Non-Disclosure, Non-Compete, Non-Circumvent) before we disclose anything.

Getting a high ranking person to sign the document or get the company seal on it. Not the sales rep’s signature who holds no responsibility. Getting the owner to sign is the best think you can do to protect your business and ideas.

What we’ve found is that the good and honest manufacturers keep it simple and will comply. It’s nothing new or wrong for them. They also see that:

  1. you are serious
  2. they go through the document, ask questions and want to negotiate on clauses
  3. they sign and return the doc with minimal fuss

The bad ones are easy to identify.

The bad manufacturers:

  1. make a big deal out of it and say all the buzzwords like we’ll never do this and that
  2. they take a long time to sign or make it look like they forgot about it
  3. if it is signed, an entry level sales rep is the signer which is useless

Get a Chinese English manufacturing agreement signed

chinese english PO package for manufacturing
chinese english PO package for manufacturing

If we find a good manufacturer and the project continues to move along smoothly and we get to the point of wanting to manufacture with the supplier, we then send over a manufacturing agreement which details all the different clauses and specifics you’d want to discuss before you start anything.

We use a 9-page document in simple language, stating the terms and conditions in Chinese and English.

Getting a lawyer to draft up agreements is great, but getting the Chinese supplier to go through each draft revision, give feedback, negotiate terms, or even get back to you in a timely manner is going to be a nightmare.

Try to push them with western style contract negotiation and you’ve lost a potential manufacturer. It’s important to remember that manufacturers don’t have law departments like in the US.

This is why having a very simple and easy to understand document that everyone agrees on is critical to having a successful relationship.

With China, the manufacturing relationship is expected to be a lot like a marriage, where if troubles come up, it is expected that you’d work through it together.

Compare that to the western culture where if you receive a shipment of defective items, you sue, or get money back somehow and then the business relationship is called off.

If you are a lawyer reading this, keep in mind that I am speaking from a practical viewpoint of being in the trenches. 9 out of 10 people who run an Amazon FBA business do not generate $100M in revenue where a contract scrutinizing every word is vital.

For the majority of people, using common sense, putting guardrails in place and doing business is what is needed.

Spending more upfront to create entry barriers

One common theme of copycats is speed over quality and investment. They will try to make a cheaper, crappier version as quick as possible with short-term profit as a goal.

Our first hit product that was copied from head to toe came out in 2 months after we launched.

The copied version was thin and flimsy, used cheap materials, but sold at 30% of our price. Despite what consumers say about wanting “quality” or “made in USA”, the wallet does the talking and people wanted cheap first and foremost.

We now invest a little more upfront to make it harder to copy.

For example, if we were making pots, we’d spend a little extra to create a unique handle or lid that would require a new mold. We’ll make this feature pop in our sales pages so that buyers can see how it is different and unique.

We never sell OEM or straight private label.

Aiming for ODM products works for us because we follow the first step of finding a trustworthy supplier and getting documents signed to protect our product.

This way, if a copycat were to buy our product and ship it to China to copy, they will have to invest in a new mold themselves.

With our first product that got copied, we found that the manufacturer did not maintain the mold in their facility. It was held at a 3rd party mold company and our mold was being shopped around to other manufacturers for other copycats to use.

Over-engineer the product

Until the early ’90s, German cars would last forever.

They over-engineered their engines and cars so that it was built to last – like a tank. Nowadays, everything has been peeled back in order to save cost and that’s why many German luxury brands have bad reliability.

We purposely try to over-engineer where it makes sense to focus on quality, durability and to make customers super proud and pleased with their purchase.

We have not heard a single customer complain our products lasted too long.

The downside is that our customers won’t buy from us that often. But this has helped us to get plenty of word of mouth referral business.

Because we also try to apply special manufacturing techniques and materials, it makes it cost prohibitive for cheap copycats to steal our show. They would have to first find our manufacturer, come up with a variant of our design and then order enough volume for their cost to come down to where we are.

Cheap versions of anything will always surface. We just don’t want cheap and fake versions being listed on our listings.

So far, this strategy has also helped us as we are able to thrive in a competitive category as the Chinese just cannot copy it.

Find backup manufacturers

We’ve experienced China’s “quality fade” on so many occasions.

This is where the quality will gradually degrade over time. Manufacturers are all head over heels with new business, but they will start to find new ways to shave cost in order to increase their margin while you believe it is the same product.

If you sold a bag of 1,000 gummy bears, the manufacturer may slowly reduce the quantity from 1,000 to 998 to 990 until a buyer counts it and leaves a scathing 1 star review.

This happened to us again just a few months ago.

Luckily, we went directly to the factory to inspect and monitor as it was a brand new product series.

We caught them red-handed and we got the factory to redo it 3 times. They throw their hands up in the air and say how unfair we are or how strict we have become.

Bad manufacturers don’t understand that it is always cheaper to make it properly the first time. Instead they cut corners, then claim your quality standards are too high and will raise prices on your.

I show them the original approved samples but they still make excuses.

Luckily, we had sourced a backup factory from a trade show we visited in Chicago.

Side note: the good and legit manufacturers also attend US trade shows. Easiest way to find good suppliers.

With this leverage, we informed our current factory they have 1 final chance. Screw it up any further and they lose every current and future business.

They got the message, but in our next order, they screwed it up again and so we are switching everything over to the new manufacturer.

Keep this rule in mind.

Don’t believe what manufacturers say and only believe half of what you see.

Design and utility patents

Most of our products now has a design or utility patent. You’d be surprised at what you can patent.

My initial mistake was thinking that patents were expensive, took forever and had to be totally unique and never done before.

Nope.

My patent guy was quick to point out that every invention is an improvement of something else. Rarely do you see something that’s never been done before.

Some claims will be rejected if it’s not a clear improvement over an existing solution. But isn’t difficult to come up with a unique design or mechanical aspect to patent.

To date, we have 6 patents issued that cover a wide range of our products.

At first, I thought design patents were weak and a waste of money. But if I can protect a product from being copied for $500, my question will always be “where do I sign?”.

Although design patents can easily be maneuvered around, it can help to delay copycats or used to scare them off. Amazon doesn’t care about protecting your products, so it helps to ward off potential copycats.

It also makes business life much easier.

I don’t have to constantly look over my shoulder and see who is copying our stuff. I can focus on coming up with new products, developing, testing and improving our processes. Not getting stuck in the weeds of having to send messages back and forth with seller support.

Using a patent lawyer will set you back at a minimum of $1,000. But filing a patent does not have to break your budget.

see many people try to save a few cents by using Google Patents. If you know exactly what do and how wide you need to search, go for it. But I advise against it, even for seasoned sellers.

The reason is that patents are not “binary”. What I mean is, a patent won’t be issued just because your exact design doesn’t exist.

If there are different inventions along similar concepts or ideas, classified under different terms and names, your patent will not be issued.

This is the hard part that most folks miss out.

Let’s say I’ve designed a yoga mat. I obviously can’t patent a yoga mat. But what if I created a built-in strap and bag into the yoga mat.

The built-in strap part likely won’t be patentable because the patent officer could believe it’s a natural extension of the yoga mat. So that one has a high chance of getting denied.

But what about a built-in bag? Not a separate yoga bag, but one that is directly attached to it.

There’s a good chance this is possible, but to make sure, I’d have to dig deep into the patent office to make sure there is nothing like that.

This is where you’d have to search and go through the patents of things like a foldable poncho, foldable tote bag, foldable jacket etc and make a case that there is no such thing as a built-in foldable bag in the yoga category.

And so far, we have a 100% success rate.

If you can afford a lawyer, that’s great, but if you can’t, for a few hundred dollars, you can find a patent agent with extensive experience to do all the legwork for you from detailed searches to recommendations, to submissions and follow-ups.

Using a patent agent should be a flat fee of around $150-$250 for detailed searches and $300-$550 for patent submissions depending on the complexity.

Recently we also started filing patents in China. The process is just as simple. There are plenty of patent agents in China who will guide you through the entire process, from choosing a Chinese name for yourself and your company, what documents to provide, what to sign and so on.

Or you can contact us and we’ll get your patents filed and submitted on your behalf hassle and worry free.

Because China is a first to file system, the quicker you can submit a patent, the better.

Why do you need a patent in China?

No one will be able to make and sell your products in China. There is the issue of having to go after infringers, but placing protection around your ideas in China comes out on top if you ask me.

If you end up ordering 10,000 units and spend $1,000 on patents, it’s an extra $0.10 per unit for the initial order. But it’s also an asset and will pay for itself 10x.

For that extra $0.10, we get:

  • peace of mind (not 100%, but still..)
  • scare away competition
  • can put “patent pending” or “patented” on our listing and product

A small upfront investment can yield huge ROI in intangibles for long-term business.

Create culture related items

I’ll explain this with a story.

It was the middle of summer and we made a trip to China for an inspection. It’s hot and humid where our supplier is. We are working out of this empty, dusty warehouse and ask for some cold water.

The person comes back with a kettle.

A kettle of hot water.

It’s 90-100 degrees F (32-37 Celsius) with humidity at 95%. I’m literally a walking fountain because I’m dripping in sweat.

But the Chinese believe that drinking cold water is unhealthy. It creates an imbalance between your body temperature and outside temperature.

Now if I were to sell ice cold water bottles in China, I’ll go bankrupt. It’s not a cultural fit.

In order to stay ahead of China, creating items that Chinese people don’t understand gives you a head start on the competition.

Oh sure, they will create imitations, but if they don’t understand what they are making and why, quality will be poor and reviews will be bad.

Poo-Pourri is an example. It’s a toilet spray to deodorize and hide your poo smell.

I have yet to see a single Chinese seller make their own version.

Mainly because:

  1. the Chinese are very weak with formulation
  2. they won’t have proper SDS documents that meet US OSHA requirements
  3. they don’t understand why Americans would want such a thing

If you can find or create products that are outside of China culturally, you have a great chance of succeeding without getting sabotaged or flooded with copies and fakes.

Leverage established brands

Probably one of the easiest ways to beat the competition is an old strategy, but still a very good one.

The truth is unless you are spending millions on advertising or getting millions of visits to your site a month, you don’t have a brand.

However, if you can get a reseller or wholesale account with a real brand and manufacturer and create bundles, it gives your product instant cred.

Think about it.

If you are able to package your product with a well-known product, the consumer will associate your product with the big brand.

My wife just purchased a couple of fitness trackers. But let’s say she sells bands for fitness trackers.

And after networking at trade shows, she manages to become a reseller of Fitbit.

But instead of just selling a single Fitbit, she offers 2 or 3 wristbands the consumer can choose to customize.

The Fitbit example isn’t a great one, but you get the point. It doesn’t have to be a huge name. Just a well known one from within your industry.

Our #1 best seller is actually directly based on this tactic.

Because the name is already recognizable, our ACoS for this product is our lowest. So we get volume and spend less because the marketing has already been done for us.

The great thing?

  • China can’t get these branded products because they are overseas
  • Newbie sellers focus on what their scouting software tells them to do

So there you have it. There’s nothing groundbreaking here.

Just a lot of hard work but it gets easier and quicker as you do more.

Well worth it in the end.

How to protect your FBA business from China and copycats – Part 1

Imagine every product you source turns into a winner becomes the best seller in the category.

Unfortunately, that’s not how it works.

We’ve gone through countless painful steps in our FBA journey.

Our painful product experience

How it feels when you get 100% defects
How it feels when you get 100% defects

Here’s a short list of credentials attached to our business.

  • I’ve ordered 10,000 units which were all 100% defective, which we fixed by hand at home, garage and anywhere we could.
  • I reordered and another 10,000 defects came.
  • After 2 months of selling, people were selling an exact copy of our product.
  • I found people posing as buyers in order to find our supplier.
  • I was in a battle with China sellers and I ran away with my tail between my legs.
  • Our mold was shopped around from factory to factory.
  • Our supplier refused to send the mold we owned, to our new supplier.

The worst thing?

Everything noted above was for a single product.

What happens if you don’t document everything

Amazon business mistakes
Amazon business mistakes

With little to no standard operating procedures (SOP), we made the same mistake multiple times because we didn’t want to put in the little effort to document and make checklists.

Such a simple task ended up costing us over $20k of product and labor, not to mention overhead and other soft costs associated with this fiasco.

When first shipment of defects came in, that was our fault for not sending an inspection company to verify the QC.

So for the second order, we figured out a way to eliminate a lot of the manual process that was causing the issue.

Doh…

It wasn’t made to spec again and so we ended up with another 10,000 units of defects.

Because I didn’t understand the Chinese culture of doing business, I applied western thinking to the business relationship.

“I’m sure it won’t happen again.”

“I’ve explained our standards to them and they said they understood.”

Those two thoughts have cost us tens of thousands of dollars.

It’s why we now meticulously enforce suppliers to sign Chinese and English NNN agreements and a detailed manufacturing agreement covering our expectations and how issues will be handled.

With all these mistakes and heartbreak under our belt, here’s a framework of how we protect our business and products from China suppliers and sellers.

I call it a framework and not a checklist because it has to be adapted for each situation and business.

Understand China’s strength and weakness

Our core goal is to create products that China sellers will have a hard time copying. They can copy the idea, but not directly rip off our products.

We also want to sell timeless products. Not fad items like shoes with wheels, or fidget spinners and other short term thinking products.

To do this, we try to broadly understand the areas where we have and don’t have an advantage over China.

Where China has an advantage

First, China has strong advantages in:

  • speed
  • copying
  • shipping
  • manufacturing and infrastructure
  • raw materials
  • ignoring rules and regulations

Speed – you can find or produce a product super fast. The China network of suppliers and sub-factories makes it possible to create a new product in 30-60 days.

Copying – send them a sample and they can copy it.

Shipping – very fast and cheap when originating from China to all around the world.

Manufacturing and infrastructure – tons of expertise in all forms of manufacturing. Each region specializes in a certain product/industry.

Raw materials – can find any form of raw material. Makes everything efficient and cost effective when everything can be done in-house in China.

Ignoring rules and regulations – as an exporter they can get away with a lot when doing business internationally. The Lumber Liquidators flooring scandal is an example where the factory was making flooring with extremely high levels of formaldehyde – cancer causing chemicals.

But this isn’t everything because China also has plenty of weaknesses which you can use to your advantage.

China’s weaknesses

China has weaknesses in:

  • low quality (a.k.a if it works, it’s good enough) being the default cultural standard
  • quick cash mentality
  • bad at formulations and liquid products
  • low food quality
  • being across the ocean
  • “Made in China” reputation
  • ignoring rules and regulations

This does not apply to every company, but something to keep in mind. You never know.

Low quality – suppliers will and do cut corners and degrade quality without you noticing in order to shave off cost. This way, they expand their own margins and you don’t know until it’s too late. Rightfully called “quality fade“.

Quick cash – focus is on getting money now, instead of a long term relationship where everyone wins.

Formulations, liquid, food products – there is no way I’ll knowingly buy made in China food, supplements or liquid products. Same with beauty, pet food or other liquid products requiring formulation and quality control. I’ve never heard a woman say they want the latest made in China lipstick.

Being across the ocean – If a product is complex and expensive, it can be cheaper to manufacture in the US. Make the mold in China and have it shipped to your country. Then you get the Made in USA or Germany label with lower costs, lower transport fees, taxes etc.

Made in China – Despite some of the best and expensive products being made in China (iPhone and many electronics, furniture), their reputation won’t go away anytime soon.

Ignoring rules and regulations: Many baby products require documentation and certifications in order to sell in the US. Other health related products governed by the FDA all require certification and documentation. Many factories will claim they are compliant, but their certification expired 5 years ago, or it’s for a different product line.

What we do to protect our business from China and copycats

With all the hair pulling losses we’ve experienced, our framework for new products and business is to:

  • sign legal documents in Chinese and English like NNN, supplier agreements, vendor scorecards
  • spend more upfront to create barriers of entry like quality molds, applying for patents
  • over-engineer the product where it makes sense
  • find backup factories we can switch to
  • file trademarks, design and utility patents in the US and China
  • create culture related items that China won’t catch onto quickly – organic food, liquids, service related
  • leverage established brands in the US – wholesaling

In part 2, I go through the points above in detail and how it protects us from China and copycats.

Get historical BSR rank for any custom time period

You can now get historical BSR rankings using:

=GORILLA_RANKHIST( )

This is super useful if you want to create your own charts and keep an eye on trends.

  • You can create a chart of the last 30 days to see whether there is any sudden spike or drop off.
  • You can create a chart based on seasonal periods to help you predict your sales and inventory.
  • Or create a chart of this year compared to last year to see how your products have performed.

Don’t forget, you can already get daily and live BSR using

=gorilla_ranking()

The latest list of functions is available at the Amazon Google Functions page or from the documentation.

GORILLA_RANKHIST( ) function

Get the daily historical BSR (Best Seller Rank) over any time period for a single or range of sku’s.

Only works for single marketplaces at a time. You can not grab the historical BSR rank for all marketplaces you sell in from a single formula.

historical BSR rank
historical BSR rank

The syntax is:

=GORILLA_RANKHIST(period, sku, [marketplace], [queryType], [start_date], [end_date])

period
Predefined periods and any custom date frames to get the rank history. See full list of predefined periods below.

sku
SKU or ASIN of the product. Can be a range like A2:A100 or an array of strings like {“SKU12345″,”ASIN12345”} or a string like “A734-FBE4-MDUS”.

marketplace
OPTIONAL – Marketplace filter. Use Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.

queryType
OPTIONAL – What to retrieve (defaults to “top”):
top = Top Level category;
all = All categories (only 1 SKU/ASIN allowed);
child = Child category.

returnStyle
OPTIONAL – Return style (defaults to “rank”):
rank = Rank Only;
r_cat = Rank + Category (accepts array input);
r_full= Rank + Category Path (accepts array input).

Matrix array inputs like A1:E10 is only accepted for when “rank” is used. Cannot use arrays for r_cat and r_full.

historical BSR rank in action
historical BSR rank in action

Examples of getting historical BSR rank

Use closed ranges (e.g.A2:A100) in your formula wherever possible for quicker results and to prevent Google blocking your account for 24 hrs due to overloading their resources.

=GORILLA_RANKHIST("last 30 days", A2:A100)

Bulk returns the BSR over the preset “last 30 days” for all SKU’s from A2 to A100. No marketplace is specified so the default marketplace for your account is returned.

=GORILLA_RANKHIST("custom", A2:A5, "US", "top", "2018-06-01", "2018-06-30")

Bulk returns BSR from June 1 to June 30 for each SKU from A2 to A5. Also returns the top level category the SKU is located in. USA marketplace is requested.

=GORILLA_RANKHIST("this month", "B00YD545CC", "JP", "child")

Returns the rankings for each day this month for the sku. Requesting Japan marketplace ranking and category will show the top level category as well as the deepest child category.

Like the GORILLA_SALESCOUNT() and GORILLA_SALESTOTAL() functions, you can use the diverse time presets we offer to make it easier.

period Attributes:

  • Today
  • Yesterday
  • Same day last week
  • Same day last month
  • Same day last year
  • Last 7 Days
  • Last 14 Days
  • Last 30 Days
  • This week
  • This month
  • This quarter
  • This year
  • This Year-to-Last-Month
  • Last Week
  • Last Month
  • Last Quarter
  • Last Year
  • Last 60 Days
  • Last 12 Months
  • Last 7 Days Week Ago
  • Last 7 Days Year Ago
  • Last 30 Days Month Ago
  • Last 30 Days Year Ago
  • Last Week Year Ago
  • Last Month Year Ago