Real-Time ERP Margin, Stock & Sales Telemetry on Google Cloud Run

01 Enterprise Context and Commercial Profile

The client is an established European distributor and direct importer of consumer electronics and home equipment generating over $8,000,000 (30,000,000+ PLN) in annual turnover. The commercial infrastructure spans a multi-channel retail mix consisting of proprietary e-commerce storefronts, top regional marketplaces (Allegro, Amazon), and a nationwide wholesale network serving retail chains.

Performance marketing operations were executed at massive scale: managing a structure of over 40 Google Ads MCC sub-accounts alongside 8 complex Meta Ads campaign portfolios, deploying hundreds of thousands of dollars annually in media spend. Across an active inventory catalog exceeding 1,800 active SKU items, executive leadership faced a critical financial controlling breakdown: there was zero real-time visibility into which individual products generated true net profit versus which items silently eroded working capital once factoring escalating CPC bids, logistics overhead, and marketplace commission schedules.

02 Quantitative Bottleneck & Baseline Problem

The incumbent controlling workflow relied entirely on end-of-month manual database dumps from SQL Server ERP ledgers into multi-tab Microsoft Excel workbooks. This operational model suffered from severe structural vulnerabilities:

• 14-Day Latency in Executive Intelligence: Month-end margin reports were finalized in the middle of the following calendar month. Executive decisions regarding inventory replenishment and ad budget scaling were made against stale historical data.
• Silent Budget Leakages (8,000–15,000 PLN / month): Performance Max and Meta Advantage+ bidding algorithms indiscriminately poured media budget into items operating at zero or negative net margin due to unrecorded vendor cost increases or marketplace tariff shifts.
• Out-of-Stock Response Lag (24–48 Hours): When high-velocity products sold out at the central logistics hub, automated ad campaigns continued bidding on empty inventory listings for up to 48 hours, incurring wasteful click costs and generating customer dissatisfaction.
• 35 Analyst Hours Consumed Monthly: Two senior financial controllers spent the first seven days of every month manually reconciling fragmented CSV files, return notes, and commission statements.

03 Technical Architecture & Real-Time Data Flow

I engineered and deployed a sovereign, serverless telemetry pipeline on Google Cloud Run and Cloud Pub/Sub. The architecture captures warehouse stock mutations and order webhooks in real time via Change Data Capture (CDC), calculating product net margin in under 3 seconds:

graph TD
    subgraph S1 ["1. Data Sources & ERP"]
        ERP["Comarch ERP SQL / Subiekt API"]
        MKT["Marketplace APIs (Allegro, Amazon)"]
        SHOP["E-commerce Storefronts"]
    end

    subgraph S2 ["2. Streaming Event Bus"]
        CDC["CDC Worker / Webhooks"]
        PUBSUB["Google Cloud Pub/Sub Event Bus"]
    end

    subgraph S3 ["3. Serverless Compute (Cloud Run)"]
        FASTAPI["FastAPI Ingestion Gateway"]
        ENGINE["Net Margin Calculation Engine (Python 3.12)"]
        POKAYOKE["Deterministic Poka-Yoke Guardrail"]
    end

    subgraph S4 ["4. Analytical Warehouse"]
        BQ[("Google BigQuery (Streaming Ingestion)")]
        PG[("Cloud SQL PostgreSQL")]
    end

    subgraph S5 ["5. Ad Platform Mutation Gateway"]
        GADS["Google Ads API (AdGroup & PMax Pause)"]
        META["Meta Conversions & Marketing API"]
    end

    subgraph S6 ["6. Telemetry & Observability"]
        DASH["Live Grafana / Metabase Dashboard"]
        SLACK["Slack Margin Breach Alerting"]
    end

    ERP --> CDC
    MKT --> CDC
    SHOP --> CDC
    CDC --> PUBSUB
    PUBSUB --> FASTAPI
    FASTAPI --> ENGINE
    ENGINE --> POKAYOKE
    POKAYOKE --> BQ
    POKAYOKE --> PG
    POKAYOKE -- "Negative Margin / OOS" --> GADS
    POKAYOKE -- "Negative Margin / OOS" --> META
    BQ --> DASH
    POKAYOKE -- "Critical Margin Breach" --> SLACK
          

Architecture Layer Breakdown:

