Building an Intelligent Inventory Forecasting Pipeline: From Sales Data to Stock Predictions

E-commerce teams struggle with inventory management. Overstock ties up capital; understock leads to lost sales. Manual forecasting is inaccurate and reactive. Teams can't predict demand spikes, seasonal patterns, or product lifecycle changes. The result: excess inventory costs, stockouts, and lost revenue.

The problem: A product sells 100 units per week. The team orders 500 units monthly. Then a viral social media post drives demand to 500 units per week. Stock runs out in 3 days. Customers can't buy, competitors get the sales, and the team loses $50K in revenue. By the time they realize what happened, it's too late.

The solution: An automated inventory forecasting pipeline that analyzes sales history, product trends, seasonal patterns, and external factors—generating accurate stock predictions and automated reorder recommendations. This post walks through building a repeatable architecture that transforms reactive inventory management into proactive demand forecasting.

The Problem with Manual Inventory Forecasting

Manual inventory forecasting breaks down at scale. Here's what e-commerce teams face:

The Data Gathering Problem

Forecasting requires data from multiple sources:

• Sales Data: Transaction history, order volumes, product sales
• Inventory Data: Current stock levels, warehouse locations, reorder points
• Product Data: SKUs, categories, lifecycle stages, seasonality
• External Factors: Promotions, marketing campaigns, competitor actions

The math: If gathering data from 4 sources takes 2 hours and you forecast 1,000 products monthly, that's 2,000 hours per year just collecting data—before any analysis.

The Pattern Detection Problem

Teams can't identify demand patterns:

• Seasonal spikes go unnoticed until stockouts occur
• Product lifecycle changes aren't detected early
• Demand trends are discovered after they've peaked
• External factors (promotions, viral content) aren't factored in

The impact: A seasonal product needs 3x inventory in December, but the team orders the same amount year-round. Stockouts occur during peak season, losing $200K in potential revenue.

The Prediction Accuracy Problem

Manual forecasts are inaccurate:

• Gut-feel estimates miss actual demand by 30-50%
• Historical averages don't account for trends
• One-size-fits-all models don't work for different product categories
• Forecasts aren't updated as new data arrives

The cost: Overstocking slow-moving items ties up $500K in capital. Understocking fast-moving items loses $300K in sales. Total impact: $800K annually.

The Timing Problem

Reorder decisions are delayed:

• Teams review inventory weekly or monthly
• By the time they order, stock is already low
• Lead times mean stockouts before new inventory arrives
• No automated alerts for critical stock levels

The missed opportunity: Automated forecasting could predict demand 4-6 weeks ahead, enabling proactive ordering and preventing 80% of stockouts.

A Repeatable Architecture for Demand Forecasting

The solution is a pipeline architecture that aggregates sales data, detects patterns, generates forecasts, and triggers reorder recommendations.

Architecture Components

1. Data Aggregation Layer

• Connects to sales systems (e-commerce platform, POS, marketplaces)
• Retrieves inventory data (warehouse systems, inventory management)
• Fetches product data (catalog, categories, attributes)
• Captures external factors (promotions, marketing events)

2. Sales History View

• Materialized view aggregating sales by product, time period
• Pre-computed aggregations (daily, weekly, monthly)
• Calculated fields (growth rates, trends, seasonality)
• Time-series data for forecasting

3. Trend Analysis Engine

• Identifies sales trends (increasing, decreasing, stable)
• Detects growth rates and acceleration
• Surfaces product lifecycle stages
• Flags demand shifts

4. Seasonal Pattern Detection

• Identifies seasonal patterns (holidays, weather, events)
• Calculates seasonal multipliers
• Adjusts forecasts for seasonality
• Tracks seasonal accuracy

5. Forecast Generation

• Time-series forecasting models
• Demand prediction algorithms
• Confidence intervals
• Multiple forecast horizons (1 week, 1 month, 3 months)

6. Reorder Recommendation Engine

