Metrics

Comparable Stores Analysis

Comparable (comp) stores are stores that are open for business for a set period of time and were in operation within the time period of analysis. In other words, comp stores are really established stores as opposed to new or closed stores. Comp store measurements are important to an analyst because profits and sales from the more established stores provide stable indicators of business performance. New or closed stores tend to be more volatile and can have a skewing effect on business performance indicators. Sales and profits from new or closed stores are not really comparable in business analysis, and as a result, they are not included in the comp store measurements.

The Comparable Store Flag can be sent from the retailer's merchandising source system (not including RMFCS, which does not provide RI with comp flags), or can be provided manually through a flat file interface. Regardless of the method used to determine comparable store status, the Comp Flag attribute is provided as a means of reporting metrics based on the comp status of a location. Additionally, special Comp metrics are provided in multiple subject areas to support side-by-side reporting of comp data with other metrics. If using the Same Store type of reporting, the Comp Anchor Year attribute must be used to specify which year the comp flags will be taken from. For example, if the reporting period is March 2017, then a filter must be set such that Comp Anchor Year = 2017.

The Comp Store Measurements measure the growth in sales and profit, excluding the impact of newly opened stores. Sales and profits from new stores are not reflected in same-store comparisons until those stores are converted to comparable stores. With this approach, stores whose open dates are not captured in the source system are not included in these comparisons. Each store needs to have a store open date as well as a store close date when a store is closed. If there is no close date, the store is assumed to be still in operation.

Cost and Profit

Cost and profit analysis helps a retailer to understand the financial impact of various business decisions such as:

Stock levels for high-profitability items
Deals negotiation for low-profitability items
Promotions worthiness

Profit is calculated as the difference between sales amount and cost of the item in the transaction. The cost of the item in the sales transaction is based on the weighted average cost of that item in the merchandising system at the end of the day.

Net cost (sometimes called deal cost) measures are held at the supplier level.

Net cost is populated with data from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) or another source system. The data from RMFCS consists of cost values that represent different discounts on base cost that the supplier provides. These discounts can include the following:

Deals with deal partners for items, or items at specific locations

Deal partners can be suppliers, wholesalers, distributors, and manufacturers. Within a deal, you create deal components, specify the items for the deal component, and define thresholds.
Fixed deals with suppliers

Your organization receives payments from suppliers in return for mentioning their products in promotions, or for displaying their products on prime shelf space.
Bracket costing deals with suppliers

Your organization receives a certain deal price on an order, depending on the size of the order. Different types of brackets can be established based on mass, volume, pallet, case, each, or stat case.

Markdowns and Markups

Retailers plan markdown strategies carefully, as they make pricing decisions for their products with an eye toward keeping inventories at optimal levels, while driving gross margin revenue across key areas of the merchandise hierarchy.

Retail Insights markdown analysis allows reporting on a range of data related to markdowns and markups. These include permanent and point-of-sale markdowns and markups, as well as markup and markdown cancellations, at granularities of item, location, day, and retail type (regular, promotion, employee discount, and clearance).

A buyer planning a promotion strategy for a category of goods might be interested in promotional markdown totals in a certain department, year to date. On the other hand, a finance executive might want to analyze clearance markdown amounts compared to promotional markdown amounts at the corporate level, on the same report with profit comparisons from clearance versus promotional sales.

RI also captures reference fields from RMFCS transactional data, which may contain details about markdowns initiated in Retail Price Management. For example, Ref Number 1 will contain the price change event ID and Ref Number 2 will contain the markdown number assigned to a clearance event (such as First or Second). These attributes will allow the user to report on clearance activity using the data in these fields, such as limiting an item's sales and inventory data to the time it is in First Markdown status. Such analysis is used for determining the profitability and sales patterns relating to specific markdowns on a product.

When reporting on markdown data, it is important to understand that the records for such price changes are only captured at the time the price change goes into effect (this is comparable to the time the transaction is registered in RMFCS). For example, when a clearance markdown is applied to an item, RI will record the impact of that markdown on all item-locations affected by it, in terms of the change to retail amounts for available stock on hand. Even though the item continues to be in clearance status for many weeks, the markdown data exists only for the day of the original price change event. This represents the impact to the total retail value of the stock on hand due to the change in selling price.

Sales Forecast

A sales forecast is a calculation of the potential sales of an item for a future period, based on past performance of the product. Sales forecast analysis helps a retailer to develop a marketing budget, allocate resources, and get a early sense of deviations from financial goals. Sales forecast analysis also helps the retailer to determine the effectiveness of forecasting techniques.

Retail Insights stores sales forecast data in one of two configurable planning interfaces called Plan Forecast 1 and Plan Forecast 2. The default data level of these interfaces is item-location-week level. The sales forecast quantities exclude value-added tax (VAT). Retail Insights can accept forecast data directly from IPO Cloud Service-Demand Forecasting or from an external application.

For a more detailed explanation of how configurable planning facts work, refer to the Planning section later in this chapter, as well as the Oracle Retail Insights Implementation Guide.

Inventory Adjustments

Inventory Adjustments are changes to inventory level in units, retail and cost value. Inventory Adjustment analysis provides visibility to Inventory analysts, Inventory controllers, Inventory managers, Category managers and store managers for analyzing the reasons and plan accordingly to overcome the potential problems that are causing the stock adjustments. Inventory Adjustments impact the stock ledger as the inventory value for a location is impacted. Ending stock value will be increase/decreased making the stock as over-valued or de-valued.

Retail Insights holds the inventory adjustment units and value by transaction code, reason code, item, location and day level. Merchandising transaction codes on the fact can include 22, 23, and 41. Additionally, RI may hold the clearance status and clearance markdown numbers associated with the items on this fact, in order to enable clearance-based inventory reporting. The Clearance Dimension is used to maintain these status codes on the adjustments fact during nightly batch processing. If the dimension is not used, then all records will default to "N" or non-clearance status.

Inventory Transfers

Inventory transfer is the movement of stock between the retailer's locations. Inventory transfers analysis will help the retailer in taking appropriate and profitable decisions to improve the sales by initiating stock transfer from nearby locations to avoid lost sales.

Retail Insights supports three types of transfers, normal, book and intercompany, with an attribute called transfer type.

Book transfer items are inventory units moved from one part of the retailer's location to the virtual location.
Normal transfers are the inventory moved between the retailer's physical locations. (Store or warehouse)
Intercompany transfer items are inventory units moved from one legal entity into another legal entity. RI holds transfer units and cost and retail values of transferred units.

Inventory transfers are held at the item or subclass, destination (to) location, shipping (from) location, and day or week levels. When you want to see total inventory transferred to a location (with no regard for the From Location) then simply use the standard Organization attributes, such as Loc Number. If you want to see the movement of inventory between locations, then you must use the From Organization dimension as well as the Organization dimension.

Inventory Transfer Details

