Gorilla ROI helps Amazon sellers and agencies easily load Amazon data inside Google Sheets
Functions to Pull Amazon Data into Google Sheets
Easy to use formulas to pull data from Amazon to Google Sheets
Haven’t joined Gorilla ROI?
ALL MARKETPLACES Supported
Description:
Set your main marketplace with the attributes below. If you sell in multiple marketplaces and want to get the total data, use “ALL” for the marketplace attribute in the functions below.
|
|
|
|
Functions to pull Amazon FBA and FBM Data
Full list of functions
Basic guide to build your custom formulas
Assuming you have authenticated, installed and activated the addon properly following the install guide.
Examples: 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_ASINLIST() If no attributes are entered, it will list all your current active and inactive ASIN from all marketplaces.
=GORILLA_ASINLIST( , , , “FBA”) This will list only the FBA ASINs for the default marketplace for your account as the other attributes are empty.
=GORILLA_ASINLIST( , , “INCLUDE”) This will list the entire list of ASINs in your account including all the deleted, inactive, closed, suppressed products. If no inputs are entered like GORILLA_ASINLIST(), the default value is “EXCLUDE”.
=GORILLA_ASINLIST(“ALL”, “B43”) Lists all ASINs from all marketplaces where the ASIN starts with “B43”.
=GORILLA_ASINLIST(“US” , , “INCLUDE”, “FBM”) This will list all FBM ASINs for the US marketplace only. Will also show closed, inactive, deleted, suppressed ASINs.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_ASINLIST(“SELLER ID”) =GORILLA_ASINLIST(“SELLER ID”, , , “INCLUDE”, “FBM”) =GORILLA_ASINLIST(“SELLER ID”, “ALL”, “B43”) =GORILLA_ASINLIST(“SELLER ID”, “US” , , “INCLUDE”, “FBA”)
|
GORILLA_ASINLIST( ) Function
Description:
Return a list of active and inactive ASINs from your marketplace(s).
Syntax:
GORILLA_ASINLIST([marketplace], [filter_sku], [status], [fulfillment], [sort])
for Gorilla AGENCY, the syntax is
GORILLA_ASINLIST(sellerID, [marketplace], [filter_sku], [status], [fulfillment], [sort])
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
filter_asin – OPTIONAL
Part of an ASIN to filter result. The filter will act only on the start of the ASIN like ASINPART*.
status – OPTIONAL
Valid values are ALL, ACTIVE, INACTIVE.
ALL lists both active and inactive SKUs.
ACTIVE lists active SKUs only.
INACTIVE lists inactive only.
Default is set to ALL.
fulfillment – OPTIONAL
default is “ALL” to show FBA and FBM ASINs. “FBA” will only display FBA ASINs. FBM will only display FBM ASINs.
sort – OPTIONAL
Default value is “date_asc” if not specified in the formula.
“date_asc” lists ASINs in ascending order by created date.
“date_desc” lists ASINs in descending order by created date.
“asin_asc” lists ASINs in ascending alphabetical order.
“asin_desc” lists ASINs in descending alphabetical order.
Examples: 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_ASINLIST() If no attributes are entered, it will list all your current active and inactive ASIN from all marketplaces.
=GORILLA_ASINLIST( , , , “FBA”, “date_asc”) This will list only the FBA ASINs in order of created date for the default marketplace for your account as the other attributes are empty.
=GORILLA_ASINLIST( , , “ALL”) This will list the entire list of ASINs in your account including all the deleted, inactive, closed, suppressed products. If no inputs are entered like GORILLA_ASINLIST(), the default value is “EXCLUDE”.
=GORILLA_ASINLIST(“ALL”, “B43”) Lists all ASINs from all marketplaces where the ASIN starts with “B43”.
=GORILLA_ASINLIST(“US” , , “INACTIVE”, “FBM”) This will list all inactive FBM ASINs for the US marketplace only. Will also show closed, inactive, deleted, suppressed ASINs.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_ASINLIST(“SELLER ID”) =GORILLA_ASINLIST(“SELLER ID”, , , “ALL”, “FBM”) =GORILLA_ASINLIST(“SELLER ID”, “ALL”, “B43”) =GORILLA_ASINLIST(“SELLER ID”, “US” , , “INACTIVE”, “FBA”, “asin_asc”)
|
GORILLA_ASINSTATUS( ) Function
Description:
Returns the status of the ASIN whether it is active or inactive.
Syntax:
GORILLA_ASINSTATUS([marketplace], ASIN/SKU)
for Gorilla AGENCY, the syntax is
GORILLA_ASINLIST(sellerID, [marketplace], ASIN/SKU)
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
ASIN/SKU – REQUIRED
REQUIRED – The ASINs or SKUs to get the status of.
Examples: 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_ASINSTATUS(“US”, A2:B100) This will return “active” or “inactive” for all the ASINs or SKUs listed from A2 to B100 for the US marketplace. The function supports 2D arrays such as a set of values in column A and column B.
=GORILLA_ASINSTATUS( , A1:A1000) If the marketplace is left out, it will get the listing status of the ASIN/SKUs from the default marketplace associated with your account. Use ranges in your formula to get the results in bulk.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_ASINSTATUS(“SELLER ID”, “US”, A2:B100) =GORILLA_ASINSTATUS(“SELLER ID”, A1:A1000)
|
GORILLA_BUYBOXOFFERS( ) Function
Description:
Get the list of all sellers and offerings on the listing and competing for the buy box. Does not pull information for SKU’s that you do not sell. Only works for SKU’s and ASIN’s in your inventory.
Syntax:
GORILLA_BUYBOXOFFERS(sku, [marketplace], [header])
for Gorilla AGENCY, the syntax is
GORILLA_BUYBOXOFFERS(sellerID, sku, [marketplace], [header])
sku – REQUIRED
SKU or ASIN of the product. Unlike other functions, this one does NOT accept array inputs or ranges like {“SKU12345″,”ASIN12345”} or A1:A100.
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.
header – OPTIONAL
Display header in the table of results
- TRUE = auto creates a header as the first row of results. Selected by default if no values are entered.
- FALSE = only shows the table of data. No headings or titles.
Examples: 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_BUYBOXOFFERS(“B00YD545CC”, “US”, “TRUE”) Returns all sellers for the listing in the US market along with details of the buy box. A header row is automatically generated.
=GORILLA_BUYBOXOFFERS(“B00YD545CC”, “DE”, “FALSE”) Returns all sellers for the listing in the German market along with details of the buy box. A header row is not present. Only the data is displayed.
=GORILLA_BUYBOXOFFERS(“B00YD545CC”) If only the SKU is entered, it will display data for all sellers in the default marketplace and include the header in the table.
For Gorilla AGENCY, the same formulas will start with the seller ID: =GORILLA_BUYBOXOFFERS(“SELLERID837834”, “B00YD545CC”, “US”, “TRUE”) =GORILLA_BUYBOXOFFERS(“SELLERID837834”, “B00YD545CC”, “US”, “FALSE”) =GORILLA_BUYBOXOFFERS(“SELLERID837834”, “B00YD545CC”)
|
GORILLA_BUYBOXPRICE( ) Function
Description:
Get the current buy box price of the seller winning the buy box.
Syntax:
GORILLA_BUYBOXPRICE(sku, [marketplace], [condition], [returnStyle], [priceType])
for Gorilla AGENCY, the syntax is
GORILLA_BUYBOXPRICE(seller ID, sku, [marketplace], [condition], [returnStyle], [priceType])
sku – REQUIRED
SKU or ASIN of the product. Use arrays and ranges like A1:A1000 to bulk load data. See examples below. If a range of SKU is used in the formula, it will display the buy box price only.
marketplace – OPTIONAL
Marketplace filter. Use Default for the seller default. Other valid values are country codes US, DE, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
condition – OPTIONAL
Filter for new or used conditions
- NEW = only displays the prices for products that are being sold in NEW condition.
- USED = only displays the prices for products that are being sold as USED condition.
returnStyle – OPTIONAL
Displays the results in a table or as a list of prices.
- Default results show the price for the lowest price for each SKU/ASIN provided.
- FULL – loads all buy box data like price, fulfillment channel, offer count, listing price, suggested price, landed price.
- FULLWITHHEADER – shows the “full” data with a header row as the first row.
pricetype – OPTIONAL
Displays the results as either the following:
- LandedPrice – Displays the results based on the LandedPrice of the SKU that is entered in Seller Central
- ListingPrice – Displays the results based on the ListingPrice of the SKU that is entered in Seller Central
- ShippingPrice- Displays the results based on the ShippingPrice of the SKU that is entered in Seller Central
Examples: 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_BUYBOXPRICE(A1:A1000, “US”, “ANY”, “PRICE”) Returns the lowest offered buy box prices only for all SKU’s in A1:A1000. Pulls from the US marketing for any condition.
=GORILLA_BUYBOXPRICE(“B00YD545CC”, “DEFAULT”, “NEW”, “FULL”) Returns the buy box price for the ASIN in the default marketplace. Looking up NEW condition products only. Full results are displayed with no header in the table of results.
=GORILLA_BUYBOXPRICE(“B00YD545CC”) If only the SKU is entered, it will display the price of the buy box only.
For Gorilla AGENCY, the same formulas will start with the seller ID: =GORILLA_BUYBOXPRICE(“SELLERID837834”, A1:A1000, “US”, “ANY”, “PRICE”) =GORILLA_BUYBOXPRICE(“SELLERID837834”, “B00YD545CC”, “DEFAULT”, “NEW”, “FULL”) =GORILLA_BUYBOXPRICE(“SELLERID837834”, “B00YD545CC”)
|
GORILLA_BUYBOXWINNER( ) Function
Description:
Returns “winner” or “loser” based on whether you are winning or losing the buy box.
Syntax:
GORILLA_BUYBOXWINNER(sku, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_BUYBOXWINNER(seller ID, sku, [marketplace])
sku – REQUIRED
SKU or ASIN of the product. Use arrays and ranges like A1:A1000 to bulk load data. See examples below. If a range of SKU is used in the formula, it will display the buy box price only.
marketplace – OPTIONAL
Marketplace filter. Use Default for the seller default. Other valid values are country codes US, DE, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
Examples: 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_BUYBOXWINNER(A1:A1000, “US”) Returns “winner” or “loser” for each of the SKU or ASIN listed. Results are for the US marketplace.
=GORILLA_BUYBOXWINNER(“B00YD545CC”) Returns “winner” or “loser” for the ASIN B00YD545CC. The default marketplace is used as no marketplace is defined.
=GORILLA_BUYBOXWINNER(A1:A1000) Returns “winner” or “loser” for each of the ASINs or SKUs listed from A1 to A1000.
For Gorilla AGENCY, the same formulas will start with the seller ID: =GORILLA_BUYBOXWINNER(“SELLERID837834”, A1:A1000, “US”) =GORILLA_BUYBOXWINNER(“SELLERID837834”, “B00YD545CC”) =GORILLA_BUYBOXWINNER(“SELLERID837834”, A1:A1000)
|
GORILLA_CATEGORY( ) Function
Description:
Get the categories a product is listed under. Only works for single marketplaces at a time. Not “ALL”.
Syntax:
GORILLA_CATEGORY(sku, [marketplace], [queryType], [returnStyle])
for Gorilla AGENCY, the syntax is
GORILLA_CATEGORY(sellerID, sku, [marketplace], [queryType], [returnStyle])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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 “full”):
- full = Full category path;
- inner = Only inner-most category name.
Examples: 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_CATEGORY(“B00YD545CC”, “US”, “all”, “full”) Returns all categories and child categories the product is listed under. If B00YD545CC is a garlic press, the result will show both “Home&Kitchen” and “Home&Kitchen->Kitchen Utensils”
=GORILLA_CATEGORY(“B00YD545CC”, “US”, “all”, “inner”) Returns all categories and child categories the product is listed under. If B00YD545CC is a garlic press, the result will show “Home&Kitchen” and “Kitchen Utensils” instead of the full category path.
=GORILLA_CATEGORY(“B00YD545CC”, “US”, “top”, “full”) If “top” is selected, you may use it for array inputs to bulk display the category information. “Top” returns only the main category. If B00YD545CC is a garlic press, the result will show only the main category “Home&Kitchen”.
For Gorilla AGENCY, the same formulas will start with the seller ID: =GORILLA_CATEGORY(“SELLERID837834”, “B00YD545CC”, “US”, “all”, “full”) =GORILLA_CATEGORY(“SELLERID837834”, “B00YD545CC”, “US”, “all”, “inner”) =GORILLA_CATEGORY(“SELLERID837834”, “B00YD545CC”, “US”, “top”, “full”)
|
GORILLA_CHARGETOTAL( ) Function
This function is now replaced by FINANCES() – will be discontinued
Description:
Get the financials breakdown of the sales and charges related to the sale of a product. Supports getting the total account financials or narrowing down by SKU.
IMPORTANT: This is NOT an accounting tool. Amazon transaction reports via the API are inconsistent and subject to change on a regular basis. Use this to identify areas of your business and spot anything that needs attention. It can help with tax returns, but should NOT be used solely for tax and financial statements.
A bookkeeper is required to match your numbers to the nearest cent.
Syntax:
GORILLA_CHARGETOTAL(period, [marketplace], [sku], [chargeType], [event])
for Gorilla AGENCY, the syntax is
GORILLA_CHARGETOTAL(sellerID, period, [marketplace], [sku], [chargeType], [event])
period – REQUIRED
|
|
|
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
Any SKU or ASIN to limit results. Enter 1 SKU only. Do not use ranges for this function.
chargeType – OPTIONAL
To get the names of all charge types in the spreadsheet, enter
=GORILLA_CHARGETYPE()
or for Agency,
=GORILLA_CHARGETYPE(“SELLERID”)
See GORILLA_CHARGETYPE().
event – OPTIONAL
- Shipment
- Refund
- GuaranteeClaim
- Chargeback
- CouponPayment
- Adjustment
- Other
Examples: =GORILLA_CHARGETOTAL(“This Month”) or =GORILLA_CHARGETOTAL(“2019-04”) Simplest formula allowed to return all sales and charges for the selected period. This will return an unsorted list of all the categories. For a more organized list, see the following examples. =GORILLA_CHARGETOTAL(“This Month”, “FR”, “B3XJJDD7”) =GORILLA_CHARGETOTAL(“2020Q4”, “UK”, “B3XJJDD7”) Will return all charges assigned to the ASIN in the French and UK marketplace for the selected time period. =GORILLA_CHARGETOTAL(“Last 30 Days”,”ALL”,,”ShippingTax”, “shipment”) or =GORILLA_CHARGETOTAL(“Last 30 Days”,”ALL”,,”ShippingTax”) Will return the total ShippingTax related charges. sales and charges for the last 30 days. For the full list of periods, and the different types of charges you can narrow it down to, see the table after the examples. =GORILLA_CHARGETOTAL(“This Year”,”default”,,, “shipment”) Returns all shipment related sales and charges for this year to date, default marketplace and for all charges. No sku has been specified and so it will pull for total SKU’s. =GORILLA_CHARGETOTAL(“2019Q2″,”US”,”B038434K3″, , “refund”) Returns all refund related charges for Q2 of 2019, in the US marketplace, for ASIN B038434K3. =GORILLA_CHARGETOTAL(“Last 30 Days”,”ALL”,,, “ADJUSTMENT”) Returns all adjustment related charges for the last 30 days across ALL marketplaces. Sku’s have been left out which will default to all sku’s. =GORILLA_CHARGETOTAL(“2109Q1″,”DE”,,, “OTHER”) Returns all other charges that are not grouped under shipment, refund, adjustment. Returns values for Q1 of 2019 for German marketplace. =GORILLA_CHARGETOTAL(“2019Q1”, “US”, “B00YD545CC”, “All”) To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period. =GORILLA_CHARGETOTAL(“2019-05”, “US”, “B00YD545CC”, “All”) To get monthly data, use the format YYYY-MM as the preset period. For Gorilla Agency, the formulas will start with the seller ID: =GORILLA_CHARGETOTAL(“SELLER384k3”, “This Month”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “2019-04”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “Last 30 Days”,”ALL”,,”ShippingTax”, “shipment”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “Last 30 Days”,”ALL”,,”ShippingTax”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “2019Q2″,”US”,”B038434K3″,, “refund”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “Last 30 Days”,”ALL”,,, “ADJUSTMENT”) =GORILLA_CHARGETOTAL(“SELLER384k3”, “2109Q1″,”DE”,,, “OTHER”) =GORILLA_CHARGETOTAL(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “All” =GORILLA_CHARGETOTAL(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “All”) |
GORILLA_CHARGETYPE( ) Function
Description:
Returns a list of the line item names for all “charges” to your Amazon account when it is related to sales. Names includes charges related to both income and expense.
Syntax:
GORILLA_CHARGETYPE([marketplace], [filter], [direction], [full_list])
for Gorilla AGENCY, the syntax is
GORILLA_CHARGETYPE(sellerID, [marketplace], [filter], [direction], [full_list])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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.
filter – OPTIONAL
Enter a string to filter for a particular list of charges.
direction – OPTIONAL
Default is “column” if nothing is entered. It will return the list in a column. Use “row” to get the result in a horizontal row.
full_list – OPTIONAL
Default is “no” so that it only displays the charges associated with your account. To see the FULL list of charges that Amazon takes, enter “yes”. This will list transactions that are also not part of your account.
For an explanation of each item, read the Amazon seller fees article.
Examples: 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_CHARGETYPE() If no attributes are entered, it will list all the line items categorized as charges related to the Amazon account..
=GORILLA_CHARGETYPE(“US”, “refund” , “column”, “yes”) Lists all charges types for the US marketplace, searching for any charges that start with “refund”, displayed in a column and only displaying the charges associated with the Amazon account. If “YES” is used, it will return all Amazon chargetype names.
For Gorilla AGENCY, the same formulas will start with the seller ID: =GORILLA_CHARGETYPE(“SELLER349d3”) =GORILLA_CHARGETYPE(“SELLER349d3”, “US”, “Marketpl”, “Column”, “No”) |
GORILLA_FEESESTIMATE( ) Function
Description:
Get the estimated fees associated with the product. (Not the actual settlement product fees and charges to your accounts).
Syntax:
GORILLA_FEESESTIMATE(sku, [marketplace], [returnStyle], [fulfillment])
for Gorilla AGENCY, the syntax is
GORILLA_FEESESTIMATE(sellerID, sku, [marketplace], [returnStyle], [fulfillment])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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.
returnStyle – OPTIONAL
- Full – displays full information of each seller offering the product without header information.
- Fullwithheader – autocreates a header row and displays full information of each seller offering the product.
- FBAFees – Amazon’s FBA fee for your item
- PerItemFee – the per item fee Amazon charges for your product
- ReferralFee – the referral fee amount based on the category of your product
- VariableClosingFee – the variable closing fee
fulfillment – OPTIONAL
- ALL – lists the default SKU fees for either FBA or FBM (default)
- FBA – lists the FBA fees for the SKU
- FBM – lists the FBM fees for the SKU
For deeper understanding of all fees, please refer to the Ultimate Amazon Seller Fees Guide.
Examples: 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_FEESESTIMATE(D2) Uses the ASIN or SKU entered in cell D2 and shows the total estimate fee (Fulfillment fee + referral fee) for the SKU. If Amazon does not have the price information, no estimate will be provided.
=GORILLA_FEESESTIMATE(A2:C10) Supports 2 dimensional ranges. Also works horizontally. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:C10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_FEESESTIMATE(“738-JDUY-32F”, “default”, “fullwithheader”) Entering all the attributes will display the full breakdown of the fee estimate for the product.“Default” refers to your main marketplace. “Fullwithheader” creates another row before the results with the table headers to make it easy to identify.
=GORILLA_FEESESTIMATE(“738-JDUY-32F”, “US”, “FBAFees”, “FBA”) This example will pull in the FBA fees for your product as a single value for the SKU in the US market. It will pull the fees for the FBA version of the product.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_FEESESTIMATE(“SELLERID832”, D2) =GORILLA_FEESESTIMATE(“SELLERID832”, A2:C10) =GORILLA_FEESESTIMATE(“SELLERID832”, “738-JDUY-32F”, “default”, “fullwithheader”) =GORILLA_FEESESTIMATE(“SELLERID832”, “738-JDUY-32F”, “US”, “FBAFees”, “FBA”) |
GORILLA_FEETOTAL( ) Function
This function is now replaced by FINANCES() – will be discontinued
Description:
Get the financials breakdown of all fees related to the sale of a product. Supports getting the total account numbers or narrowing down by SKU.
IMPORTANT: This is NOT an accounting tool. Amazon reports via the API are inconsistent and subject to change on a regular basis. Use this to identify areas of your business and spot anything that needs attention. It can help with tax returns, but should NOT be used solely for tax and financial statements.A bookkeeper is required to match your numbers to the nearest cent.
Syntax:
GORILLA_FEETOTAL(period, [marketplace], [sku], [fee], [event], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_FEETOTAL(sellerID, period, [marketplace], [sku], [fee], [event], [start_date], [end_date])
period – OPTIONAL
|
|
|
sku – OPTIONAL
Any Seller SKU or ASIN to limit results.
fee – OPTIONAL
For the latest list, enter =GORILLA_FEETYPE(, , , “yes”).
For an explanation of each item, read the Amazon seller fees article.
- Amazon Imaging fee
- AmazonExclusivesFee
- Cash on Delivery Chargeback
- Closing FeesCommission
- Coupon clip fee
- Coupon redemption fee
- Cross-Border Fulfillment Fee
- Easy Ship Fee
- EPSO Chargeback fee
- EPSO Cross-Border fee
- EPSO Payment Authorization fee
- EPSO Payment Settle fee
- EPSOChargebackFee
- FBA Amazon-Partnered Carrier Shipment Fee/Inbound Transportation Charge
- FBA Fee for cash on delivery
- FBA Fulfillment Fees
- FBA Inbound Shipment Carton Level Info Fee
- FBA Inbound Transportation Program Fee
- FBA Inventory Disposals
- FBA Inventory Placement Service Fees
- FBA Inventory Removals
- FBA Inventory Storage Fee
- FBA Label Service Fee
- FBA Liquidation Fee
- FBA Long-Term Storage Fees
- FBA Multi-Channel Fulfillment Weight Handling
- FBA Per Order Fulfillment Fee
- FBA Prep Service Fees (Bubble Wrap)
- FBA Prep Service Fees (Labeling)
- FBA Prep Service Fees-Adult-Bagging (black or opaque)
- FBA Returns Processing Fee-Order Handling
- FBA Returns Processing Fee-Pick & Pack
- FBA Returns Processing Fee-Weight Handling
- FBA Taping Fee
- FBA transportation fee
- FBA Unplanned Prep Service Fees
- FBA Weight Based Fee
- FBACustomerReturnWeightBasedFee
- FBADeliveryServicesFee
- FBAInboundDefectFee
- FBAInternationalInboundFreightFee
- FBAInternationalInboundFreightTaxAndDuty
- FBALocalizationFee
- FBAOverageFee
- FBAPerUnitFulfillment
- FeeFBATransportationFee
- FBAWeightBasedFee
- Fresh Inbound Transportation Fee
- Get Paid Faster Fee
- GetPaidFasterFee
- Gift Wrap Charge-Back Fee
- Gift Wrap Commission
- GiftwrapChargeback
- GiftwrapCommission
- Global Inbound Transportation Fee
- GlobalInboundTransportationDuty
- GlobalInboundTransportationFreight
- High-volume listing fee
- JumpStartYourWebstoreFee
- MFNDeliveryServiceFee
- MFNPostageFee
- Monthly subscription
- and more…
event – OPTIONAL
Return data for specific event type. Default is empty value for any event type.
- Shipment
- Refund
- GuaranteeClaim
- Chargeback
- CouponPayment
- Adjustment
- ServiceFee
- Other
start_date – OPTIONAL
Starting date to get charges from when period is custom.
end_date – OPTIONAL
Ending date to get charges from when period is custom.
Examples:
=GORILLA_FEETOTAL(“This Month”) or =GORILLA_FEETOTAL(“2019-04”) Simplest formula allowed to return all sales and fees for the selected period. This will return an unsorted list of all the categories. For a more organized list, see the following examples. =GORILLA_FEETOTAL(“This Month”, “FR”, “B3XJJDD7”) =GORILLA_FEETOTAL(“2020Q4”, “UK”, “B3XJJDD7”) Will return all fees assigned to the ASIN in the French and UK marketplace for the selected time period. =GORILLA_FEETOTAL(“2019Q2″,”US”,”B038434K3″,, “refund”) Returns all refund related fees for Q2 of 2019, in the US marketplace, for ASIN B038434K3. =GORILLA_FEETOTAL(“Last 30 Days”,”US”,,,”servicefee”) Returns all adjustment related fees for the last 30 days across US marketplace. SKUs have been left out which will default to all sku’s. =GORILLA_FEETOTAL(“last year”,”US”,,,”other”) Returns all other fee that are not grouped under shipment, refund, adjustment. Returns values for Q1 of 2019 for German marketplace. =GORILLA_FEETOTAL(“2019Q1”, “US”, “B00YD545CC”, “SUM”) =GORILLA_FEETOTAL(“2019-05”, “US”, “B00YD545CC”, “SUM”) To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period. To get monthly data, use the format YYYY-MM as the preset period.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_FEETOTAL(“SELLER384k3”, “2019-04”) =GORILLA_FEETOTAL(“SELLER384k3”, “This Year”,”default”,, , “shipment”) =GORILLA_FEETOTAL(“SELLER384k3”, “2019Q2″,”US”,”B038434K3″, , “refund”) =GORILLA_FEETOTAL(“SELLER384k3”, “Last 30 Days”, , , “servicefee”) =GORILLA_FEETOTAL(“SELLER384k3”, “last year”,”US”, , , “other”) =GORILLA_FEETOTAL(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “SUM”) =GORILLA_FEETOTAL(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “SUM”) |
GORILLA_FEETYPE( ) Function
Description:
Return a list of the line item names associated as fees with the Amazon account. All variables are optional.
Syntax:
GORILLA_FEETYPE([marketplace], [filter_fee], [direction], [full_list])
for Gorilla AGENCY, the syntax is
GORILLA_FEETYPE(sellerID, [marketplace], [filter_fee], [direction], [full_list])
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.
filter – OPTIONAL
Enter a string to filter for a particular list of fees.
direction – OPTIONAL
Default is “column” if nothing is entered. It will return the list in a column. Use “row” to get the result in a horizontal row.
full_list – OPTIONAL
Default is “no” so that it only displays the fees associated with your account. To see the FULL list of fees that Amazon takes, enter “yes”. This will list transactions that are also not part of your account.
Examples:
=GORILLA_FEETYPE() If no attributes are entered, it will list all the line items categorized as fees related to the Amazon account. =GORILLA_FEETYPE(“US”, “FBA”, “Column”, “No”) Lists all fee types for the US marketplace, searching for any fees that start with “FBA”, displayed in a column and only displaying the fee’s associated with the Amazon account. If “YES” is used, it will return all Amazon fee names whether it is charged by the account or not.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_FEETYPE(“SELLER349d3”) =GORILLA_FEETYPE(“SELLER349d3”, “US”, “FBA”, “Column”, “No”) |
GORILLA_FINANCES( ) Function
This function now replaces the older CHARGETOTAL() and FEETOTAL()
Description:
Get inventory movement status of a product such as how many is in stock, transfer or inbound.
Syntax:
GORILLA_FINANCES(period, type, [event], [marketplace], [sku], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_FINANCES(sellerID, period, type, [event], [marketplace], [sku], [start_date], [end_date])
period – REQUIRED
Predefined period to get sales. See full list of predefined periods below.
Can be one value or an array of values. If the period array is vertical (on the left as a column), then the “type” parameter must be a horizontal array or string (as a header up the top).
|
|
|
type – REQUIRED
List of Charges/Fees to get the values for. Data is grouped by fee/charge type. Can be a string or an array. Has to be valid fee names (See function GORILLA_FEETYPE() and GORILLA_CHARGETYPE() to get a list of them). If type array is vertical, period has to be horizontal or a string.
event – OPTIONAL
Filter finance results for a specific event type.
Valid event values are:
- Shipment
- Refund
- GuaranteeClaim
- Chargeback
- CouponPayment
- Adjustment
- Other
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
SKU or ASIN of the product. Only one SKU or ASIN is supported. This function does NOT support arrays for SKU or ASIN input.
start_date – OPTIONAL
Starting date to get data when period attribute is “custom”.
end_date – OPTIONAL
Ending date to get data when period attribute is “custom”.
Examples: Use 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_FINANCES(“Custom”, “Selling price (Principal)”, “shipment”, “US”, “SKU12345”, “2021-01-01”, “2021-03-16”) The full formula in action. Gets the “Selling Price (Principal)” from the “shipment” event in the US market, for SKU1234 between 2021-01-01 and 2021-03-16.
=GORILLA_FINANCES(“Last Year”, A1:A13) where A1:A13 is
Displays the consolidated numbers for the transaction “type” listed above for “last year”.
=GORILLA_FINANCES(A1:A5, A2:A12, “adjustment”, “US”) where A1:A5 is
where A1:A12 is
This formula will pull data for the 5 monthly time periods as well as the adjustment transactions for the US market.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_FINANCES(“SellerID”, “Custom”, “Selling price (Principal)”, “shipment”, “US”, “SKU12345”, “2021-01-01”, “2021-03-16”) =GORILLA_FINANCES(“SellerID, “Last Year”, A1:A13) =GORILLA_FINANCES(“SellerID”, A1:A5, A2:A12, “adjustment”, “US”) |
Type Attributes:
Due to the ever increasing and changing list of charges and fees by Amazon, get the latest and full list by entering:
=GORILLA_CHARGETYPE( , , , “YES”)
=GORILLA_FEETYPE( , , , “YES”)
for agency users
=GORILLA_CHARGETYPE(“sellerID”, , , , “YES”)
=GORILLA_FEETYPE(“sellerID”, , , , “YES”)
GORILLA_INVENTORY( ) Function
Description:
Get inventory movement status of a product such as how many is in stock, transfer or inbound.
Syntax:
GORILLA_INVENTORY(sku, [marketplace], [status])
for Gorilla AGENCY, the syntax is
=GORILLA_INVENTORY(sellerID, sku, [marketplace], [status])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
status – OPTIONAL
Supply status. Valid values are:
- Total (or ALL) = fulfillable + inbound_working + inbound_shipped + inbound_receiving +reserved_transfer
- warehouse
- fulfillable (instock)
- Local_Stock (only available for EU marketplaces)
- unsellable
- reserved = reserved_orders + reserved_transfer + reserved_processing
- inbound_working
- inbound_shipped (or “inbound”)
- inbound_receiving
- researching
- reserved_orders
- reserved_transfer (or “transfer”)
- reserved_processing
Examples: Use 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_INVENTORY(D2, “ALL”, “warehouse”) Uses the ASIN or SKU entered in cell D2 and shows the total number of units that are currently in the Amazon warehouse.
=GORILLA_INVENTORY(“B00YD545CC”, “UK”, “transfer”) Gets the total number of units for ASIN B00YD545CC that are in transfer status for Italy marketplace.
=GORILLA_INVENTORY(A2:A10, “ALL”, “inbound”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_INVENTORY(“SELLER63DJ3”, D2, “ALL”, “instock”) =GORILLA_INVENTORY(“SELLER63DJ3”, “B00YD545CC”, “UK”, “transfer”) =GORILLA_INVENTORY(“SELLER63DJ3”, A2:A10, “ALL”, “inbound”)
What you will see when using this is how Amazon provides inventory numbers across marketplaces.
For example, if you have 100 units in Germany, it is technically available in DE, UK, ES, FR, IT.So Amazon enters your inventory as 100×5 = 500 inventory available.
So to get the number you want only for Germany, you have to enter the formula to be specific for the marketplace.
=GORILLA_INVENTORY(ASIN,”DE”,”inbound”) In any spreadsheet, try =GORILLA_INVENTORY(ASIN123123,”DE”,”fulfillable”) =GORILLA_INVENTORY(ASIN321321,”DE”,”inbound”) =GORILLA_INVENTORY(ASIN567856,”DE”,”researching”)
And you will see the numbers for each marketplace. |
GORILLA_INVENTORYAGE( ) Function
Description:
Gets the inventory age as shown in your seller central reports.
Syntax:
GORILLA_INVENTORYAGE(sku, [marketplace], [condition], [datapoint], [header])
for Gorilla AGENCY, the syntax is
GORILLA_INVENTORYAGE(sellerID, sku, [marketplace], [condition], [datapoint], [header])
sku – REQUIRED
Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string. If a datapoint is also specified in the formula, only single value or a column of skus can be provided.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
condition – OPTIONAL
Return only data for specific product condition. Default is empty value for any condition type.
Valid values are: New, Used.
datapoint – OPTIONAL
Return data for specific data point. Default is empty value which will load the data for all the following data points.
|
|
header – OPTIONAL
Show column header when data point is not specified, TRUE to show header, FALSE (default) to not show it.
Examples: Use 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_INVENTORYAGE(A1:A100) The most basic table format where the SKUs or ASINs are listed from A1 to A100. It will list all the “datapoints” in the previous section above, without a header. =GORILLA_INVENTORYAGE(A1:A100, “US”, “NEW”, ,TRUE) Returns a table of results. All datapoints will be listed for SKUs listed in A1:A100 , for the US market, in new condition. The TRUE condition will display a table header.
=GORILLA_INVENTORYAGE(A1:A100, , , “InvAge91to180Days”) Bulk returns a list of results for SKUs listed in A1:A100 showing how many units of each sku is aged InvAge91to180Days.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_INVENTORYAGE(“SELLER63DJ3”, A1:A100) =GORILLA_INVENTORYAGE(“SELLER63DJ3”, A1:A100, “US”, “NEW”, ,TRUE) =GORILLA_INVENTORYAGE(“SELLER63DJ3”, A1:A100, , , “InvAge91to180Days”) |
GORILLA_INVENTORYHIST( ) Function
Description:
Shows historical inventory levels of past dates. Works like GORILLA_INVENTORY. Only works on going forward basis. Historical data before using Gorilla ROI cannot be retrieved.
Syntax:
GORILLA_INVENTORYHIST(period, sku, [marketplace], [status], [start date], [end date], [transpose])
for Gorilla AGENCY, the syntax is
GORILLA_INVENTORYHIST(sellerID, period, sku, [marketplace], [status], [start date], [end date], [transpose])
sku – REQUIRED
SKU or ASIN. Select or enter a range of values.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
status – OPTIONAL
- Total (or ALL)
- warehousefulfillable
- Unsellable
- reserved
- inbound_working
- inbound (or inbound_shipped)
- inbound_receiving
- researching
- reserved_orders
- transfer (or reserved_transfer)
- reserved_processing
start_date – OPTIONAL
Only works if “days” is left blank.
Starting date to get sales between any two periods. Date format should be in YYYY-MM-DD.
end_date – OPTIONAL
Only works if “days” is left blank.
End date to get sales between any two periods. Date format should be in YYYY-MM-DD.
transpose – OPTIONAL
default is set to “YES”.
It will display the SKUs on the left side and the dates horizontally.
“NO” will display the SKUs in a horizontal row at the time. Dates vertically down.
Examples: =GORILLA_INVENTORYHIST(“last 14 days”, D2, “US”, “fulfillable”) Uses the ASIN or SKU entered in cell D2 and shows the total number of fulfillable units across all marketplaces. =GORILLA_INVENTORYHIST(“last 3 months”, “B00YD545CC”, “UK”, “transfer”) Displays in a list, the transfer inventory for each day of the last 3 months for ASIN B00YD545CC from the UK marketplace. =GORILLA_INVENTORYHIST(“custom”, A1:A10, “ALL”, “inbound”, “2019-12-01”, “2019-12-12”, “yes”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A1:A10 and returns all values at once. Using the “yes” parameter to transpose will list the SKUs in a vertical column and the values going horizontal.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_INVENTORYHIST(“SELLER ID”, “last 14 days”, D2, “US”, “reserved”) =GORILLA_INVENTORYHIST(“SELLER ID”, “last 3 months”, “B00YD545CC”, “UK”, “transfer”) =GORILLA_INVENTORYHIST(“SELLER ID”, “custom”, A1:A10, “ALL”, “inbound”, “2019-12-01”, “2019-12-12, “yes”) |
GORILLA_INVENTORYRECEIVED( ) Function
Description:
Get inventory received and check shipment data
Syntax:
GORILLA_INVENTORYRECEIVED(period, [filter], [shipmentid], [center], [received], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_INVENTORYRECEIVED(sellerID, period, [filter], [shipmentid], [center], [received], [start_date], [end_date])
period – REQUIRED
Predefined period to get orders or use custom to specify a start and end date.
filter – OPTIONAL
SKU, ASIN or FNSKU to filter the results. Can be a range of values like A1:A100 or SKU111, SKU222, SKU333 or FNSKU123.
shipmentID – OPTIONAL
Filter data using the Amazon shipment ID
center – OPTIONAL
Fulfillment center filter. Valid values: Fulfillment Center Id, Country or State of the FC location. Country codes as US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, PO, SL, SE, AU, SG, JP… States as Oregon, Ohio, Texas, Mecklar, Prague, Madrid, Leipzig… Use ALL to get values from all centers.
received – OPTIONAL
Unit quantity received. Valid values are: All or Missing as default.
start_date – OPTIONAL
Starting date for the returning range when period is “custom”.
end_date – OPTIONAL
Ending date for the returning range when period is “custom”.
Examples: =GORILLA_INVENTORYRECEIVED(“THIS YEAR”) Returns the full Amazon shipment and inventory received report. Auto generates a table with headers. Default settings will only show the missing or negative received quantity shipments. To see the full list, use the filter for “received”. =GORILLA_INVENTORYRECEIVED(“THIS MONTH”, , , “MX”,”ALL”) This will create a table showing the shipments from this month for Mexico shipments. It will display the full list of all quantities. =GORILLA_INVENTORYRECEIVED(“custom”, “SKU12345”, “FBA12XYZ34”, “US”, “Missing”, “2021-01-01”, “2021-02-01”) This is the most detailed version of pulling all shipments for the SKU defined, in a certain shipment ID, for the US market that are missing. Custom date period is used.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_INVENTORYRECEIVED(“SELLER3382”, “THIS YEAR”) =GORILLA_INVENTORYRECEIVED(“SELLER3382”, “THIS MONTH”, , , “MX”,”ALL”) =GORILLA_INVENTORYRECEIVED(“SELLER3382”, “custom”, “SKU12345”, “FBA12XYZ34”, “US”, “Missing”, “2021-01-01”, “2021-02-01”) |
GORILLA_INVENTORYRESTOCK( ) Function
Description:
Get inventory restock data based on the restock report for a list of SKUs or ASINs
Syntax:
GORILLA_INVENTORYRESTOCK(sku, [marketplace], [condition], [datapoint], [header])
for Gorilla AGENCY, the syntax is
GORILLA_INVENTORYRESTOCK(sellerID, sku, [marketplace], [condition], [datapoint], [header])
sku – REQUIRED
SKU or ASIN. Select or enter a range of values.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
condition – OPTIONAL
Return only data for specific product condition. Default is empty value for any condition type.
Other Valid values are:
- New
- Used
datapoint – OPTIONAL
Data points supported by the function. Default value is an empty value. This will return all the data points.
- Condition
- DaysOfSupply
- Alert
- RecommReplenishmentQty
- RecommShipDate
- CurrMonthMaxInvThreshold
- Utilization
- MaxShipmentQty
header – OPTIONAL
default is set to “FALSE”.
Show column header when data point is not specified, TRUE to show header, FALSE (default) to not show it.
Examples: =GORILLA_INVENTORYRESTOCK(A1:A100, “US”, , ,TRUE) Returns the full restock report for the SKUs or ASINs listed in A1:A100 for the US market. The TRUE parameter generates a table header. =GORILLA_INVENTORYRESTOCK(A1:A100) The simplest form where the SKUs or ASINs are listed in A1:A100. This will display the full table with all the datapoints. No table header is generated. Only the raw data. =GORILLA_INVENTORYRESTOCK(A1:A100, “US”, “NEW”, “MaxShipmentQTY”) This loads the data only for “MaxShipmentQTY” for the SKUs in A1:A100. Enter the datapoint manually to define which datapoint you wish to see.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_INVENTORYRESTOCK(“SELLER3774”, A1:A100, “US”, , ,TRUE) =GORILLA_INVENTORYRESTOCK(“SELLER3774”, A1:A100) =GORILLA_INVENTORYRESTOCK(“SELLER3774”, A1:A100, “US”, “NEW”, “MaxShipmentQTY”) |
GORILLA_ISFBA( ) Function
Description:
Check if a SKU or ASIN is FBA or FBM or both
Syntax:
GORILLA_MYPRICE(sku, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_MYPRICE(sellerID, sku, [marketplace])
sku – REQUIRED
SKU or ASIN to filter the results. Can be a range of values like A1:A100 or SKU111, SKU222, SKU333 or ASIN123.
marketplace – OPTIONAL
Marketplace country filter. Country codes are US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, SE, AU, SG, JP. Use DEFAULT for seller default. Amazon Marketplace ID like ATVPDKIKX0DER can be used too.
Examples: Use 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_ISFBA(D2) Uses the ASIN or SKU entered in cell D2 and returns the result of whether it is FBA or FBM
=GORILLA_ISFBA(A1:A1000, “US”) Bulk method to check whether the SKUs or ASINs listed in A1:A1000 are FBA, FBM or both.
=GORILLA_ISFBA(A1:E10) Formula supports 2D directions. Not just a single row or column. For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_ISFBA(“SELLER8832”, D2) =GORILLA_ISFBA(“SELLER8832”, A1:A1000, “US”) =GORILLA_ISFBA(“SELLER8832”, A1:E10) |
GORILLA_MYPRICE( ) Function
Description:
Get the lowest seller price for the SKU or ASIN.
Syntax:
GORILLA_MYPRICE(sku, [marketplace], [condition], [subcondition], [priceType])
for Gorilla AGENCY, the syntax is
GORILLA_MYPRICE(sellerID, sku, [marketplace], [condition], [subcondition], [priceType])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
condition – OPTIONAL
Acts as a filter. Valid values are:
- New
- Used
- Collectible
- Refurbished
- Club
SubCondition – OPTIONAL
Acts as a filter. Valid values are:
- Mint
- Very Good
- Good
- Acceptable
- Poor
- Club
- OEM
- Warranty
- Refurbished Warranty
- Refurbished
- Open Box
- Other
priceType – OPTIONAL
Value to return. Valid values are:
- LandedPrice
- ListingPrice
- ShippingPrice
- RegularPrice
Examples: Use 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_MYPRICE(D2) Uses the ASIN or SKU entered in cell D2 and shows the price being offered by the seller.
=GORILLA_MYPRICE(“B03H39FJ32”, “US”, “New”) Will return the current price for ASIN B03H39FJ32 being sold in the US with new condition only. Will not return any prices for used or other conditions.
=GORILLA_MYPRICE(“B03H39FJ32”, “US”, “New”, “mint”, “ListingPrice”) Filtering formula to get the price of ASIN B03H39FJ32 that is sold in the US, in new and mint condition. Returns the listing price.
=GORILLA_MYPRICE(A2:C10) Supports 2 dimensional ranges. Also works horizontally. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:C10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37. For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_MYPRICE(“SELLER23HH”, D2) =GORILLA_MYPRICE(“SELLER23HH”, “B03H39FJ32”, “US”, “New”) =GORILLA_MYPRICE(“SELLER23HH”, “B03H39FJ32”, “US”, “New”, “mint”, “ListingPrice”) =GORILLA_MYPRICE(“SELLER23HH”, A2:C10) |
GORILLA_LOWESTPRICE( ) Function
Description:
Get the lowest price of the SKU or ASIN that’s competing for the buy box. Only pulls a number if there is more than one seller on the listing.
Syntax:
GORILLA_LOWESTPRICE(sku, [marketplace], [condition], [returnStyle], [priceType])
for Gorilla AGENCY, the syntax is
GORILLA_LOWESTPRICE(sellerID, sku, [marketplace], [condition], [returnstyle], [priceType])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
condition – OPTIONAL
Acts as a filter. Valid values are:
- New
- Used
- Collectible
- Refurbished
- Club
priceType – OPTIONAL
Value to return. Valid values are:
- LandedPrice
- ListingPrice
- ShippingPrice
- RegularPrice
Examples: Use 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_LOWESTPRICE(D2) Uses the ASIN or SKU entered in cell D2 and shows the price being offered by the seller.
=GORILLA_LOWESTPRICE(“B03H39FJ32”, “US”, “New”) Will return the current price for ASIN B03H39FJ32 being sold in the US with new condition only. Will not return any prices for used or other conditions.
=GORILLA_LOWESTPRICE(“B03H39FJ32”, “US”, “New”, “ListingPrice”) Filtering formula to get the price of ASIN B03H39FJ32 that is sold in the US, in new and mint condition. Returns the listing price.
=GORILLA_LOWESTPRICE(A2:C10) Supports 2 dimensional ranges. Also works horizontally. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:C10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37. For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_LOWESTPRICE(“SELLER23HH”, D2) =GORILLA_LOWESTPRICE(“SELLER23HH”, “B03H39FJ32”, “US”, “New”) =GORILLA_LOWESTPRICE(“SELLER23HH”, “B03H39FJ32”, “US”, “New”, “ListingPrice”) =GORILLA_LOWESTPRICE(“SELLER23HH”, A2:C10) |
GORILLA_LOWESTOFFER( ) Function
Description:
Get the lowest offered listing price from any seller.
Similar to lowestprice(), but it will load the lowest listing price even if there is only one seller.
Syntax:
GORILLA_LOWESTOFFER(sku, [marketplace], [returnStyle], [priceType])
for Gorilla AGENCY, the syntax is
GORILLA_LOWESTOFFER(sellerID, sku, [marketplace], [returnStyle], [priceType])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
returnStyle – OPTIONAL
Formatting of how the results are displayed. Valid values are:
- Full
- Fullwithheader
priceType – OPTIONAL
Value to return. Valid values are:
- LandedPrice
- ListingPrice
- ShippingPrice
- RegularPrice
Examples: 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_LOWESTOFFER(D2) Uses the ASIN or SKU entered in cell D2 and shows the lowest price being offered for the SKU. The single lowest price is returned after comparing against all sellers.
=GORILLA_LOWESTOFFER(“738-JDUY-32F”, “default”, “fullwithheader”) Entering all the attributes will display the full list of all offers on the listing. 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.
=GORILLA_LOWESTOFFER(A2:C10) Supports 2 dimensional ranges. Also works horizontally. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:C10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_LOWESTOFFER(“SELLER3729”, D2) =GORILLA_LOWESTOFFER(“SELLER3729”, “738-JDUY-32F”, “default”, “fullwithheader”) =GORILLA_LOWESTOFFER(“SELLER3729”, A2:C10) |
GORILLA_MANAGEORDER( ) Function
Description:
Get the detailed list of orders and the order details for a SKU or ASIN.
WARNING: For large sellers, do NOT use this function for a long time period, like “last year”. It will try to load thousands and thousands of orders. Google will timeout and will block your requests for the next 24hrs as it thinks you are a bot.
Syntax:
GORILLA_MANAGEORDER(period, [filter], [marketplace], [status], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_MANAGEORDER(sellerID, period, [filter], [marketplace], [status], [start_date], [end_date])
period – REQUIRED
Predefined period to get refunded units. See full list of predefined periods below.
|
|
|
filter – OPTIONAL
OPTIONAL – Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string. If no SKUs are specified, it will load the total number across the account.
marketplace – OPTIONAL
OPTIONAL – Marketplace filter. Use Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
status – OPTIONAL
OPTIONAL – Order status. Valid values are:
- All
- Canceled
- Pending
- PartiallyShipped
- Unshipped
- Shipped
- Shipping
start_date – OPTIONAL
OPTIONAL – Starting date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
OPTIONAL – Ending date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: 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_MANAGEORDER(“last 7 days”) This will list all orders over the last 7 days include all statuses of the orders. You will get details such as the order ID, purchase date, order status, fulfillment channel, quantity shipped, price, taxes and more.
=GORILLA_MANAGEORDER(“last month”, “U378-JEKD-423D”, “US”) Returns a list of all order from last month for the SKU in the US marketplace. You can also reference a cell instead of manually entering it into the formula.
=GORILLA_MANAGEORDER(“last 30 days”,C49,”US”,”SHIPPED”) Returns a filtered list of only shipped orders over the last 30 days for the ASIN or SKU in cell C49 from the US marketplace only.
=GORILLA_MANAGEORDER(“custom”, , “US”, “canceled”, “2020-09-01”, “2020-09-15”) Use custom date ranges to extract data. Between Sep 1st, 2020 and Sep 15th, 2020. Listing all canceled orders in the US marketplace.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_MANAGEORDER(“last 7 days”) =GORILLA_MANAGEORDER(“last month”, “U378-JEKD-423D”, “US”) =GORILLA_MANAGEORDER(“last 30 days”,C49,”US”,”SHIPPED”) =GORILLA_MANAGEORDER(“custom”, , “US”, “canceled”, “2020-09-01”, “2020-09-15”) |
GORILLA_PRODUCT( ) Function
Description:
Get product information for a SKU or ASIN such as title, image URL, weight, color and other supported attributes.
Syntax:
GORILLA_PRODUCT(sku, attributes, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_PRODUCT(sellerID, sku, attributes, [marketplace])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
attribute – REQUIRED
Product’s attribute to retrieve shown below.
|
|
|
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.
Examples: 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_PRODUCT(“IPHONE7382”, “SmallImage_URL”, “US”) This formula will pull the main small image URL of SKU IPHONE7382 from the US marketplace. You can then combine it with the regular IMAGE( ) to display it as an image. =IMAGE(GORILLA_PRODUCT(“IPHONE7382”, “SmallImage_URL”, “US”))
=GORILLA_PRODUCT(“B03H39FJ32”, “Scent”) Displays the scent name for ASIN B03H39FJ32. If no marketplace is assigned, it will grab the data from the main marketplace.
=GORILLA_PRODUCT(A2:A10,”Title”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_PRODUCT(“SELLER1378”, “IPHONE7382”, “SmallImage_URL”, “US”) =GORILLA_PRODUCT(“SELLER1378”, “B03H39FJ32”, “Scent”) =GORILLA_PRODUCT(“SELLER1378″, A2:A10,”Title”) |
GORILLA_PROFITLOSS( ) Function
Description:
Gets the financial data to build a profit and loss statement for your Amazon FBA account or by SKU/ASIN.
Automatically generated a prebuilt table, or pull detailed profit and expense line items to build your own statements.
Syntax:
GORILLA_PROFITLOSS(period, [marketplace], [sku], [category], [details])
for Gorilla AGENCY, the syntax is
GORILLA_PROFITLOSS(sellerID, period, [marketplace], [sku], [category], [details])
period – REQUIRED
Predefined periods and any custom dates. See full list of predefined periods below.
|
|
|
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.
sku – OPTIONAL
SKU or ASIN of the product. Only accepts ONE SKU or ASIN input. Does not work with multiple SKUs or ranges like other formulas.
category – OPTIONAL
Specify the line item to get the total value for that single category
- Sales
- Discounts/Promotions
- Amazon Reimbursements
- Shipping Income
- Income-Other
- Amazon Lending
- COGS
- Amazon Fees
- Advertising
- Tax
details – OPTIONAL
Defaults to “no”:
- Yes = Will list all the transactions associated with the category
- No = Will only show the total value of the category
Examples: 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_PROFITLOSS(“2020”) Will automatically generate a condensed and summarized profit and loss table in the form of:
=GORILLA_PROFITLOSS(“THIS YEAR”, “US”) Will generate a table like the example above for the US marketplace profit and loss only.
=GORILLA_PROFITLOSS(A2,B2,”MYSKU”) Using cells A1 and B2 to point to the period and the marketplace. Creates the P&L based on the SKU “mysku”. When a SKU is entered, not all transactions are available like storage costs as it is applied to your account and not the SKU.
=GORILLA_PROFITLOSS(“THIS YEAR”, “US”, , “Income-Other”) Returns the total value only for the line item “Income-Other”. You can specify a category to get single values rather than generate the full table each time.
=GORILLA_PROFITLOSS(“LAST 30 DAYS”, “FR”, ,”Amazon fees”, “YES”) List all the transactions that make up the category you specify. In this example, it will list all the expenses that are rolled up inside “Amazon Fees”.
To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period. 2020Q1, 2019Q4 etc
To get monthly data, use the format YYYY-MM as the preset period. 2020-04, 2021-07 etc
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_PROFITLOSS(sellerID, “2020”) =GORILLA_PROFITLOSS(sellerID, “THIS YEAR”, “US”) =GORILLA_PROFITLOSS(sellerID, A2,B2,”MYSKU”) =GORILLA_PROFITLOSS(sellerID, “THIS YEAR”, “US”, , “Income-Other”) =GORILLA_PROFITLOSS(sellerID, “LAST 30 DAYS”, “FR”, ,”Amazon fees”, “YES”) |
GORILLA_RANKHIST( ) Function
Description:
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. Not “ALL”.
Syntax:
GORILLA_RANKHIST(period, sku, [marketplace], [queryType], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_RANKHIST(sellerID, period, sku, [marketplace], [queryType], [start_date], [end_date])
period – REQUIRED
Predefined periods and any custom date frames to get the rank history. See full list of predefined periods below.
|
|
|
sku – REQUIRED
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.
Examples: 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.
=GORILLA_RANKHIST(“2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_RANKHIST(“2019-05”, “US”, “B00YD545CC”, “All”)
To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period.
To get monthly data, use the format YYYY-MM as the preset period.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_RANKHIST(“SELLERID732”, “last 30 days”, A2:A100) =GORILLA_RANKHIST(“SELLERID732”, “custom”, A2:A5, “US”, “top”, “2018-06-01”, “2018-06-30”) =GORILLA_RANKHIST(“SELLERID732”, “this month”, “B00YD545CC”, “JP”, “child”) =GORILLA_RANKHIST(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_RANKHIST(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “All”) |
GORILLA_RANKING( ) Function
Description:
Get Best Sellers Rank (BSR) for the broadest top level category or deepest child category. Only works for single marketplaces at a time. Not “ALL”.
Syntax:
GORILLA_RANKING(sku, [marketplace], [queryType], [returnStyle])
for Gorilla AGENCY, the syntax is
GORILLA_RANKING(sellerID, sku, [marketplace], [queryType], [returnStyle])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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.
Examples: Use 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_RANKING(“B00YD545CC”, “US”, “top”, “r_cat”) Returns BSR of the top category as well as the category name for unit B00YD545CC for the US marketplace.
=GORILLA_RANKING(“A2:A10”, , “top”, “rank”) Supports array inputs. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Default marketplace is used as it is not set.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_RANKING(“SELLERA73J42”, “B00YD545CC”, “US”, “top”, “r_cat”) =GORILLA_RANKING(“SELLERA73J42”, “A2:A10”, , “top”, “r_full”) |
GORILLA_RATINGS( ) Function
Description:
Get the average review rating of a product. Only works for single marketplaces at a time. Not “ALL”.
Syntax:
GORILLA_RATINGS(sku, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_RATINGS(sellerID, sku, [marketplace])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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.
Examples: Use 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_RATINGS(“B00YD545CC”, “CA”) Returns the review rating for ASIN B00YD545CC for Canadian marketplace.
=GORILLA_RATINGS(A2:A10, ,) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37. Default marketplace data is returned as no marketplace is set.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_RATINGS(“SELLERID38”, “B00YD545CC”, “CA”) =GORILLA_RATINGS(“SELLERID38”, A2:A10, ,) |
GORILLA_RATINGSCOUNT( ) Function
Description:
Get the total number of ratings of a product. Includes local and global ratings.
Syntax:
GORILLA_RATINGSCOUNT(sku, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_RATINGSCOUNT(sellerID, sku, [marketplace])
sku – REQUIRED
SKU or ASIN to filter the results. Can be a range of values like A1:A100 or SKU111, SKU222, SKU333 or ASIN123.
marketplace – OPTIONAL
OPTIONAL – Marketplace country filter. Country codes are US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, SE, AU, SG, JP. Use DEFAULT for your market default. Amazon Marketplace ID like ATVPDKIKX0DER can be used too.
Examples: Use 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_RATINGSCOUNT(“B00YD545CC”, “CA”) Returns the review rating for ASIN B00YD545CC for Canadian marketplace.
=GORILLA_RATINGSCOUNT(A2:A10, ,) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37. Default marketplace data is returned as no marketplace is set.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_RATINGSCOUNT(“SELLERID38”, “B00YD545CC”, “CA”) =GORILLA_RATINGSCOUNT(“SELLERID38”, A2:A10, ,) |
GORILLA_REFUNDSCOUNT( ) Function
Description:
Gets total number of units refunded for a product between any time period. Only the period is required. Other attributes are optional.
Syntax:
GORILLA_REFUNDSCOUNT(period, [marketplace], [sku], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REFUNDSCOUNT(sellerID, period, [marketplace], [sku], [start_date], [end_date])
period – REQUIRED
Predefined period to get refunded units. See full list of predefined periods below.
|
|
|
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.
sku – OPTIONAL
Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string. If no SKUs are specified, it will load the total number across the account.
start_date – OPTIONAL
Starting date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
Ending date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REFUNDSCOUNT(“THIS YEAR”) Returns the total count of refunded units for this year. No SKUs are specified which will show the count across the entire account. E.g. if the result is 200, then it means that 200 units were refunded for “this year”.
=GORILLA_REFUNDSCOUNT(“LAST YEAR”,”US”, A2:A34) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A34 and returns the count of the refunded units for each SKU/ASIN. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_REFUNDSCOUNT(“CUSTOM”, “FR”, A2:A34, “2020-01-01”, “2020-01-15”) Returns the total count of refunded units based on a custom date. The start date is 2020-01-01 and the end date is 2020-01-15. The SKU/ASIN is listed in A2 to A34. The marketplace is defined as France.
=GORILLA_REFUNDSCOUNT(“2020”, “US”, B7:B27) =GORILLA_REFUNDSCOUNT(“2020Q1”, “US”, A1:A100)
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REFUNDSCOUNT(“SELLERID38”, “THIS YEAR”) =GORILLA_REFUNDSCOUNT(“SELLERID38”, “LAST YEAR”,”US”, A2:A34) =GORILLA_REFUNDSCOUNT(“SELLERID38”, “CUSTOM”, “FR”, A2:A34, “2020-01-01”, “2020-01-15”) =GORILLA_REFUNDSCOUNT(“SELLERID38”, “2020”, “US”, B7:B27) =GORILLA_REFUNDSCOUNT(“SELLERID38”, “2020Q3”, “US”, A1:A100) |
GORILLA_REFUNDSORDER( ) Function
Description:
Gets the list of all orders that were refunded. Filter by SKU to track your refunded orders.
WARNING: For large sellers, do NOT use this function for a long time period, like “last year”. It will try to load thousands and thousands of orders. Google will timeout and will block your requests for the next 24hrs as it thinks you are a bot.
Syntax:
GORILLA_REFUNDSORDER(period, [filter], [marketplace], [status], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REFUNDSORDER(sellerID, period, [filter], [marketplace], [status], [start_date], [end_date])
period – REQUIRED
Predefined period to get refunded units. See full list of predefined periods below.
|
|
|
filter – OPTIONAL
SKU, ASIN or Amazon Order Id to filter the results. Can be a range of values like A1:A100 or SKU111, SKU222, SKU333 or ASIN123..
marketplace – OPTIONAL
Marketplace country filter. Country codes are US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, SE, AU, SG, JP. Use ALL to combine values from a single account, EU for all Europe markets EXCEPT UK, EURO to combine countries using EURO currency. Amazon Marketplace ID like ATVPDKIKX0DER can be used too.
status – OPTIONAL
Order status. Valid values are:
- All
- Canceled
- Pending
- PartiallyShipped
- Unshipped
- Shipped
- Shipping
start_date – OPTIONAL
Starting date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
Ending date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REFUNDSORDER(“LAST 30 DAYS”) Returns the list of all orders that were refunded in the last 30 days. No SKUs are specified which will pull the orders for all SKUs that had a refund in the time period.
=GORILLA_REFUNDSORDER(“THIS MONTH” ,”SKU111,SKU222,SKU333″, “DE”) =GORILLA_REFUNDSORDER(“THIS MONTH” , A1:A34, “DE”) Returns a list of all refunded orders this month for the 3 SKUs manually entered for the German market. It will not show other SKU refunded orders. Supports bulk SKUs by using ranges in your spreadsheet. You can have a list of SKUs or ASINs in A2:A34.
=GORILLA_REFUNDSORDER(“CUSTOM”, A2:A34, “FR”, “ALL”, “2022-01-01”, “2022-01-15”) Returns the list of refunded orders for the SKUs listed in A2:A34 in the French market between the custom start and end date.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REFUNDSORDER(sellerID, “LAST 30 DAYS”) =GORILLA_REFUNDSORDER(seller ID, “THIS MONTH” ,”SKU111,SKU222,SKU333″, “DE”) =GORILLA_REFUNDSORDER(seller ID, “THIS MONTH” , A1:A34, “DE”) =GORILLA_REFUNDSORDER(seller ID, “CUSTOM”, A2:A34, “FR”, “ALL”, “2022-01-01”, “2022-01-15”) |
GORILLA_REFUNDSTOTAL( ) Function
Description:
Gets total value in dollars of units refunded for a product between any time period. Only the period is required. Other attributes are optional.
Syntax:
GORILLA_REFUNDSTOTAL(period, [marketplace], [sku], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REFUNDSTOTAL(sellerID, period, [marketplace], [sku], [start_date], [end_date])
period – REQUIRED
Predefined period to get the refunds value. See full list of predefined periods below.
|
|
|
marketplace – OPTIONAL
OPTIONAL – Marketplace filter. Use Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
OPTIONAL – Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string. If no SKUs are specified, it will load the total number across the account.
start_date – OPTIONAL
OPTIONAL – Starting date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
OPTIONAL – Ending date to get sales from when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REFUNDSTOTAL(“THIS YEAR”) Returns the total count of refunded units for this year. No SKUs are specified which will show the count across the entire account. E.g. if the result is 200, then it means that 200 units were refunded for “this year”.
=GORILLA_REFUNDSTOTAL(“LAST YEAR”,”US”, A2:A34) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A34 and returns the count of the refunded units for each SKU/ASIN. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_REFUNDSTOTAL(“CUSTOM”, “FR”, A2:A34, “2020-01-01”, “2020-01-15”) Returns the total count of refunded units based on a custom date. The start date is 2020-01-01 and the end date is 2020-01-15. The SKU/ASIN is listed in A2 to A34. The marketplace is defined as France.
=GORILLA_REFUNDSTOTAL(“2020”, “US”, B7:B27) =GORILLA_REFUNDSTOTAL(“2020Q1”, “US”, A1:A100)
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REFUNDSTOTAL(“SELLERID38”, “THIS YEAR”) =GORILLA_REFUNDSTOTAL(“SELLERID38”, “LAST YEAR”,”US”, A2:A34) =GORILLA_REFUNDSTOTAL(“SELLERID38”, “CUSTOM”, “FR”, A2:A34, “2020-01-01”, “2020-01-15”) =GORILLA_REFUNDSTOTAL(“SELLERID38”, “2020”, “US”, B7:B27) =GORILLA_REFUNDSTOTAL(“SELLERID38”, “2020Q3”, “US”, A1:A100) |
GORILLA_REIMBURSEDETAILS( ) Function
Description:
Dump the reimbursement report showing all details of the reimbursement.
Syntax:
GORILLA_REFUNDSTOTAL(period, [filter], [reason], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REFUNDSTOTAL(sellerID, period, [filter], [reason], [start_date], [end_date])
period – REQUIRED
Predefined period to get the refunds value. See full list of predefined periods below.
|
|
|
filter – OPTIONAL
OPTIONAL – SKU, ASIN or FNSKU to filter the results. Can be a range of values like A1:A100 where A1 to A100 consists of values like SKU111, SKU222, SKU333 or ASIN123.
reason – OPTIONAL
OPTIONAL – Reason for adjustment. Valid values:
- ALL (default)
- CustomerReturn
- Damaged_Warehouse
- CustomerServiceIssue
- Lost_Warehouse
- Lost_Inbound
- Reimbursement_Reversal
- FeeCorrection
- Lost_Outbound
- ReturnAdjustment
- Damaged_Inbound
- Damaged_Outbound
start_date – OPTIONAL
OPTIONAL – Starting date for when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
OPTIONAL – Ending date for when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REIMBURSEDETAILS(“LAST 7 DAYS”) Returns a details list of all reimbursed transactions and SKU over the last 7 days. No SKUs are specified which will show all transactions across the entire account.
=GORILLA_REIMBURSEDETAILS(“THIS YEAR”, A2:A10) Supports 2 dimensional ranges. Bulk return the reimbursement details of the SKUs listed in A2:A10. Other SKUs will not be loaded.
=GORILLA_REIMBURSEDETAILS(“CUSTOM”, , “WAREHOUSE_DAMAGED”, “2022-01-01”, “2022-01-15”) Returns all warehouse damaged related reimbursements for all the SKUs in the account. Data is loaded for the custom date period.
=GORILLA_REIMBURSEDETAILS(“2022”, B7:B27, “LOST_INBOUND”) Returns data for Lost Inbound SKUs listed in B7:B27 for 2022.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REIMBURSEDETAILS(“SELLERID38”, “LAST 7 DAYS”) =GORILLA_REIMBURSEDETAILS(“SELLERID38”, “THIS YEAR”, A2:A10) =GORILLA_REIMBURSEDETAILS(“SELLERID38”, “CUSTOM”, , “WAREHOUSE_DAMAGED”, “2022-01-01”, “2022-01-15”) =GORILLA_REIMBURSEDETAILS(“SELLERID38”, “2022”, B7:B27, “LOST_INBOUND”) |
GORILLA_REIMBURSEINVENTORY( ) Function
Description:
Get full details of the inventory reimbursements applied to your account.
Syntax:
GORILLA_REIMBURSEINVENTORY(period, [filter], [reason], [unreconciled], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REIMBURSEINVENTORY(sellerID, period, [filter], [reason], [unreconciled], [start_date], [end_date])
period – REQUIRED
Predefined period to get the refunds value. See full list of predefined periods below.
|
|
|
filter – OPTIONAL
OPTIONAL – SKU, ASIN or FNSKU to filter the results. Can be a range of values like A1:A100 where A1 to A100 consists of values like SKU111, SKU222, SKU333 or ASIN123.
reason – OPTIONAL
OPTIONAL – Reason for adjustment. Can be a range of values like A1:A100. When empty, the default value will look up reimbursment code E, M.
Valid values:
|
|
unreconciled – OPTIONAL
OPTIONAL – A shortcut filter to show Unreconciled transactions. Valid values are: ALL (default), TRUE qty greater than 0, FALSE shows qty equal to 0.
start_date – OPTIONAL
OPTIONAL – Starting date for when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
OPTIONAL – Ending date for when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REIMBURSEINVENTORY(“LAST 7 DAYS”) Returns a detailed list of all inventory that was reimbursed over the last 7 days. Returns the values for SKU, FNSKU, Transaction ID, Fulfillment Center, Country, State, Quantity, Reason, Disposition, Reconciled and Unreconciled.
=GORILLA_REIMBURSEINVENTORY(“THIS YEAR”, A2:A10) Supports 2 dimensional ranges. Bulk return the reimbursement details of the SKUs listed in A2:A10. Other SKUs will not be loaded.
=GORILLA_REIMBURSEINVENTORY(“CUSTOM”, , “DEFAULT”, , “2022-01-01”, “2022-01-15”) Returns all line items where the inventory reimbursement reason code is E or M between the two dates.
=GORILLA_REIMBURSEINVENTORY(“2022”, A2:A10, A1:K10, “TRUE”) Returns the data for only the SKUs in A2:A10 and the reason codes listed in A1:K10 where the unreconciled quantity is greater than 0. This is to quickly show only the inventories that are unreconciled where you can see how much Amazon owes you.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REIMBURSEINVENTORY(“SELLERID38”, “LAST 7 DAYS”) =GORILLA_REIMBURSEINVENTORY(“SELLERID38”, “THIS YEAR”, A2:A10) =GORILLA_REIMBURSEINVENTORY(“SELLERID38”, “CUSTOM”, , “DEFAULT”, , “2022-01-01”, “2022-01-15”) =GORILLA_REIMBURSEINVENTORY(“SELLERID38”, “2022”, A2:A10, A1:K10, “TRUE”) |
GORILLA_REIMBURSERETURNS( ) Function
Description:
Get the customer returns report and all the details showing what was returned, the reason, disposition, LPN and more.
Syntax:
GORILLA_REIMBURSERETURNS(period, [filter], [status], [disposition], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REIMBURSERETURNS(sellerID, period, [filter], [status], [disposition], [start_date], [end_date])
period – REQUIRED
Predefined period to get the refunds value. See full list of predefined periods below.
|
|
|
filter – OPTIONAL
OPTIONAL – SKU, ASIN or FNSKU to filter the results. Can be a range of values like A1:A100 where A1 to A100 consists of values like SKU111, SKU222, SKU333 or ASIN123.
status – OPTIONAL
OPTIONAL – Product status.
Valid values:
- ALL (default)
- Unit returned to inventory
- Reimbursed
disposition – OPTIONAL
OPTIONAL – Product disposition.
Valid values:
- All
- Sellable
- Nonsellable
- Customer_damaged
- Carrier_damaged
- Defective
- Damaged
- Distributor_damaged
- Carrier_damaged.
If disposition is left empty, the default option will return all dispositions except sellable.
start_date – OPTIONAL
OPTIONAL – Starting date to for when period is custom. To use custom dates, specify the period as “CUSTOM”.
end_date – OPTIONAL
OPTIONAL – Ending date for when period is custom. To use custom dates, specify the period as “CUSTOM”.
Examples: Use 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_REIMBURSERETURNS(“LAST 7 DAYS”) Returns a detailed list of all returns that was reimbursed over the last 7 days. Returns the values for SKU, FNSKU, ASIN, Order Id, Fulfillment Center Id, Country, State, Quantity, Reason, Disposition, Status, License Plate Number, Customer Comments.
=GORILLA_REIMBURSERETURNS(“THIS YEAR”, A2:A10) Supports 2 dimensional ranges. Bulk return the return reimbursement details of the SKUs listed in A2:A10. Other SKUs will not be loaded.
=GORILLA_REIMBURSERETURNS(“CUSTOM”, , “Unit Returned to Inventory”, , “2022-01-01”, “2022-01-15”) Returns all line items where the inventory reimbursement status is “Unit Returned to Inventory” between the two dates.
=GORILLA_REIMBURSERETURNS(“2022”, , “ALL”, “ALL”) Returns the returns data for all SKUs in 2022 for all Status and All dispositions.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REIMBURSERETURNS(“SELLERID38”, “LAST 7 DAYS”) =GORILLA_REIMBURSERETURNS(“SELLERID38”, “THIS YEAR”, A2:A10) =GORILLA_REIMBURSERETURNS(“SELLERID38”, “CUSTOM”, , “Unit Returned to Inventory”, , “2022-01-01”, “2022-01-15”) =GORILLA_REIMBURSERETURNS(“SELLERID38”, “2022”, , “ALL”, “ALL”) |
GORILLA_REVIEW( ) Function
Description:
Get the full reviews and details of a product based on SKU or ASIN. Only works for single marketplaces at a time. Not “ALL”.
Syntax:
GORILLA_REVIEW(sku, [marketplace], [count])
for Gorilla AGENCY, the syntax is
GORILLA_REVIEW(sellerID, sku, [marketplace], [count])
sku – REQUIRED
SKU or ASIN of the product. Only one accepted.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
count – OPTIONAL
Number of reviews to retrieve (default value is 5) or “ALL” to retrieve all reviews
Examples: 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_REVIEW(“B00YD545CC”, “US”, 5) Returns the latest 5 reviews, title, name, link and rating for ASIN B00YD545CC selling in the USA marketplace.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REVIEW(“SELLER832”, “B00YD545CC”, “US”, 5) =GORILLA_REVIEW(“SELLER832”, A2:A10, ,) |
GORILLA_REVIEWCOUNT( ) Function
Description:
Get the number of reviews for a given product based on SKU or ASIN. Only works for single marketplaces at a time. Not “ALL”.
Syntax:
GORILLA_REVIEWCOUNT(sku, [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_REVIEWCOUNT(sellerID, sku, [marketplace])
sku – REQUIRED
SKU or ASIN of the product. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
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.
Examples: 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_REVIEWCOUNT(“B00YD545CC”, “CA”) Returns the number of reviews for ASIN B00YD545CC selling in the Canadian marketplace.
=GORILLA_REVIEWCOUNT(A2:A10, ,) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37. Default marketplace data is returned as no marketplace is set.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REVIEWCOUNT(“SELLER832J”, “B00YD545CC”, “CA”) =GORILLA_REVIEWCOUNT(“SELLER832J”, A2:A10, ,) |
GORILLA_REVIEWSFILTER( ) Function
Description:
Gets total number of 1,2,3,4,5 or all reviews for a product between any time period. Only the time period is required. Other attributes are optional.
Syntax:
GORILLA_REVIEWSFILTER(period, [marketplace], [sku], [stars], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_REVIEWSFILTER(sellerID, period, [marketplace], [sku], [status], [start_date], [end_date])
period – REQUIRED
Predefined period to get the number of reviews. See full list of predefined periods below.
|
|
|
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
stars – OPTIONAL
The review star rating to filter for
- All (default)
- 1
- 2
- 3
- 4
- 5
start_date – OPTIONAL
Starting date to get number of reviews from when period is custom.
end_date – OPTIONAL
Ending date to get number of reviews from when period is custom.
Examples: Use 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_REVIEWSFILTER(“Custom”, “US”, “B00YD545CC”, “5”, “2021-01-01”, “2021-01-21”) Gets total number of 5 star reviews for B00YD545CC in the US marketplace between Jan 1, 2021 and Jan 21, 2021.
=GORILLA_REVIEWSFILTER(“This Week”,”ALL”,A2:A10,”ALL”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns the count of all reviews 1-5. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_REVIEWSFILTER(“2019Q1”, “US”, “B00YD545CC”, “1”) =GORILLA_REVIEWSFILTER(“2019-05”, “US”, “B00YD545CC”, “3”) To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period.
To get monthly data, use the format YYYY-MM as the preset period.
=GORILLA_REVIEWSFILTER(“This Month”, “ALL”, “IPHN3724”, “5”) Gets the total number of 5 star reviews for SKU IPHN3724 from all marketplaces.
=GORILLA_REVIEWSFILTER(“Same Day Last Month”, “ALL”, “IPHN3724”, “1”) Gets the total number of 1 star reviews for SKU IPHN3724 from all marketplaces.
=GORILLA_REVIEWSFILTER(“This Year”) Gets the total number of reviews all products, account across all marketplaces.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_REVIEWSFILTER(“SELLERID83”, “Custom”, “US”, “B00YD545CC”, “5”, “2021-01-01”, “2021-01-21”) =GORILLA_REVIEWSFILTER(“SELLERID83”, “This Week”,”ALL”,A2:A10,”ALL”) =GORILLA_REVIEWSFILTER(“SELLERID83”, “2019Q1”, “US”, “B00YD545CC”, “1”) =GORILLA_REVIEWSFILTER(“SELLERID83”, “2019-05”, “US”, “B00YD545CC”, “3”) =GORILLA_REVIEWSFILTER(“SELLERID83”, “This Month”, “ALL”, “IPHN3724”, “5”) =GORILLA_REVIEWSFILTER(“SELLERID83”, “Same Day Last Month”, “ALL”, “IPHN3724”, “1”) |
GORILLA_SALESCOUNT( ) Function
Description:
Gets total number of units sold for a product between any time period. Only period is required. Other attributes are optional.
Syntax:
GORILLA_SALESCOUNT(period, [marketplace], [sku], [status], [mcf], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_SALESCOUNT(sellerID, period, [marketplace], [sku], [status], [mcf], [start_date], [end_date])
period – REQUIRED
Predefined period to get sales. See full list of predefined periods below.
|
|
|
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
status – OPTIONAL
Order Status. Valid values are:
- AllButCanceled(default)
- All
- PendingAvailability (JP only)
- Pending
- Unshipped
- PartiallyShipped
- Shipped
- InvoiceUnconfirmed
- Canceled
- Unfulfillable
mcf – OPTIONAL
- OPTIONAL – Whether to display, include or exclude MCF orders.
- Exclude (default option is not specified)
- Include
- Only (only displays data for MCF orders. Does not show data for FBA.)
start_date – OPTIONAL
Starting date to get sales from when period is custom.
end_date – OPTIONAL
Ending date to get sales from when period is custom.
Examples: Use 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_SALESCOUNT(“Custom”, “US”, “B00YD545CC”, “Shipped”, “Exclude”, “2018-01-01”, “2018-01-21”) Gets total number of units sold for B00YD545CC that has been shipped in the US marketplace between Jan 1, 2018 and Jan 21, 2018. Excludes MCF orders.
=GORILLA_SALESCOUNT(“This Week”,”ALL”,A2:A10,”ALL”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_SALESCOUNT(“2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESCOUNT(“2019-05”, “US”, “B00YD545CC”, “All”) To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period. To get monthly data, use the format YYYY-MM as the preset period.
=GORILLA_SALESCOUNT(“This Month”, “ALL”, “IPHN3724”, “Canceled”) Gets the total number of units for SKU IPHN3724 from all marketplaces that were canceled this month.
=GORILLA_SALESCOUNT(“Same Day Last Month”, “ALL”, “IPHN3724”, “ALL”) Gets the total number of units for SKU IPHN3724 from all marketplaces that are sold and pending.
=GORILLA_SALESCOUNT(“This Year”) Gets the total number of units sold for all products, account across all marketplaces.
=GORILLA_SALESCOUNT(“2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESCOUNT(“2019-05”, “US”, “B00YD545CC”, “All”) To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset period. To get monthly data, use the format YYYY-MM as the preset period.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SALESCOUNT(“SELLER37432”, “Custom”, “US”, “B00YD545CC”, “Include”, “Shipped”, “2018-01-01”, “2018-01-21”) =GORILLA_SALESCOUNT(“SELLER37432”, “This Week”,”ALL”,A2:A10,”ALL”) =GORILLA_SALESCOUNT(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESCOUNT(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESCOUNT(“SELLER37432”, “This Month”, “ALL”, “IPHN3724”, “Canceled”) =GORILLA_SALESCOUNT(“SELLER37432”, “Same Day Last Month”, “ALL”, “IPHN3724”, “ALL”) =GORILLA_SALESCOUNT(“SELLER37432”, “This Year”) =GORILLA_SALESCOUNT(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESCOUNT(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “All”) |
GORILLA_SALESCOUNTHIST( ) Function
Description:
The fastest way to get a daily count of historical sales between any two dates.
Syntax:
GORILLA_SALESCOUNTHIST([sku], [marketplace], [status], [mcf], [days], [start_date], [end_date], [transpose])
for Gorilla AGENCY, the syntax is
GORILLA_SALESCOUNTHIST(sellerID, [sku], [marketplace], [status], [mcf], [days], [start_date], [end_date], [transpose])
sku – OPTIONAL
SKU or ASIN. Select or enter a range of values.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
status – OPTIONAL
Order Status. Valid values are:
- AllButCanceled(default)
- All
- PendingAvailability (JP only)
- Pending
- Unshipped
- PartiallyShipped
- Shipped
- InvoiceUnconfirmed
- Canceled
- Unfulfillable
mcf – OPTIONAL
- Whether to display, include or exclude MCF orders.
- Exclude (default option is not specified)
- Include
- Only (only displays data for MCF orders. Does not show data for FBA.)
days – OPTIONAL
By default, the last 30 days is pulled. Enter the number of days to display in the results.
start_date – OPTIONAL
Only works if “days” is left blank. Starting date to get sales between any two periods. Date format should be in YYYY-MM-DD.
end_date – OPTIONAL
Only works if “days” is left blank. End date to get sales between any two periods. Date format should be in YYYY-MM-DD.
transpose – OPTIONAL
Default is set to “YES”. It will display the SKUs on the left side and the dates horizontally.
“NO” will display the SKUs in a horizontal row at the time. Dates vertically down.
Examples: Use 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_SALESCOUNTHIST() When no attributes are entered into the formula, it will create a table of the daily units sold over the last 30 days of the entire account.
=GORILLA_SALESCOUNTHIST(“B00YD545CC”) Pulls in the daily units sold over the last 30 days of ASIN B00YD545CC into a table.
=GORILLA_SALESCOUNTHIST(A1:A20) Pulls in the daily units sold over the last 30 days for the SKUs or ASINs listed in cell A1 to A20.
=GORILLA_SALESCOUNTHIST(A1:A10, “US”, “ALL”, “EXCLUDE”, 60) Returns a table of daily historical sales for all SKUs in cell A1 to A10, in the US marketplace only, the order status includes all, excludes MCF (Merchant Fulfilled) sales for the last 60 days.
=GORILLA_SALESCOUNTHIST(A1:A10, “US”, “ALL”, “EXCLUDE”, 60, , ,”YES”) This returns the same table as above, but is transposed where the SKU is displayed vertically.
=GORILLA_SALESCOUNTHIST(A1:A10, “DE”, “Canceled”, “EXCLUDE”, , “2020-01-01”, “2020-03-12”) Returns a table of daily historical sales for all SKUs in cell A1 to A10, in the German marketplace only, the order status is canceled, excludes MCF (Merchant Fulfilled) sales, between the two dates in the formula. The “days” field is blank so that the start and end date will activate. If days value is entered, then the start and end date won’t work.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SALESCOUNTHIST(“SELLER37432”) =GORILLA_SALESCOUNTHIST(“SELLER37432”, “B00YD545CC”) =GORILLA_SALESCOUNTHIST(“SELLER37432”, A1:A20) =GORILLA_SALESCOUNTHIST(“SELLER37432”, A1:A10, “US”, “ALL”, “EXCLUDE”, 60) =GORILLA_SALESCOUNTHIST(“SELLER37432”, A1:A10, “US”, “ALL”, “EXCLUDE”, 60, , ,”YES”) =GORILLA_SALESCOUNTHIST(“SELLER37432”, A1:A10, “DE”, “Canceled”, “EXCLUDE”, , “2020-01-01”, “2020-03-12”) |
GORILLA_SALESTAX( ) Function – Removed temporarily
Description:
Get total sales and taxes collected by Amazon Marketplace Facilitator owed by the seller. Only the period attribute is required. Other attributes are optional.
Syntax:
GORILLA_SALESTAX(period, [marketplace], [grouping], [start_date], [end_date], [header])
for Gorilla AGENCY, the syntax is
GORILLA_SALESTAX(sellerID, period, [marketplace], [grouping], [start_date], [end_date], [header])
period – REQUIRED
Predefined period to get sales or custom to specify dates. See full list of predefined periods below.
|
|
|
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
grouping – OPTIONAL
The grouping level for results. Select to group the totals by city or by state. Default is total by City.
start_date – OPTIONAL
Starting date to get sales from when period is custom.
end_date – OPTIONAL
Ending date to get sales from when period is custom.
header – OPTIONAL
Include a header line in the results.
Possible values: TRUE/FALSE.
Default: TRUE
Examples: Use 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_SALESTAX(“LAST MONTH”) The easiest way to use this function. Define the period to get the total sales and taxes collected by city and state.
=GORILLA_SALESTAX(“THIS MONTH”, “UK”, “STATE”) Loads the sales and the taxes collected for the period “this month” in the UK marketplace and the results are totaled by state.
=GORILLA_SALESTAX(“CUSTOM”, “UK”, “CITY”, “2020-01-01”, “2020-01-15”, “FALSE”) Get sales tax data over a custom period. The results are based on the UK marketplace with results totaled by city between Jan 1, 2020 and Jan 15, 2020.
The “FALSE” attribute means the header of the table is not generated.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SALESTAX(“SELLER382”, “LAST MONTH”) =GORILLA_SALESTAX(“SELLER382”, “THIS MONTH”, “UK”, “STATE”) =GORILLA_SALESTAX(“SELLER382”, “CUSTOM”, “UK”, “CITY”, “2020-01-01”, “2020-01-15”, “FALSE”)
|
GORILLA_SALESTOTAL( ) Function
Description:
Get total sales dollar amount for any product for any specified time period. Only the period attribute is required. Other attributes are optional.
Syntax:
GORILLA_SALESTOTAL(period, [marketplace], [sku], [status], [mcf], [start_date], [end_date])
for Gorilla AGENCY, the syntax is
GORILLA_SALESTOTAL(sellerID, period, [marketplace], [sku], [status], [start_date], [end_date])
period – REQUIRED
Predefined period to get sales. See full list of predefined periods below.
|
|
|
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
status – OPTIONAL
Order Status. Valid values below.
- All
- Unshipped
- InvoiceUnconfirmed
- PendingAvailability (JP marketplace only)
- PartiallyShipped
- Pending
- Canceled
- Shipped
- Unfulfillable
mcf – OPTIONAL
Whether to display, include or exclude MCF orders.
- Exclude (default option is not specified)
- Include
- Only (only displays data for MCF orders. Does not show data for FBA.)
start_date – OPTIONAL
Starting date to get sales from when period is custom.
end_date – OPTIONAL
Ending date to get sales from when period is custom.
Examples: Use 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_SALESTOTAL(“Custom”, “US”, “B00YD545CC”, “Shipped”, “Exclude”, “2018-01-01”, “2018-01-21”) Returns total dollar in sales for unit B00YD545CC that has been shipped in the US marketplace between Jan 1, 2018 and Jan 21, 2018. Excludes MCF orders from the data.
=GORILLA_SALESTOTAL(“This Week”,”ALL”,A2:A10,”ALL”) Supports 2 dimensional ranges. Bulk return an array of values by assigning the range of the SKU or ASIN. In this example the SKU or ASIN is listed in A2:A10 and returns all values at once. Can also work for rows and ranges like A1:Z1 or D3:G37.
=GORILLA_SALESTOTAL(“This Month”, “ALL”, “IPHN3724”, “Canceled”) Gets the total dollar amount for SKU IPHN3724 from all marketplaces that were canceled this month.
=GORILLA_SALESTOTAL(“Same Day Last Month”, “ALL”, “IPHN3724”, “pending”) Gets the total dollar in sales for SKU IPHN3724 from all marketplaces that are sold and pending.
=GORILLA_SALESTOTAL(“This Year”) Gets the total dollar in sales for all products, across all marketplaces, so far year to date. =GORILLA_SALESTOTAL(“2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALETOTAL(“2019-05”, “US”, “B00YD545CC”, “All”)
To get quarterly data use the format YYYYQ1, YYYYQ2, YYYYQ3, YYYYQ4 as the preset .
To get monthly data, use the format YYYY-MM as the preset period.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SALESTOTAL(“SELLER382”, “Custom”, “US”, “B00YD545CC”, “Exclude”, “Shipped”, “2018-01-01”, “2018-01-21”) =GORILLA_SALESTOTAL(“SELLER382”, “This Week”,”ALL”,A2:A10,”ALL”) =GORILLA_SALESTOTAL(“SELLER382”, “This Month”, “ALL”, “IPHN3724”, “Canceled”) =GORILLA_SALESTOTAL(“SELLER382”, “Same Day Last Month”, “ALL”, “IPHN3724”, “pending”) =GORILLA_SALESTOTAL(“SELLER382”, “This Year”) =GORILLA_SALESTOTAL(“SELLER37432”, “2019Q1”, “US”, “B00YD545CC”, “All”) =GORILLA_SALESTOTAL(“SELLER37432”, “2019-05”, “US”, “B00YD545CC”, “All”)
|
GORILLA_SALESTOTALHIST( ) Function
Description:
The fastest way to get a daily sales total of historical sales between any two dates.
Syntax:
GORILLA_SALESTOTALHIST([sku], [marketplace], [status], [mcf], [days], [start_date], [end_date], [transpose])
for Gorilla AGENCY, the syntax is
GORILLA_SALESTOTALHIST(sellerID, [sku], [marketplace], [status], [mcf], [days], [start_date], [end_date], [transpose])
sku – OPTIONAL
SKU or ASIN. Select or enter a range of values.
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
sku – OPTIONAL
Any Seller SKU/ASIN to limit results. Can be an array of strings like {“SKU12345″,”ASIN12345”} or a string.
status – OPTIONAL
Order Status. Valid values are:
- AllButCanceled (default)
- AllPendingAvailability (JP only)
- Pending
- Unshipped
- PartiallyShipped
- Shipped
- InvoiceUnconfirmed
- Canceled
- Unfulfillable
mcf – OPTIONAL
Whether to display, include or exclude MCF orders.
- Exclude (default option is not specified)
- Include
- Only (only displays data for MCF orders. Does not show data for FBA.)
days – OPTIONAL
By default, the last 30 days is pulled. Enter the number of days to display in the results.
start_date – OPTIONAL
Starting date to get sales from when period is custom.
end_date – OPTIONAL
Ending date to get sales from when period is custom.
transpose – OPTIONAL
Default is set to “NO”. It will display the SKUs on the top row horizontally.
“YES” will display the SKUs in a column. Vertically down.
Examples: Use 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_SALESTOTALHIST() When no attributes are entered into the formula, it will create a table of the daily sales figures over the last 30 days of the entire account.
=GORILLA_SALESTOTALHIST(“B00YD545CC”) Pulls in the daily sales figures over the last 30 days of ASIN B00YD545CC into a table.
=GORILLA_SALESTOTALHIST(A1:A20) Pulls in the daily sales figures over the last 30 days for the SKUs or ASINs listed in cell A1 to A20.
=GORILLA_SALESTOTALHIST(A1:A10, “US”, “ALL”, “EXCLUDE”, 60) Returns a table of daily historical sales figures for all SKUs in cell A1 to A10, in the US marketplace only, the order status includes all, excludes MCF (Merchant Fulfilled) sales for the last 60 days.
=GORILLA_SALESTOTALHIST(A1:A10, “US”, “ALL”, “EXCLUDE”, 60, , ,”YES”) Returns the same table as the previous example, but with the SKUs in a vertical column.
=GORILLA_SALESTOTALHIST(A1:A10, “DE”, “Canceled”, “EXCLUDE”, , “2020-01-01”, “2020-03-12”) Returns a table of daily historical sales for all SKUs in cell A1 to A10, in the German marketplace only, the order status is canceled, excludes MCF (Merchant Fulfilled) sales, between the two dates in the formula. The “days” field is blank so that the start and end date will activate. If days value is entered, then the start and end date won’t work. For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SALESTOTALHIST(“SELLER37432”) =GORILLA_SALESTOTALHIST(“SELLER37432”, “B00YD545CC”) =GORILLA_SALESTOTALHIST(“SELLER37432”, A1:A20) =GORILLA_SALESTOTALHIST(“SELLER37432”, A1:A10, “US”, “ALL”, “EXCLUDE”, 60) =GORILLA_SALESTOTALHIST(“SELLER37432”, A1:A10, “US”, “ALL”, “EXCLUDE”, 60, , ,”YES”) =GORILLA_SALESTOTALHIST(“SELLER37432”, A1:A10, “DE”, “Canceled”, “EXCLUDE”, , “2020-01-01”, “2020-03-12”)
|
GORILLA_SKUASIN( ) Function
Description:
Convert ASIN to SKU and SKU to ASIN. Bulk convert compatible.
Syntax:
GORILLA_SKUASIN(SKU/ASIN, [direction], [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_SKUASIN(sellerID, SKU/ASIN, [direction], [marketplace])
sku – REQUIRED
Product’s SKU/ASIN. Can be an array of strings like {“ASIN12345″,”ASIN67890”} or a string.
direction – OPTIONAL
- sku2asin
- asin2sku
marketplace – OPTIONAL
- enter the marketplace you want to get convert to. If you have multiple accounts and the same sku across different marketplaces, use this to narrow the data down.
Examples: 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_SKUASIN(“IPHN3724”) Displays the ASIN for SKU IPHN3724. As the direction is not set, the function assumes the value is a SKU and will return the ASIN. If you have a long list of SKU’s from a file, you can get the matching ASIN quickly with this function.
=GORILLA_SKUASIN(“B00YD545CC”, “sku2asin”, “DE”) When the direction of sku2asin is entered, it will convert from SKU to ASIN. If you have a long list of SKU’s from a file, you can get the matching ASIN quickly with this function.
=GORILLA_SKUASIN(D33:E39, “asin2sku”) Return an array of SKU’s by entering a column, row or range of ASIN’s like this example.
=GORILLA_SKUASIN(D33:E39, “sku2asin”) Return an array of ASIN’s by entering a column, row or range of ASIN’s like this example.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SKUASIN(“SELLER832J”, “IPHN3724”) =GORILLA_SKUASIN(“SELLER832J”, “B00YD545CC”, “asin2sku”, “DE”) =GORILLA_SKUASIN(“SELLER832J”, D33:E39, “asin2sku”) =GORILLA_SKUASIN(“SELLER832J”, D33:E39, “sku2asin”)
|
GORILLA_SKUASINPARENT( ) Function
Description:
Get the parent SKU or ASIN based on a child SKU or ASIN. Can be done in bulk using ranges.
Syntax:
GORILLA_SKUASINPARENT(SKU/ASIN, [direction], [marketplace])
for Gorilla AGENCY, the syntax is
GORILLA_SKUASINPARENT(sellerID, SKU/ASIN, [direction], [marketplace])
sku – REQUIRED
Product’s SKU/ASIN. Can be an array of strings like {“ASIN12345″,”ASIN67890”} or a big list.
direction – OPTIONAL
- skuparent
- asinparent
marketplace – OPTIONAL
- enter the marketplace you want to get convert to. If you have multiple accounts and the same sku across different marketplaces, use this to narrow the data down.
Examples: 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_SKUASINPARENT(“SKU1234”) For a child product SKU1234, this formula will return the parent SKU by default as the direction has not been entered. The default is to return the parent SKU if nothing is entered.
=GORILLA_SKUASINPARENT(“B00YD545CC”, “ASINPARENT”, “DE”) When the direction of ASINPARENT is entered, it will load the parent ASIN of the SKU or ASIN you have entered.If the child SKU is sold in multiple marketplaces, you can narrow it down with the marketplace filter.
=GORILLA_SKUASINPARENT(A1:A1000, “SKUPARENT”) Bulk loads the parent SKU for the SKU or ASINs listed in A1:A1000.
=GORILLA_SKUASINPARENT(A1:Z1, “SKUPARENT”, “US”) If you have your SKU or ASIN listed horizontally, this will show the parent SKU for the US marketplace.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SKUASINPARENT(“SELLER832J”, “SKU1234”) =GORILLA_SKUASINPARENT(“SELLER832J”, “B00YD545CC”, “ASINPARENT”, “DE”) =GORILLA_SKUASINPARENT(“SELLER832J”, A1:A1000, “SKUPARENT”) =GORILLA_SKUASINPARENT(“SELLER832J”, A1:Z1, “SKUPARENT”)
|
GORILLA_SKUFNSKU( ) Function
- Description:
- Convert single or bulk SKU or ASIN to FNSKU and vice versa.
- Syntax:
- GORILLA_SKUFNSKU(sku, [direction], [marketplace])
- for Gorilla AGENCY, the syntax is
GORILLA_SKUFNSKU(sellerID, sku or asin, [direction], [marketplace])
sku – REQUIRED
Product’s SKU/ASIN. Can be an array of strings like {“ASIN12345″,”ASIN67890”} or a range like A2:A100 where cells 2 to 100 in column A would all have SKU’s or ASIN’s.
direction – OPTIONAL
- sku2fnsku
- fnsku2sku
marketplace – OPTIONAL
- enter the marketplace you want to get convert to. If you have multiple accounts and the same sku across different marketplaces, use this to narrow the data down.
Examples: Use ranges in your formula wherever possible for quicker results and to prevent Google blocking your account for 24 hrs due to overloading their resources.
=GORILLA_SKUFNSKU(“IPHN3724”) Displays the FNSKU for SKU IPHN3724. As the direction is not set, the function assumes the value is a SKU and will return the FNSKU. If you have a long list of SKU’s from a file, you can get the matching FNSKU quickly with this function.
=GORILLA_SKUFNSKU(“B00YD545CC”, “sku2asin”, “FR”) When the direction of sku2fnsku is entered, it will convert from SKU to FNSKU. If you have a long list of SKU’s from a file, you can get the matching FNSKU quickly with this function.
=GORILLA_SKUFNSKU(D33:E39, “fnsku2sku”) Return an array of SKUs by entering a column, row or range of ASIN’s like this example.
=GORILLA_SKUFNSKU(D33:E39,”sku2fnsku”) Return an array of FNSKUs by entering a column, row or range of SKU’s like this example.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SKUFNSKU(“SELLER832J”, “IPHN3724”) =GORILLA_SKUFNSKU(“SELLER832J”, “B00YD545CC”, “sku2asin”, “FR”) =GORILLA_SKUFNSKU(“SELLER832J”, D33:E39, “fnsku2sku”) =GORILLA_SKUFNSKU(“SELLER832J”, D33:E39,”sku2fnsku”)
|
GORILLA_SKULIST( ) Function
Description:
Return a list of active and inactive SKUs from your marketplace(s).
Syntax:
GORILLA_SKULIST([marketplace], [filter_sku], [status], [fulfillment])
for Gorilla AGENCY, the syntax is
GORILLA_SKULIST(sellerID, [marketplace], [filter_sku], [status], [fulfillment])
marketplace – OPTIONAL
Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.
filter_sku – OPTIONAL
Part of an SKU to filter result.
The filter will act only at the start of the SKU. If there multiple SKUs like 54145484, 5414345, 54145FD5, you can search for 5414* to list all SKUs starting with 5414.
status – OPTIONAL
Valid values are ALL, ACTIVE, INACTIVE.
ALL lists both active and inactive SKUs.
ACTIVE lists active SKUs only.
INACTIVE lists inactive only.
Default is set to ALL.
fulfillment – OPTIONAL
default is “ALL” to show FBA and FBM ASINs. “FBA” will only display FBA ASINs. FBM will only display FBM ASINs.
sort – OPTIONAL
Default value is “date_asc” if not specified in the formula.
“date_asc” lists SKUs in ascending order by created date.
“date_desc” lists SKUs in descending order by created date.
“sku_asc” lists SKUs in ascending alphabetical order.
“sku_desc” lists SKUs in descending alphabetical order.
Examples: =GORILLA_SKULIST() If no attributes are entered, it will list all your current active and inactive SKUs from all your marketplaces. =GORILLA_SKULIST( , , , “FBA”,”sku_asc”) This will list only the FBA SKUs in alphabetical order for the default marketplace for your account as the other attributes are empty.
=GORILLA_SKULIST( , , “ALL”) This will list the entire list of SKUs in your account including all the deleted, inactive, closed, suppressed products. If no inputs are entered like GORILLA_SKULIST(), the default value is “EXCLUDE”.
=GORILLA_SKULIST(“UK”,”IPHONE”) Lists all SKUs that start with the phrase “IPHONE” listed on the UK marketplace. You can enter any text string to use as a filter.
=GORILLA_SKULIST(“US” , , “ACTIVE”, “FBM”) This will list all active FBM SKUs for the US marketplace only. Will also show closed, inactive, deleted, suppressed SKUs.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SKULIST(“SELLER832J”) =GORILLA_SKULIST(“SELLER832J”, “UK”, “IPHONE”) =GORILLA_SKULIST(“SELLER832J”, , , “ALL”) =GORILLA_SKULIST(“SELLER832J”, “US” , , “ACTIVE”, “FBM”, “DATE_DESC”)
|
GORILLA_SNSFORECAST( ) Function
Description:
Return the subscribe and save forecast report for the skus that are activated in your account.
Syntax:
GORILLA_SNSFORECAST([filter], [marketplace], [returnStyle], [datapoint])
for Gorilla AGENCY, the syntax is
GORILLA_SNSFORECAST(sellerID, [filter], [marketplace], [returnStyle], [datapoint])
filter – OPTIONAL
SKU or ASIN to filter the results. Can be a range of values like A1:A100 or “SKU111, SKU222, SKU333” or ASIN123.
marketplace – OPTIONAL
Marketplace country filter. Country codes are US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, SE, AU, SG, JP. Use ALL to combine values from a single account, EU for all Europe markets EXCEPT UK, EURO to combine countries using EURO currency. Amazon Marketplace ID like ATVPDKIKX0DER can be used too.
returnStyle – OPTIONAL
Return style (defaults to fullWithHeader).
Possible values
- full
- fullWithHeader
- singleDataPoint. SKU filter and datapoint are mandatory when using singleDataPoint.
datapoint – OPTIONAL
Return data for specific data point. Default is empty value which shows “full” returnStyle. Can be a range of values like A1:S1.
- avgDiscount8weeks
- activeSubscriptions
- week1startDate
- scheduledUnitsWeek1
- scheduledUnitsWeek2
- …
- scheduledUnitsWeek8
- offerState
Examples: =GORILLA_SNSFORECAST() If no attributes are entered, it will list all your current active SNS products and display a full table of results with all support data points offered. The forecast data will load based on the closet starting date according to the current date. =GORILLA_SNSFORECAST(“SKU111, SKU222, SKU333”) This will the subscribe and save forecast report for the range of SKUs entered.
=GORILLA_SNSFORECAST(A3:A12, “US”, “fullwithheader”) This will auto generate a table of the SNS forecast report for the SKUs or ASINs listed in A3:A12 for the US market. The table will generate a header. The header can be removed by using “full” instead of “fullwithheader”.
=GORILLA_SNSFORECAST(A3:A12, “US”, “singledatapoint”, “activeSubscriptions”) To get one data point only, select the return style as “singledatapoint” and then reference the data point you wish to get. In this example, it will get the “activesubscriptions” for each of the SKUs listed in A3:A12.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SNSFORECAST(sellerID) =GORILLA_SNSFORECAST(sellerID, “SKU111, SKU222, SKU333”) =GORILLA_SNSFORECAST(sellerID, A3:A12, “US”, “fullwithheader”) =GORILLA_SNSFORECAST(sellerID, A3:A12, “US”, “singledatapoint”, “activeSubscriptions”) |
GORILLA_SNSPERFORMANCE( ) Function
Description:
Return the subscribe and save performance report for the skus that are activated in your account.
Syntax:
GORILLA_SNSPERFORMANCE(start_date, [filter], [marketplace], [returnStyle], [datapoint])
for Gorilla AGENCY, the syntax is
GORILLA_SNSPERFORMANCE(sellerID, start_date, [filter], [marketplace], [returnStyle], [datapoint])
start_date – REQUIRED
Enter as YYYY-MM-DD format. Gets the closest matching SNS report based on the date.
filter – OPTIONAL
SKU or ASIN to filter the results. Can be a range of values like A1:A100 or SKU111, SKU222, SKU333 or ASIN123.. Filter is mandatory when returnStyle is singleDataPoint.
marketplace – OPTIONAL
Marketplace country filter. Country codes are US, CA, MX, BR, UK, DE, ES, IT, FR, NL, SE, TR, IN, SE, AU, SG, JP. Use ALL to combine values from a single account, EU for all Europe markets EXCEPT UK, EURO to combine countries using EURO currency. Amazon Marketplace ID like ATVPDKIKX0DER can be used too.
returnStyle – OPTIONAL
Return style (defaults to fullWithHeader).
Possible values
- full
- fullWithHeader
- singleDataPoint. SKU filter and datapoint are mandatory when using singleDataPoint.
datapoint – OPTIONAL
Return data for specific data point.
Default is empty value which shows “full” returnStyle. Can be a range of values like A1:S1.
Valid values:
- wStartDate
- DaysOfSupply
- productName
- country
- unitsShipped
- oosRate
- salePrice
- discount
- offerState
Examples: =GORILLA_SNSPERFORMANCE(“2022-01-01”) Date is required. Format is in YYYY-MM-DD. Can be entered directly or reference a cell that contains the date. This formula pulls the SNS Performance report with a start date closest to the entered period. =GORILLA_SNSPERFORMANCE(“2022-01-01”, “SKU1111”, “US”, “FULLWITHHEADER”) This formula will generate a table of results of all the data provided from the performance report for the SKU “SKU1111” in the US market with the starting date closest to 2022-01-01. The table auto generates headers.
=GORILLA_SNSPERFORMANCE(A3, “US”, “singleDataPoint”, “DaysOfSupply”) This will load the single data point for days of supply for the US market where the period is defined in cell A3.
=GORILLA_SNSPERFORMANCE(“2022-12-31”, “FR”, “singledatapoint”, A2:G2) This will pull the SNS performance report closest to the start date of 2022-12-31 for the French market. It will pull data where the data points are entered in A2 to G2.
For Gorilla AGENCY, the formulas will start with the seller ID: =GORILLA_SNSPERFORMANCE(SELLERID, “2022-01-01”) =GORILLA_SNSPERFORMANCE(SELLERID, “2022-01-01”, “SKU1111”, “US”, “FULLWITHHEADER”) =GORILLA_SNSPERFORMANCE(SELLERID, A3, “US”, “singleDataPoint”, “DaysOfSupply”) =GORILLA_SNSPERFORMANCE(SELLERID, “2022-12-31”, “FR”, “singledatapoint”, A2:G2) |
Fulfilled By Merchant (FBM) – works just like FBA
FBM is fully supprot just like FBA. The formulas are all the same and works the same way.
Why choose us?
We specialize in delivering data from seller central straight to your spreadsheets.
The way you want it, when you want it, how you want it.
WE UNDERSTAND AMAZON SELLERS AND AGENCIES
30 DAY MONEY BACK GUARANTEE NO QUESTIONS ASKED
HIGHEST RATED AMAZON ADDON FOR GOOGLE SHEETS
100% SECURE VIA 256-BIT SSL. DATA OWNED BY YOU.
STELLAR SUPPORT IS STANDARD