How to Build a Sales Forecasting Capstone from Raw CSVs
For Data analysts moving into forecasting · Based on Simplilearn AI Capstone Project Architect
// TL;DR
Data analysts moving into forecasting should choose Track 3 of the AI Capstone Project Architect. If you have store, item, and transaction CSVs and must predict sales quantity over time, this track gives you a reproducible pipeline: merge DataFrames in two steps, derive a sales column (unit_price times item_count), aggregate to a daily series, split by time with the last 6 months as test, and compare Linear Regression, Random Forest, and XGBoost by RMSE. You'll finish with a champion model and a next-year forecast — a complete, defensible analytics project.
How do you combine three CSVs without breaking your data?
Never attempt a three-way merge in one call. Use the merge-in-two-steps pattern: merge the restaurants CSV with the items CSV on `store_id`, capture the result, then merge that result with the sales CSV on `item_id` to produce one unified DataFrame. Choose merge keys that exist in both DataFrames being joined at each step, and inspect row counts after each merge to catch dropped rows early.
Before merging, audit every CSV with `.info()`, `.head()`, and null and duplicate checks. Convert your date column to datetime immediately with `pd.to_datetime()`, and check for outliers using z-score or Isolation Forest so anomalous transactions don't skew your forecast.
Why can't you model raw price or quantity directly?
Because the forecasting target is total sales, not its components. Use the generate-then-aggregate approach: first create a row-level `sales` column equal to `unit_price × item_count`, then group by date and sum to produce a daily total sales series. All resampling and modelling flows from this aggregated series. Skipping this derivation and modelling raw price or item_count separately is a common, forecast-breaking mistake.
Once you have daily sales, use `.resample('W')`, `.resample('M')`, and `.resample('Q')` to produce weekly, monthly, and quarterly views, then plot each to reveal trends at different scales. Group by restaurant_id to rank stores by sales, and by item_id plus store_id to find the most popular item per store.
How do you split time-series data correctly?
Sort by date ascending, then use the last 6 months as your test set and all prior data as training. Never shuffle or randomly split time-series data — random splits leak future information into training and inflate your metrics dishonestly. The chronological split is what makes your forecast validation trustworthy.
Before splitting, extract time features from the datetime column using the `.dt` accessor: day_of_week, month, quarter, year, and day_of_month. These features let tree-based models capture seasonality and weekly cycles, and they become the predictors of your daily sales target.
How do you pick the best forecasting model?
Don't guess — run a three-model comparison. Train Linear Regression, Random Forest Regressor, and XGBoost Regressor in parallel on the same time features, all predicting daily sales. Evaluate each with RMSE and build a summary table comparing all three before declaring a champion. Select the lowest-RMSE model, then use it to forecast the next twelve months.
XGBoost often captures nonlinear interactions best, but Random Forest or even Linear Regression can win on some datasets — the RMSE table removes the guesswork. Finish by plotting actual vs predicted sales over the test period and the next-year forecast as a time series.
What documentation completes the project?
Report RMSE as your metric and explicitly present the three-model comparison table before naming your champion. Include the resampled trend plots, store rankings, and the next-year forecast chart. This transforms a modelling exercise into a defensible analytics deliverable that a stakeholder can act on.
Next step
Audit your three CSVs, convert dates to datetime, and run the merge-in-two-steps pattern. Derive your sales column, aggregate to daily, extract time features, and split chronologically. Then train all three regressors and let RMSE crown your champion.
// FREQUENTLY ASKED QUESTIONS
What if my merge keys don't match between CSVs?
Merge only DataFrames that share a key at each step — restaurants and items share store_id, while the result and sales share item_id. If keys don't align, verify column names and types match exactly, and check row counts after each merge. A large row drop usually means mismatched or missing keys in one of the two DataFrames.
Why is RMSE the right metric here instead of accuracy?
RMSE is the metric for regression tasks like sales forecasting, where you predict a continuous quantity. Accuracy applies only to classification. RMSE penalizes larger errors more heavily and is measured in the same units as sales, making it interpretable. Compare all three models' RMSE in a table, then pick the lowest as your champion.
Can I use more than 6 months as my test set?
The methodology specifies the last 6 months as test and all prior data as training, preserving temporal order. You can adjust the horizon to your business need, but never shuffle the split. Keep the test window at the chronological end so you're validating genuine future prediction, not leaking future patterns into training.