The company had many items with plenty of stock overall but scattered one unit per store. The goal was to consolidate each item into the minimum number of stores while respecting how well it sold in each of them. The process was manual and split between four analysts, who reviewed items one by one, evaluating sales and rotation per store to pick the best destination for the surplus. It was slow and inefficient, so the company decided to automate it.
From sales to rotation
I began by analyzing all the data the analysts relied on. It was not clean and had many missing values, so cleaning and imputation came first. Once usable, sales alone turned out to be a misleading signal: store sizes differ so much that a modest seller in a small store can outperform a "good" seller in a large one. I used the rotation of each item per store instead:
A metric to rank destinations
Rotation alone favors stores with almost no stock, so I weighted it by the units currently in stock to get a score that tells whether a store is a good place to send the surplus:
Items were then grouped into the store with the highest score. On top of that I encoded the business rules the analysts applied implicitly: items cannot move to a store in a different city, and franchise stores cannot receive stock from other stores.
Output for the logistics team
Logistics needed the result as a matrix to physically separate the items, so the script produces a table with items per size in the rows, stores in the columns and the number of units to send in each cell.
Beyond the numbers, the project meant working closely with the logistics team to understand how they operate and what they needed to make their job easier. It taught me a lot about the business and about turning an informal process into one that runs automatically.