Premise

On the back of our success modernizing a client’s fraud detection system the same client retained my team to re-imagine their inventory management system and analysis processes. Executives at the Fortune 500 client had no visibility into their organization-wide inventory, how errors were reconciled and what controls exist to prevent internal fraud.

Myself and my team were tasked to create tooling and processes that:

• Consolidated and cataloged all inventory data
• Quantify COGS (Cost of Goods) price and expected retail price
• Determine on-hand amounts based on prior use
• Identify egregious fraud
• Architect the solution with an eye towards future expansion

The client was using CrunchTime as their primary data tracking system.

Process

To begin the process I led a two-fold effort that involved:

• User interviews with location management, executives, technical staff and vendors.
• Re-organize and inventory relevant CrunchTime data

The two-pronged approach enabled maximum flexibility and background to better contextualize user interviews by knowing what data was and was not available. At the end of the initial investigation I became a data subject matter expert on inventory data provided by CrunchTime. At the conclusion of the user interviews myself and my team were prepared to create a useful solution.

The technical component of the project was likewise split into two steps; both of which I led:

• Inventory data ETL
• KPI creation and validation

From the very beginning we were determined to create a solution that was extendable. This philosophy dictated that we build a stable ETL process to load data into Synapse. By focusing on the ETL process and refining the data we could direct our remaining focus into making sense of the data with confidence in the data. We were further able to refine the data to make it analysis-ready. Creating the KPIs involved close collaboration with key stakeholders and on-the-ground management.

Results

• $20 Million dollars in inventory savings
• Visibility into $67 Million dollars of inventory stock
• Knowledge of CrunchTime’s initially complex data

Skills Used

• Python
• SQL
• Data modeling
• Spark
• PySpark
• Azure
• Synapse

Lessons Learned

• Extendable frameworks are worth the initial effort