Case study 03 / 06
Bonjout Beauty
Beauty · E-commerce
From spreadsheets to an MRP platform
- Data engineering
- Software engineering
- Cloud & infrastructure
Key figures
- From Shopify order to the MRP
- Minutes
- Weeks of stock projection
- 12 to 52
- Backdated COGS restatement
- 2 years
- Sales data landed in BigQuery
- Daily
The brief
The challenge
A fast-growing DTC and wholesale beauty brand running on gigantic Excel files, then on a patchwork of tools: stock spread across warehouses, filling labs and partners, and no single answer to what it owns, what it really cost, and when it will run out.
What we built
First, daily analytics: the brand's sales data lands in BigQuery every day (today through DataSync) and feeds its dashboards. Then a full MRP platform (purchasing, lot-level inventory, stock projection, production planning, landed-cost COGS and Shopify sync) and its integration with a new API-only 3PL.
Outcome
One system of record for purchasing, stock, production, sales and COGS, in place of the spreadsheets: Shopify orders applied within minutes, every unit traceable to its lot, and purchased stock to its actual landed cost.
Related services: Full-stack developmentData collection and analysisPlatform integrations
Expertise
What it took.
Data engineering
- Event-sourced inventory on the app's append-only stock-movement ledger: business-dated movements, oldest-lot-first depletion, physical-count replay and as-of reconstruction of any past day.
- A landed-cost COGS engine (PO price × daily ECB rate plus freight, frozen per lot) rolled into a daily weighted-average ledger that restates history when a backdated event lands.
- Shopify ingestion with exactly-once effects: HMAC-verified webhooks drained every 5 minutes, a 15-minute reconcile job and a full historical backfill.
- DataSync pipelines that land Shopify data in BigQuery every day for dashboards and accounting exports.
Software engineering
- A Flask API and a React 19 app: Recharts dashboards, drag-and-drop production and arrival planning.
- Automated tests: unit, integration against a real Postgres, and frontend.
- A defensive, tested client for a 3PL API with no webhooks and no updates: ambiguous writes are never retried and implausible receipts are held.
- Wholesale orders rebuilt natively from a legacy app: retailer POs from PDFs and emails parsed by an LLM, then invoices, packing lists and stock reservations in the MRP.
Cloud & infrastructure
- Cloud Run services plus a Cloud Run Job for long Shopify syncs.
- A Cloud Build pipeline that runs database migrations before each API deploy, so a failing migration stops the release.
- OIDC-secured Cloud Scheduler jobs managed from inside the app, and separate DEV and PROD environments with their own databases and stores.
Project 01
Bonjout MRP
Flask · React 19 · PostgreSQL · Cloud Run
The brand's operational system of record: purchase orders through shipments and freight to lot-level receipts; an append-only stock ledger across warehouses and labs; 12-to-52-week stock projections with what-if arrivals; lab production planning and versioned BOMs; wholesale orders parsed from retailers' POs; and actual landed-cost COGS. It is built on, and extends, the database of the brand's previous internal app.
- Goal
- Answer, at any time, what the brand owns, where, what it really cost, and when it will run out.
- Highlights
- Weekly projection per location: 21-day velocity, BOM-driven component demand, safety stock and reorder-by week
- Replenishment suggestions that convert into purchase orders
- Daily COGS ledger with backdated restatement up to two years
- In-app API reference and a help guide with flow diagrams
- Stack
- Python
- Flask
- SQLAlchemy
- React 19
- Recharts
- PostgreSQL
- Cloud Run
Project 02
3PL integration
3PL API · Shopify GraphQL · Cloud Scheduler
Moving fulfilment to Capacity, an API-only 3PL with no webhooks and no update endpoints, meant the MRP had to drive everything: send the orders Shopify routes to the warehouse, announce each inbound shipment as a receiving order, in whole cases where the case pack allows, book receipts from six daily batch windows, write tracking back to Shopify and reconcile stock against the warehouse's nightly count.
- Goal
- Switch 3PLs without double-shipping, double-counting or losing stock accuracy.
- Highlights
- Tested end to end from a Shopify development store against the 3PL's sandbox, with its UAT test plan automated
- Receipts at twice the announced quantity are held, never booked
- A nightly count that would zero most SKUs is refused
- Readiness checklist, request log and per-shipment panels for operators
- Stack
- Python
- Flask
- Shopify GraphQL
- Cloud Scheduler
- REST APIs
Project 03
Analytics on DataSync
DataSync · BigQuery · Looker Studio
Before the MRP, DataGem replaced the spreadsheets with daily dashboards on BigQuery; those feeds now run on DataSync, its ingestion platform: Shopify data is exported, staged and merged into BigQuery on a daily schedule with no human step, and the same warehouse now feeds the MRP's accounting exports.
- Goal
- Daily, accurate business metrics without anyone touching a spreadsheet.
- Highlights
- Shopify bulk exports streamed into BigQuery in date windows
- Staging tables merged into history by key, so re-runs don't duplicate rows
- Stack
- DataSync
- BigQuery
- Cloud Run Jobs
- Cloud Scheduler
- Looker Studio
The dashboard created by Datagem Consulting has been a game-changer for our start-up. Pierre has demonstrated an excellent understanding of our needs, delivering a customized solution tailored specifically to our business requirements.
We now have immediate access to all our data with 100% accuracy, presented exactly how we want to see our business – all in one place, automatically updated daily without any human intervention.
Our needs continue to evolve, and over time, we have requested additional custom analysis and data tracking. Pierre has always responded promptly, day or night 😊, listening carefully, with patience.
He has been the perfect partner in this project. We have made many people envious of our Dashboard, and I certainly don’t miss our gigantic Excel files!