TensorFlow Time Series + BigQuery Analytics

Solution for the use case Inventory Demand Forecasting with Dynamic Pricing.

AI tools

TensorFlow, Google BigQuery, Cloud Functions, Google Cloud ML, Looker Studio

Language

Python

Accuracy

83%

Published

2025-07-05

Code (Python)

import tensorflow as tf
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import LSTM, Dense, Dropout
import pandas as pd
import numpy as np
from google.cloud import bigquery
from google.cloud import functions_v1
import joblib
from sklearn.preprocessing import MinMaxScaler
from sklearn.metrics import mean_absolute_percentage_error

class TimeSeriesForecaster:
    def __init__(self):
        self.model = None
        self.scaler = MinMaxScaler()
        self.lookback_window = 60  # 60 days of historical data
        self.forecast_horizon = 30  # 30 days ahead
        
        # Initialize BigQuery client
        self.bq_client = bigquery.Client()
        
    def prepare_features(self, sku, store_id, start_date, end_date):
        """Extract and prepare features from BigQuery"""
        
        query = f"""
        WITH sales_data AS (
          SELECT 
            DATE(transaction_date) as date,
            SUM(quantity) as daily_sales,
            AVG(unit_price) as avg_price,
            COUNT(DISTINCT transaction_id) as transaction_count
          FROM `project.dataset.sales_transactions`
          WHERE sku = '{sku}'
            AND store_id = '{store_id}'
            AND DATE(transaction_date) BETWEEN '{start_date}' AND '{end_date}'
          GROUP BY DATE(transaction_date)
        ),
        weather_data AS (
          SELECT 
            date,
            temperature,
            precipitation,
            humidity
          FROM `project.dataset.weather_data`
          WHERE store_location = (
            SELECT location FROM `project.dataset.stores` WHERE store_id = '{store_id}'
          )
        ),
        events_data AS (
          SELECT 
            event_date as date,
            event_type,
            impact_score
          FROM `project.dataset.local_events`
          WHERE store_location = (
            SELECT location FROM `project.dataset.stores` WHERE store_id = '{store_id}'
          )
        )
        SELECT 
          s.date,
          s.daily_sales,
          s.avg_price,
          s.transaction_count,
          EXTRACT(DAYOFWEEK FROM s.date) as day_of_week,
          EXTRACT(MONTH FROM s.date) as month,
          CASE WHEN h.holiday_name IS NOT NULL THEN 1 ELSE 0 END as is_holiday,
          w.temperature,
          w.precipitation,
          w.humidity,
          COALESCE(e.impact_score, 0) as event_impact,
          LAG(s.daily_sales, 1) OVER (ORDER BY s.date) as sales_lag_1,
          LAG(s.daily_sales, 7) OVER (ORDER BY s.date) as sales_lag_7,
          AVG(s.daily_sales) OVER (
            ORDER BY s.date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
          ) as sales_ma_7,
          AVG(s.daily_sales) OVER (
            ORDER BY s.date ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING  
          ) as sales_ma_30
        FROM sales_data s
        LEFT JOIN weather_data w ON s.date = w.date
        LEFT JOIN events_data e ON s.date = e.date
        LEFT JOIN `project.dataset.holidays` h ON s.date = h.holiday_date
        ORDER BY s.date
        """
        
        return self.bq_client.query(query).to_dataframe()
    
    def create_sequences(self, data, target_col):
        """Create sequences for LSTM training"""
        
        # Prepare features
        feature_cols = [col for col in data.columns if col not in ['date', target_col]]
        features = data[feature_cols].values
        target = data[target_col].values
        
        # Scale features
        features_scaled = self.scaler.fit_transform(features)
        
        X, y = [], []
        for i in range(self.lookback_window, len(features_scaled) - self.forecast_horizon + 1):
            X.append(features_scaled[i-self.lookback_window:i])
            y.append(target[i:i+self.forecast_horizon])
        
        return np.array(X), np.array(y)
    
    def build_model(self, input_shape):
        """Build LSTM model for time series forecasting"""
        
        model = Sequential([
            LSTM(128, return_se
…

Prompts

TIME_SERIES_PROMPT = """
Configure time series forecasting model for retail demand prediction:

Data Configuration:
- Target Variable: daily_sales_quantity
- Time Granularity: Daily forecasts with weekly/monthly aggregations
- Forecast Horizon: 30 days ahead with confidence intervals
- Training Window: 2 years historical data minimum

Feature Engineering:
1. Temporal Features:
   - Day of week (1-7)
   - Month of year (1-12) 
   - Holiday indicators (binary)
   - Season encoding (winter/spring/summer/fall)

2. Lagged Features:
   - Sales lag 1, 7, 14, 30 days
   - Moving averages (7, 14, 30 day windows)
   - Price changes from previous periods

3. External Features:
   - Weather data (temperature, precipitation)
   - Local events (concerts, sports, festivals)
   - Competitor pricing index
   - Economic indicators (consumer confidence)

Model Architecture:
- LSTM layers: 3 layers with 128, 64, 32 units
- Dropout: 0.2 between layers
- Dense output: 30 timesteps (forecast horizon)
- Loss function: Mean Absolute Percentage Error (MAPE)
- Optimizer: Adam with learning rate scheduling

Return training configuration and expected performance metrics.
"""

PRICING_OPTIMIZATION_PROMPT = """
Implement dynamic pricing strategy based on demand forecasts:

Input Parameters:
- Demand Forecast: {forecast_data}
- Current Inventory: {inventory_level}
- Cost Structure: {unit_cost}, {holding_cost}, {stockout_cost}
- Market Position: {competitor_prices}, {brand_premium}

Optimization Objectives:
1. Primary: Maximize revenue over forecast period
2. Secondary: Minimize inventory holding costs
3. Constraint: Maintain brand positioning (price bounds)
4. Constraint: Avoid stockouts (service level > 95%)

Algorithm Steps:
1. Calculate price elasticity from historical data
2. Simulate demand response for price ranges
3. Optimize using constrained optimization
4. Apply business rules and brand guidelines
5. Generate pricing recommendations with explanations

Output Format:
{
  "current_price":
…