🛒 Retail & CPG: Demand Forecasting and Customer Analytics on Microsoft Fabric¶

Industry: Retail / Consumer Packaged Goods Compliance: PCI-DSS v4.0 Fabric SKU: F64 (P1 equivalent) Estimated Cost: ~$22,000/month

Executive Summary¶

A mid-market omnichannel retailer with 450 stores, $2.8B annual revenue, and 12 million loyalty members leverages Microsoft Fabric to unify point-of-sale streams, demand forecasting, inventory optimization, and customer analytics into a single lakehouse platform. The solution replaces a patchwork of legacy data warehouses, standalone forecasting tools, and siloed CRM systems with a cohesive medallion architecture powered by Delta Lake, Eventstream, and Direct Lake.

Key Business Outcomes¶

Estimated annual ROI: $18–24M from markdown reduction, stockout avoidance, and promotion optimization.

1. Business Context¶

1.1 Company Profile¶

1.2 Pain Points¶

1. Fragmented demand signals — POS, e-commerce, loyalty, and weather data live in separate systems with no unified view.
2. Forecast lag — Legacy batch forecasting runs weekly; merchants need daily SKU-store-level predictions for replenishment.
3. Markdown inefficiency — Without granular sell-through curves, buyers mark down too early or too late, eroding margin.
4. Customer blindness — CRM captures transactions but lacks real-time behavioral signals; next-best-offer models are stale.
5. Promotion black box — Marketing spends $180M/yr on promotions with limited incrementality measurement.
6. Supply chain opacity — No consolidated view of vendor lead times, fill rates, or DC throughput.

1.3 Compliance Requirements (PCI-DSS v4.0)¶

Critical rule: No Primary Account Number (PAN) enters the analytics lakehouse. All card data is tokenized at the edge (POS terminal or payment gateway) before streaming to Fabric.

2. Solution Architecture¶

2.1 High-Level Data Flow¶

graph LR
    subgraph Sources
        POS[POS Terminals<br/>450 stores]
        ECOM[E-Commerce<br/>Platform]
        LOYALTY[Loyalty<br/>Platform]
        SUPPLY[Supply Chain<br/>ERP]
        WEATHER[Weather<br/>API]
    end

    subgraph Fabric
        ES[Eventstream]
        EH[Eventhouse<br/>Real-Time Inventory]
        BRZ[Bronze<br/>Lakehouse]
        SLV[Silver<br/>Lakehouse]
        GLD[Gold<br/>Lakehouse]
        ML[ML Model<br/>Endpoints]
        PBI[Power BI<br/>Direct Lake]
    end

    POS -->|Tokenized POS| ES
    ECOM -->|Orders| ES
    LOYALTY -->|Events| ES
    SUPPLY -->|Batch/daily| BRZ
    WEATHER -->|API pull| BRZ

    ES --> EH
    ES --> BRZ
    BRZ --> SLV
    SLV --> GLD
    GLD --> ML
    ML --> GLD
    GLD --> PBI
    EH --> PBI

2.2 Eventstream Configuration¶

2.3 Eventhouse (Real-Time Inventory)¶

The Eventhouse provides sub-second inventory position queries for store associates and the e-commerce platform:

// Real-time inventory position by store-SKU
InventoryEvents
| where EventTime > ago(1h)
| summarize
    LastQtyOnHand = arg_max(EventTime, QtyOnHand),
    TotalSold = sumif(Qty, EventType == "SALE"),
    TotalReceived = sumif(Qty, EventType == "RECEIPT")
    by StoreId, SKU
| extend AvailableToPromise = LastQtyOnHand - TotalSold + TotalReceived
| where AvailableToPromise < 10
| order by AvailableToPromise asc

2.4 Medallion Lakehouse Layers¶

Bronze (lh_bronze)¶

Silver (lh_silver)¶

Gold (lh_gold)¶

3. Demand Forecasting¶

3.1 Feature Engineering¶

The demand model operates at SKU × Store × Day granularity (~38M combinations for active items):

3.2 Model Architecture¶

