Inventory Data Requirements
Inventory data (INVENTORY.csv
) has several special requirements that need to be followed when generating
historical data and ongoing daily feeds, due to the way the data is stored within Retail Insights as well as the different
use-cases in each of the Science and Planning applications. A retailer may not have the data in the required format in their
source system, and adjustments would have to be made in the data extraction process to ensure these rules are followed.
Requirement | File Type | Explanation |
---|---|---|
Records may be needed before the item/location has any stock on hand |
Historical and Ongoing |
The inventory position contains fields for inventory movement, such as on-order, in-transit, and reserved quantities. As soon as any of those values may contain data for an item/location (and you intend to use those fields in RI), a record should be included for inventory, even if the actual stock on hand is still zero that day or week. |
Zero balances must be sent after an item/location has started carrying a non-zero position |
Historical and Ongoing |
Inventory data is stored positionally, meaning we must maintain the current daily balance of stock on hand for every item/location. If no change comes into the system on a given day, we carry forward that balance. This means that you cannot send only non-zero values in the data files, as it is assumed the last known value is also the current value for the day or week. You must send a zero balance any time the inventory has moved from non-zero to zero. |
Clearance indicator is used to show the end-of-period status of the item/location’s inventory |
Historical (Weekly) |
Inventory data has a required column for a Clearance Flag ( |
Inventory records should continue to be sent based on an item/location’s active ranging status |
Historical (Weekly) |
Once you begin sending inventory records for an item/location, it is ideal that you continue to send records every week even if the balance is zero, until that item/location is no longer active/ranged. This is important for out-of-stock detection and other analytical checks on inventory levels. A null or missing record is not automatically assumed to be zero. |
Any change to values on the inventory position should send an update of the full record from the source system. |
Ongoing (Daily) |
If you are using other inventory values besides stock on hand (such as on-order or in-transit), you must ensure the extracts will send a complete record to the inventory interface when any of those values change. For example, a new item/location may carry an on-order balance or in-transit balance for several weeks before it has any stock on hand, so your extracts must trigger changes to those values, not just changes to stock on hand. Most other values on the interface are only used in RI reporting at this time. |
For historical loads, this results in the following flow of data across all your files:
-
Generate the first month of week-ending inventory balances in INVENTORY.csv for all active item/locations in each week of data. Include zero balances if the item/loc is actively ranged to a location and is actively selling, so that out-of-stock detection is accurate. Load using the historical inventory load adhoc process.
-
Repeat the monthly file generation process, including sets of week-ending balances in chronological order. Remember that you cannot load inventory data out of order, and once a week is loaded you cannot go backwards to update past periods. All files must continue to include zero balances and other data as defined in the list of requirements.
-
Load every week of inventory snapshots through to the end of your historical period. If there will be a gap of time before starting nightly batches, plan to load an additional history file at a later date to catch up.
-
When you are ready to cutover to batches, you must also re-seed the positions of all item/locations that need to have an inventory record on Day 1 of nightly batch execution (same as for all positional facts in RI). This is needed to fill in any gaps where currently active item/locations are not present in the historical files but need to have an inventory record added on day 1. Use the Seeding Adhoc process for Inventory to do this step, not the historical load.