top of page

Forecasting Customer Demand 6 Months Ahead: A Real-World Sales Forecasting Project

  • Jun 30
  • 7 min read


How I turned 3 years of raw transaction data into a forward-looking planning tool for a tourism & travel company — with full code, real results, and honest limitations.

The Problem

Orient Group, a tourism and travel company in Egypt, had no systematic way to anticipate customer demand. Staffing decisions, marketing pushes, and operational planning were all reactive — made after the numbers came in, not before.

The business question was simple to ask and hard to answer without data:

Based on past customer behavior, how many customers should we expect each week for the next 6 months — and which months need the most attention?

This post walks through the full project: the business reasoning, the actual code at every stage, the statistical results, and what they mean in plain terms.

The Data

  • Source: Internal transaction system (real production data, not a sample dataset)

  • Size: 20,914 customer records

  • Time range: 2023 – May 2026

  • Granularity: Daily transactions, aggregated to weekly customer counts for forecasting

Step 1 — Load and Clean the Data

The raw export had Arabic column names, inconsistent date formats, and placeholder dates (1900-01-01) used by the source system to represent "no value."

import pandas as pd
import numpy as np
import statsmodels.api as sm
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
import matplotlib.pyplot as plt
import seaborn as sns

# Load raw data
df = pd.read_csv(r'path_to_source_file.csv')

# Remove duplicates and fix data types
df = df.drop_duplicates()
df['DayDate'] = pd.to_datetime(df['DayDate'])
df['winning_date'] = pd.to_datetime(df['DeliveryDate'])
df['travel_date'] = pd.to_datetime(df['DeliveryDate2'])
df['DeliveryDate'] = pd.to_datetime(df['DeliveryDate'])
df['DeliveryDate2'] = pd.to_datetime(df['DeliveryDate2'])

# 1900-01-01 is a null placeholder in the source system — flag it properly
df['travel_status'] = df['DeliveryDate2'].apply(
    lambda x: "travelled" if x > pd.Timestamp('1900-01-01') else "not_travelled")
df['winning_status'] = df['DeliveryDate'].apply(
    lambda x: "winner" if x > pd.Timestamp('1900-01-01') else "not_winner")

Why this matters for business: messy dates and undocumented placeholder values are the most common reason forecasting projects fail before they even start. Getting this step right is what makes every number downstream trustworthy.

Step 2 — Build the Weekly Time Series

A forecasting model needs one row per time period, not one row per transaction. So the first real transformation is collapsing daily transactions into weekly customer counts.

# Build week and year identifiers
df_clean = df.copy()
df_clean['week_number'] = df_clean['DayDate'].dt.isocalendar().week
df_clean['year'] = df_clean['DayDate'].dt.isocalendar().year
df_clean['year_week'] = (df_clean['year'].astype(str) 
                          + df_clean['week_number'].astype(str).str.zfill(2))
df_clean['month'] = df_clean['DayDate'].dt.strftime('%b')

# Count customers per week
customer_count = df_clean.groupby('year_week').size()

df_forecast = df_clean[['year', 'year_week', 'month', 'week_number']].copy()
df_forecast['customer_counts'] = df_forecast['year_week'].map(customer_count)

This gives a clean weekly table: each row is one week, with the number of customers that came in during that week.

Step 3 — Feature Engineering: Seasonality + Momentum

Two things drive customer volume in this business:

  1. The season (month) — some months are naturally stronger or weaker.

  2. Recent momentum — demand doesn't reset to zero every week. If last week was busy, this week is likely to be busy too.

# Momentum features: last week and the week before
df_forecast['lag_1'] = df_forecast['customer_counts'].shift(1)   # last week
df_forecast['lag_2'] = df_forecast['customer_counts'].shift(2)   # two weeks ago

# Encode months as dummy variables (April is the baseline / reference month)
df_dummies = pd.get_dummies(df_forecast['month'], drop_first=True).astype(int)
df_forecast = pd.concat([df_forecast, df_dummies], axis=1).dropna()
df_forecast.drop(columns='month', inplace=True)

Why drop one month (April)? Including all 12 months as separate variables would create a mathematical conflict (multicollinearity) — the model can't distinguish a month from "all months combined." One month has to serve as the baseline that every other month is compared against. April was kept as that baseline because it's the natural reference point in the data.

Step 4 — Train / Test Split

Before trusting any model, it has to be tested on data it hasn't seen.

x = df_forecast.drop(columns=['customer_counts', 'week_number', 'year_week'])
y = df_forecast['customer_counts'].astype(float)
x = x.astype(float)

