咨询：如何利用data.table中的时序变量开展销售预测

Hey there! Let’s walk through how to make the most of those time-series variables in your data.table for accurate sales forecasting. I’ll break this down into practical, actionable steps based on your data structure:

The raw variables you have are great, but we need to transform them into features that models can interpret easily.

Date Variable #

• First, ensure your Date column is properly formatted as a date type:

  DT[, Date := as.Date(Date)]
  

• Derive core time-based features that drive sales:
  • Basic granularity: year, month, week, quarter, day_of_week (e.g., DT[, month := as.integer(format(Date, "%m"))])
  • Special time markers: is_weekend (check if day_of_week is 6 or 7), is_holiday (if you have holiday data to join in)
  • Relative time: Days since the start of the year, or days since the first date in your dataset to capture long-term trends.

Promo2 (week, year) + PromoInterval #

These two variables work together to tell you when a store is running its ongoing Promo2 campaigns.

• First, convert the Promo2(week, year) into a concrete start date:

  DT[, Promo2StartDate := ISOweek2date(paste(Promo2Year, Promo2Week, 1, sep = "-"))]
  

• Create a binary feature is_in_promo2 to flag if the current date falls within an active Promo2 period (after the start date and matching the PromoInterval):

  DT[, is_in_promo2 := mapply(function(date, start_date, interval) {
    if (is.na(start_date)) return(FALSE)
    if (date < start_date) return(FALSE)
    # Split interval into months and convert to numeric
    promo_months <- strsplit(interval, ",")[[1]]
    promo_month_nums <- match(promo_months, month.abb)
    return(as.integer(format(date, "%m")) %in% promo_month_nums)
  }, Date, Promo2StartDate, PromoInterval)]
  

• Add a feature for duration since Promo2 started:

  DT[, promo2_weeks_since_start := as.integer(difftime(Date, Promo2StartDate, units = "weeks"))]
  DT[is.na(promo2_weeks_since_start), promo2_weeks_since_start := 0] # Handle stores not in Promo2
  

  This helps capture if promo effectiveness fades or grows over time.

CompetitionOpenSince (Month, Year) #

This variable tells you when a competitor opened—critical for modeling sales cannibalization.

• Convert to a concrete opening date:

  DT[, CompetitionOpenDate := as.Date(paste(CompetitionOpenYear, CompetitionOpenMonth, 1, sep = "-"))]
  

• Create binary and duration features:
  • competition_active: Flag if the competitor is already open on the current date

    DT[, competition_active := Date >= CompetitionOpenDate]
    DT[is.na(competition_active), competition_active := FALSE] # No competitor = inactive
    

  • competition_months_since_open: Calculate how long the competitor has been open to capture evolving impact

    DT[, competition_months_since_open := as.integer(difftime(Date, CompetitionOpenDate, units = "days")) %/% 30]
    DT[is.na(competition_months_since_open), competition_months_since_open := 0]
    

• Single-store forecasting: If you’re predicting for individual stores, classic time-series models like ARIMA or Prophet work well—they naturally handle trends and seasonality. You can also feed your engineered features into a tree-based model (XGBoost/LightGBM) for better capture of non-linear relationships (like promo-competition interactions).
• Multi-store (panel) forecasting: For multiple stores, use hierarchical time-series models (like Facebook’s Hierarchical Forecasting) to account for store-level differences, or add store_id as a categorical feature in tree-based models.
• Avoid data leakage: Never randomly split your data into train/test sets—split chronologically (e.g., use all data before 2023-01-01 for training, and after for testing) to mimic real-world forecasting.

• Use time-series-specific metrics: MAE (Mean Absolute Error), RMSE (Root Mean Squared Error), or MAPE (Mean Absolute Percentage Error—note: avoid MAPE if sales can be zero).
• Use time-series cross-validation (instead of standard K-fold) for hyperparameter tuning. For example, in R, you can use tscv from the forecast package, or implement rolling window validation manually.
• Test feature interactions: Try adding features like is_in_promo2 * competition_active to capture how promotions perform when a competitor is present—this often reveals valuable non-linear effects.

内容的提问来源于stack exchange，提问作者Amir