Item Library and Unit Cost Management
Tracking your unit cost is only available with a subscription to Square for Retail. If you don’t have a subscription yet, you can sign up for a free 30-day trial.
Note Square for Retail does not currently integrate with Bigcommerce.
Once you have signed up, before you can take advantage of the reports available with Inventory Plus, you need to set a unit cost for each item in your library. There are three ways to set your Unit Cost: via online bulk import, manually using your online Square Dashboard Item Editor, or by receiving Purchase Orders.
Note: Tracking stock and including a unit cost for item variations will ensure your reporting going forward will accurately reflect your cost of goods sold, profit, and profit margin; however, to correct your historical stock adjustments, you’ll need to head to your History log on your online Square Dashboard.
Once a unit cost is set, it will be used to pre-populate for future purchase orders and manual stock adjustments — such as stock received, restocked, or re-counts that adjust up.
Import or export your item library to take bulk action on inventory, SKUs, and unit costs from your online Square Dashboard. To get started, you’ll need to download our Excel or CSV template.
Export Your Library
Your export file includes all of your Items, unit costs, and inventory across all locations.
Keep in mind:
- Excel (.xlsx) file format is recommended for most users. Excel format is supported by all modern spreadsheet editors including Microsoft Excel, Apple Numbers, and Google Spreadsheets.
- The Excel file template is formatted to text, not numbers. If you wish to use the exported item library file to perform number functions, you must change the file to a numbers format.
To export a copy of your library:
- Visit Items in your online Square Dashboard > Items.
- Click Actions > Export at the top-right > Confirm Export.
- Select between Excel (.xlsx) or CSV format. A spreadsheet will then download to your computer.
|Export File Field Descriptions|
|Token||Your Square Dashboard will automatically populate this token. If you are importing a new item, leave this field blank, if you are editing items do not change the token.|
|Item Name||Can include numbers, letters or symbols. Maximum 128 characters.|
|Description||Can include numbers, letters or symbols. Maximum 1024 characters.|
|Category||Can include numbers, letters or symbols. Maximum 128 characters.|
|SKU||Each variation should be assigned a unique SKU.|
|Variation Name||Can include numbers, letters or symbols. Maximum 128 characters.|
|Price||Numeric. Must be $0.00 or greater (a fixed price needs to be a positive number). You can also leave blank or write in “Variable” to indicate a variable price.|
|Current Unit Cost||Numeric. In the case where there are multiple vendors associated with a variation, you may select a default unit cost. This is what will be exported.|
|New Unit Cost||Numeric. Update the Unit Cost per item variation.|
|Enabled [Your Location Name]||Yes or No. This will set your item as visible in individual locations. If you only have one location and see this column, make sure all items are set to Yes.|
|Current Quantity [Your Location Name]||Numeric. If you are using Inventory Management this is the current quantity of this item defined as in stock. Changes to this field are ignored on import.|
|New Quantity* [Your Location Name]||Numeric. If you are using Inventory Management, enter your new stock count here. This count will replace any existing stock count.|
|Stock Alert Enabled [Your Location Name]||If you are using Inventory Management, you can set Yes or No to turn on items for inventory alerts.|
|Stock Alert Count [Your Location Name]||Numeric. If you are using Inventory Management, and have alerts enabled, this is the quantity at which you will be alerted.|
|Price [Your Location Name]||Numeric.|
|Tax [Your Tax Name]||If you have created a tax, set to Yes or No to apply that that to the item.|
- Note on New Quantity field: Make sure to enter your new stock count in this column. If you enter your new stock count in the Current Quantity field, your stock will not update.
Identify and Update Missing Unit Costs
You can determine missing unit costs by exporting your item library. In the column with header Current Unit Cost, variations without a set unit cost will be blank.
If you are transferring your items from another POS or Inventory system to Square, you must convert any existing file format you have to the Square Item Library file which is unique to your account.
Note: The header/first row of the template file is required. You must not change any of the column names or delete any columns.
To update your unit costs:
- Update your item library template with your unit costs in the New Unit Costs column.
Visit Items in your online Square Dashboard > click Actions > Import.
- Drag your updated Excel or CSV file to the upload box (or click upload from your computer) > click Actions > Import.
When you update your unit costs from your item library, the item variations will update so your future stock actions and sales will reflect your new unit costs. However, If you’ve already received, restocked, or re-counted items without entering a unit cost, head to your History log.
Identify and Update Missing Unit Costs for Historical Adjustments
Keep in mind, if an item is missing a unit cost, you’ll need to update each variation from your item library before you can bulk resolve missing costs from your History log.
To get started:
- Log into your item library in your online Square Dashboard to confirm each item variation has an associated unit cost.
- Once you’ve checked each item variation has an associated unit cost, head to the History tab.
- Select Resolve Unit Costs > Resolve Costs.
When you select See Stock Actions, you’ll see each historical adjustment missing an associated unit cost.
When you update one historical adjustment, the unit cost for every other adjustment will reflect the new unit cost for that particular item.
You will not able to bulk update unit costs for variations you’ve deleted from your item library. To update historical adjustments for a deleted variation, you’ll need to update each variation unit cost from your History.
Read more about updating the unit cost for historical adjustments.
Edit CSV File
While you are able to import the CSV data into a spreadsheet if your item library contains SKUs do not open the CSV file directly as some spreadsheet editors will modify your SKUs in unexpected ways. Specifically, they may remove leading 0s (ex. 0012345 will become 12345) or convert them to scientific notation (ex. 12345678999 will become 1.2346E+10).
If you have SKUs in your CSV, follow these steps to ensure there are no errors in your CSV files:
Import CSV data in Microsoft Excel
- Create a new spreadsheet in Microsoft Excel.
- Select File then Import from the main menu.
- Select the CSV file option and click Import.
- Choose the CSV file that you exported from your Item Library and click Get Data.
- Your Import Wizard pop up will appear:
- Click Next.
- Ensure that only the “Comma” delimiter is checked, then click Next.
- Scroll over to the SKU column and change the column data format to Text, then press Finish
- Choose the New Sheet option and press OK to complete the import.
Import CSV data into Google Spreadsheets
If you’re importing your stock for the first time with Square for Retail, the stock intake action will reflect as Received. If you’ve imported your item library before, your stock intake will be considered a Re-count.
Note: Depending on whether your stock intake is considered a receive or re-count, your Cost of Goods Sold report will be affected differently. Receiving stock will not have an effect on your Cost of Goods Sold (COGS). However, re-counts that adjust your stock up will credit your COGS (decrease), and re-counts that adjust your stock down, will debit your COGS (increase). If you’re only interested in seeing profit and profit margin for sold items (i.e. excluding re-counts), apply the Sales filter.
To get started:
- Create a new spreadsheet in Google Drive.
- Select File then Import from the main menu.
- Select the Upload tab and choose the CSV file that you exported from your Item Library.
- After the file uploads, an “Import File” popup will open. Be sure to change “Convert texts to numbers and dates” to No.
- Click Import to complete the import.
Upload Your Item Library
Before getting started, bear in mind updating your unit costs via bulk upload only works once when you first set up your item library with Square for Retail.
To upload your inventory count:
- Visit Items in your online Square Dashboard.
- Click Actions > Import at the top-right of the page.
- Select Modify Item Library to add new items and update existing items, or Replace Item Library to delete all existing items and replace them with your uploaded items.
- Click Next > “Download our template file.” Select if you’d like to download the template in Excel (.xlsx), which is recommended for most users, or CSV.
Note: If you’re using Safari as your web browser, you’ll need to right-click the “Download our template” link and save the file to your computer.
- Open the “catalog-YYYY-MM-DD-XXXX.xlsx or .csv” file saved to your computer in step 4 using Excel (.xlsx) or another spreadsheet program.
Note: When editing your Excel file, we recommended using the most up to date version of your spreadsheet program.
- Fill in the template columns with your item information. When editing the template, keep the following tips in mind:
- “Current Unit Cost” is simply for reference, and only “New Unit Cost” will edit the cost in your Dashboard.
- Don’t change any of the information in the Token column or delete empty columns.
- Any changes made to the ‘New Quantity’ column will replace the existing item quantity. If you put a 0 in the ‘New Quantity’ column, your inventory for that item for that location will be set to 0.
- Leaving the Category field blank will leave the item as Uncategorized.
- New categories will be created for any new values identified in the Category column.
- Leaving the Price field blank or writing Variable will create a variably priced item.
- Leaving the Modifier field blank will default the modifier to off. Place a Y in the appropriate Modifier Set column if you’d like a specific modifier applied to an item.
Note: You can assign a location-specific price, stock count, or availability by setting values in the location-specific columns. For example, setting a price in the “Price Location 2” column will assign a price to that location only.
- Save the edited Excel (.xlsx) or CSV file to your computer.
- Drag and drop the Excel (.xlsx) or CSV file into the upload area or click “select it from your computer” > Upload.
- Review your changes and click Confirm Import to upload your new item library.
- You will be prompted to align Square’s core product attributes to your imported columns from your Excel (.xlsx) or CSV. If you did not change the column names in the file downloaded from Square, the product attributes should be automatically matched. Review the mapping from your file to product attributes and make changes as necessary.
- The following columns are required in your Excel (.xlsx) or CSV: Item Name, Variation Name, Description, SKU. If you have more than one location, the Enabled [Location Name] column is also required.
- The Tax column also requires a specific heading format. Tax column names must include the tax percentage value in parentheses within the header. For example, “Tax - Sales (7%)”.
- Click Confirm to upload your changes.
Once you click Confirm Import, you’re unable to revert back to your previous item library.
Learn how to troubleshoot your CSV file.