How Do I Bulk Edit / Download / Upload COGS For All My Products ?

Overview of the Bulk COGS Management Process

In December 2003, we implemented a simplified format to bulk upload COGS.
Please make sure to read the following article first to understand the impact of this fundamental improvement
Irrespective of which GOGS entry format you have chosen, the process to manage your COGS in bulk is the same.

Please follow the steps below. Details for each step follow later in this same article.

Select the Marketplace for which you want to manage COGS. Here’s an article that explains how to do that.
Download the COGS report onto an Excel spreadsheet
Retrieve the downloaded report, open it in Excel, and update the COGS in the spreadsheet
Save the updated file and upload the modified COGS file back to SellerLegend
Once the upload is completed, check for errors and correct them if needed
Repeat the upload process until there are no errors

Downloading the COGS Template

Follow these steps to download the COGS bulk upload spreadsheet template:

From the Main Menu, select Products->Product List. While on the Products List screen, click on the Import/Export button

2. In the EXPORT section of the resulting pop-up to the right of the screen, under Report Type
3. tick the button for your choice of format in which to download the template

COST OF GOODS SIMPLE VIEW FORMAT
COST OF GOODS DETAILED VIEW FORMAT

4. Choose the account for which you want to download and edit the COGS
5. Check the box if you do not want to include hidden or ignored products
6. Click on the Export button

The system will require a few moments to download the template file. Meanwhile, the rotating busy icon will be displayed. A message will appear when the file is successfully downloaded, and a link in the message will invite you to proceed to the Downloaded Reports screen to retrieve your downloaded template. The message is dismissed after a few seconds.

If you have not been able to click on the link in the message before it disappeared, from the Main Menu Navigate to Reports –> Exported Reports
In the Exported Reports screen, a table will show you all the reports available for download.
Locate your desired report in the table.
- The Report Type column will show report format,
- The Filters column shows the Marketplace, Marketplace Currency and whether hidden products are the included.
- The Request Made column will show the timestamp of the request.
Wait until the Status column shows DONE, or DONE WITH ERRORS,
- You may need to refresh your screen to see the status change.
- Please note that while usually the change to DONE states takes a few seconds, in some cases it can take up to 20 minutes for the file to be available
Click the Actions button on the left of the row and select Download.
- This will download the file in your operating system’s (Windows, MacOS, Linux) downloads folder. Click on the file icon in your downloads folder to open the COGS file in Excel.

Updating COGS in Excel

Once you have opened the COGS template in Excel, as you would expect, the template file format varies depending on which format you have chosen to use (Simple View or Detailed View )
We will describe each format in a separate section

Updating COGS in Simple View

Here’s the excel file format for the simple view:

Entering a COGS values with a single cost period which will run forever

Columns A to C are automatically populated with SKU, ASIN and Parent ASIN for your reference
Columns D and E would contain the start and end date of applicability of the $2.25 COGS for every order arriving in the date range
- Since we have left them blank, they default to infinity
- So the $2.25 COGS will be applied to every order, irrespective of its date of arrival
- In essence, the $2.25 applies from the beginning of times to the end of times (infinity to infinity)
The COGS is $2.25, as shown in column F
- However, please refrain from entering the COGS in column F, as it has a more fundamental purpose
- Instead, use ANY column after column F for COGS recording
  - For example, we have entered the $2.25 COGS in cell G2, and we have labelled the cell G1 ‘Manufacturing’
  - We have thereby declared that the COGS contains a cost element ‘Manufacturing’ for a cost of $2.25

Adding Cost Elements to a Cost Period

Notice how column F contains the Excel formula =Sum(G2:ZD2) which sums all values from column G to ZD
You can therefore surmise that adding an additional cost element is merely a matter of entering the name of the element in cell H1 and a COGS value for that element in cell H2
Here is an example with a product COGS composed of 3 elements, Manufacturing, Packaging cartons and Product Inserts

Notice how cell F2 now contains the sum of the 3 individual cost element values

Adding Cost Periods

Let’s say you now want to handle the situation whereby the cost of a product changes over time.
This is handled by adding an additional cost period.
The easiest way to add an additional cost period is to copy down the information from row 2 onto row 3.
Each row with the same SKU, ASIN and Parent ASIN in columns A to C represent a separate cost period for the same product
Each cost period shows different costs per date range
The last step is to set the respective dates for each period

It is easiest to start with the oldest cost period and enter the dates in ascending order
- In the example above, we are showing 3 cost periods.
- Starting with the oldest first (row 4)
  - Its from date is infinity (blanks = from the beginning of time) and its end date indicates the day before the product went up (or down) in price
