Data Science POC: Product Allocation Optimization for Data Management & Analysis

Josh Miramant

Posted On:
May 23, 2022


The client is a private, American family-owned and operated distillery that produces and markets numerous brands of Kentucky Straight Bourbon Whiskey. The whiskey and bourbon market has experienced an annualized growth of 5.3% from 2017 to 2022, and to stay competitive the client was looking to apply modern data science approaches to improve their product allocation decisions.

In particular, they wanted to track quantities for inventory shipped during the allocation period, product scheduled to ship, total depletions, and inventory part in stock. Their present product allocation data lived in an IFS ERP system with the data manipulation and analysis being conducted by hand in Excel. The company employed Blue Orange for support in building a system to automate extraction and improve self-service allocation analysis to save time and make their data management more efficient.

The Challenge

The client’s current product allocation processes required significant manual effort for data manipulation and analysis across their sales, marketing, customer service, and supply chain workflows.  Their data infrastructure lacked a single source of truth for allocations across these departments, which resulted in more time needed for manual data manipulation and analysis. To remedy these pain points, Blue Orange helped create project goals that included: 

  • The ability to create Allocation Plans to contain the data for updating the Sales Part Cross Reference.
  • Automatic update of allocation values in the IFS Sales Part Cross Reference.
  • Landing pages initialized for relevant markets.

The data sources included Oracle 12c and SQL Server 2017 Pro for sales, inventory, customer, and allocation data.  Blue Orange would consolidate these sources into one, automated source of truth that would meet functionality and parameter requirements through POC deliverables including:

  • An Allocation Dashboard.
  • A holistic Allocation Plan.
    • Including the ability for DMs and Allocations Analyst to update the allocation plan and the ability for the Allocations Analyst to publish the plan.
  • An Approval Workflow.
  • Automated Update of Allocations Values in IFS Sales Part Cross Reference.

The Solution

Blue Orange proposed a 4-week development engagement to deliver the Allocations Data Science project. The resources for the project would include a Senior Data Scientist, a Solutions Architect, and a Technical Project Manager.  The roadmap of the initial effort would leverage data extracts and upon completion of this extraction the front-end integration would then be handled by internal client resources.

The proposed project scope would entail the creation of a Shiny R application that would present a KPI Dashboard that would enable the allocation manager to distribute the product allocation plan automatically for self-service allocation analysis. The main source of data for the application would be a new schema on the SQL database that would be fed by ERP tables on Oracle. The ELT pipelines would then be developed by the client.

The outcome of the engagement would be a model that allows the client’s users to update regional allocations, perform required self-service analysis of the allocations data, and apply the developed data science model. 


The client was seeking a modern data science approach to improve their product allocation decisions, automate data extraction, and manage their allocation plan and dashboards efficiently.  Blue Orange experts were able to evaluate their current system and propose a solution that would automatically unify data from their sales, marketing, customer service, and supply chain activities into a consolidated dashboard.  Thereby, eliminating manual data analysis for the internal client team so they could gain efficiencies and save time on their workflows.

This highly curated process is applicable across industries and sectors and if your business requires these consulting services contact our team today to discuss your project!  If you are interested in reading further about our additional client success stories, you can view our full Case Studies listing here.

See where smart data management can take your business