1. Transaction Layer (ERP & Marketplaces): Dedicated Change Data Capture (CDC) workers listen directly to SQL transaction logs, receiving webhooks from marketplaces and storefronts within 200 ms of order placement.
2. Event Bus (Google Cloud Pub/Sub): Decouples transaction ingestion from heavy analytics with guaranteed at-least-once delivery, safeguarding data integrity during major retail spikes like Black Friday.
3. Serverless Compute (Google Cloud Run): Scalable Docker containers running FastAPI in Python 3.12 consume message streams, fetch live foreign currency rates from central banks, and compute deterministic unit economics.
4. Analytical Warehouse (BigQuery & Cloud SQL): Transformed records stream directly into partitioned BigQuery tables for sub-second OLAP reporting across multi-year data horizons.
5. Ad Platform Automation Gateway: When net margin breaches pre-set safety thresholds, the engine dispatches authenticated mutations to Google Ads API and Meta Marketing API, instantly disabling unprofitable ad sets.
6. Observability Layer (Grafana & Slack): Real-time executive dashboards present contribution margin per SKU, category, and sales channel, coupled with priority Slack notifications for commercial leadership.

04 Technology Stack and Infrastructure

The solution is engineered according to strict Cloud-Agnostic standards, free from recurring third-party SaaS seats:

• Google Cloud Run: Serverless Docker execution environment scaling from zero to 20 concurrent container instances in milliseconds, keeping cloud hosting costs below $40 per month.
• Python 3.12 & Pydantic V2: Enforces strict data models on all financial events, mathematically barring ill-formatted accounting entries from entering the ledger.
• FastAPI & AsyncIO: High-throughput asynchronous pipeline benchmarking over 1,200 transactions per second under stress tests.
• Google BigQuery: Enterprise data warehouse delivering low-latency querying over tens of millions of historical order lines.
• Google Ads API v16 & Meta Conversions API: Direct OAuth2 service account integration enabling automated bid adjustments and server-side tracking.
• Langfuse & Grafana: Complete immutable audit log verifying every programmatic campaign pause action.

05 Deterministic Net Margin Formula and Poka-Yoke Logic

Most retail systems erroneously rely on superficial "Gross Margin" (Sale Price − Cost of Goods Sold). In reality, multichannel commerce is heavily burdened by variable transaction costs.

The deployed engine computes true third-degree contribution margin (True Contribution Margin 3 — CM3):

Deterministic Poka-Yoke Guardrail: In the event that any input variable (such as an updated foreign currency exchange rate or current category fee) is missing from the registry, the system is strictly prohibited from estimating or averaging. The transaction is placed into a quarantine queue, and an immediate alert is routed to the technical controller.

06 Hard KPI Metrics: Before vs After Benchmark

All operational performance metrics were validated on live production data across 90 days of continuous system operation:

07 Human-in-the-Loop Architecture and Role Division

The telemetry engine does not usurp executive leadership. It is designed upon an uncompromising division of responsibilities: deterministic software handles instant telemetry and automated policy enforcement, while human executives retain total command over commercial strategy and risk appetite:

08 Critical Edge Case: Currency Spike & Supplier Price Surge

The physical resilience of the architecture was proven during an operational crisis in the second month of production deployment:

A key Asian manufacturing supplier announced an overnight 14% price hike on a flagship consumer electronics product line. Concurrently, foreign exchange markets experienced an unexpected 2.5% surge in the USD/PLN rate over a 48-hour period. Under the legacy operating model, the commercial team would have remained oblivious to the margin collapse for nearly four weeks until monthly accounting reconciliations. During that gap, Google Shopping and Performance Max campaigns would have converted hundreds of loss-making sales, bleeding significant profit per unit.

Telemetry System Reaction: As the logistics team logged the incoming goods receipt into Comarch ERP with the higher USD procurement invoice, the CDC worker triggered a webhook. The engine recalculated the Break-Even Point across 18 affected SKU items in 3 seconds, detected that rising unit costs dropped contribution margin into negative territory under current CPC bid caps, and immediately executed programmatic pauses across Google Ads and Meta Ads campaigns. Simultaneously, the Commercial Director received an automated Slack notification with suggested retail price adjustments. Zero deficit orders were permitted to ship.

09 Executive Testimonial and Board Verification

10 Engineering Conclusions, Code Sovereignty and CTA

This deployment proves that in an era of escalating advertising costs and dynamic marketplace fees, reliance on monthly spreadsheet reviews is an unacceptable threat to enterprise profitability.

• 100% Code Ownership: The entire software stack (FastAPI microservices, orchestration DAGs, BigQuery schemas, and Grafana dashboards) resides inside the client's private Git repository in Docker containers.
• Zero Per-Seat SaaS Overhead: The enterprise pays only direct Google Cloud infrastructure costs (under $40/month), free from restrictive vendor seat licenses or platform royalties.
• Immutable Auditability: Every automated bid adjustment and campaign pause is permanently logged with complete cryptographic timestamps and mathematical justification.