fbpx

Import your data

In this page you will discover how to use the Intuendi’s file format, with all the data needed to exploit the potential of our demand forecasting and inventory optimization service. Formats available for your files are the following:

  • 1. CSV: comma-separated values file.
  • 2. Excel (XLSX): Microsoft Excel file with XLSX extension.
  • 3. Excel (XLS): Microsoft Excel file with XLS extension.

Sample Catalog

Download our sample data in CSV or XLSX format. You can use it as a first step to build your own data.

CSV XLSX

The catalog file contains some information about your products. Here is an example of a catalog with three products: pants_0 and pants_1 belong both to category Clothes, while belts_0 belongs to category Accessories.

Category SKU Name Publishing Date End of Life Date Location Stock on Hand Lead Time Service Level Purchase Price Selling Price Coverage MOQ Packing Unit Supplier
Clothes pants_0 Winter pants 2018/12/18 2020/12/18 A 50 15 0.95 20 27 30 50 50 A&B
Clothes pants_1 Summer pants 2018/12/20 2020/12/18 B 40 10 0.90 25 30 20 10 5 C&D
Accessories belts_0 Leather belts 2018/12/01 C 300 10 0.97 35 42 45 1 1 X&Co

Two different kinds of information should be provided.

Catalog details

  • 1. Category: the category a product belongs to. If not provided, the category Default will be created.
  • 2. SKU: a unique identifier for the product, it can be a Stock Keeping Unit or a user-defined value.
  • 3. Name: a human-readable name for the product.
  • 4. Publishing Date: when the product was published (or is going to be published).
  • 5. End of Life Date: when the product was considered as discontinued (or is going to be considered as discontinued).
  • 6. Seasonal Break Start (optional): in case the product is seasonal, when the product starts to be temporarily considered as discontinued.
  • 7. Seasonal Break End (optional): in case the product is seasonal, when the product terminated to be temporarily considered as discontinued.
  • 8. Image URL (optional): the url of the product image.
  • 9. Thumbnail URL (optional): the url of the thumbnail image.
  • 10. Page URL (optional): the url of the product page.

Inventory details

  • 1. Location: the location/warehouse where the product is stocked. If not provided, a default location will be created.
  • 2. Stock on Hand: the current product stock level in a given location.
  • 3. Lead Time: required time (in days) for the replenishment of the product.
  • 4. Service Level: the probability to meet demand during the reorder lead time, in which a stockout may possibly occur. If not provided, the default value is set to 0.95 (95%).
  • 5. Purchase Price: the cost at which the product is bought from the supplier.
  • 6. Selling Price: the cost at which the product is put up for sale.
  • 7. Coverage: the number of days the suggested reorder quantity by Intuendi should last for.
  • 8. MOQ: Minimum Order Quantity, the smallest amount of the product required by the supplier in a purchase order (default 1).
  • 9. Packing Unit: the number of products bundled for shipment (default 1).
  • 10. Production Rate: if the supplier is a manufacturer, how many units can be produced per day.
  • 11. Production Time: if the supplier is a manufacturer, how much time is required to a product to complete its production cycle.
  • 12. Unavailability Start: if the product is not available to the supplier, the start date of unavailability.
  • 13. Unavailability End: if the product is not available to the supplier, the estimated end date of unavailability.
  • 14. Supplier: SKU supplier’s name.
  • 15. Excluded: whether or not the product should be considered excluded from inventory analysis and optimization.

Please, note that all the inventory informations are optional. For the computation of the replenishment suggestions we strongly suggest to provide all the information about your inventory. Some of them, for instance the lead time, dramatically affects the timing of your replenishment events.

In the sample catalog file, at the end of the header, you will find the columns Color, Size and Collection. These columns are not standard ones, they are custom attributes that have been added to enrich the product description (for an apparel business, in this case). You can provide Intuendi with your custom attributes to break down data in all the ways you need and to improve the Intuendi’s forecasting engine capabilities.

The order lines file contains all your recorded order lines: every line represents a sold product quantity related to a sales order.

Order ID SKU Date Quantity Amount Region Location
ORDER_174312 pants_0 2018/12/22 10 270.00 ASIA A
ORDER_463462 pants_1 2018/12/22 27 810.00 ASIA B
ORDER_844213 pants_0 2018/12/24 18 486.00 US C

  • 1. Order ID: the identifier of the sales order.
  • 2. SKU: the identifier for the product.
  • 3. Date: the date in which the sales order occurred.
  • 4. Quantity: the sold product quantity.
  • 5. Amount: the amount of the order line.
  • 6. Region: the region/channel/customer which issues the order.
  • 7. Location: the location which serves the region/channel/customer.

The promotions file contains all your promotions: every line represents a promotion identified by its start date and end date. You can define promotions at each aggregation level (whole catalog, category, sku, region). Here are some examples:

Name Category SKU Region Start Date End Date Discount Units
Black Friday pants_0 US 2017/11/01 2017/11/15 30 1000
Christmas 2017 2017/12/15 2017/12/31 40
Chinese New Year ASIA 2018/02/01 2018/02/18 20

