Getting Started
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.
Catalog
The catalog file contains all the information about your products that are useful to demand forecasting and inventory optimization. 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.
SKU | Name | Category | Publishing Date | End of Life Date | Purchase Price | Selling Price |
---|---|---|---|---|---|---|
pants_0 | Winter pants | Clothes | 2018/12/18 | 2020/12/18 | 20 | 27 |
pants_1 | Summer pants | Clothes | 2018/12/20 | 2020/12/18 | 25 | 30 |
belts_0 | Leather belts | Accessories | 2018/12/01 | – | 35 | 42 |
Here we describe the main fields:
- 1. SKU: a unique identifier for the product, it can be a Stock Keeping Unit or a user-defined value.
- 2. Name: a human-readable name for the product.
- 3. Category: the category a product belongs to. If not provided, the a default category will be created.
- 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. Purchase Price: the cost needed to have the product ready for sale.
- 7. Selling Price: the price at which the product is put up for sale.
Some other fields that can be added to the file:
- 8. Seasonal Break Start: in case the product is seasonal, when the product starts being temporarily considered as discontinued.
- 9. Seasonal Break End: in case the product is seasonal, when the product ends being temporarily considered as discontinued.
- 10. Image URL: the url of the product image.
- 11. Thumbnail URL: the url of the thumbnail image.
- 12. Page URL: the url of the product page.
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 want and to perform new products’ forecasting by looking at existing products with similar features (for instance, similar color or size).
Inventory
The inventory file contains information about the stock in your locations (or warehouses) of your products and a few parameters that are very important to be considered when optimizing the inventory. Here is an example of the inventory of one product in three different locations.
SKU | Location | Stock on Hand | Lead Time | Service Level | Coverage | Min OQ | Packing Unit | Supplier |
---|---|---|---|---|---|---|---|---|
pants_0 | A | 50 | 15 | 0.90 | 30 | 5 | 5 | A&B |
pants_0 | B | 40 | 10 | 0.80 | 35 | 10 | 5 | C&D |
pants_0 | C | 300 | 10 | 0.85 | 45 | 10 | 10 | X&Co |
Here we describe the main fields:
- 1. SKU: the unique identifier for the product that has been used in the catalog file.
- 2. Location: the location/warehouse where the product is stocked. If not provided, a default location will be created.
- 3. Stock on Hand: the current product stock on hand in a given location.
- 4. Lead Time: required time (in days) for the replenishment of the product.
- 5. Service Level: the probability of meeting 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%).
- 6. Coverage: the number of days the suggested reorder quantity by Intuendi should last for once stock arrives in the location.
- 7. Min OQ: Minimum Order Quantity, the smallest amount of the product required by the supplier in a purchase order (default 1).
- 8. Packing Unit: the number of products bundled for shipment (default 1).
- 9. Supplier: SKU supplier’s name.
Some other fields that can be added to the file:
- 10. Stock Available: the stock on hand quantity that is available to new customers.
- 11. Stock on Hand Committed: the stock on hand quantity that is not available because it has been already committed.
- 12. Stock Back Ordered: the stock that has been sold although it is neither available nor has been ordered yet (it should be included in the next purchase order).
- 13. Stock on Order Committed: the stock on order quantity that has been already committed and it will be not available to new customers.
- 14. Max OQ: Maximum Order Quantity, the maximum number of units that the supplier delivers in a single purchase order (default unlimited).
- 15. Production Rate: if the supplier is a manufacturer, how many units of that product the supplier can produce per day.
- 16. Production Time: if the supplier is a manufacturer, how much time is required to the supplier to complete the product production cycle.
- 17. Unavailability Start: if the product is not available to the supplier, the start date of unavailability.
- 18. Unavailability End: if the product is not available to the supplier, the estimated end date of unavailability.
- 19. Excluded: whether or not the product should be considered excluded from inventory analysis and optimization (although having stock levels is of interest).
Please, note that providing the system with at least the main inventory parameters is very important for a complete inventory optimization. Some of them, for instance the lead time, dramatically affects the timing of your replenishment events.
Locations
The locations file contains some information about your locations. Here is an example of a file with three locations.
Name | Description | Address | Capacity | Notes |
---|---|---|---|---|
A | Location named A | Washington | 1000 | – |
B | Location named B | Pechino | 2500 | – |
C | Location named C | Milan | 800 | – |
- 1. Name: the location name.
- 2. Description: a description of the location.
- 3. Address: the address of the location.
- 4. Capacity: the maximum capacity of the location. It could be units, cubic meters or whatever unit of measure is used to express the size of every single product.
- 5. Notes: some notes about the location.
In the sample locations file, at the end of the header, you will find the column Group. This column is not a standard column, is a custom attribute that has been added to enrich the location description. You can provide Intuendi with your custom attributes on locations to break down data in all the ways you need and to improve the Intuendi’s forecasting engine capabilities.
Regions
The regions file contains some information about your regions, from where demand on your products comes from. Regions could be geographical areas, sales channels, customers, etc. In this case, regions are geographical areas.
Name | Description | Address | Notes |
---|---|---|---|
US | The US region | Washington | – |
ASIA | The Asia region | – | – |
EUROPE | The European region | – | – |
- 1. Name: the region name.
- 2. Description: a description of the region.
- 3. Address: the address field could be meaningful when regions are used as customers.
- 4. Notes: some notes about the region.
In the sample regions file, at the end of the header, you will find the column Area. This column is not a standard column, is a custom attribute that has been added to enrich the region description. In this case it could be used as a way to group together different geographical areas. You can provide Intuendi with your custom attributes on regions to break down data in all the ways you need and to improve the Intuendi’s forecasting engine capabilities.
Product Regions
The product regions file describes relationships among products, locations and regions. For example, it will tell the system when a product was made available to a specific region with the publishing date. Moreover, it tells the system where stock of a given product is picked up to serve a specific region. This is a very important info when it comes to understand how much stock has to be replenished for each product in every location based on an estimation of the future demand that comes from all the regions.
SKU | Region | Location | Publishing Date | End of Life Date | Selling Price | Currency |
---|---|---|---|---|---|---|
pants_0 | US | A | 2018/12/18 | 2020/12/18 | 27 | USD |
pants_0 | ASIA | B | 2019/01/31 | 2021/03/31 | 183.35 | CNY |
pants_0 | EUROPE | C | 2018/12/31 | – | 25 | EUR |
Here we describe the main fields:
- 1. SKU: the unique identifier for the product that has been used in the catalog file.
- 2. Region: the region where the product is available.
- 3. Location: where to pick stock when the product is sold on that region.
- 4. Publishing Date*: when the product was published on that region (or is going to be published).
- 5. End of Life Date*: when the product was considered as discontinued on that region (or is going to be considered as discontinued).
- 6. Selling Price*: the price at which the product is put up for sale on that region.
- 7. Currency: the currency used on that region.
Some other fields that can be added to the file:
- 8. Seasonal Break Start*: in case the product is seasonal on that region, when the product starts being temporarily considered as discontinued.
- 9. Seasonal Break End*: in case the product is seasonal on that region, when the product ends being temporarily considered as discontinued.
* It will be used in place of the value defined in the catalog file.
Suppliers
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 | MOQ | MOB | Shipping Interval | Ordering Interval | Notes | |
---|---|---|---|---|---|---|---|
A&B | A&B | info@ab.com | 100 | 1000 | 3 | 30 | Closed on Monday |
C&D | C&D | info@cd.com | – | 10000 | 5 | 60 | – |
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 and Intuendi should suggest a new reorder if it’s been at least 30 days since the last one (ordering interval).
- 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 Inventory File section for more details)
- 7. Ordering Interval: the minimum interval in days between two orders to be considered when suggesting purchase orders.
- 8. 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).
Order Lines
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 unique identifier for the product that has been used in the catalog file.
- 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.
Purchase Orders
The entire purchase orders life cycle is handled using the following 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: 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 (or it was arrived if marked as CLOSED).
- 7. Status: the PO’s status. It could be set to DRAFT, OPEN, CLOSED or CANCELED.
- 8. Total: 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 unique identifier for the product that has been used in the catalog file.
- 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: quantity received (in case only a few arrived).
- 8. Issue Date: when the PO line 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: the product price per unit.
- 11. Amount: 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 unique identifier for the received product that has been used in the catalog file.
- 5. Location: where the receipt arrived (it should be the same as in the purchase order file).
- 6. Supplier: the supplier who sent the product units (it should be the same as in the purchase order file).
- 7. Quantity: the received quantity.
Promotions
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: the promotion name.
- 2. Category: the category involved in the promotion, if any.
- 3. SKU: the SKU involved in the promotion, if any.
- 4. Region: the region/channel/customer involved in the promotion, if any.
- 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: the number of discounted units, if any.
Anomalies
The anomalies file contains all stockouts and exceptions that occured in the history and that may affect the forecasting analysis and the 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: the quantification of the anomaly, if any. If Stockout, it is the number of lost sales. If Exception, the quantity could be positive (an unexpected sales) or negative (an unexpected return).
Stock Levels
The stock levels file contains the level of stock of a product in a given location at the end of a given date (or at the beginning). With such info, we can estimate the past stock levels timeseries that could be used for several purposes.
SKU | Location | Date | 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 unique identifier for the product that has been used in the catalog file.
- 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.