Sales Data Requirements
Sales data (SALES.csv
) operates with the assumption that the source system for the data is an auditing
system (like Oracle Sales Audit) or non-Oracle data warehouse system. It applies minimal transformations to the inputs and
assumes all the needed cleansing and preparation of transaction data has happened outside of RI. Whether you are sourcing
history data from one of those systems or directly from a POS or non-Oracle auditing application, there are some business
rules that should be followed.
Requirement | File Type | Explanation |
---|---|---|
Sales Units |
Historical and Ongoing |
The values provided for unit quantities should represent the total transaction-line values for an item, split across the gross sales units and return units. In the case of an exchange, you could have both sales and return units on the same line in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Quantity metrics in RI, so they should almost always be positive. |
Sales Retail Amounts |
Historical and Ongoing |
The retail amounts on a sale or return represent the actual selling/return value, after all discounts are subtracted from the base price of the item. In the case of an exchange, you could have both sales and return units on the same line in RI, but most of the time only SLS or RET fields will have a value. These values will be subtracted from each other to display Net Sales Amount metrics in RI, so they should almost always be positive. |
Sales Profit Amounts |
Historical and Ongoing |
Profit calculations must take into consideration the cost of the item at the time it was sold, and will vary based on the retailer's costing methods. The standard approach is to use the value for Weighted Average Cost (WAC) multiplied by the units sold/returned, and subtract that total cost value from the retail amount. An item that is sold and later returned may not have the same profit amounts, if the cost has changed between the two transactions or the return was not for the full price of the item. Most POS systems do not track item costs, so providing this data requires an external process to do the calculations for you. |
Sales Taxes |
Historical and Ongoing |
Tax amounts generally represent Value Added Tax (VAT); however this column could be used to capture other tax amounts if loading directly from the POS or external audit system. |
Employee Discounts |
Historical and Ongoing |
These columns are specifically for employee discounts when the employee purchases a product at the POS and gets a special discount (or has the discounted amount returned later on). These values are just the discount amount, meaning the reduction in value from the selling price. |
Promotional Discounts |
Historical and Ongoing |
These values represent the total discount taken off the initial selling price for the line-item in the transaction. These values will almost always be populated for promotional sales. However, a regular or clearance sale could have a further discount applied (like a coupon) and that should also be captured here. These values are used to populate the Sales Promotion fact table for retail type “P” transactions. So make sure that any change in price related to a promotion is included in this discount amount, so that it is copied into other tables for Promotion-level reporting. |
Liabilities |
Historical and Ongoing |
Liabilities are sales that have not yet been charged to the customer, either due to layaway practices or customer orders that are posted as a liability transaction before they are fulfilled. Liabilities are posted with a positive value when incurred, and reversed with a negative value when they are converted into a regular sale or cancelled. Liabilities are a separate set of metrics in RI and do not interact with sales values, as it is expected that the liability will always result in a cancellation or a sale being posted at a later date. |
Liability Cancels |
Historical and Ongoing |
Liabilities that are cancelled should first be reversed and then posted to these fields as a positive amount. A cancelled liability will have no impact on sales and has a separate set of metrics in RI. The retailer can use liability cancellation metrics to track the value of customer orders that were cancelled before being charged. |
Retail Type |
Historical and Ongoing |
The retail type represents the category of sale as one of Regular,
Promotion, or Clearance. We use the codes |
Transaction Reversals and Revisions |
Historical and Ongoing |
When using Sales Audit to audit sales, the export process will automatically handle reversing a transaction and posting revisions to a transaction. Without that, you must manually create a process to send reversals and revisions to RI matching the same data format. These two records come at the same time. A reversal is an exact opposite of the original transaction line (usually all negative values, unless the original value was negative). This will be added to RI’s data and zero it out. The revision record should come next and contain the current actual values on the transaction (not the delta or difference in values). Keep in mind that, depending on the method used for calculating sales cost/profit amounts, a reversal and revision may have different values from the original profit amount. This could result in a very small residual profit from the prior revision. |
Original Selling Locations |
Historical and Ongoing |
When including the original selling location of a return transaction, you must also make sure that is a real location included on your Organization input data. Some source systems allow the manual entry of a return’s original selling location, so ensure that all such locations are included, or the records will be rejected by RI. |