Acme Corp is a national distributor of electronics and hardware to small and medium-sized companies. Because size and cashflow prevent Acme Corps' SMB customers from carrying large inventories, they frequently stockout or request expensive last-minute air freight deliveries.
Acme Corp recognized that by combining customer sell-out with product shipment data, they could apply forecasting models that would automate and optimize product replenishment for their SMBs. This would deliver a better customer experience, while preventing stockout and creating new opportunities for optimizing product distribution logistics.
Unfortunately, although SMBs were happy to send till data to Acme Corp, the Domo implementation team did not have access to reliable starting inventories at the customer locations, so they approached me to design a process for deriving a starting inventory.
In this tutorial, we'll:
While this blog post is more developer-focused, it's important to recognise that these types of data normalization activities are the bridge step to building forecasting models and implementing workflow automation.
With any project like this, validation is almost more important than the solution designed. Make sure to identify and confirm assumptions with the domain experts before proceeding to the next step.
In any case... on to the tutorial!
Acme Corp only has sell-out data starting from the project commencement date, April 15, and did not know the starting inventory. As a result, on some days, the calculated Inventory on Hand appeared to be below zero.
The Problem: Calculate the minimum starting adjustment that guarantees that inventory never dips below zero.
Inventory with and without Adjustment.
What is a Window Function?
Cumulative Sum is a window function that can be calculated in MagicETL using the Window & Rank tile.
Next Steps / Where is the Optimization?
Now that you have 'good data', the next step is automated replenishment. If Acme Corp knows their lead time is 2 weeks and avg consumption is 5 units per week, then when inventory hits 10 units, they know it's time to send more inventory.
Advanced Nerd Note about Blocking Functions
Compared to other transformations, window functions are very expensive, because they are 'blocking'.
Blocking functions halt progress until the entire operation is completed.
If you'd like to bring my skills to your Domo implementation or governance team, please contact me.
I am a freelance consulting with experience accumulated over 30+ Domo projects and decades in the BI and data analytics space.