• Calculates optimal reorder points
• Considers lead times and safety stock
• Factors in warehouse capacity
• Generates purchase orders

Aggregating Sales Data Across Sources

The first stage is aggregating sales data from e-commerce platforms, marketplaces, and POS systems into a unified view.

Building the Sales History View

Create a comprehensive view that joins sales data with product and inventory context:

CREATE MATERIALIZED VIEW sales_history_comprehensive AS
SELECT 
    -- Sales Info
    s.order_id,
    s.order_date,
    s.product_id,
    s.product_sku,
    s.quantity_sold,
    s.revenue,
    s.channel, -- 'website', 'marketplace', 'pos', 'mobile'
    
    -- Product Context
    p.product_name,
    p.category,
    p.brand,
    p.product_lifecycle_stage, -- 'new', 'growth', 'mature', 'declining'
    p.seasonality_type, -- 'none', 'holiday', 'seasonal', 'year_round'
    
    -- Inventory Context
    i.current_stock,
    i.warehouse_location,
    i.reorder_point,
    i.safety_stock,
    i.lead_time_days,
    
    -- External Factors
    e.promotion_active,
    e.marketing_campaign,
    e.competitor_action,
    
    -- Date Dimensions
    DATE(s.order_date) as sale_date,
    DATE_PART('week', s.order_date) as sale_week,
    DATE_PART('month', s.order_date) as sale_month,
    DATE_PART('quarter', s.order_date) as sale_quarter,
    DATE_PART('year', s.order_date) as sale_year,
    DATE_PART('dow', s.order_date) as day_of_week,
    
    -- Calculated Fields
    CASE 
        WHEN p.seasonality_type = 'holiday' AND DATE_PART('month', s.order_date) IN (11, 12) THEN 'peak_season'
        WHEN p.seasonality_type = 'seasonal' AND DATE_PART('month', s.order_date) IN (6, 7, 8) THEN 'peak_season'
        ELSE 'normal'
    END as seasonality_period
    
FROM sales_transactions s
LEFT JOIN products p ON s.product_id = p.product_id
LEFT JOIN inventory_current i ON s.product_id = i.product_id
LEFT JOIN external_factors e ON s.order_date = e.event_date AND s.product_id = e.product_id;

Weekly Sales Aggregation

Pre-aggregate data for faster forecasting:

CREATE MATERIALIZED VIEW sales_history_weekly AS
SELECT 
    product_id,
    product_sku,
    product_name,
    category,
    sale_week,
    sale_year,
    DATE_TRUNC('week', sale_date) as week_start_date,
    
    -- Volume Metrics
    SUM(quantity_sold) as total_units_sold,
    COUNT(DISTINCT order_id) as order_count,
    AVG(quantity_sold) as avg_units_per_order,
    
    -- Revenue Metrics
    SUM(revenue) as total_revenue,
    AVG(revenue) as avg_order_value,
    
    -- Channel Breakdown
    SUM(quantity_sold) FILTER (WHERE channel = 'website') as website_units,
    SUM(quantity_sold) FILTER (WHERE channel = 'marketplace') as marketplace_units,
    SUM(quantity_sold) FILTER (WHERE channel = 'pos') as pos_units,
    
    -- Trends (vs previous week)
    SUM(quantity_sold) - LAG(SUM(quantity_sold)) OVER (PARTITION BY product_id ORDER BY sale_week, sale_year) as units_change,
    (SUM(quantity_sold) - LAG(SUM(quantity_sold)) OVER (PARTITION BY product_id ORDER BY sale_week, sale_year))::float / 
    NULLIF(LAG(SUM(quantity_sold)) OVER (PARTITION BY product_id ORDER BY sale_week, sale_year), 0) * 100 as units_change_percent,
    
    -- Seasonality
    MODE() WITHIN GROUP (ORDER BY seasonality_period) as seasonality_period,
    
    -- External Factors
    COUNT(*) FILTER (WHERE promotion_active = true) as promotion_days,
    COUNT(*) FILTER (WHERE marketing_campaign IS NOT NULL) as campaign_days
    