The first example contains a Black Friday promotion which applies a 30% discount to 1000 units of the SKU pants_0 over the region US. It starts on 2017/11/01 and terminates on 2017/11/15. If the user wants to define a promotion for the whole catalog, it should leave blank the category, SKU and region fields (as in the second example).

  • 1. Name (optional): the promotion name.
  • 2. Category (optional): the category involved in the promotion.
  • 3. SKU (optional): the SKU involved in the promotion.
  • 4. Region (optional): the region/channel/customer involved in the promotion.
  • 5. Start Date: the promotion start date.
  • 6. End Date: the promotion end date.
  • 7. Discount: the percentage of discount related to the promotion.
  • 8. Units (optional): the number of discounted units.

The anomalies file contains all stockouts and exceptions that occured in the history and that may affect the forecasting analysis and inventory optimization.

SKU Region Start Date End Date Type Quantity
pants_0 US 2018/10/01 2018/10/09 Stockout
pants_1 ASIA 2018/05/14 2018/05/17 Stockout 10
pants_2 US 2018/09/01 2018/09/30 Stockout
pants_2 US 2018/09/27 2018/09/30 Exception
pants_3 US 2018/12/05 2018/12/11 Exception +20
pants_3 ASIA 2018/06/11 2018/06/17 Exception -15

The first row contains a stockout for SKU pants_0 registered on region US from 2018/10/01 to 2018/10/09 and there is no info about the lost sales in terms of units.

  • 1. SKU: the sku involved in the anomaly.
  • 2. Region: the region involved in the anomaly.
  • 3. Start Date: the anomaly start date.
  • 4. End Date: the anomaly end date.
  • 4. Type: the anomaly type. Could be either Stockout or Exception.
  • 5. Quantity (optional): the quantification of the anomaly. If Stockout, is the number of lost sales. If Exception, the quantity could be positive (an unexpected sales) or negative (an unexpected return).

Purchase orders are handled using three different files:

  • 1. Purchase Orders File: a file containing all the info about a purchase order.
  • 2. Purchase Order Lines File: a file containing all the purchase order lines.
  • 3. Purchase Order Receipts File: a file containing all the receipts related to existing purchase orders and purchase order lines.

Purchase Orders File

The purchase orders file contains all the general info about a purchase order: the supplier and the location where stock has to be sent, when the PO was issued and when it is expected to be delivered, the current status, etc.

Purchase Order Id Name Location Supplier Issue Date ETA Status Total
PO-0001 P0 #0001 A A&B 2018/02/01 2018/03/01 CLOSED 145.67
PO-0002 P0 #0002 A C&D 2018/02/15 2018/03/10 OPEN 134.12
PO-0003 P0 #0003 B A&B 2018/02/25 2018/03/10 CANCELED 12.32

The first example shows a purchase order to supplier A&B that has been already arrived in location A (with status CLOSED), whereas the second example is a purchase order that is still in progress (with status OPEN).

  • 1. Purchase Order Id: a required unique identifier of the purchase order.
  • 2. Name (optional): a user-friendly name that will help you search it.
  • 3. Location: the location where goods will be stocked.
  • 4. Supplier: the supplier.
  • 5. Issue Date: when the PO was issued.
  • 6. ETA: when the PO is expected to arrive.
  • 7. Status: the PO’s status. It could be set to DRAFT, OPEN, CLOSED or CANCELED.
  • 8. Total (optional): the total amount of the purchase order.

Purchase Order Lines File

The purchase order lines file contains all the lines of the purchase orders, one for each product with info about the quantity, how many units are already arrived, the price per unit, etc.

Purchase Order Id Line Id SKU Location Supplier Quantity Received Issue Date ETA Price Per Unit Amount
PO-0001 POL-00001 pants_0 A A&B 15 0 2018/02/01 2018/03/01 2.0 30.0
PO-0001 POL-00002 pants_1 A A&B 10 5 2018/02/01 2018/02/28 4.5 45.0
PO-0001 POL-00003 belts_0 A A&B 5 0 2018/02/01 2018/03/01 1.0 5.0

It is worth noticing that purchase order lines may have a ETA that is not the same as the related purchase order: in the second example, the ETA is 2018/02/28, while the PO ETA is 2018/03/01 in the purchase orders file (with Id PO-0001).

  • 1. Purchase Order Id: the Id of the PO the purchase order line is related to.
  • 2. Line Id: a unique identifier of the PO line.
  • 3. SKU: the SKU of the PO line.
  • 4. Location: the location where the product will be stocked (it should be the same as in the purchase order file).
  • 5. Supplier: the supplier (it should be the same as in the purchase order file).
  • 6. Quantity: the ordered quantity.
  • 7. Received (optional): quantity received (in case only a few arrived).
  • 8. Issue Date: when the PO was issued (it should be the same as in the purchase order file).
  • 9. ETA: when the PO line is expected to arrive.
  • 10. Price Per Unit (optional): the product price per unit.
  • 11. Amount (optional): the total amount of the PO line.

Purchase Order Receipts File

