---
title: "Retail Chain"
canonical: "https://www.owox.com/data-models/retail-chain"
updated: "2026-09-26"
---

# Retail Chain Data Model

10 data marts128 fields[Vlad Flaks](https://github.com/vladflaks)[Rus Obolonsky](https://github.com/Obolrus)

Shoppers move from footfall through priced and discounted sales lines to nightly stock counts, replenishment and shrink, with loyalty accounts carrying recency, frequency and spend for identified customers.

## Overview

A United States general-merchandise-and-grocery chain in the supercenter format, modelled from the moment a shopper walks through a door to the moment goods come back over the service desk. People arrive at a store and some fraction of them buy; every line they buy is priced, discounted and costed, so margin is available at the till rather than reconstructed at month end; the stock behind those lines is counted every night, ordered from suppliers on a lead time, and written off when it is stolen, damaged or out of date. Loyalty accounts sit across the whole of it, carrying recency, frequency, spend and a home store, so identified baskets can be followed over time while anonymous ones still count towards the trade. Because a sale line carries its store, its SKU and its day, footfall and the day's closing stock position are one join away from any sales figure — which is what makes a weak week diagnosable as fewer visitors, worse conversion, a smaller basket or an empty shelf, rather than merely visible as a smaller number.

**Scope:** this model covers store trade end to end — traffic, sales and margin, promotions, loyalty, stock, replenishment, shrink and returns. Its boundaries are worth stating plainly. There is no store labour or staffing data, so nothing here answers sales per labour hour, schedule efficiency or the cost of running a shift. Suppliers appear as a name on a product and on a replenishment order rather than as an entity, so supplier scorecards go only as far as that name carries them. There is no distribution centre — orders run from a store to a supplier, and the warehouse leg between them is not modelled — and no price or markdown history beyond the promotions themselves, so elasticity questions that need a full price ladder cannot be answered. There is also no online channel: this is a bricks-and-mortar chain, and its traffic is people walking through a door, not sessions on a site.

## Example Questions

*   When a `store`'s sales fall, which lever moved — did fewer people come in, did fewer of them buy, did they spend less per basket, or did the lines they came for finish the day out of stock?
*   Which `promotions` earned their discount and which merely bought volume we already had, judged on line-level margin rather than revenue, and does the answer change by funding source?
*   What does the reverse flow really cost — refunds plus the value destroyed by disposition, plus the shrink that never reaches a till at all — and where do the two concentrate by `store`, by category and by the way each loss was detected?

[Explore on canvas →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/retail-chain)

## Inventory (daily)

What was on the shelf, store by store and line by line, at the close of every day: units on hand and what they are worth, units still on order, the reorder point the line is managed against, whether it ended the day with nothing left, and how many weeks the remaining stock would last at current demand. Availability is the constraint on everything a store can sell — an empty shelf produces no sale, no margin and no loyalty, and the sale it loses rarely comes back later — so this is the mart that explains sales the sales figures themselves cannot. It is also where working capital sits: stock is cash on a shelf, and the same daily position that reveals a stockout reveals overstock in the lines nobody is buying. Reading the two together is the whole of inventory management — too little loses sales, too much ties up money and, in fresh food, is thrown away.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `snapshot_id` | STRING | Snapshot ID | PK. Unique identifier for this store, SKU and day position. |
| `store_id` | STRING | Store ID | Location the stock is held at. FK to [Store](#mart-store) |
| `product_id` | STRING | Product ID | Line the position is recorded for. FK to [Product](#mart-product) |
| `snapshot_date` | DATE | Snapshot Date | Day the position was taken at close of trade. With `store_id` and `product_id` this is the grain of the mart. |
| `on_hand_units` | INTEGER | On-Hand Units | Selling units left on this line at the close of the day. |
| `on_hand_value` | NUMERIC | On-Hand Value | Value of the units on hand at unit cost, in USD. The working capital standing on the shelf. |
| `on_order_units` | INTEGER | On-Order Units | Units already ordered and not yet received. A line can be empty and still covered if a delivery is inbound. |
| `reorder_point` | INTEGER | Reorder Point | Demand the line must serve before its next delivery lands. On-hand below it means the line is depending on that delivery arriving on time — normal and frequent on lines held to a few days of cover, near-absent on lines held to weeks of it, so compare the share of days below it across lines stocked the same way rather than reading any single figure as a fault. |
| `is_stockout` | BOOLEAN | Is Stockout | True when the line had no stock left at the close of the day. A line can still have sold during a day that ends in stockout — the snapshot is taken at day end, not across it. |
| `weeks_of_supply` | FLOAT | Weeks of Supply | How many weeks the stock on hand would last at current demand. Low means lost sales are close; high means cash tied up, and on perishables, write-offs ahead. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Product](#mart-product) | `product_id = product_id` | N:1 | The SKU counted in this snapshot. |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store this stock snapshot is for. |

## Loyalty Members

Everyone enrolled in the loyalty program, and what each of them is worth to the chain: the store they treat as home, the tier they have reached, when they joined and how they have shopped since — lifetime spend, how many baskets it took, the average basket behind it, and how long it has been since the last one. This is the only place a shopper exists as a person rather than as an anonymous basket, which makes it the entry point for every question about retention, frequency and share of wallet. The RFM block turns that history into segments that can be acted on: who is shopping most, who is spending most, and who has quietly stopped coming. `home_store_id` matters more here than it looks — a member's value is earned at a location, so store performance and member value are two views of the same trade, and a store losing gold members is in trouble long before its sales line shows it.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `member_id` | STRING | Member ID | PK. Unique identifier for this loyalty account. |
| `home_store_id` | STRING | Home Store ID | Location the member shops most often, which is where their value is earned. FK to [Store](#mart-store) |
| `enrolled_at` | DATE | Enrolled Date | Date the member joined the loyalty program. The gap to `first_purchase_date` shows how long enrolment takes to turn into trade. |
| `tier` | STRING | Loyalty Tier | Program tier earned on spend: `base`, `silver` or `gold`. Tier is partly a result of basket size, so treat it as a segment rather than a cause. |
| `city` | STRING | City | City the member lives in, which need not be the city of their home store. |
| `state` | STRING | State | US state the member lives in, e.g. `TX`, `OH`, `FL`. |
| `age_band` | STRING | Age Band | Banded age group the member falls into. Banding keeps the cut usable without holding a date of birth. |
| `email_opt_in` | BOOLEAN | Email Opt-In | True when the member has agreed to receive email offers. The reachable base for any mailed campaign. |
| `is_app_user` | BOOLEAN | Is App User | True when the member uses the mobile app, which is what makes an offer targetable in the moment rather than a week ahead. |
| `rfm_label` | STRING | RFM Segment | Segment summarising the three scores below into one label, e.g. champions, loyal, at risk, lapsed. The everyday cut for campaign selection. |
| `recency_score` | INTEGER | Recency Score | 1 to 5, where 5 is a member who shopped most recently. |
| `frequency_score` | INTEGER | Frequency Score | 1 to 5, where 5 is a member who shops most often. |
| `monetary_score` | INTEGER | Monetary Score | 1 to 5, where 5 is a member who has spent the most. |
| `lifetime_spend` | NUMERIC | Lifetime Spend | Total spend by this member since enrolment, in USD. |
| `lifetime_baskets` | INTEGER | Lifetime Baskets | Number of separate shopping trips the member has made. Divides into `lifetime_spend` to give `avg_basket_value`. |
| `avg_basket_value` | NUMERIC | Average Basket Value | Average spend per shopping trip for this member, in USD. |
| `first_purchase_date` | DATE | First Purchase Date | Date of the member's first purchase on the program. |
| `last_purchase_date` | DATE | Last Purchase Date | Date of the member's most recent purchase. |
| `days_since_last_purchase` | INTEGER | Days Since Last Purchase | Whole days between the last purchase and the reporting date. The lapse trigger behind any recall campaign. |
| `is_active` | BOOLEAN | Is Active | True while the member is still shopping within the program's activity window. Exclude the dormant tail before comparing per-member averages. |
| `count_members` | INTEGER | Member Count | Always `1` on every row; SUM to count members. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Store](#mart-store) | `home_store_id = store_id` | N:1 | The member's home store. |

## POS Sales

Every line rung through a till across the chain: what was sold, where, when, at what price, at what discount and at what margin. This is the mart the rest of the model exists to explain — one row per receipt line, with the basket it belonged to, the promotion that priced it, the loyalty account behind it when the shopper was identified, and the way it was paid for and scanned. Price is carried alongside cost so margin is available at line level rather than reconstructed afterwards, which is what lets a discount be judged on the profit it left rather than the volume it moved. Because a sale line also carries its store, its SKU and its calendar day, it reaches straight into the two day-grained marts around it: the footfall the store saw that day and the stock position that line finished the day on. That is what turns a sales figure into a diagnosis — whether a weak day was fewer visitors, worse conversion, a smaller basket, or a shelf that had nothing left on it.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `sale_id` | STRING | Sale ID | PK. Unique identifier for this receipt line. |
| `store_id` | STRING | Store ID | Location the line was sold at. FK to [Store Traffic](#mart-store-traffic) |
| `product_id` | STRING | Product ID | SKU that was sold. FK to [Product](#mart-product) |
| `promotion_id` | STRING | Promotion ID | NULL when the line sold at regular price, so promoted and full-price trade separate on this column alone. FK to [Promotion](#mart-promotion) |
| `member_id` | STRING | Member ID | NULL when the basket was not identified, which is how an anonymous shopper appears. FK to [Loyalty Members](#mart-loyalty-members) |
| `basket_id` | STRING | Basket ID | Identifier of the basket this line was paid for in. Groups the lines of one transaction, so basket size and mix are a roll-up of this mart. |
| `sold_at` | TIMESTAMP | Sold At | Exact moment the line was rung through the till. Use this for hour-of-day and daypart questions. |
| `sale_date` | DATE | Sale Date | Calendar date of the sale. Carried alongside `sold_at` because day-grained marts — store traffic and the daily stock position — join on a date, not a timestamp. FK to [Store Traffic](#mart-store-traffic) |
| `quantity` | INTEGER | Quantity | Selling units sold on this line. |
| `unit_price` | NUMERIC | Unit Price | Shelf price per unit before any discount, in USD. |
| `discount` | NUMERIC | Discount | Value taken off the line by the promotion it was on, in USD. Zero on a line sold at regular price — there is no markdown history in this model, so `promotion_id IS NULL` and a zero discount mean the same thing. |
| `net_sales` | NUMERIC | Net Sales | What the line actually took after discount, in USD. The revenue figure to sum. |
| `line_cost` | NUMERIC | Line Cost | What the units on this line cost the chain, in USD. |
| `gross_margin` | NUMERIC | Gross Margin | `net_sales` less `line_cost`, in USD. The only one of the money columns that says whether the line was worth selling — read promotions and categories on this, with revenue beside it. |
| `checkout_type` | STRING | Checkout Type | Where the line was scanned: `staffed` or `self_checkout`. Summed by store it gives each site's self-checkout share of trade, which is the figure to set beside that store's `Shrinkage` events with `detected_by = 'self_checkout_audit'` — the two marts are read side by side at store level rather than joined. |
| `payment_method` | STRING | Payment Method | How the basket was settled: `card`, `cash`, `mobile_wallet`, `ebt` or `gift_card`. `ebt` marks a benefits-funded basket, which shops a distinctly different assortment. |
| `count_sale_lines` | INTEGER | Sale Line Count | Always `1` on every row; SUM to count receipt lines. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Inventory (daily)](#mart-inventory-daily) | `store_id = store_id, product_id = product_id, sale_date = snapshot_date` | N:1 | Stock of this SKU at this store on the day of sale. |
| [Loyalty Members](#mart-loyalty-members) | `member_id = member_id` | N:1 | The member who bought, where a card was scanned. |
| [Product](#mart-product) | `product_id = product_id` | N:1 | The SKU sold on this line. |
| [Promotion](#mart-promotion) | `promotion_id = promotion_id` | N:1 | The promotion applied to this line. |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store that rang up this line. |
| [Store Traffic](#mart-store-traffic) | `store_id = store_id, sale_date = traffic_date` | N:1 | Footfall at this store on the day of sale. |

## Product

The assortment, one row per SKU: what the line is, who makes it, who supplies it, what it costs the chain and what it lists at. Two fields do most of the analytical work here. `velocity_band` is the A/B/C classification that decides replenishment priority and shelf position — an A line sells every day and a stockout on it costs real money, a C line may sit for weeks and is mostly a question of whether it earns its space. `is_perishable` separates the lines with a shelf life from the rest, and perishability drives both how often a line has to be reordered and how much of it is written off before it ever sells. Alongside those, `unit_cost` against `list_price` gives the intended margin on every line before any promotion touches it, and `is_private_label` separates the chain's own brands — typically higher margin, and the lever a grocer pulls when shoppers trade down.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `product_id` | STRING | Product ID | PK. Unique identifier for this SKU. |
| `sku` | STRING | SKU | Stock-keeping unit code as it appears on the shelf edge and in ordering. |
| `name` | STRING | Product Name | Description of the line as it reads on the shelf and the receipt. |
| `category` | STRING | Category | Top level of the merchandising hierarchy, e.g. `Grocery`, `Fresh Food`, `Apparel`, `Home`. |
| `subcategory` | STRING | Subcategory | Second level of the merchandising hierarchy within the category. |
| `brand` | STRING | Brand | Brand the line is sold under. Compare against `is_private_label` to separate own brands from national ones. |
| `supplier_name` | STRING | Supplier | Vendor the chain buys this line from. Matches the supplier on replenishment orders, so supplier service level is answerable from these two together. |
| `unit_cost` | NUMERIC | Unit Cost | Cost to the chain of one selling unit. The basis of margin on every sale line. |
| `list_price` | NUMERIC | List Price | Shelf price of one selling unit before any promotion. The gap to `unit_cost` is the intended margin. |
| `pack_size` | STRING | Pack Size | How the line is packaged for sale, e.g. `12-count`, `2 lb`, `single`. Normalise on this before comparing prices across brands. |
| `unit_of_measure` | STRING | Unit of Measure | Unit the line is sold in: `each`, `lb`, `oz`, `pack`, `case`. |
| `velocity_band` | STRING | Velocity Band | ABC velocity class: `A` for the fastest sellers, `C` for the slowest. A stockout on an A line costs far more than one on a C line. |
| `is_private_label` | BOOLEAN | Is Private Label | True for the chain's own brands, which carry higher margin and gain share when shoppers trade down. |
| `is_perishable` | BOOLEAN | Is Perishable | True for lines with a shelf life. Perishability drives both replenishment cadence and expiry write-offs. |

## Promotion

Every offer the chain ran, and the four things that decide whether it was worth running: the mechanic it used, the channel it reached shoppers through, the categories it covered, and — the one most promotional reporting leaves out — who actually paid for the discount. A vendor-funded deal costs the chain nothing but shelf space and can be judged on volume alone; a retailer-funded one comes straight out of margin and has to earn it back in incremental units, not units that would have sold at full price anyway. `start_date` and `end_date` bound the window a promotion can be credited for, which is what any pre-period, promo-period and post-period read is built on, and `category_scope` is what lets an offer be set against the categories it was supposed to move rather than against total sales.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `promotion_id` | STRING | Promotion ID | PK. Unique identifier for this promotion. |
| `name` | STRING | Promotion Name | Name of the offer as it appears in the promotional calendar. |
| `promo_type` | STRING | Promotion Type | Mechanic used: `discount`, `bogo` (buy one get one), `loyalty_points`, `bundle`. Mechanics are not comparable on discount depth alone. |
| `promo_channel` | STRING | Promotion Channel | How the offer reached shoppers: `weekly_circular`, `app_offer`, `in_store_display`, `loyalty_targeted`. Separates broad reach from targeted precision. |
| `funding_source` | STRING | Funding Source | Who paid for the discount: `vendor_funded` when the supplier covered it, `retailer_funded` when the chain absorbed it. This is what decides whether a promotion made money. |
| `category_scope` | STRING | Category Scope | Part of the assortment the offer covered. Measure uplift against these categories, not against total store sales. |
| `start_date` | DATE | Start Date | First day the offer was live. |
| `end_date` | DATE | End Date | Last day the offer was live. Sales outside this window were not on this deal. |
| `discount_pct` | FLOAT | Discount % | Headline depth of the offer as a percentage off the shelf price. |

## Replenishment

Every order placed to refill a store's shelves, from the day it was raised to the day the goods arrived — how much was asked for, how much actually turned up, what it cost, how long it took, and whether it landed when it was promised. This is where an availability problem is diagnosed rather than merely observed: a shelf can be empty because the store never ordered, because the supplier short-shipped, or because the delivery was late, and only the order record separates the three. Fill rate and on-time delivery are the two halves of on-time-in-full, the standard measure of supplier service, and `supplier_name` is what makes them answerable per vendor. Lead time is the other half of the story, because a supplier who reliably takes ten days can be planned around, while one who takes anywhere between three and fifteen forces every store to carry cover stock it should not need.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `order_id` | STRING | Order ID | PK. Unique identifier for this replenishment order. |
| `store_id` | STRING | Store ID | Location the goods were ordered for. FK to [Store](#mart-store) |
| `product_id` | STRING | Product ID | Line being replenished. FK to [Product](#mart-product) |
| `supplier_name` | STRING | Supplier | Vendor the order was placed with. Matches the supplier on the product, so supplier service level is answerable from the two together. |
| `ordered_at` | DATE | Ordered Date | Date the order was raised with the supplier. |
| `expected_at` | DATE | Expected Date | Date the supplier promised delivery. The bar `is_on_time` is measured against. |
| `received_at` | DATE | Received Date | Date the goods actually arrived. NULL while the order is still open, so exclude open orders from lead-time and service-level calculations rather than treating them as received today. |
| `status` | STRING | Order Status | Where the order stands: `open` (raised, goods not yet received — including orders already overdue, whose `expected_at` has passed with nothing delivered), `partial` (short-shipped), `received` (complete) or `cancelled` (it will never arrive). |
| `quantity_ordered` | INTEGER | Quantity Ordered | Selling units requested from the supplier. |
| `quantity_received` | INTEGER | Quantity Received | Selling units actually delivered. Below `quantity_ordered` on a short shipment. |
| `order_cost` | NUMERIC | Order Cost | Value of the order at unit cost, in USD. |
| `lead_time_days` | INTEGER | Lead Time (Days) | Whole days from order to receipt. Its variance matters as much as its level — unpredictable lead time has to be covered with stock. |
| `fill_rate_pct` | FLOAT | Fill Rate % | Quantity received divided by quantity ordered, as a **percentage on a 0–100 scale** — `96.5` means 96.5% of the order arrived, not 9650%. The "in full" half of on-time-in-full, and the standard measure of how completely a supplier serves an order. |
| `is_on_time` | BOOLEAN | Is On Time | True when the goods arrived by `expected_at`. The "on time" half of on-time-in-full; read it alongside `fill_rate_pct`, since a punctual short shipment is still a failure. |
| `count_orders` | INTEGER | Order Count | Always `1` on every row; SUM to count replenishment orders. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Product](#mart-product) | `product_id = product_id` | N:1 | The SKU being replenished. |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store the order was raised for. |

## Returns

What came back, and what happened to it: one row per returned line, with the sale it came from when there was a receipt, the reason the shopper gave, and what the chain did with the goods afterwards. Across a general-merchandise assortment returns are a material flow rather than a rounding error, and they cost twice over — the refund handed back and the value destroyed when the returned unit cannot go straight back on the shelf. That second cost is what `disposition` measures: a line resold at full price loses almost nothing, one marked down loses part of its margin, one sent back to the vendor recovers cost from someone else, and one disposed of loses everything. The reason a return was made says where the problem actually sits — a defect belongs to the supplier, a wrong item to the shelf edge or the pick, and a change of mind to nobody but the shopper. Elapsed time and whether a receipt was produced complete the picture, because the returns that arrive late and unreceipted behave differently from the rest in both cost and risk.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `return_id` | STRING | Return ID | PK. Unique identifier for this returned line. |
| `sale_id` | STRING | Sale ID | NULL when the goods came back without a receipt, which is why store and product are also carried directly on this mart. FK to [POS Sales](#mart-pos-sales) |
| `store_id` | STRING | Store ID | Location that accepted the return, which need not be the store that sold the item. FK to [Store](#mart-store) |
| `product_id` | STRING | Product ID | Line that was returned. FK to [Product](#mart-product) |
| `member_id` | STRING | Member ID | NULL when the return was not tied to an identified shopper. FK to [Loyalty Members](#mart-loyalty-members) |
| `returned_at` | TIMESTAMP | Returned At | Exact moment the return was processed at the service desk. |
| `return_date` | DATE | Return Date | Calendar date the return was processed. |
| `quantity_returned` | INTEGER | Units Returned | Selling units handed back on this line. |
| `refund_amount` | NUMERIC | Refund Amount | Money refunded to the shopper for this line, in USD. Only the direct cost — the value destroyed by the disposition sits alongside it. |
| `reason` | STRING | Return Reason | Why the goods came back: `damaged`, `defective`, `wrong_item`, `changed_mind`, `expired` or `price_dispute`. Separates faults the chain or its suppliers can fix from the cost of a generous returns policy. |
| `disposition` | STRING | Disposition | What became of the unit: `resell` (back on the shelf at full price), `markdown` (sellable at a reduced price), `vendor_return` (cost recovered from the supplier), `disposal` (written off entirely). This is where the larger cost of a return sits. |
| `is_receipted` | BOOLEAN | Is Receipted | True when a receipt was produced, so the return resolves to an original sale line. False rows have no `sale_id` and are the harder population to control. |
| `days_since_purchase` | INTEGER | Days Since Purchase | Days between the original sale and the return. Meaningful only on receipted rows, where the original sale date is known. |
| `count_returns` | INTEGER | Return Count | Always `1` on every row; SUM to count returned lines. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Loyalty Members](#mart-loyalty-members) | `member_id = member_id` | N:1 | The member who returned it. |
| [POS Sales](#mart-pos-sales) | `sale_id = sale_id` | N:1 | The receipt line being returned. |
| [Product](#mart-product) | `product_id = product_id` | N:1 | The SKU returned. |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store that took the return. |

## Shrinkage

Stock the chain paid for and never sold: one row per write-off event, recording what was lost, where, how much of it, why, and how it came to light. Shrink is one of the largest controllable costs in retail and it comes off the bottom line directly — a dollar of shrink has to be replaced by several dollars of extra sales to break even on it. The reason separates problems that need entirely different responses: theft is a security and layout question, expiry is an ordering and rotation question, damage is a handling question, and administrative error means the loss may not be a physical loss at all but a bookkeeping one. How the loss was _found_ matters just as much, because it divides known shrink, where the cause is documented at the moment it happens, from unknown shrink, which only surfaces when a count fails to match the book and by then the cause is gone. Each loss is valued twice — at what it cost the chain and at what it would have sold for — because those two numbers answer different questions.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `shrink_id` | STRING | Shrink ID | PK. Unique identifier for this write-off event. |
| `store_id` | STRING | Store ID | Location the loss was recorded at. FK to [Store](#mart-store) |
| `product_id` | STRING | Product ID | Line that was written off. FK to [Product](#mart-product) |
| `recorded_at` | DATE | Recorded Date | Date the write-off was booked. For a loss found by cycle count this is when it was discovered, not necessarily when it occurred. |
| `reason` | STRING | Shrink Reason | Why the stock was lost: `theft`, `damage`, `expiry` or `admin_error`. Each points at a different owner — security, handling, ordering and rotation, or the back office. |
| `detected_by` | STRING | Detected By | How the loss came to light: `cycle_count` (a stock count found the book and the shelf disagreed, so the cause is inferred — unknown shrink), `self_checkout_audit` (an audit of an unattended checkout), `security` (loss prevention caught it in the act), `receiving_check` (a discrepancy found at the door before the stock reached the floor). |
| `units_lost` | INTEGER | Units Lost | Selling units written off in this event. |
| `shrink_cost` | NUMERIC | Shrink Cost | The loss valued at unit cost, in USD — the money the chain is out of pocket. Use this basis for any margin or profit question. |
| `retail_value` | NUMERIC | Retail Value | The same loss valued at shelf price, in USD — the trade that will never be rung through the till. Use this basis when quoting shrink as a percentage of sales. |
| `count_shrink_events` | INTEGER | Shrink Event Count | Always `1` on every row; SUM to count write-off events. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Product](#mart-product) | `product_id = product_id` | N:1 | The SKU written off. |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store that wrote the stock off. |

## Store

Every location the chain trades from, and the handful of facts that decide what to expect of it: which format it trades as, how much selling space it has, the region and state it reports into, and when it opened. Format is the first thing to hold constant in any store comparison — a supercenter, a supermarket and a neighborhood market carry different assortments, draw different footfall and turn their space at different rates, so ranking them against one another says more about the format than about the store. Selling area is the denominator behind sales per square foot, the standard measure of retail productivity, and the opening date is what makes a like-for-like read possible at all, since a store needs a full year behind it before this year can be set against last. This is the dimension every sales, stock, shrink and footfall number in the model rolls up through.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `store_id` | STRING | Store ID | PK. Unique identifier for this location. |
| `name` | STRING | Store Name | Store name as it appears in reporting and on the fascia. |
| `region` | STRING | Region | Reporting region the store belongs to — the level the estate is managed at. |
| `state` | STRING | State | US state the store trades in, e.g. `TX`, `OH`, `FL`. The standard external comparison cut. |
| `city` | STRING | City | City the store is located in. |
| `format` | STRING | Store Format | Trading format: `supercenter`, `supermarket` or `neighborhood_market`. Hold this constant when comparing stores — the three formats trade nothing alike. |
| `opened_at` | DATE | Opened Date | Date the store opened. Like-for-like comparison needs at least thirteen months of history behind a store. |
| `selling_area_sqft` | INTEGER | Selling Area (sq ft) | Trading floor area in square feet, excluding back-of-house. The denominator for sales per square foot. |
| `is_active` | BOOLEAN | Is Active | True while the location is still trading. Exclude closed sites before comparing per-store averages. |

## Store Traffic

How many people walked through each door each day, how many of them bought something, and what they spent when they did. Footfall is the denominator retail is missing whenever it looks only at sales: a store whose revenue fell may have been busier than ever and converted worse, or quieter and converted the same, and those two are completely different problems with completely different fixes. One row per store and day, with the day itself described — weekend and holiday flags are carried because retail demand is driven by the calendar more than by anything a store does, and a Tuesday is not comparable to a Saturday. Conversion and average basket sit alongside footfall so the three levers of a store day — traffic, conversion, basket — can be separated instead of collapsing into one revenue number.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `traffic_id` | STRING | Traffic ID | PK. Unique identifier for this store-day. |
| `store_id` | STRING | Store ID | Location the footfall was counted at. FK to [Store](#mart-store) |
| `traffic_date` | DATE | Traffic Date | Calendar day the count covers. Together with `store_id` this is the grain of the mart. |
| `is_weekend` | BOOLEAN | Is Weekend | True for Saturday and Sunday. Weekend footfall runs far above weekday, so match days of week before comparing periods. |
| `is_holiday` | BOOLEAN | Is Holiday | True on a public holiday. Holidays shift both footfall and basket size and should be isolated, not averaged in. |
| `footfall` | INTEGER | Footfall | People who entered the store that day, counted at the door. The denominator behind conversion and sales per visitor. |
| `transactions` | INTEGER | Transactions | Baskets paid for that day. Reconciles with the receipt lines recorded for the same store and day. |
| `conversion_pct` | FLOAT | Conversion % | Transactions divided by footfall, as a **percentage on a 0–100 scale** — `87.4` means 87.4%, not 8740%. In food and general-merchandise retail this runs high — most people who walk in buy something — so read it against a grocery benchmark rather than a fashion one. |
| `avg_basket_value` | NUMERIC | Average Basket Value | Average spend per basket that day, in USD. The third lever on a store day, alongside footfall and conversion. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Store](#mart-store) | `store_id = store_id` | N:1 | The store this day of footfall belongs to. |

## Apply to your project

1.  1

    ### Install the Import Model plugin

    One plugin, installed once, in your own OWOX workspace.

    [Get the plugin →](https://github.com/OWOX/import-model)

2.  2

    ### Import this model

    Point it at this bundle and it creates every data mart above, joins and all.

    [Open the model →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/retail-chain)

3.  3

    ### Plug in your data and destinations

    Connect your own sources and send the results where your team already works.

    [Browse connectors →](/connectors)

**4\. Optional — customize as you wish.** Rename a column, drop a mart, add your own: once it is imported it is yours, and nothing here syncs back.

## References

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Browse connectors →](https://www.owox.com/connectors) — /connectors.md