In addition to the aggregated transfer in/out information, RI also captures the lowest level details about individual transfer actions taking place in the merchandising system. The Transfer Status Detail fact will maintain an up-to-date record of all transfer lines, including the initial quantities, shipped quantities, cancelled quantities, and all other unit values associated with the inventory movement action. This fact is equivalent to the RMFCS table TSFDETAIL and will have all data columns from that source when it is used. RI also maintains a linked dimension for the Transfer Status header (TSFHEAD) in order to expose the attributes associated with each transfer. The transfer status fact is held at a level of Transfer header, Purchase Order (when linked to a PO), receiving location, item, and transfer creation date.

Inventory Receipts

Inventory receipts are units purchased and placed in inventory or units received from other retailer locations as part of a transfer or an allocation. Inventory receipts analysis provides visibility to and control of your accrued liabilities for inventory items. Inventory receipts transactions are recorded in the general ledger at the time of receipts.

Retail Insights holds the number of units received at the day and week level, at both retail and cost value. Inventory receipts are held at item level for day and week and at subclass (segment) level for day and week. Additionally, RI may hold the clearance status and clearance markdown numbers associated with the items on this fact, in order to enable clearance-based inventory reporting. The Clearance Dimension is used to maintain these status codes on the receipts fact during nightly batch processing. If the dimension is not used, then all records will default to "N" or non-clearance status.

Receipts are also differentiated by type, based on whether they are the result of a purchase order, allocation, or non-allocation transfer. A Transaction Code dimension is provided to separate inventory receipts by type, as well as metrics to allow reporting on specific types of receipts.

Inventory Unavailable

Unavailable inventory is on-hand inventory which is currently in a non-sellable state, such as damaged goods. Such inventory is still considered part of a location's total on-hand inventory, but it cannot be sold to customers. Unavailable inventory analysis provides visibility into the types and amounts of non-sellable goods currently being held at a location.

Retail Insights maintains a record of unavailable units and the value of unavailable units in cost and retail amount, grouped by status code, item, location, and day.

Return to Vendor

RTV units are units returned to the vendor for any reason (overstock, poor quality, etc.). Return to vendor analysis gives retailer valuable insights for evaluating vendor performance.

Retail Insights maintains record of RTV units and the value of RTV units in cost and retail amount. RTV facts are held at the item/supplier/location/day/return reason level.

In addition to the units, cost, and retail of an RTV, RI also captures the tran code 65 records from the Merchandising system for RTV restocking fees and costs.

Sales

Sales reporting helps the merchandising executive to identify sales key performance indicators and determine the operational effectiveness of sales, to evaluate whether sales achieve the results set during sales planning. This can help sales managers to take timely corrective actions when they see deviations from projected values.

Gross sales value is the total amount the retailer sells to consumers. Gross sales value is calculated by multiplying the unit price of an item by the number sold to consumers. Returns are the portion of sales that are returned to the store for a refund. Net sales value is the net value after customer returns are subtracted from gross sales value.

Retail Insights maintains gross sales and returns for amounts and numbers of units in separate fact columns. Separation of these values allows analysis of returns and the use of gross sales in calculations where this is desirable. Net sales value is required for most calculations.

In addition, the retailer may need to track sales according to price type to allow analysis of sales for promotional and clearance items. Retail Insights holds sales amount and units by retail price type to allow analysis at this level. The terms for the price types are Regular (Reg), Promotional (Pro) and Clearance (Clr). A fourth group called Non-Clr is the addition of Reg and Pro metrics into one value.

