Preventing Stockout and Improving Supply Chain Logistics with Data

The Problem.

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:

• build a dataflow that derives starting inventory
• learn why certain ETL (blocking functions) tiles have such a drastic impact on query performance and how you can optimize around them.

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.

• Ex.  If we build a forecasting model around customer sales but don't consider available inventory, it's impossible to gauge whether the lack of sales is 'normal customer behaviour' or attributable to stock out.
• In a similar vein, before 'advanced analytics' can begin, we must carefully evaluate whether the available data represents reality.  If we don't apply domain expertise to the problem, we might dismiss rows with negative inventory as invalid or outlier measurements and therefore exclude them from our forecasting analysis.

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.

Solution

• Inventory is calculated as the cumulative sum of daily net change, i.e. shipment - sales.
• To guarantee that reported inventory never falls below zero, the starting adjustment must be the MIN(reported inventory) * -1. 

What is a Window Function?
Cumulative Sum is a window function that can be calculated in MagicETL using the Window & Rank tile.

• The tile accepts parameters modeled after a standard SQL implementation of windowed functions.
• A window refers to the set of rows considered in the calculation.  For a cumulative sum, the range of rows considered should be all rows preceding the current day and none after.
• Partitions refer to how windows are subdivided.  If our data spanned multiple customers, we'd partition by the customer field to accumulate inventory on a per-customer basis.  Similarly if our data could be subdivided by customer and product, we'd partition by those two columns.
• The explicit sort order defines how rows should be arranged inside the partition to achieve an appropriate accumulation.  In our case, date.

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.  

• In Domo, a notification can be automated with dataset alerts, or reports.  
• Advanced development teams seeking to take things a step further could lever Domo's Integration cloud offering to construct writeback connectors that kick off automated product-replenishment workflows in source systems.

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.  

• In our case, the ETL cannot proceed to the next step until the entire data has been loaded, sorted, and then aggregated, hence the moniker 'blocking function.'  In contrast, most other functions, ex. UPPER(), ROUND(), CAST, or Add Constant, are non-blocking -- data processing can complete as it arrives.
• Better tools for window functions in descending order would be Beast Mode, Data View Beta, Adrenaline Dataflow Beta, Magic ETL, SQL.

The End

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.