- Up to the next period in time sequence (row 3), its start date is one day after the end of the earlier period and its end date when the product changed price again
- Up to the next period in time sequence (row 2), its start date is one day after the end of the earlier period and its end date is some time in the future, which we represent with a blank value (infinity)
When adding cost periods, or modifying cost periods start and end dates, SellerLegend will retrospectively adjust all COGS prices in all your historical orders
Don’t forget, if you need additional cost elements to describe the complete set of price changes, feel free to add new cost elements by using the next available column (in the example above, it would be column J)

Updating COGS in Detailed View

Re-Uploading The Modified COGS File

Once you have updated the COGS file, you’ll need to re-upload it to SL for validation and, if found error-free, for updating the COGS in our databases. To upload the updated COGS file:

From the Main Menu, go to the Products->Products List screen and click on the Import/Export button

2. In the IMPORT section of the resulting pop-up, select either COST OF GOODS SIMPLE VIEW FORMAT OR COST OF GOODS DETAILED VIEW FORMAT as the Report Type, depending on the type of format you are using
3. Choose the Account for which you want to upload the COGS
4. Select the updated Excel file you want to upload from the location where you saved it
5. Click on the Import button

SellerLegend will now upload the COGS file and apply the COGS changes to your products

Correcting Errors and Re-uploading

Once the upload is initiated, from the Main Menu, go to Reports –> Imported Reports to check the outcome of your upload.
If the Status column shows DONE, the Excel file was uploaded without errors.
If the Status column shows DONE_WITH_WARNINGS, the Excel file contains errors. Click on the Actions dropdown and select Download to download the file. The errors will be shown in the COGS table fields. Correct the errors, and repeat the upload process until the COGS file is error-free
If the Status column shows FAILED, you have attempted to upload a spreadsheet with an invalid format, or the file size has exceeded the limit of 10,000 rows. Ensure that the order of the fields and column heading titles correspond to the given format and that the file does not contain more than 10,000 rows.

Locating Errors In the Excel File

When a COGS upload completes with a status of DONE_WITH_WARNINGS, here’s how you can locate the errors in the downloaded spreadsheet:

Valid COGS definitions rows will be highlighted in green. These COGS values in green rows will all have been successfully applied.
Erroneous COGS definitions rows will show in grey, with a red Excel comment tickmark at the top right of any erroneous cell.
To discover why the data is erroneous, hover over each read tick mark, and the erroneous cells will show an Excel comment with the appropriate error message.

Troubleshooting Errors

In case of errors, the typical issues include:

End dates are earlier than start dates.
Dates are not formatted in the appropriate yyyy-mm-dd format.
The number of units is 0
SKUs not found
The COGS spreadsheet contains more than one tab
The COGS spreadsheet contains formulas

Understanding the Excel File Format and Rules

Familiarize yourself with the Excel file format and rules before attempting to edit the COGS file. Make sure not to disrupt the order of the columns or modify the column headers. The file must not have more than one tab, and no cell formulas are allowed.

Excel File Format

Before attempting to understand the file format and rules for the Excel file, ensure you have mastered how COGS works in SellerLegend. Read the following articles and familiarize yourself with the Cost-Of-Goods management in SellerLegend:

Learn all there is to know about COGS

Excel File Fields

Every row of the Excel file must respect the following format. Both the column position and the column header title determine the column’s content, so make sure not to disrupt the order of the columns.

The Excel file format includes the Product SKU field from the Cost-Of-Goods Period as well as fields from the Cost Element. The file also contains the Internal name and the Title of the Product. These fields are there just to make product identification easier, but these are ignored during the upload.

To identify which Product you want to update, each row must contain at least one of the following three fields:

Product SKU
ASIN
Parent ASIN

If all of the above three fields are missing, we cannot identify which Product you are addressing, so COGS will not update. An error message will be issued for that row so that you can correct it.

Detailed View Format COGS File Fields

Source	Field	Excel File Header Title	Notes
Product	Product SKU	product_sku	Mandatory – must be an existing SKU if Product_ASIN and Parent_ASIN are missing.
–	Product ASIN	ASIN	Mandatory – must be an existing ASIN if Product_SKU and Parent_ASIN are missing.
–	Parent ASIN	parent_asin	Mandatory – must be an existing Parent_ASIN if Product_SKU and Product_ASIN is missing
–	Product Internal Name	internal_name	For visual reference only, not used and not updated
–	Product Title	title	For visual reference only, not used and not updated