x_train, x_test, y_train, y_test = train_test_split(
    x, y, test_size=0.3, random_state=42)

x = sm.add_constant(x)

70% of the data trains the model, 30% is held back purely to check honesty — how well does it perform on weeks it never saw during training?

Step 5 — Train the Forecasting Model

model = sm.OLS(y, x)
result = model.fit()
print(result.summary())

Final model results:

Metric

Value

R-squared

0.696

F-statistic

3,993 (highly significant)

Observations

20,912

All variables

Statistically significant (p < 0.001)

In plain terms: the model explains about 70% of the variation in weekly customer counts. The remaining 30% comes from factors outside the data — pricing changes, promotions, competitor activity, economic conditions — things no historical pattern alone can capture.

What drives the forecast (coefficients)

Driver

Effect

Meaning

Year (trend)

+72.5 customers/year

Steady underlying growth, independent of season

Last week's volume (lag_1)

Strong positive effect

~38% of last week carries into this week

Two weeks ago (lag_2)

Smaller positive effect

Momentum fades but doesn't disappear

November

Best month

Highest customer volume relative to baseline

March

Weakest month

Lowest customer volume relative to baseline

Step 6 — Honest Model Evaluation

This is the step most forecasting projects skip — and it's the most important one for trust.

x_test_const = sm.add_constant(x_test, has_constant='add')
y_pred = result.predict(x_test_const)

mae  = mean_absolute_error(y_test, y_pred)
rmse = np.sqrt(mean_squared_error(y_test, y_pred))
r2   = r2_score(y_test, y_pred)
mape = np.mean(np.abs((y_test - y_pred) / y_test)) * 100

print(f"MAE:  {mae:.2f}")
print(f"RMSE: {rmse:.2f}")
print(f"R²:   {r2:.3f}")
print(f"MAPE: {mape:.2f}%")

Results on unseen test data:

Metric

Result

Plain-English Meaning

MAE

62.87

On average, the weekly forecast is off by about 63 customers

RMSE

85.23

Confirms the average error, slightly weighted toward larger misses

R²

0.684

Consistent with training — the model isn't overfitting

MAPE

49.37%

Percentage error is larger in slow weeks (small denominators inflate the %) and much smaller in busy weeks — this is a known limitation of week-level % error and improves significantly at the monthly level

Why publish the MAPE honestly instead of hiding it? A forecasting tool is only useful if the people using it understand its real boundaries. The model is strong for spotting trends and seasonal direction — it should not be read as a guaranteed exact headcount for any single week.

Step 7 — Forecasting the Next 6 Months

With a validated model, the next step is predicting forward. Because the model depends on "last week's volume," the forecast has to be generated one week at a time, feeding each prediction back in as the next week's lag value.

future_dates = pd.date_range(
    start=df['DayDate'].max() + pd.Timedelta(weeks=1),
    periods=26, freq='W')

future_df = pd.DataFrame({'date': future_dates})
future_df['year'] = future_df['date'].dt.year
future_df['month'] = future_df['date'].dt.month_name().str[:3]

for m in ['Aug','Feb','Jan','Jul','Jun','Mar','Nov','Oct','Sep']:
    future_df[m] = (future_df['month'] == m).astype(int)

lag1 = df_forecast['customer_counts'].iloc[-1]
lag2 = df_forecast['customer_counts'].iloc[-2]
preds = []

for _, row in future_df.iterrows():
    x_vals = [1, row['year'], lag1, lag2, row['Aug'], row['Feb'], row['Jan'],
              row['Jul'], row['Jun'], row['Mar'], row['Nov'], row['Oct'], row['Sep']]
    pred = round(result.params @ x_vals)
    preds.append(pred)
    lag2, lag1 = lag1, pred

future_df['forecast'] = preds


Step 8 — Adding a Confidence Range (Not Just a Single Number)

A single forecasted number gives false confidence. A range is more honest and more useful for planning two scenarios: conservative and optimistic.

lag1 = df_forecast['customer_counts'].iloc[-1]
lag2 = df_forecast['customer_counts'].iloc[-2]
preds, se_obs_list = [], []

