Retail Chain Data Model

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

Relationships

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

Relationships

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

Relationships

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

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

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

Relationships

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

Relationships

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

Relationships

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

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

Relationships