FROM sales_history_comprehensive
GROUP BY product_id, product_sku, product_name, category, sale_week, sale_year, DATE_TRUNC('week', sale_date);

Time-Series Analysis and Trend Detection

Analyze sales trends to identify growth patterns, detect shifts, and predict future demand.

Trend Detection

Identify sales trends over time:

CREATE MATERIALIZED VIEW product_trends AS
SELECT 
    product_id,
    product_name,
    category,
    
    -- Recent Sales (last 4 weeks)
    SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '4 weeks') as recent_units,
    
    -- Previous Period (4 weeks before that)
    SUM(total_units_sold) FILTER (
        WHERE week_start_date >= CURRENT_DATE - INTERVAL '8 weeks' 
        AND week_start_date < CURRENT_DATE - INTERVAL '4 weeks'
    ) as previous_units,
    
    -- Trend Calculation
    CASE 
        WHEN SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '4 weeks') > 
             SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '8 weeks' AND week_start_date < CURRENT_DATE - INTERVAL '4 weeks') * 1.2 
        THEN 'accelerating'
        WHEN SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '4 weeks') > 
             SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '8 weeks' AND week_start_date < CURRENT_DATE - INTERVAL '4 weeks') * 1.05
        THEN 'growing'
        WHEN SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '4 weeks') < 
             SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '8 weeks' AND week_start_date < CURRENT_DATE - INTERVAL '4 weeks') * 0.8
        THEN 'declining'
        ELSE 'stable'
    END as sales_trend,
    
    -- Growth Rate
    (
        SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '4 weeks')::float / 
        NULLIF(SUM(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '8 weeks' AND week_start_date < CURRENT_DATE - INTERVAL '4 weeks'), 0) - 1
    ) * 100 as growth_rate_percent,
    
    -- Average Weekly Sales
    AVG(total_units_sold) FILTER (WHERE week_start_date >= CURRENT_DATE - INTERVAL '12 weeks') as avg_weekly_sales
    
FROM sales_history_weekly
GROUP BY product_id, product_name, category;

Anomaly Detection

Detect unusual sales patterns:

CREATE MATERIALIZED VIEW sales_anomalies AS
WITH weekly_stats AS (
    SELECT 
        product_id,
        week_start_date,
        total_units_sold,
        AVG(total_units_sold) OVER (
            PARTITION BY product_id 
            ORDER BY week_start_date 
            ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
        ) as avg_previous_weeks,
        STDDEV(total_units_sold) OVER (
            PARTITION BY product_id 
            ORDER BY week_start_date 
            ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
        ) as stddev_previous_weeks
    FROM sales_history_weekly
)
SELECT 
    product_id,
    week_start_date,
    total_units_sold,
    avg_previous_weeks,
    CASE 
        WHEN total_units_sold > avg_previous_weeks + (2 * stddev_previous_weeks) THEN 'spike'
        WHEN total_units_sold < avg_previous_weeks - (2 * stddev_previous_weeks) THEN 'drop'
        ELSE 'normal'
    END as anomaly_type,
    ABS(total_units_sold - avg_previous_weeks) / NULLIF(avg_previous_weeks, 0) as deviation_percentage
FROM weekly_stats
WHERE total_units_sold > avg_previous_weeks + (2 * stddev_previous_weeks)
   OR total_units_sold < avg_previous_weeks - (2 * stddev_previous_weeks);

Seasonal Pattern Recognition

Identify and account for seasonal patterns in demand forecasting.

Seasonal Multiplier Calculation

CREATE MATERIALIZED VIEW seasonal_multipliers AS
SELECT 
    product_id,
    category,
    DATE_PART('month', sale_date) as month,
    DATE_PART('dow', sale_date) as day_of_week,
    
    -- Calculate average sales by month
    AVG(total_units_sold) as avg_monthly_sales,
    
    -- Calculate overall average
    (SELECT AVG(total_units_sold) FROM sales_history_weekly WHERE product_id = shw.product_id) as overall_avg_sales,
    
    -- Seasonal Multiplier
    AVG(total_units_sold) / NULLIF(
        (SELECT AVG(total_units_sold) FROM sales_history_weekly WHERE product_id = shw.product_id), 
        0
    ) as seasonal_multiplier
    
