How Databricks and Prophet Power Retail Demand Forecasting for Store‑Item Sales

1. Importance of Consumer Demand Forecasting

Accurate demand forecasting is essential for retailers because over‑stocking leads to limited shelf space, product expiration, and tied‑up capital, while under‑stocking causes lost sales and drives customers to competitors. Timely and precise predictions help control costs and capture new market opportunities.

2. Data Preparation and Visualization

The dataset (2012‑2017) contains daily sales for 50 products across 10 stores, with columns for date, store ID, product ID, and sales quantity. After uploading the CSV files to an OSS bucket, the data is read into a Spark DataFrame with a predefined schema to speed up loading. A temporary view is created for SQL‑style analysis, revealing steady yearly sales growth and strong seasonal patterns.

3. Building a Store‑Item Model with DDI

Using Databricks DDI notebooks and Facebook Prophet, a model is built for a single store‑product pair (e.g., store 1, item 1). The time series shows strong weekly and yearly seasonality, so Prophet is configured with weekly=true and yearly=true. After fitting the model, a 90‑day forecast is generated, demonstrating increasing sales trends and the impact of holidays.

4. Extending the Model to All Store‑Item Combinations

The workflow is scaled to predict every combination of the 10 stores and 50 products. Training data with the same four columns is prepared, a Prophet object is created with weekly and yearly seasonality enabled, and forecasts for the next 90 days are produced for each pair. The predictions are concatenated with historical data, written back to OSS, and later loaded into Spark for evaluation.

Model performance metrics such as MAE, MSE, and RMSE are calculated using a UDF, providing quantitative insight into forecast accuracy across all store‑product pairs.