Import data via csv files (version 1.0)

Avatar

By Tomas Formanek

updated 17 days ago
You can download demo data here.

In order to load data to Inventoro, you can either use one of our connectors if your data reside on the connected platforms or use this general import to include your data into the Inventoro platform. The data should contain your product and sales information for optimum at least last 2 years in order to perfectly compute seasonality or any other trends.

Data spaces

There are 2 data spaces for the Inventoro. 

  • Sales Data 
  • Promotion Data

Sales Data contains all your product, warehouse and transaction information. Based on past transactions we can forecast future sales and based on product-warehouse data (current state of amount on stock for example) we can tell you what to order and when. 

In order for perfectly analyse sales and predict future we need information about your promotions. Past promotions serve as a benchmark to see if the high sales are due to higher demand or due to promotion, future promotion serves us to higher the expected forecast because of it. In the best scenario both past and planned promotions will help us compute better forecast and orders.

Entity types

There are several entity types we import in order to compute all necessary data. 

  • Warehouse - this entity holds information about warehouse or any other place where we store items (point of sales etc) 
  • Product - basic information about product - name, category 
  • Supplier - basic information about suppliers 
  • Category - categorisation of the products (like product lines, brands etc.) - Inventoro currently supports only one level of categories (so no nested or parent-child categories are taken into account) 
  • Warehouse-product - In warehouse product connecting data are specific data for each product on given warehouse (so for example product A on Warehouse 1 has different inventory on hand or price than on Warehouse 2) 
  • Transactions - Each transaction is one sales item, so if one order has 2 products sold, it is 2 transactions in Inventoro. Data can be grouped per item per day (so all sold Product A per day from Warehouse 1)
  • Currency - Indicating primary and (if applicable) secondary currency of the system to be displayed.

Sales data



entity

field

Type

Comment

CURRENCY

currency_id

ISO code of currency

for example USD

default

tinyint

0/1.There should be only one 1 per file (the default one)

WAREHOUSE

warehouse_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


parent_warehouse_id

generated id [0-9a-zA-Z_-]

ID of parent warehouse, for use in case of supplying your warehouse/shop from central warehouse for example


PRODUCT

product_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


category_id

FK -category_id

FK = Foreign key - link to category based on the category_id (that category must exist)

SUPPLIER

supplier_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


currency_id

ISO code of currency


CATEGORY

category_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


WAREHOUSE_PRODUCT

warehouse_id

FK - warehouse_id

Link to Warehouse via id

product_id

FK - product_id

Link to product via id

available_supply

Decimal

available amount of this goods on stock

stock_price

Decimal

price of cost for this unit

sale_price

Decimal

usual selling price of this goods

supplier_id

FK - supplier_id


minimal_order_quantity

Decimal

minimal order quantity for this product, if not available empty value (as NULL)

minimal_order_set

Decimal

Minimal order set (for example packaging) for this product, if not available empty value (as NULL)  If the minimal quantity is 100 and minimal set is 10 the orders will be 100,110,120 etc.

if minimal quantity is 0, the orders are in multiply of minimal_order_set (10,20,30..)

TRANSACTION

transaction_id

generated ID 

usually line number

transaction_type_id

see transaction types


warehouse_id

FK - warehouse_id

warehouse from which this product was sold

product_id

FK - product


date_of_transaction

YYYY-MM-DD


amount

Decimal

amount sold in units

price

Decimal

sell price per unit sold

stock_price

Decimal

cost of this goods (per unit)

supplier_id

FK supplied_id

if the transaction is of type Purchase 

promo_sale

0,1

If this sale had some discount or other promo

extreme_sale

0,1

if the sale is extreme(1) it is not used for forecasting

date_of_order

YYYY-MM-DD

if the transaction os of type Purchase, empty otherwise


Transaction types: 

  1. Sales Transaction (if the amount is <0 than it is Cancellation of the sales) 
  2. Purchase Transaction (if the amount is <0 than it is Cancellation) - we are purchasing the product from our supplier


Optional Delivery Data


SUPPLIER_WAREHOUSE

supplier_id

FK


warehouse_id

FK


minimal_period

int

minimal period of ordering

leadtime

int

usual leadtime for this provider/supplier

leadtime_std 

int

days of standard deviation for leadtime


Promotion Data


Filename

Field

Type

Comment

PROMOTION

promotion_type

coupon, gift, discount, other

one of selected

promotion_code

generated id [0-9a-zA-Z_-]

identifier of  promotion to separate multiple promotions

product_id

FK -product_id


warehouse_id

FK - warehouse_id

if empty, applies to products on all warehouses

promotion_date_from

YYYY-MM-DD

start of the promotion

promotion_date_to

YYYY-MM-DD

End of the promotion

power

Decimal

power of promotion (10% discount is 10) 


Forecast only Sales data

If you need to compute only forecast and do not need to compute orders or help with stock, the minimal amount of information we need to get from you is these entities.


entity

field

Type

Comment

WAREHOUSE

warehouse_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


PRODUCT

product_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


category_id

FK category_id


CATEGORY

category_id

generated id [0-9a-zA-Z_-]


name

Varchar 64


WAREHOUSE_PRODUCT

warehouse_id

FK - warehouse_id


product_id

FK - product_id


TRANSACTION

transaction_id

generated ID 

usually line number

transaction_type_id

see transaction types


warehouse_id

FK - warehouse_id

warehouse from which this product was sold

product_id

FK - product


date_of_transaction

YYYY-MM-DD


amount

Decimal

amount sold in units

promo_sale

0,1

If this sale had some discount or other promo

extreme_sale

0,1

if the sale is extreme(1) it is not used in forecasting


Data Import CSV

Data are sent as CSV files. Entity type (entity type is for example Warehouse or Product) consists of all records of that entity type (so all warehouses are lines in one file warehouse.csv, we do not support multiple csv per type - for example it is not possible to send half of the products in one csv file and half in other csv file). 

Foreign key - FK fields(connections between entities - like for example category to product link) must be filled and must match foreign entity, otherwise the row will not be inserted. For example if we find transaction which does not have product attached we ignore it for both forecast and orders suggestions. 

Filename for each entity type is a possible to set up in initial setting, but should follow these naming conventions: 

  • warehouse.csv 
  • product.csv 
  • supplier.csv 
  • category.csv 
  • warehouse_product.csv 
  • transaction.csv 
  • supplier_warehouse.csv
  • currency.csv
You can download demo data here.

Version 

This document describes the data structure in following version: 

  • Version of sales data  - 1.0 
  • Version of promotion data - 1.0

Did this answer your question?