1. Implementation Guide
  2. Data File Generation
  3. Fact Files
  4. Price Data Requirements

Price Data Requirements

Pricing data (PRICE.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 the RI and Science 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

The first price file must include records for all active item/locations

Historical

The pricing fact is stored positionally, meaning that it first needs a starting position for an entity (for example, an initial price for an item/location) and then it may not be sent again unless there is a change to the value. The very first price file loaded into RI must contain a starting position for all active item/locations. How you determine which item/locations were active on that day in history will depend on your source system (for example, you can base it on items having stock on hand, historical ranging status, or a price history table if you have one).

The first record sent for an item/location must come as a new price transaction type

Historical and Ongoing

Price records have a column for the price type (PRICE_CHANGE_TRAN_TYPE) with a fixed list of possible values. The value 0 represents a new price, which means it is the first time our system is getting a price for this item/location. All item/locations must be given a type 0 record as their first entry in the historical or ongoing data files. All the initial position records in the first file will have type=0. Also, all new item/locations coming in later files must first come with type=0.

Price records should follow a specific lifecycle using the type codes

Historical and Ongoing

The typical flow of price changes that will occur for an item/location should be as follows:

  • New price/cost (PRICE_CHANGE_TRAN_TYPE=0)

  • Regular cost changes (PRICE_CHANGE_TRAN_TYPE=2)

  • Regular price changes (PRICE_CHANGE_TRAN_TYPE=4)

  • Promotional/temporary markdowns (PRICE_CHANGE_TRAN_TYPE=9)

  • Clearance markdowns (PRICE_CHANGE_TRAN_TYPE=8)

  • Price reset due to new selling cycle or season change (PRICE_CHANGE_TRAN_TYPE=0)

An item/location may have many changes with types 2/4/8/9 before eventually staying at 8 (for a final markdown) or resetting to 0 (if the item lifecycle should restart).

Price changes are for the end-of-day value only

Historical and Ongoing

An item price may change many times a day, but you must only send the end-of-day final position for the item/location. The file interface assumes only one record will be sent per item/location/effective date, representing the final price on that date.

For historical loads, this results in the following flow of data across all your files:

  1. Generate an initial position PRICE.csv that has all type=0 records for the item/locations you want to specify a starting price for. Load this as the very first file using the historical load ad hoc process.

  2. Generate your first month of price change records. This will have a mixture of all the price change types. New item/location records may come in with type=0 and records already established can get updates using any of the other type codes. Only send records when a price or cost value changes; do not send every item/location on every date. You also must not send more than one change per item/location/date.

  3. Repeat the monthly file generation (or more than one month if your data volume for price changes is low) and load process until all price history has been loaded for the historical timeframe.

  4. When you are ready for the cutover to batches, you must also re-seed the positions of all item/locations that need a price 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 a price record added on day 1. Use the Seeding Ad Hoc process for Pricing to do this step, not the historical load.