FROM sales_history_weekly shw
GROUP BY product_id, category, DATE_PART('month', sale_date), DATE_PART('dow', sale_date);

Seasonal Forecast Adjustment

CREATE MATERIALIZED VIEW seasonal_adjusted_forecast AS
SELECT 
    pt.product_id,
    pt.product_name,
    pt.avg_weekly_sales,
    pt.sales_trend,
    pt.growth_rate_percent,
    
    -- Base Forecast (next 4 weeks)
    pt.avg_weekly_sales * 4 as base_forecast_4_weeks,
    
    -- Apply Seasonal Multiplier
    pt.avg_weekly_sales * 4 * COALESCE(
        (SELECT seasonal_multiplier 
         FROM seasonal_multipliers 
         WHERE product_id = pt.product_id 
           AND month = DATE_PART('month', CURRENT_DATE + INTERVAL '2 weeks')
         LIMIT 1),
        1.0
    ) as seasonal_adjusted_forecast,
    
    -- Apply Trend Adjustment
    CASE 
        WHEN pt.sales_trend = 'accelerating' THEN pt.avg_weekly_sales * 4 * 1.3
        WHEN pt.sales_trend = 'growing' THEN pt.avg_weekly_sales * 4 * 1.15
        WHEN pt.sales_trend = 'declining' THEN pt.avg_weekly_sales * 4 * 0.85
        ELSE pt.avg_weekly_sales * 4
    END as trend_adjusted_forecast,
    
    -- Final Forecast (combines seasonal + trend)
    pt.avg_weekly_sales * 4 * 
    COALESCE(
        (SELECT seasonal_multiplier 
         FROM seasonal_multipliers 
         WHERE product_id = pt.product_id 
           AND month = DATE_PART('month', CURRENT_DATE + INTERVAL '2 weeks')
         LIMIT 1),
        1.0
    ) *
    CASE 
        WHEN pt.sales_trend = 'accelerating' THEN 1.3
        WHEN pt.sales_trend = 'growing' THEN 1.15
        WHEN pt.sales_trend = 'declining' THEN 0.85
        ELSE 1.0
    END as final_forecast_4_weeks
    
FROM product_trends pt;

Reorder Point Calculation

Calculate optimal reorder points based on forecasted demand, lead times, and safety stock.

Reorder Point Logic

CREATE MATERIALIZED VIEW reorder_recommendations AS
SELECT 
    saf.product_id,
    saf.product_name,
    saf.final_forecast_4_weeks as forecasted_demand_4_weeks,
    
    -- Current Inventory
    i.current_stock,
    i.reorder_point,
    i.safety_stock,
    i.lead_time_days,
    
    -- Forecasted Weekly Demand
    saf.final_forecast_4_weeks / 4 as forecasted_weekly_demand,
    
    -- Demand During Lead Time
    (saf.final_forecast_4_weeks / 4) * (i.lead_time_days / 7) as demand_during_lead_time,
    
    -- Recommended Reorder Point
    (saf.final_forecast_4_weeks / 4) * (i.lead_time_days / 7) + i.safety_stock as recommended_reorder_point,
    
    -- Reorder Quantity (4 weeks of demand)
    saf.final_forecast_4_weeks as recommended_reorder_quantity,
    
    -- Current Status
    CASE 
        WHEN i.current_stock <= (saf.final_forecast_4_weeks / 4) * (i.lead_time_days / 7) + i.safety_stock THEN 'reorder_now'
        WHEN i.current_stock <= (saf.final_forecast_4_weeks / 4) * (i.lead_time_days / 7) + i.safety_stock * 1.5 THEN 'reorder_soon'
        ELSE 'sufficient_stock'
    END as reorder_status,
    
    -- Days Until Stockout
    CASE 
        WHEN saf.final_forecast_4_weeks / 4 > 0 
        THEN (i.current_stock / (saf.final_forecast_4_weeks / 4))::integer
        ELSE 999
    END as days_until_stockout
    