Daily sales (SKU-Store-Day)
    ├── Base forecast: LightGBM ensemble (7/14/28-day horizons)
    ├── Seasonal adjustment: Fourier terms + holiday calendar
    ├── Promotion uplift: Separate causal model (Double ML)
    └── Safety stock: Quantile regression (P95) for inventory planning

• Training cadence: Weekly retrain on 2 years of history
• Serving: Batch scoring via Fabric ML Model Endpoints; results written to gold_retail_demand
• Evaluation: WMAPE < 20% target; monitored via Power BI dashboard

3.3 Inventory Optimization¶

The forecast feeds downstream inventory decisions:

4. Customer 360 & Loyalty Analytics¶

4.1 RFM Scoring¶

# RFM calculation in gold layer
rfm = (
    silver_sales
    .groupBy("customer_id")
    .agg(
        datediff(current_date(), max("txn_date")).alias("recency_days"),
        countDistinct("txn_id").alias("frequency"),
        sum("line_total").alias("monetary")
    )
)

# Score 1-5 using quintiles
for metric in ["recency_days", "frequency", "monetary"]:
    rfm = rfm.withColumn(
        f"{metric}_score",
        ntile(5).over(Window.orderBy(col(metric)))
    )

4.2 Customer Lifetime Value (CLV)¶

• Model: BG/NBD for purchase frequency × Gamma-Gamma for monetary value
• Horizon: 12-month forward-looking CLV
• Segments: Platinum (top 5%), Gold (5–20%), Silver (20–50%), Bronze (50–80%), At-Risk (bottom 20%)

4.3 Next-Best-Offer¶

5. Promotion Lift Measurement¶

5.1 Methodology¶

Each promotion is evaluated for true incrementality using causal inference:

1. Test vs. Control: Matched-store design (propensity score matching)
2. Causal Impact: Bayesian structural time-series model
3. Cannibalization: Cross-elasticity matrix within category
4. Halo effect: Basket analysis for promoted vs. non-promoted items in same trip

5.2 Promotion KPIs¶

6. Supply Chain Visibility¶

6.1 Vendor Scorecard¶

6.2 Distribution Center Throughput¶

// DC throughput monitoring
DCOperations
| where Timestamp > ago(24h)
| summarize
    UnitsReceived = sumif(Units, OperationType == "INBOUND"),
    UnitsShipped = sumif(Units, OperationType == "OUTBOUND"),
    AvgPickTime = avg(PickDurationMinutes)
    by DCId, bin(Timestamp, 1h)
| extend Utilization = round(100.0 * UnitsShipped / Capacity, 1)

7. Power BI Dashboards¶

7.1 Dashboard Suite¶

7.2 Row-Level Security¶

// RLS filter: Regional managers see only their region
[Region] = USERPRINCIPALNAME_REGION_LOOKUP()

// Store managers see only their store
[StoreId] = LOOKUPVALUE(
    StoreAccess[StoreId],
    StoreAccess[UserPrincipalName], USERPRINCIPALNAME()
)

8. PCI-DSS Implementation Details¶

8.1 Tokenization Flow¶

POS Terminal → Payment Gateway (tokenizes PAN) → Event Hub → Fabric
                                                      ↓
                                              Only token + last4
                                              enter the lakehouse

8.2 Data Classification¶

8.3 Audit Trail¶

All access to PCI-scoped tables is logged via Fabric audit logs routed to Eventhouse:

FabricAuditLogs
| where Timestamp > ago(24h)
| where TableName has "pos" or TableName has "card"
| summarize AccessCount = count() by UserId, TableName, Operation
| where AccessCount > 100
| order by AccessCount desc

9. Cost Model¶

9.1 Monthly Fabric Cost Breakdown¶

9.2 ROI Calculation¶

10. Implementation Roadmap¶

Total: ~21 weeks (5 months) from kickoff to production.

11. Notebooks Reference¶

12. Related Resources¶

• Tutorial 49: Retail/CPG End-to-End
• Direct Lake Best Practices
• Medallion Architecture Deep Dive
• PCI-DSS Tokenization Patterns
• Capacity Planning
• Real-Time Intelligence

Last Updated: 2026-04-27 Author: Fabric POC Team Compliance Review: PCI-DSS v4.0 scope validated