Return metrics may additionally be reported on using Reasons and Selling Location. Reason codes are the reasons that a customer returned a product, as specified at the point of sale. Analyzing returns by reason could highlight potential problems with inventory from a specific supplier or products that have poor sizing measurements. The original selling location is the location where the sale occurred for a verified return (for example, a return where the receipt was provided or the original sale could be found in the customer's history). Analyzing the original selling locations of returns can identify customer trends such as buying online and returning in store, or buying in one location and returning at a nearby different location.

RI provides fields on the sales interfaces for a number of reference values that are commonly included with transactional data. The main sales transaction interface contains 19 reference fields, which by default align to the fields available in Sales Audit for transaction headers, items, and discounts. The transaction tender interface also includes four reference fields, which default to those found in Sales Audit's tender table. RI also has fields available to capture optional attributes and descriptive elements on a transaction, such as drop shipment indicators, sales types, and price override codes.

Sales Extensions

The sales transaction fact can also be extended using a source-agnostic interface for additional facts and attributes. The interface (W_RTL_SLS_TRX_EXT_IT_LC_DY_FS) contains all of the external identifiers of a transaction line, such as POS transaction numbers and sales associate IDs, as well as 50 flex columns for data. The interface could be populated with POS data not interfaced through other means, as well as manually-generated attributes needed for advanced sales reporting. The interface also supports having multiple sales associates per transaction line, which provides reporting at a lower grain of detail than what Sales Audit provides to RI directly.

Sales Fulfillment

The sales transaction fact can also support reporting on sales by fulfillment location, when the sale is a customer order or online sale fulfilled at a different location. This information is interfaced from Sales Audit if the POS has been configured to populate the fulfillment location ID on the RTLOG. When the fulfillment location ID is populated in RI, you may use the Fulfillment Organization dimension to see which locations are fulfilling such sales. A number of different business scenarios can be analyzed using this data, such as the amount of online purchases fulfilled in stores and the number of in-store customer orders that are not fulfilled from that point of sale.

Sales Pack

A sales pack is a group of individual items grouped together by the retailer to be sold as one item. An example is a bottle of shampoo and a bottle of conditioner, both individual items on their own, but packaged together to be sold as a unique pack item.

Retailers require visibility to pack sales contribution information by regular, clearance, and promotion retail types. This analysis provides the ability to compare and contrast location performance of pack sales using retail type measures.

These metrics can help to determine:

How a SKU sold as a single item
How the pack itself has sold historically
How a SKU sold when it was included in a specific pack

Retail Insights extraction, transformation, and loading processing prorates the value of a pack into its component items (see "Prorating of Packs" later in this section). This helps in analysis of component pack item contribution to pack sales.

Prorating of Packs

The prorating of a pack's value into its component items requires calculation. The following formulas are used for prorating packs:

Item Prorated Sales Value = Pack Sales Value * Item Prorate %

Item Prorate % = (Item Price * Pack Item Qty) / Pack Component Sales Value

Pack Component Sales Value = (Item A Price * Item A Qty) + (Item B Price * Item B Qty) + (Item C Price * Item C Qty) + …+ (Item n Price * Item n Qty)

Example

Pack A has a pack sales value of $90,000. Each pack is priced at $9 and contains the following:

Table 6-1 Pack A Example

Item	Quantity	Price
Item A	2	$4
Item B	1	$2
Item C	1	$1

Calculation Steps

Calculate pack component sales value:
1. Item A Price * Quantity of Item A in Pack A
  
  4 * 2 = 8
  
  Item B Price * Quantity of Item B in Pack A
  
  2 * 1 = 2
  
  Item C Price * Quantity of Item C in Pack A
  
  1 * 1 = 1
2. 8 + 2 + 1 = 11
Calculate item prorate percent:

8/11 = .7273 (Item A)

2/11 = .1818 (Item B)

1/11 = .0909 (Item C)
Calculate item prorated sales value:

$90,000 * .7273 = $65,457.00 = Item A Prorated Sales Value

$90,000 * .1818 = $16,362.00 = Item B Prorated Sales Value

$90,000 * .0909 = $8,181.00 = Item C Prorated Sales Value

Simple Pack Reporting

RI provides additional ways to report on sales involving Simple Packs (pack items having only one kind of component item within them). For these pack items, it can be necessary to get the sales for the pack item as well as the component item, either separately on different lines or together in one measure.

To see the sales of simple pack items with their component item number, you can create reports having the attributes Item Number and Simple Pack Comp Number along with regular Sales metrics. The Item Number will show the pack number if it was a pack sale, or the component item if it was a direct sale of the component. The Simple Pack Comp Number will always show the component item.

Example

Pack Item 5360 has within it a component item 5319. Both the pack and the component are sellable items and may appear in RI sales data. Using the Simple Pack Comp attributes, I can see which component belongs to the pack when reviewing its sales, and how many units of the component were sold as part of the pack:

Table 6-2 Simple Pack Comp Attributes

Item Number	Simple Pack Comp Number	Simple Pack Comp Qty	Net Sales Qty	Net Sales Amt
5360	5319	10	2	$38
5319	5319	1	5	$10

Note:

This data is not the prorated component sales as described earlier, but the original sales of the packs and components as they came from the source system. If I want to see the combined total sales of a component item (both on its own and when spread from pack sales to the component values) then there are a set of Sales Pack metrics prefixed "Simple Pack Total". When using these metrics, only the Item Number attribute is necessary, since the Sales Pack is already spread to component level.

Example

For the same pack and component item in the example above, we want to see the total sales spread to the component level and summed together, so I can accurately report on only the component items being sold.

Table 6-3 Example

Item Number	Simple Pack Total Net Sales Qty	Simple Pack Total Net Sales Amt
5319	25	$48

Sales Consignment and Concession

RI has a separate fact area designated for the sale of consignment and concession items, when using the consignment/concession functionality available with RMFCS version 19 or later. The Sales Consignment set of metrics will display only the sale of non-owned inventory. These sales are also included in your total sales amounts, in order to provide a complete picture of revenue and profits.

The calculation of profit and cost amounts for such sales transactions will be derived using the consignment/concession rates or unit costs configured in RMFCS (depending on the type of item cost used).

When an item's sales are portioned out based on a percent of the selling price, the general formula is: Sales Profit = Sales Amt * (1 - Purchase Rate/100)

When an item's sales are paid out using a fixed unit cost, the general formula is: Sales Profit = Sales Amt - (Sales Qty * Unit Cost)

Supplier Invoice

Supplier invoice reporting can help retailers achieve control of a supplier's payment process and assess the discrepancies for a supplier.

Supplier invoice cost is the actual cost as shown on the supplier invoice (from Oracle Retail Invoice Matching or other source system). Supplier invoice purchase order cost is the expected cost previously agreed upon in the purchase order, before any deals or discounts. A difference between the two can reflect deals, discounts, clerical errors, or dishonesty.

Supplier invoice cost and supplier invoice purchase order cost are held at the supplier-item-location-day level.

Supplier Performance and Compliance

The merchandising organization must carefully select, monitor, and adjust relationships with suppliers. Before negotiations with suppliers, the retailer can prepare by running supplier performance and compliance reports.

Supplier performance considers typical merchandising measures such as net sales, profit/margin, markups, and return rates, to compare the profitability and inventory costs of goods provided by different primary suppliers.
Supplier compliance measures allow buyers to assess supplier delivery timeliness and purchase order fill rates. For example, how many advance shipping notices came in early, on time, and late? Were overall purchase order counts at expected levels, under, or over?

This analysis can help the retailer to negotiate supplier-funded promotion negotiations and supplier bill-backs, and reward responsive and flexible suppliers. This in turn can reduce inventory costs, prevent out-of-stock conditions, and increase profitability.

Supplier Performance

This functional area focuses on reporting that provides supplier performance information based on key performance indicators. Collection of this data makes the following types of analyses possible:

Compare and contrast supplier performance over time
Compare and contrast department performance by primary supplier
Monitor department performance in terms of sales volume and value
Compare and contrast market supplier with supplier performance

Primary Supplier

Department managers in particular need to understand sales and profit contribution information about their suppliers. Retailers can monitor supplier performance better by identifying suppliers of profitable items, measuring contributions to total department performance, and identifying how categories are performing relative to other categories, and relative to last year.

Unless facts (such as net cost) are stored by supplier, all facts in that data can only be attributed to the primary supplier.

Performance Metrics

The following types of measures are a part of supplier performance:

Sales and profit
- Sales value and variance in sales value from last year
- Sales units and variance in sales units from last year
- Profit amount and variance in percent profit from last year
- Percent contribution to total sales value for the department
Inventory position and movement
- Sell-through
- Stock turns
- Beginning stock on hand (BOH) and ending stock on hand (EOH) retail value
- Receipts
- Gross margin return per dollar of inventory (GMROI)
Net (deal) cost

Net cost (sometimes referred to as deal cost) measures are held at the supplier level. Net cost is populated with data from Oracle Retail Merchandising Foundation Cloud Service (RMFCS) or another source system. The data consists of cost values that represent different discounts on base cost that the supplier provides. These discounts may be:
- Deals with deal partners for items, or items at specific locations
  
  Deal partners can be suppliers, wholesalers, distributors, and manufacturers. Within a deal, you create deal components, specify the items for the deal component, and define thresholds.
- Fixed deals with suppliers
  
  Your organization receives payments from suppliers in return for mentioning their products in promotions or for displaying their products on prime shelf space.
- Bracket costing deals with suppliers
  
  Your organization receives a certain deal price on an order, depending on the size of the order. Different types of brackets can be established based on mass, volume, pallet, case, each, or stat case.

Supplier Compliance

Supplier compliance measures supplier performance based on key performance indicators such as timeliness and accuracy of deliveries. The supplier compliance functionality supports supplier evaluation based on the following parameters:

Timeliness
Delivery accuracy
Order fulfillment

Supplier Invoice Cost

Supplier invoice cost is the actual cost as shown on the supplier invoice (from Oracle Retail Invoice Matching or other application). Supplier invoice purchase order cost is the expected cost previously agreed upon in the purchase order, before any deals or discounts. A difference between the two can be reflective of deals, discounts, clerical errors, or dishonesty.

Supplier invoice cost and supplier invoice purchase order cost are held at the supplier-item-location-day level.

Receipts by Supplier

Retail Insights supplier compliance data provides the ability to report receipt units grouped by supplier, item, location, and day. For example, the fact column RECEIVED_QTY contains the quantity from the qty_received column in the RMFCS SHIPSKU table.

The supplier compliance data does not contain cost or sales data, so it cannot be used to report sales or cost by supplier. The quantity in the supplier compliance data should not be confused with receipt units in the inventory movement data.

Timeliness

Timeliness measures the supplier's ability to deliver according to schedule. Early, late, and on-time shipments are tracked in the supplier compliance area. You can measure supplier timeliness on a daily basis.

Timeliness = No of On Time Deliveries / ( No of On Time Deliveries + No of Early Deliveries + No of Late Deliveries )

For example, if the number of on-time deliveries is 75 and the total of all deliveries is 100, the timeliness rating is 75 percent.

Missed deliveries are deliveries that did not take place within the time frame specified. A late delivery is also a missed delivery. Because the timeliness measure would not be meaningful if two of its components were counted twice, missed deliveries are not included in the timeliness measure. Missed deliveries can be reported at the supplier-location-time level as a separate metric.

Delivery Accuracy

Delivery accuracy measures the supplier's ability to deliver the correct items and quantities on the order. The rating is determined by comparing the total number of deliveries for the supplier to the number of deliveries where the quantity or item was incorrect.

Delivery Accuracy = Number of ASN Expected Deliveries / Number of Deliveries

where:

Number of Deliveries = No of ASN Expected Deliveries + No of ASN Over Deliveries + No of ASN Under Deliveries + No of Mismatched Deliveries

A mismatched delivery is a delivery that contains at least one mismatched item.

For example, if the number of on-time deliveries is 75 and the total number of deliveries is 100, the delivery accuracy rating is 75 percent.

Order Fulfillment

Order fulfillment measures the supplier's ability to deliver on order in full. The rating is determined by calculating the ratio of completely filled order to the total number of orders.

Order Fulfillment = No of Full Order Deliveries/ Total Orders

where:

Total Orders = Orders Received in Full + Orders Received in Part + Orders Received in Excess

For example, a supplier earns an order fulfillment rating of 75 percent if the total number of orders is 4 and the number of partial deliveries is 1.

Inventory Position Analysis

Retail Insights holds stock position at a very low level, which is the ending position for every day for every item at every stockholding location. The available stock position measures include quantity, retail value, and cost amount (usually interfaced from source systems based on weighted average cost calculation).

There are four distinct groupings of stock position in Retail Insights:

On-hand stock (goods owned by the retailer and received in a location)
In-transit stock (goods owned by the retailer, received into one location such as a distribution center, but currently in transit to another store or warehouse)
On-order stock (goods on an approved Purchase Order which have not yet been received)
Reserved and expected stock (stock that has been held for a transfer or return but has not been shipped)

Two examples of on-hand measures are ending on-hand (EOH) for a time period, as well as beginning on-hand (BOH) for a time period. The EOH position for week 1 is the BOH position for week 2.

Stock position is a constant state in which a value or position shifts over time. Stock on hand is at a certain position at the beginning and end of a week and at any point between. Positional values cannot be added together to arrive at a meaningful number. For example, the ending stock-on-hand values for the days in a week do not add up to the ending value for a week. Rather, there is a position at the end of each day and, in this example, the ending position for the week is the same as the position for the last day of the week. For this reason, positional measurements are semi-additive. They are not additive in the time dimension. In other dimensions, they act much like transactions. For example, the ending on-hand value for a subclass can be determined by adding the ending on-hand values for all items in that subclass.

Comparing ending inventory value to the same period last year is a typical scorecard measure, but deeper analysis and more complex calculations are also required. Retail Insights offers critical inventory calculations such as gross margin return on investment (GMROI), weeks of supply, stock turnover, sell-through, weekly average inventory, and the critical out of stock percentage measures.

A buyer might use one of these calculations to pair net sales and net profit measures on the same report with the out-of-stock percentage for the current month, to assess whether a certain department had low sales performance because of stock unavailability.

An inventory analyst can track the inventory age of existing inventory at a given location. The movement of merchandise from a warehouse to the stores in a timely manner is critical to business. Merchandise lying in a warehouse for a long time adds to the expenses and also brings down profits. Inventory aging related metrics will provide the basis for calculating the inventory age, amount value and the percentage of inventory that has aged beyond a certain set time period.

Some of the questions that can be answered as part of the Inventory aging analyses are:

What is Quantity/Cost/Retail of received merchandise that is still present at the given location beyond a given time period?
How quickly is the Merchandise being distributed from the servicing DC to the stores?
What percentage of merchandise is aging at the servicing DC and for how long?
What is the trend of inventory aging this year compared to last year?

Inventory can also be maintained for consignment and concession items. RI loads the inventory position for such items with a flag identifying the ownership type (Owned, Consignment, or Concession). The Purchase Type dimension can be used to split inventory metrics by ownership status.

RI supports several variations of the Inventory Availability concept. Inventory is considered to be generally available in stores if it has enough on-hand units to meet or exceed a minimum display quantity or presentation stock. In the context of RMFCS, this presentation stock level comes from the replenishment settings for each item-location. Using the presentation stock and max stock values for an item-location, the availability is calculated in two different ways:

Numerical availability, which is based on the percent of items having on-hand quantity above the presentation stock threshold, where each item is simply available or not (0 or 1).
Weighted availability, which is based on the on-hand units/cost/retail being above or below the presentation stock, where having a non-zero value below the minimum threshold still provides some weight to the final percentage of available items.

If no replenishment parameters are available then these calculations will not be populated.

Wholesale

Wholesale metrics enable reporting on wholesale transactions as distinct from regular retail transactions, allowing retailers to understand how their wholesale business is working as a stand-alone operation. This will keep the wholesale business from being lost in the noise of their overall sales. The list below is unique wholesale metrics, but also all the regular sales metrics can be used to do wholesale analysis by filtering for transactions at wholesale locations.

Franchise

Oracle Retail Insights has three types of franchise metrics: Stockholding Franchise, Non-Stockholding Franchise, and Franchise. Which one a retailer uses will depend on their relationship with their franchise locations: if they manage inventory and replenishment for their franchisees, then Stockholding Franchise metrics are more useful, but if their franchisees operate relatively independently, Non-Stockholding Franchise metrics would be appropriate. Markdown and Markup metrics are simply known as Franchise metrics because there is no way to distinguish between stockholding and non-stockholding for this type of metric.

Consumer

Consumer analysis is a method by which retailers will analyze their target consumers in order to determine the most effective strategies to improve both their sales and profitability. The Consumer dimension and Consumer Segment dimension can be used together to create consumer profiles, analyze consumer attribute trends and patterns, and identify groups of consumers that are ideal for conversion and marketing activities. Additionally, the Consumer Segment dimension can be used along with Sales fact data to analyze the effectiveness of conversion activities on a specific segment.

Price

Pricing analytics can help retailers determine the optimal pricing of products. It focuses on the proposed pricing of merchandise. Cost elements and profit components are not evaluated as part of pricing.

Retail Insights holds price as a retail value for an item, day, and location. For the purpose of analysis, the price is semi-additive and calculated over the time period selected for the report. For example, if the Price metric is added to an analysis at the levels of Subclass, Loc, and Fiscal Week, then the value returned will be the average price for all items in the subclass, at that location, as of the end of that week.

Planning

Retail Insights holds facts for both preseason (original) and in-season (current) planning in several reporting areas, including sales, markdowns, receipts, inventory, gross margin, and open-to-buy, in both dollars and units. RI stores planning data at intersections of Merchandise Hierarchy, Product Attributes, Organization Hierarchy, Calendar Hierarchy, and Supplier Dimension. The Merchandise Hierarchy includes item, subclass, class, department, group, and division. The Organization Hierarchy includes store, district, region, area, chain, channel, and company. The Time Hierarchy includes day, week, period, quarter, half year, and year. The supplier dimension includes suppliers, parent suppliers, or no suppliers. Product attributes can include the brand, style, color, size, pattern, flavor, or scent (these align with the same-named attributes that exist in the Item Dimension).

RI provides up to 4 possible combinations of intersection of the 4 hierarchies (and optional attribute) per each implementation. The 4 possible combinations can be configured during the installation time to decide which level of Merchandise Hierarchy, Product Attribute, Organization Hierarchy, Calendar Hierarchy, and Supplier Dimension will be used. Multiple plan interfaces may exist at the same intersections of data if desired.

The following abbreviations are used in the names of Planning metrics:

CP1 to CP4: Current Plan for Plan 1 through Plan 4
OP1 to OP4: Original Plan for Plan 1 through Plan 4

In addition to metrics, the plan versions also contain a number of fact-based Flex attributes that can be populated as needed. Flexible character attributes behave differently from metrics in that they must NOT be combined in a report with attributes from other plans. For example, OP1 Flex 1 Char Value cannot be used in the same report as CP1 Flex 1 Char Value. The attributes are tied to specific plan versions and would influence the data being queried, just like when using actual dimension attributes. There would be no data existing for both OP1 and CP1 flex attribute values at the same time.

Review the following example configuration:

The first plan configuration (loaded to the staging table W_RTL_PLAN1_PROD1_LC1_T1_FS) will be used to hold the primary Merchandise Financial Plan. This plan will exist at the levels of Subclass, Chain, and Week. The Subclass's unique identifier (a concatenated value of dept~cls~sbc, e.g. 10~100~5) is provided for the product hierarchy values, the Chain identifier (e.g. 1) is provide for the location hierarchy values, and the week-ending date is provided for the calendar values. Product attributes and suppliers are not being used, so they should be set to a default value of '-1'.

The Version Number field should be set as '0' for the original plan, and '1' for the current plan. Versions after 1 represent changes to the current plan that need to be captured in RI over time. For example, if my plan is revised once a month, I may want RI to hold each monthly revision as a new version so that I can see how my initial plan compared to my end-of-quarter revised plan. A version number attribute is available in each plan to perform these comparisons. The CP metrics will always show the latest plan version if more than one exists.

All of the chosen hierarchy levels would be configured in C_ODI_PARAM settings prior to running a batch with planning data. All planning files should be bundled in the RI_MFP_DATA.zip file and uploaded to the SFTP server, where they will be picked up and processed during the nightly batch run.

Stock Ledger

Retail Insights information for stock ledger analysis comes from Oracle Retail Merchandising Foundation Cloud Service (RMFCS).

The lowest-level stock ledger facts are kept at the subclass and week level. This gives Retail Insights visibility to store/subclass/week level and subclass/month level. Stock ledger reporting is not available at the item and day levels. Reports and drills into data that are lower than the subclass/week level return null values for stock ledger facts.

If you receive stock ledger information from RMFCS, the RMFCS stock ledger feed to Retail Insights supports either a 4-5-4 fiscal calendar or Gregorian calendar.

If you have a Gregorian stock ledger, reporting in Retail Insights can be done at the subclass, location, and month levels. Reports and drills into data that are lower than the subclass/month level return null values for stock ledger facts.

If you have a 4-5-4 stock ledger, you can analyze the stock ledger at the subclass, location, week, and month levels. Reports and drills into data that are lower than the subclass/week level return null values for stock ledger facts.

Any other calendars, such as a 13-period time calendar, are not supported by the RMFCS interface to Retail Insights for stock ledger facts. If an RMFCS user customizes the stock ledger to use a 13-period calendar, there are inconsistencies with the RMFCS stock ledger interface to Retail Insights unless modifications are made.

Because the month-level stock ledger is directly related to the RMFCS MONTH_DATA table, data for a specific month is available in Retail Insights after the close of that month.

Baseline

Baseline metrics are derived from data mined during a period of time when an item is not on promotion.

The baseline process brings sales transaction data from Retail Insights into a suitable structure for performing baseline calculations. The process first transfers sales data by week, identifying which weeks are suitable to be included for baseline calculation. A set of item/location weekly sales is suitable for baseline calculation only if it does not have promotion sales for the week. The number of weeks of sales data to use for baseline calculation is configurable, with a default suggested value of 16 weeks, eight weeks prior to the promotional week and eight weeks after. You can configure both the number of weeks included and whether they are pre-promotion or post-promotion weeks. For example, 14 weeks might be included in the calculation, with eight weeks pre-promotion and six weeks post-promotion. After processing, the calculated baseline metrics are returned to Retail Insights.

These metrics are calculated at the promotion component/item/location/week level. They include baseline units, sales, profit, and transactions.

Baseline metrics can be used by a buyer during category planning, to establish expected sales for a category before promotions are added. This can help identify the level of promotion needed for the category to hit sales targets. A planner might decide that sales goals can be reached without promotions, or by promoting very little, thus saving money and adding to category margins.

Baseline metrics can also be used to calculate lift for promoted products; that is, how much over the baseline did sales increase when this category was promoted? If the difference between baseline and promoted weeks is large, and baseline sales are unacceptably low, it might be concluded that customers are shopping the category only for promoted items. Promotions might need to be cut back or changed, to avoid conditioning customers to buy items only when they are on promotion. If the difference is too small, the promotions might not be effective and not worth the cost to run them.

Trial and Repeat

Retailers want to analyze the impact of new item introductions, and item promotions, to see whether customers come back a second and third time after trying something new. Something new may be a new item introduction, or the first time an item is put on promotion, perhaps as part of raising that item (or Brand's) profile, and so on. Trial and Repeat Metrics can help to analyze the repeat purchase behavior of customer household for the merchandise.

Customer

The Customer Insights module enables you to perform retail analysis around customers and customer segments. The following are some example business questions that Customer metrics can help to answer:

Who are my most profitable customers? Who are my most frequent shoppers?
Are my customers only buying items from me when they are on promotion?
What does a customer buy from me on a typical shopping trip? Does it vary by where they live or how much money they make?
Which of my departments appeal to which of my customers? That is, who is shopping in my stores and what are they shopping for?

Customer data can be reported from the Sales, Sales Promotion, Sales Discount, and Customer Loyalty folders in Retail Insights. Combining the Customer dimension with one or more facts will display data only for known customers (those shoppers having a unique identifier in your POS and CRM systems). Unknown customer transactions are given a default customer ID of -1 and all such data will appear on a single row in reports.

Sales Promotion

Retail Insights has a number of metrics against which to measure a promotional sales, cost and forecast as well as Promotion Campaign costs. These metric provide useful insight into the processes of managing actual marketing cost, evaluating financial performance of marketing tactics, and analyzing forecast and actual spending.

The main folder for promotional activity is the Sales Promotion folder. It contains a set of metrics that must be reported on with the Promotion dimension to analyze performance of a specific promotional event or deal that was given to customers. These promotional events could come from Retail Pricing Cloud Services, Customer Engagement, or an external source such as a marketing platform or online store application.

Transactions are typically identified at the POS as being affected by one or more promotions and deals, which is then processed through Sales Audit and sent to RI with a retail type of 'P' and the ID of the promotion. Additionally, it is possible to mark transactions as belonging to an external promotion, event, or other activity not managed through the pricing or CRM tools at the retailer. These external events must be identified on a reference field in the POSLog data (such as REF_NO3 in the RTLog interface to Sales Audit). RI must then be configured to join that reference field with the External Promotions interface data having the master records for the external event. Once the above integration path is established, transactions having the external event or promotion ID on them will be moved into the Sales Promotion area, and may be reported on just like other Promotion types.

The other Promotion facts in Retail Insights are for customers to populate on-demand with their own data, such as the Promotion Forecast and Promotion Budget facts. These interfaces have no standard Oracle data source, but may be loaded with data from an external application if desired

Note:

Promotion Budget only supports as-is reporting.

Note:

Amount facts are in local and primary currency only.

Cluster

A cluster is a group of stores. Retailers make store clusters for various reasons, but the general idea is that stores in a cluster should have some key element or elements in common, which differentiates them from stores in other clusters. These elements could involve business objectives like store performance benchmarking, inventory management, and assortment/space planning. Then clusters can be used for analysis of sales, inventory, and promotions. Performance, inventory, ranging, trade area analysis, and union analysis are examples of elements around which clusters are built.

Oracle Retail Insights' cluster metrics enable retailers to analyze their clusters' sales, inventory position, inventory receipts and promotions, so that any analysis that might normally be limited to some aspect of the organizational hierarchy can instead be performed on a retailers' customized store cluster, enabling precise, actionable analysis.

The Cluster dimension also supports loading of Price Zones from RPM. These price zones will look and behave the same way as other store clusters from a reporting point of view, as the structure (zone group -> zone -> location) is the same.

Customer Order

Customer orders lie at the heart of the modern retail experience. Virtually every customer transaction that takes place outside of a brick-and-mortar store is captured as part of a customer order, whether it is a normal sale, cancellation, return or exchange. A customer order consists of a customer order header that contains one or more customer order lines. Oracle Retail Insights' customer order metrics allow retailers the flexibility to analyze the performance of their business across the various selling channels their customers use.

Retail Insights supports a number of different metrics related to customer orders to allow performance analyses of omnichannel retailing. A list of the major metrics (minus the time transformations such as LY and LW) is below.

Similarity Score

Similarities calculate how likely a customer is to switch from one item to another in a range from 0 to 1. For example, if the similarity rate for Toothpaste A and Toothpaste B is 0.75 while the similarity rate for Toothpaste A and Toothpaste C is 0.21, the customer is more likely to switch to Toothpaste B than Toothpaste C.

Competitor Pricing

A competitor is a retailer with a product range and customer base similar to those for the organization business unit [Store location in RI] and its channels. The competitor entity holds information about each competitor store and associates it with a location in the organization. Competitor pricing details can be associated with a specific competitor location and mapped to an item in the product hierarchy. This structure provides the means to compare competitor prices for similar or identical items, at a direct competitor location. With this type of timely information, promotion and pricing strategies can be implemented by retailers to prevent potentially costly customer defections.

Purchase On Order

Purchase orders and pre distribution of merchandise that is on the purchase order is instrumental to a retailers inventory movement. Analyzing various aspects of merchandise that is currently on order i.e merchandise that is on an approved purchase order where the entire quantity has not yet been received is important as it can give insight into the quantity, value and status of the merchandise that will be incoming in the near future.

A key metric that the retailers would track is the on order merchandise quantity that has been pre distributed so that the merchandise reaches the stores via an allocation without any delay.

Some of the questions that can be answered as part of the On Order analyses are:

What is the Merchandise that is on order in terms of Units/Retail/Cost by Supplier/Purchase Order/Item/location/day?
What is quantity/Cost/Retail of ordered merchandise that has been received from supplier by Purchase Order/Item/location/day?
What is the quantity/Cost/Retail of the ordered merchandise that is yet to be shipped by the vendor by Item/location/day?

Purchase On Order metrics help identify the on order, total ordered, received, cancelled merchandise quantity and value and the allocated quantity and allocated percentage of the PO on order qty. RI maintains orders with a status of A (active) for as long as they remain open, as well as orders in status C (closed) for a period of 30 days after completing the order. After 30 days have elapsed on a closed order, RI will stop showing that data in reports for the current business date (this is configurable, and historical data for the order is always available by looking at past dates).

The Purchase Order dimension may also be used in combination with Sales facts to analyze orders placed in response to a customer order (such as a drop shipment). This requires that both the sales transaction and the purchase order have a matching Customer Order Number to join the data.

Gift Card Sales

Gift cards are prepaid, stored-value money cards issued by retailers to be used instead of money for purchases. Gift cards are important for retailers because they drive foot traffic and sales, and it would be valuable for them to be able to quantify that effect and any trends up or down that could be an issue. If gift card purchases and redemptions are not up to expectations, retailers may need to take steps like consumer education, or adding mobile platform gift cards.

Oracle Retail Insights gift card metrics provide analysis on gift card amount sold and the trend with respect to last year. Counts of gift cards sold and transactions containing gift card sales are also provided, along with last year metrics for comparison.

Transaction Tender

Transaction tender identifies the tender types that have been used to pay during a given sale or return transaction. Transaction tender data can be utilized to generate gift card redemption analysis. It can also be used to better understand customer purchasing behaviors, such as preferred payment methods, and to analyze the uptake of new payment methods the retailer has introduced at the point of sale.

Oracle Retail Insights transaction tender metrics provide analysis on tender amounts per tender type, gift card redemption amount and the trend with respect to last year, as well as counts of gift cards redeemed and transactions having redemptions. The data is held at the levels of transaction, location, date, and tender type. It is not held by item or transaction-line as there is no indication from the source system to differentiate how much tender is applicable to a specific item or line.

Sales Discount

Sales Discount lists the various discounts that were applied for a given sales transaction. Analysis can be done on the discount amount, discount type and coupon discounts applied. Metrics are also available for the cost and profit associated with a discounted line, and the original pre-discount amount for the sale.

Oracle Retail Insights sales discount metrics can form a basis for analysis of coupon sales penetration that can help retailers understand if the cost of producing and distributing coupons is worthwhile. It is also possible to analyze the revenue and discount amounts created by specific discount types, in order to assess the effectiveness of a discount in generating additional sales relative to the decrease in retail value per transaction. RI further splits discounts by the retail type on the transaction, so it is possible to analyze discounts taken off full price or clearance items separately, or to understand the percentage of discounts taken off regular, promotional, or clearance-priced items.

Store Traffic

Store traffic information is used to understand the distribution of traffic by minute, hour, day of the week, store location, seasonal periods, promotion periods, total chain, etc. Retailers can also look at the conversion ratio of their store which is the total sales transactions divided by total traffic. You will be able to determine if your conversions went up, down or remained the same during the promotion.

Oracle Retail Insights store traffic metrics can be used to analyze the store traffic and conversion rate of stores in comparison to comparable stores. Traffic data is loaded and viewed in 24-hour time format, ranging from 0000 to 2359.

Customer Loyalty Activity

Customer loyalty activity refers to transactions which involve a retailer's loyalty programs, such as loyalty point accrual, redemption, expiration, and award generation. This information can be used to analyze how customers are interacting with your loyalty program and how effective the program's benefits are. For example, if customers are accruing a large number of loyalty points through sales transactions but are not redeeming them, it could indicate that the program's rewards are not enticing enough to encourage participation. It is also useful to know how many loyalty points have been issued but not redeemed, as these represent a potential liability for the retailer in terms of future discounts and coupons that may be used to purchase products.

Retail Insights loyalty activity can be extracted from Customer Engagement, and is held by program, account, customer, location, and day.

Customer Loyalty Award Activity

Loyalty award activity tracks the distribution, redemption, value, and expiration of loyalty awards issued to a customer. Loyalty awards usually come in the form of e-awards or entitlement deals that are distributed to customers who have accumulated a certain number of points as part of a Loyalty Program. The generation and distribution of loyalty awards are done via scheduled jobs in Customer Engagement. The rules determining the award type, award frequency, award amount and the number of points that will be subtracted from the customer's account are defined in the award rules linked to a loyalty program level (rules are not extracted from CE to RI).

Retail Insights loyalty award activity can be extracted from Customer Engagement, and is held by program, account, customer, and day.

Stock Counts

A standard practice in physical retail locations is to perform regular counts of on-hand inventory to ensure that there are no major deviations between the inventory management systems and the actual available inventory. A stock count generally consists of a pre-count snapshot taken in the system of record for all inventory to be counted, followed by one or more physical counts of each unit on-hand. Counts are then reconciled with the inventory management system and adjustments to the on-hand units may be created.

Retail Insights provides two methods for analyzing stock count activities, depending on the systems used by the retailer. The first method will interface directly with RMFCS to extract the stock count snapshot and counted quantities stored by that system after a stock count is performed. The second method provides a generic interface for manually loading non-RMFCS stock count results into RI. These counts would generally be sourced from a perpetual inventory management system such as SIM.

One or both of the provided interfaces may be used to load stock count results for analysis in RI. Separate metrics are available for each interface, allowing the user to compare and contrast multiple instances of a snapshot or count. The stock count snapshots from RMFCS will be loaded automatically through RDE, and will contain all available data on the STAKE_SKU_LOC table, both for snapshots and store counts. The interface for SIM stock count results provides columns both for the SIM pre-count snapshot and the physical counted/approved quantities.

POS Sales

The Retail Insights Cloud Service includes a web service capable of accepting POS sales logs from Oracle XStore using their generic broadcaster service. This service is exposed through the AI Foundation platform's SOAP APIs. If this service is enabled, then the set of metrics on the POS Sales folder become available for use against this data in near real-time. These metrics allow you to see sales and return transactions from your points of sale as they occur throughout the day, along with core measures of performance such as Sales Units and Amounts. This data is held at the transaction, item, location, retail type, day, and time-of-day levels.

Deal Income

Deal income transactions are sourced from a merchandising system, and in the case of RMFCS, are a combination of transaction codes 6 and 7.

Deal income from sales (tran code 6) is recorded to post income generated from bill back rebate or vendor funded promotion types of deals that are calculated based on sales. These are the deals from which the retailer gets income as certain part of sales. The amount of income may differ based on predefined threshold sales levels and percentage thereof. Deal income from sales is posted only to the retail value by default, but in RI all three values (qty, cost, and retail) are exposed if required.

Deal income from purchases (tran code 7) is recorded to post income generated from bill back or bill back rebate deals that are calculated based on purchases. The income from these deals is dependent upon the quantity of purchases made by the retailer from the vendor, and deal income may differ based on predefined threshold levels of purchases. Deal income from purchases is posted only to the cost value by default, but in RI all three values (qty, cost, and retail) are exposed if required.

The deal income fact in RI is held at the standard data levels of item, location, and date. You may also use the Deal dimension to report on deal income by individual deal. The Deal Income fact only goes down to the deal header level of detail (equivalent to DEAL_HEAD table in Merchandising Cloud), it does not support the individual components of a deal as Merchandising doesn’t track deal income against the components, only the deal header record.

Deal Actuals

In addition to deal income transactions, RI has a separate fact for deal actuals, which are the aggregated financial values associated with each specific deal. Deal actuals are integrated with Merchandising Foundation CS (MFCS) primarily using the source table DEAL_ACTUALS_ITEM_LOC. Deal actuals cover two key performance metrics: turnover and income. Turnover refers to the total revenue generated for a period across the merchandise hierarchy for which the deal is applicable. Income refers to the actual amount of revenue generated by the deal itself based on the deal income calculation methods used in the source system where deals are managed.

The deal actuals fact in RI is held at the standard data levels of item, location, and date. You may also use the Deal dimension to report on deal actuals by individual deal and deal component. Which data columns are populated between turnover and income for a given deal will depend on the type of deal, calculation methods, and other parameters setup in the source system.

Intercompany Margin

The intercompany margin fact refers to the intercompany margin calculations done in a merchandising system. Intercompany transfers are treated like sales between two legal entities and can result in a price that is different from the retail price of the item. Margin calculations can be performed to determine the gain or loss from the intercompany transactions. This fact can be loaded with such data for the standard quantity, cost, and retail measures populated for most transactional activities. The fact is held at the standard data levels of item, location, and date.

Inventory Reclass

In the merchandising system, reclassification transactions are written when an item is moved, or reclassified, from one department/class/subclass to another, to record the movement of inventory 'in' to the new subclass. Transaction data records are written for each location in which the item being reclassified exists in the system based on owned inventory at the location. For every Reclassification In transaction for the location, a Reclassification Out transaction also exists and the two transaction data records balance one another.

In RI, the reclassification fact simply captures the reclassification in/out transaction pairs and exposes the quantity, cost, and retail values associated with them. The fact is held at the standard data levels of item, location, and date.

Price Optimization Results

The results of the Lifecycle Pricing Optimization (LPO) application are exposed in Retail Insights as fact measures in several folders, such as Price Optimization Run Metrics and Price Optimization Recommendations. Every LPO run produces a large number of metrics and attributes at the same level as the recommendations (e.g. style/color-zone-week). These are critical to the end users to have in reports to analyze the quality of the results and any actions they might need to take within LPO after the runs are complete.

The Price Optimization Run Metrics fact contains a series of calculated values and input measures that are specific to one optimization run, such as the recent sales and inventory totals used in the run and the optimized margin and other derived outputs. It also contains all of the custom measures imported from outside of LPO that are to be used for reporting on the run results, pre-aggregated to the level of the recommendations. The run metrics are limited to results from the current processing week only in order to improve performance, and because the current week’s runs are typically the only ones needed for analysis. The dimensions supported on the run metrics are the Optimization Run, Optimization Products, Business Calendar, and Clusters (which has price zone data).

The Price Optimization Recommendations fact shows the actual price recommendations resulting from the optimization run, as well as any price overrides applied by users to the runs. The recommendations include the forecasted and optimal sales and inventory measures depending on your optimization objectives. The recommendations are limited to the current processing week, just like the run metrics. The recommendations have the same level of dimensionality as the run metrics.

The Price Optimization Actuals fact shows the aggregated historical inputs such as the sales and prices sent to the LPO models. This is specifically sourced from the PMO_ACTIVITIES table in the database for any aggregations used in the current processing week. The fact will display data for the current week plus the 4 weeks prior, as this is the data that will most directly influence the LPO results. The dimensions supported on the run metrics are the Optimization Products, Business Calendar, and Clusters (which has price zone data). If used with the Optimization Run dimension, the measures will show as total values, since they are not split by run.

The Price Optimization Inventory fact shows the aggregated historical inputs from the PRO_INVENTORY table which is also used as input to the LPO models. The fact will display data for the current processing week only. The dimensions supported on the run metrics are the Optimization Products and Clusters (which has price zone data). If used with the Optimization Run or Business Calendar dimensions, the measures will show as total values, since they are not split by run and only cover one week of data.

The Price Optimization Price Cost fact shows the aggregated historical inputs from the PRO_PRICE_COST table which is also used as input to the LPO models. The fact will display data for the current processing week only. The dimensions supported on the run metrics are the Optimization Products and Clusters (which has price zone data). If used with the Optimization Run or Business Calendar dimensions, the measures will show as total values, since they are not split by run and only cover one week of data.

The Price Optimization Run Alerts fact shows the results of the internal sanity checker which looks for data that violates any rules defined in LPO such as missing price or inventory values for an item. This fact may only be used with the Optimization Run dimension as the sanity check results are stored by run. All other values needed to analyze the errors are included within the fact itself and does not require joins to other areas.

The Price Optimization Run Exports dimension is a standalone dimension for checking integration errors from LPO to Pricing Cloud Services. These errors are returned by the Pricing API and cover a wide range of data issues such as clearance conflicts, missing items or locations, invalid price zones for the markdown, failed connections, and so on. This data does not join with any other folder in RI.

Flexible Facts

Retail Insights provides four flexible fact interfaces which can be configured to load data from an external source at any level of the product, location, supplier, and calendar hierarchies. These configurations should be set in the C_ODI_PARAM table during implementation. The full list of supported levels and configurations will be comparable to the Planning fact interfaces as the architecture is the same.

The flexible fact metrics in RI are intended to provide the retailer with a way to load data from outside of Oracle and combine that data with all other areas in RI. How the data will be reported on, and which dimensions can be used, will depend on the levels configured during implementation. For example, if the external data is provided at a level of Location and Fiscal Date, then only the Organization and Fiscal Calendar dimensions in RI can be used with the flexible fact metrics. For this reason, it is recommended to provide the data at the lowest possible levels of each available dimension, to allow RI the widest range of attributes and aggregations to use in reporting. Fact data provided at a lower level (such as Store) will support any aggregation defined above it (such as Channel or Region).

The metrics for these facts are provided with generic labels that describe the datatype of the source column. Each of the four facts have an identical set of metrics, differing only in the base numerical value of 1 through 4, to designate which flexible fact table is being used. It is expected that the retailer will relabel these metrics during implementation to something more meaningful to RI users. For example, the default label for the first metric is External Flex 1 Metric 1 Number. A total of 100 metrics are provided across several data types for each of the facts:

40 numerical non-currency fields
20 numerical currency-enabled fields
30 character-based freeform fields
5 percent fields (using a non-scaled range like 0-1)
5 date fields

Retail Insights Metric Metadata

The following chart shows Retail Insights metric metadata. Users should be aware that you cannot mix facts across as-is, as-was, and point-in-time subject areas.

Note:

Performance of reports that contain YTD metrics may become less optimal as the end of the fiscal year approaches, due to the increasing amount of data that accumulates. Users should be aware of this and take steps to mitigate any performance effects, such as being specific with filters and prompts to get back the smallest amount of data necessary for analysis.

Table 6-4 Metric Metadata

Metrics	As-Is	As-Was
Cost and Profit	X	X
Markdowns and Markups	X	X
Sales Forecast	X	X
Inventory Receipts	X	X
Sales	X	X
Sales Discount	X	X
Sales Consignment	X	X
Sales Extensions	X	X
Transaction Tender	X	X
Gift Card Sales	X	X
Store Traffic	X	X
Competitor Pricing	X	X
Sales Pack	X	X
Supplier Invoice	X	X
Supplier Performance and Compliance	X	X
Inventory Position	X	X
Wholesale	X	X
Franchise	X	X
Price	X	X
Planning	X	X
Stock Ledger		X
Trial and Repeat	X	X
Sales Promotion	X	X
Customer Order	X	X
Customer Order Promotion Transaction	X	X
Customer Order Status Fact	X	X
Customer Order Transaction	X	X
Touch Point	X	X
Retail Promotion Actuals	X	X
Retail Promotion Forecast	X	X
Promotion Baseline	X	X
Promotion Budget	X	X
Consumer Spend	X	X
Sales Promotion	X	X
Inventory Position	X	X
Return to Vendor	X	X
Inventory Adjustment	X	X
Inventory Transfers	X	X
Similarity Score	X	X
Purchase On Order	X	X
Customer Loyalty Activity		X
Customer Loyalty Award Activity		X
Stock Counts	X	X
POS Sales	X	X
Inventory Reclass	X	X
Deal Income	X	X
Deal Actuals	X	X
Intercompany Margin	X	X