Cost Period	Cost Period From Date	from_date	Mandatory – must be a valid date earlier than to_date. It MUST be in yyyy-mm-dd format.
Cost Period	Cost Period To Date	to_date	Mandatory – must be a valid date later or equal to from_date. It MUST be in yyyy-mm-dd format.
Cost Element	Cost Element Name	cost_element	Mandatory – any value acceptable
Cost Element	Provider	provider	Optional – any value acceptable – For documentation only, not used anywhere in SL
Cost Element	Notes	notes	Optional – any value acceptable- For documentation only, not used anywhere in SL
Cost Element	Total Amount Paid	total_amount	Mandatory – a positive number
Cost Element	Currency	currency	Optional – a valid ISO currency code if used (*)
Cost Element	Conversion Rate	conversion_rate	Optional – a decimal number, defaults to 1
Cost Element	Units	units	Optional -an integer number, defaults to 1
Cost Element	Amount Per Unit	amount	NOT USED, This is Calculated by SellerLegend. Do not override.

(*) See this Wikipedia article https://en.wikipedia.org/wiki/ISO_4217 for valid 3-letter ISO currency codes

Simple View Format COGS File Fields:

Source	Field	Excel File Header Title	Notes
Product	Product SKU	product_sku	Mandatory – must be an existing SKU if Product_ASIN and Parent_ASIN are missing.
Product	Product ASIN	ASIN	Mandatory – must be an existing ASIN if Product_SKU and Parent_ASIN are missing.
Product	Parent ASIN	parent_asin	Mandatory – must be an existing Parent_ASIN if Product_SKU and Product_ASIN is missing
Cost Period	Cost Period From Date	from_date	Mandatory – must be a valid date earlier than to_date. It MUST be in yyyy-mm-dd format. Blank represents infinity (since the beginning of time)
Cost Period	Cost Period To Date	to_date	Mandatory – must be a valid date later or equal to from_date. It MUST be in yyyy-mm-dd format. Blank represents infinity (trill the end of time)
Cost Element Name	Header of columns G1 to ZD1	An arbitrary cost element name of your choice	Mandatory At least one cost element needs to be present Duplicate cost element columns with identical cost element names will override one another. The last column will win. Cost Element Names can be renamed by overwriting the name in the title row. This will then affect all cost elements in every product accordingly.
Cost Element Amount	Amount Per Unit		Mandatory – A positive number

Excel File Rules for Detailed View

The Excel file must have a header row with the column titles as per the above table.
Each row in the Excel File represents a Cost Element of a Cost Period within a Cost Element.
Rows with the same SKU, from_date, and to_date are bundled into the same Cost Period.
If you have no COGS assigned yet for some products in the list, the Excel file will still contain an entry for these products with a COGS of 0.
Unless you have expressly excluded them during the download, ALL your products will be included in the downloaded Excel file, including Hidden Products and Inactive Products.

To add a Cost Element to a Cost Period:

Insert a blank row below the Product’s existing Cost Element(s).
Copy the SKU, from_date, and to_date of the existing cost element into the corresponding columns of the newly inserted row.
Fill in the other fields as required.

To add a new Cost Period to an existing product:

Change the end date of the existing cost period to reflect the date when the old COGS ceases to apply.
Insert a new blank row below the Product’s existing Cost Period(s).
Copy the SKU of the existing cost period into the corresponding column of the newly inserted row.
Enter a new from_date – to_date range to specify when the new Cost Period becomes applicable. The from_date should be the date when the new COGS starts to apply.
Fill in the other fields as required.

Additional notes:

The ASIN and Internal Names and Product Title do not need to be populated. They are ignored on upload and are only there to make visual product identification easier.
The total-amount field drives the COGS value, so there is no need to provide a value in the amount field, as this will be recalculated.
The “from date” and “to date” MUST be in the yyyy-mm-dd format.
Upon download of the files, some versions of Excel may change the format of the date fields to Excel’s internal ordinal date format. If this happens, change the format back to the yyyy-mm-dd format.
The Excel file must not have more than one tab, and no cell formulas are allowed. If you have used lookup formulae to assign COGS, copy/paste the values in place before attempting the upload.

Tips and Tricks

COGS can be assigned for past or future periods
COGS must be uploaded for each Marketplace individually but can be propagated to other EU marketplaces, learn more about it here: https://docs.sellerlegend.com/knowledgebase/how-to-propagate-cogs-for-eu-marketplaces/
COGS values must be expressed in the currency of the Marketplace
COGS can be uploaded in multiple files without overwriting previous data for non-repeating products; This is useful to circumvent the 10,000 rows maximum limit (for period-based COGS).
If you enter the Parent Asin of a product and omit the SKU and Child ASIN, the update to the Parent ASIN is propagated to all the Child ASINS.
If you have many products, expect the update to take a long time. This is because SellerLegend needs to retrospectively read ALL your orders since the beginning of time and update the COGS values order by order. You can significantly reduce the processing time by only submitting the products that need updating. Be ABSOLUTELY certain that you are submitting all the cost periods for the Product to be updated. If you submit only the row with the new cost period, all previous cost periods will be deleted! This deletion is not recoverable.

in COGS, How Do I … ?