for _, row in future_df.iterrows():
    x_vals = [1, row['year'], lag1, lag2, row['Aug'], row['Feb'], row['Jan'],
              row['Jul'], row['Jun'], row['Mar'], row['Nov'], row['Oct'], row['Sep']]
    x_df = pd.DataFrame([x_vals], columns=result.params.index)

    pred_obj = result.get_prediction(x_df)
    pred_summary = pred_obj.summary_frame(alpha=0.05)  # 95% confidence

    pred_val = pred_summary['mean'].values[0]
    se_obs = pred_obj.se_obs[0]

    preds.append(round(pred_val))
    se_obs_list.append(se_obs)
    lag2, lag1 = lag1, round(pred_val)

future_df['forecast'] = preds
future_df['se_obs'] = se_obs_list

z_score = 1.96
future_df['lower_bound'] = np.maximum(0, np.round(future_df['forecast'] - z_score * future_df['se_obs']))
future_df['upper_bound'] = np.round(future_df['forecast'] + z_score * future_df['se_obs'])
Note on AI usage: the confidence interval calculation block above was optimized with AI assistance for code efficiency. The forecasting logic, model, feature engineering, and evaluation throughout the rest of this project were built independently.

The Results: 6-Month Forecast

Monthly Forecast with 95% Confidence Range

month_order = ['May','Jun','Jul','Aug','Sep','Oct','Nov']

monthly_stats = future_df.groupby('month', sort=False).agg(
    forecast=('forecast', 'sum'),
    variance=('se_obs', lambda x: np.sum(x**2))
).reindex(month_order).reset_index()

z_score = 1.96
monthly_stats['lower_bound'] = np.maximum(0, np.round(monthly_stats['forecast'] - z_score * np.sqrt(monthly_stats['variance'])))
monthly_stats['upper_bound'] = np.round(monthly_stats['forecast'] + z_score * np.sqrt(monthly_stats['variance']))

Month

Expected Customers

Likely Range (95%)

June

1,271

942 – 1,600

July

1,267

938 – 1,596

August

2,041 🟢

1,673 – 2,409

September

1,311

982 – 1,640

October

1,355

1,026 – 1,684

November

1,325

1,040 – 1,610

August stands out as a clear peak month in this forecast window — well above the surrounding months.

Chart 1: Monthly Forecast Trend


This chart shows the base forecast trajectory — a moderate plateau through June and July, a sharp peak in August, then a stabilization in the 1,300-range for the rest of the period.

Chart 2: Forecast with Confidence Range

The shaded band shows the realistic uncertainty around each monthly prediction. Notice the range widens significantly around the August peak — when demand swings higher, the uncertainty around the exact number grows too. This is expected and important context for planning: treat August as "high demand, plan generously," not as "exactly 2,041."

What This Means for the Business

1. April and November are consistently strong, with August emerging as a sharp seasonal peak in the forecast window. Marketing budget and staffing should scale up around these periods rather than being spread evenly across the year.

2. February and March are historically the weakest months. These are better suited for retention campaigns and cost control rather than aggressive acquisition spend.

3. There's a steady year-over-year growth trend (~72 additional customers per year, independent of seasonality) — confirming the business is growing organically, not just riding seasonal cycles.

4. The forecast comes with an honest range, not a false-precision single number. This is the difference between a number that looks impressive and a number leadership can actually plan around.

Recommendations Going Forward

  1. Re-run the model monthly as new data comes in, so the forecast stays current.

  2. Use the confidence range when presenting to leadership — it sets realistic expectations.

  3. Add more business drivers over time (marketing spend, pricing changes, promotions calendar, competitor activity) to push accuracy beyond the current 70% R².

  4. Connect the output to a live Power BI dashboard so decision-makers always see the latest forecast without manual updates.

  5. Address the residual autocorrelation (Durbin-Watson ≈ 1.74, improved from 0.92 after adding momentum features) — a more advanced time-series model (SARIMA or Prophet) could close this gap further.

Technical Stack

Tool

Purpose

Python (pandas, numpy)

Data cleaning & weekly aggregation

statsmodels

Forecasting model, statistical validation, confidence intervals

scikit-learn

Train/test split & accuracy evaluation

matplotlib / seaborn

Visualization

VS Code + Jupyter

Development environment

Closing Thought

The most useful forecasts aren't the ones that claim perfect accuracy — they're the ones that are honest about their limits and still give decision-makers a real edge over guessing. This model won't predict an exact headcount for any single week, but it reliably tells the business which months to prepare for and how much growth to expect — and that's enough to plan ahead instead of reacting after the fact.

Project built on real production data from Orient Group (Tourism & Travel, Egypt) | June 2026

 
 
 

Comments

Rated 0 out of 5 stars.
No ratings yet
Add a rating
bottom of page