FROM seasonal_adjusted_forecast saf
LEFT JOIN inventory_current i ON saf.product_id = i.product_id;

Reorder Alert Generation

CREATE MATERIALIZED VIEW reorder_alerts AS
SELECT 
    rr.product_id,
    rr.product_name,
    rr.current_stock,
    rr.forecasted_demand_4_weeks,
    rr.recommended_reorder_quantity,
    rr.reorder_status,
    rr.days_until_stockout,
    
    -- Alert Priority
    CASE 
        WHEN rr.days_until_stockout <= 7 THEN 'critical'
        WHEN rr.days_until_stockout <= 14 THEN 'high'
        WHEN rr.days_until_stockout <= 21 THEN 'medium'
        ELSE 'low'
    END as alert_priority,
    
    -- Estimated Revenue at Risk
    CASE 
        WHEN rr.days_until_stockout <= i.lead_time_days 
        THEN (rr.forecasted_weekly_demand * (i.lead_time_days - rr.days_until_stockout) / 7) * p.price
        ELSE 0
    END as revenue_at_risk
    
FROM reorder_recommendations rr
LEFT JOIN inventory_current i ON rr.product_id = i.product_id
LEFT JOIN products p ON rr.product_id = p.product_id
WHERE rr.reorder_status IN ('reorder_now', 'reorder_soon')
ORDER BY 
    CASE 
        WHEN rr.days_until_stockout <= 7 THEN 1
        WHEN rr.days_until_stockout <= 14 THEN 2
        WHEN rr.days_until_stockout <= 21 THEN 3
        ELSE 4
    END,
    rr.revenue_at_risk DESC;

Schema Overview

The forecasting pipeline uses a hierarchical schema that traces from raw sales to forecasts and reorder recommendations.

Key relationships:

• sales_transactions → sales_history_comprehensive (enriched with product/inventory)
• sales_history_comprehensive → sales_history_weekly (aggregated)
• sales_history_weekly → product_trends (trend analysis)
• product_trends + seasonal_multipliers → seasonal_adjusted_forecast (forecasting)
• seasonal_adjusted_forecast + inventory_current → reorder_recommendations (reorder logic)

Traceability: Every forecast can be traced back to source sales data, trend analysis, seasonal adjustments, and inventory status.

Closing Thoughts

Building an intelligent inventory forecasting pipeline transforms inventory management from reactive to proactive. The key is treating forecasting as a data pipeline problem—not a manual process.

Key takeaways:

1. Aggregation enables speed: Pre-compute sales aggregations. Forecasting needs fast access to historical data, not complex queries at query time.

2. Trend detection surfaces patterns: Identify growth, decline, and acceleration early. Early detection enables proactive ordering.

3. Seasonality matters: Account for seasonal patterns. Products have different demand patterns throughout the year.

4. Forecasts should be dynamic: Update forecasts as new data arrives. Weekly or daily updates keep predictions accurate.

5. Reorder logic combines multiple factors: Consider forecasted demand, lead times, safety stock, and current inventory. No single factor is sufficient.

The architecture described here is repeatable. Start with basic sales aggregation, add trend detection, then implement seasonal adjustments and reorder logic. Each stage builds on the previous one.

Most importantly: this isn't about replacing human judgment—it's about augmenting it. Inventory managers still make final decisions. The forecasting pipeline provides data-driven recommendations, reducing manual work and improving accuracy.

This architecture pattern is implemented in Pylar, but the concepts apply to any system forecasting inventory. The key is the aggregation → trend analysis → seasonal adjustment → forecasting → reorder recommendation pipeline.