The purchase order receipts file contains all the info about received stock related to purchasing orders. Knowing when stock arrived and how much helps us to better learn how to optimize the inventory.

Purchase Order Id Receipt Id Received Date SKU Location Supplier Quantity
PO-0001 POR-00001 2018/02/28 pants_0 A A&B 15
PO-0001 POR-00002 2018/02/25 pants_1 A A&B 5
PO-0001 POR-00003 2018/03/02 belts_0 A A&B 5

  • 1. Purchase Order Id: the Id of the PO the purchase order receipt is related to.
  • 2. Receipt Id: a unique identifier of the PO receipt.
  • 3. Received Date: when this receipt arrived.
  • 4. SKU: the received SKU.
  • 5. Location: where the receipt arrived (it should be the same as in the purchase order file).
  • 6. Supplier: the supplier who sent goods (it should be the same as in the purchase order file).
  • 7. Quantity: the received quantity.

Here we describe how to add suppliers’ info, the lead time to your locations and even suppliers’ constraints (minimum order quantity, assortment constraints, etc.). There are three different files:

  • 1. Suppliers File: a file containing all the info about a supplier.
  • 2. Supplier Locations File: a file containing the lead time needed for a supplier to send goods to a location.
  • 3. Supplier Constraints File: a file containing all the constraints a supplier applies to groups of products.
Please, note that these files are all optional.

Suppliers File

The suppliers file contains all the general info about a supplier: the name, the email and general constraints about minimum order quantity or purchasing budget.

Name Description Email MOQ MOB Shipping Interval Notes
A&B A&B info@ab.com 100 1000 3 Closed on Monday
C&D C&D info@cd.com 10000 5
X&Co X&Co info@xco.com

The first example shows a supplier that requires a minimum order quantity equal to 100 units and a minimum order budget of $1000 (with USD currency set) among all of its products. Moreover, it delivers goods with a shipping interval of three days.

  • 1. Name: a required unique identifier of the supplier.
  • 2. Description: a description of the supplier.
  • 3. Email: the supplier email.
  • 4. MOQ: Minimum Order Quantity, the minimum total number of units required by the supplier in a purchase order.
  • 5. MOB: Minimum Order Budget, the minimum total amount required by the supplier in a purchase order.
  • 6. Shipping Interval: the interval between subsequent deliveries. The shipping interval is of interest in case the supplier sells goods that have a limited production rate (see the Catalog File for more details)
  • 7. Notes: some notes that might be useful while creating a new purchase order.

Supplier Locations File

The supplier locations file contains only the info about the lead time (in days) needed by a supplier to send goods to a location.

Supplier Location Lead Time
A&B Location A 12
A&B Location B 15
C&D Location A 20

  • 1. Supplier: the supplier.
  • 2. Location: the location.
  • 3. Lead Time: the lead time in days.

Supplier Constraints File

The supplier constraints file lets you add constraints on products that a supplier applies once you add them in a purchase order. There are two types of constraints:

  • 1. Group constraint: a constraint that is applied on a group of products. It could represent either a Minimum Order Quantity (MOQ) constraint or a Minimum Order Budget (MOB) constraint. If a product in the group is added to a purchase order, then the MOQ and/or the MOB requirements on these products have to be met.
  • 2. Assortment constraint: a constraint that is applied on an assortment of products. In this case, if a product in the assortment is added to a purchase order, then the supplier requires that all the other products are added as well, all with the quantity at least equal to MOQ defined in the Catalog File.

Supplier Name Description MOQ MOB SKUs
A&B Pants Assortment Assortment on pants pants_0; pants_1
C&D Accessories Group Group on accessories 10 500 belts_0; belts_1; sunglasses_2; sunglasses_3

The first example is an assortment constraint on pants (no MOQ or MOB), whereas the second example is a group constraint that requires a minimum order quantity of 10 units and a minimum order budget of $500 (with USD currency set) in case one among belts_0, belts_1, sunglasses_2 or sunglasses_3 is added to a purchase order.

  • 1. Supplier: the supplier.
  • 2. Name: the name of the group (or assortment) constraint.
  • 3. Description: the description of the group (or assortment) constraint.
  • 4. MOQ: Minimum Order Quantity, the minimum total number of units required by the supplier in a purchase order for the group constraint (leave it blank for an assortment constraint).
  • 5. MOB: Minimum Order Budget, the minimum total amount required by the supplier in a purchase order for the group constraint (leave it blank for an assortment constraint).
  • 6. SKUs: a semicolon-separated list of product SKUs that represents the group (or the assortment).

The stock levels file contains the level of stock of a product in a given location at the end of a given date. With such info, we can estimate the past stock levels timeseries that could be used for several purposes.

SKU LocationDate Stock Level
pants_0 Location A 2020-06-30 450
pants_0 Location B 2020-06-30 700
pants_1 Location A 2020-06-30 1800

In the first example, the SKU pants_0 was stocked in location A with 450 units on 2020-06-30.

  • 1. SKU: the identifier for the product.
  • 2. Location: the location where the product is stocked.
  • 3. Date: the date in which the stock level has been registered.
  • 4. Stock Level: the stock level in units.