Predicting Credit Card Sales: A Comprehensive Guide to Quarterly Forecasting #
Understanding the Key Components of Credit Card Sales Time Series #
Credit card sales data typically exhibits several important patterns that must be accounted for in your forecasting model:
- Trend Component: The long-term direction of sales (growth or decline)
- Seasonal Components:
- Weekly Seasonality: Different spending patterns by day of week
- Monthly Seasonality: Monthly payment cycles and salary effects
- Quarterly Seasonality: Business cycles and quarterly reporting effects
- Annual Seasonality: Yearly patterns like holiday shopping seasons
- Holiday Effects: Specific spikes or drops around holidays
- Special Events: Sales, promotions, or other company-specific events
Data Preparation and Exploration #
Before building your model, you should:
-
Gather historical data: Collect at least 2-3 years of daily credit card sales data
-
Perform exploratory data analysis:
- Plot the time series to identify obvious patterns
- Create day-of-week, month, and quarter aggregations to understand seasonality
- Calculate year-over-year growth rates to understand the trend
- Identify outliers and anomalies
-
Create calendar features:
- Day of week (1-7)
- Day of month (1-31)
- Month of year (1-12)
- Quarter (1-4)
- Is weekend (binary)
- Is holiday (binary)
- Days before/after major holidays
Modeling Approaches #
1. Facebook Prophet #
Prophet is particularly well-suited for credit card sales forecasting because it handles multiple seasonalities and holiday effects out of the box.
from prophet import Prophet
import pandas as pd
# Prepare data in Prophet format (ds for dates, y for values)
df = pd.DataFrame({
'ds': date_column,
'y': sales_column
})
# Create holiday dataframe
holidays = pd.DataFrame({
'holiday': ['Black Friday', 'Black Friday', ...],
'ds': pd.to_datetime(['2022-11-25', '2023-11-24', ...]),
'lower_window': [-1, -1, ...], # Days before holiday to include
'upper_window': [1, 1, ...] # Days after holiday to include
})
# Initialize model with weekly and yearly seasonality
model = Prophet(
yearly_seasonality=True,
weekly_seasonality=True,
daily_seasonality=False, # Enable if you have intraday data
holidays=holidays,
seasonality_mode='multiplicative', # Use if holiday effects scale with trend
changepoint_prior_scale=0.05, # Controls flexibility of trend
holidays_prior_scale=10.0 # Controls flexibility of holiday effects
)
# Add quarterly seasonality
model.add_seasonality(
name='quarterly',
period=365.25/4,
fourier_order=5
)
# Add custom regressors for special events or promotions
if 'promotion' in df.columns:
model.add_regressor('promotion')
# Fit model
model.fit(df)
# Create future dataframe for prediction (e.g., next quarter)
future = model.make_future_dataframe(periods=90) # 90 days for a quarter
# Add any regressor values to future dataframe
if 'promotion' in df.columns:
future['promotion'] = future_promotion_values
# Make prediction
forecast = model.predict(future)
# Aggregate to quarterly level
quarterly_forecast = forecast.set_index('ds').resample('Q')['yhat'].sum()2. SARIMA (Seasonal ARIMA) with External Regressors #
SARIMA models can capture multiple seasonal patterns and can be extended with external regressors for holidays.
import pandas as pd
import numpy as np
from statsmodels.tsa.statespace.sarimax import SARIMAX
# Create dummy variables for days of week
for i in range(7):
df[f'dow_{i}'] = (df['date'].dt.dayofweek == i).astype(int)
# Create dummy variables for months
for i in range(1, 13):
df[f'month_{i}'] = (df['date'].dt.month == i).astype(int)
# Create holiday dummies
holidays = ['Christmas', 'New_Year', 'Thanksgiving', ...]
for holiday in holidays:
df[holiday] = 0 # Initialize
# Set to 1 for holiday dates (and potentially days before/after)
# ...
# Fit SARIMA model with exogenous variables
model = SARIMAX(
df['sales'],
exog=df[['dow_1', 'dow_2', ..., 'month_1', ..., 'Christmas', ...]],
order=(1, 1, 1), # Non-seasonal part (p, d, q)
seasonal_order=(1, 1, 1, 7), # Seasonal part (P, D, Q, s) - weekly
)
results = model.fit()
# Create future exogenous variables
future_exog = pd.DataFrame()
# Fill with future day of week, month, holiday values
# ...
# Forecast
forecast = results.forecast(steps=90, exog=future_exog) # 90 days for a quarter3. Machine Learning Approach with Feature Engineering #
For more complex patterns, a gradient boosting approach with extensive feature engineering can be effective.
import pandas as pd
import numpy as np
from lightgbm import LGBMRegressor
from sklearn.model_selection import TimeSeriesSplit
# Feature engineering
def create_features(df):
df = df.copy()
# Date features
df['dayofweek'] = df['date'].dt.dayofweek
df['month'] = df['date'].dt.month
df['year'] = df['date'].dt.year
df['dayofyear'] = df['date'].dt.dayofyear
df['quarter'] = df['date'].dt.quarter
df['is_weekend'] = df['dayofweek'].isin([5, 6]).astype(int)
# Lag features (previous day, week, month, quarter)
df['sales_lag1'] = df['sales'].shift(1)
df['sales_lag7'] = df['sales'].shift(7)
df['sales_lag30'] = df['sales'].shift(30)
df['sales_lag90'] = df['sales'].shift(90)
# Rolling window features
df['sales_rolling_7d_mean'] = df['sales'].rolling(7).mean().shift(1)
df['sales_rolling_30d_mean'] = df['sales'].rolling(30).mean().shift(1)
# Holiday features (one-hot encoded)
# ...
# Days before/after major holidays
# ...
return df
# Prepare data
features = create_features(df)
features = features.dropna() # Drop rows with NaN from lag features
# Define features and target
X = features.drop(['date', 'sales'], axis=1)
y = features['sales']
# Time series cross-validation
tscv = TimeSeriesSplit(n_splits=5)
# Train model
model = LGBMRegressor(
n_estimators=1000,
learning_rate=0.05,
max_depth=5
)
model.fit(X, y)
# Create future features for prediction
future_features = create_features(future_df)
future_X = future_features.drop(['date', 'sales'], axis=1)
# Predict
predictions = model.predict(future_X)
# Aggregate to quarterly level
future_df['predictions'] = predictions
quarterly_forecast = future_df.set_index('date').resample('Q')['predictions'].sum()Handling Specific Challenges #
1. Handling Holidays #
Holidays have a significant impact on credit card sales and require special attention:
- Create a comprehensive holiday calendar: Include not just official holidays but also shopping events (Black Friday, Cyber Monday)
- Account for holiday shifts: Some holidays fall on different dates each year (Easter, Thanksgiving)
- Consider pre-holiday and post-holiday effects: Shopping often increases before holidays and decreases after
- Differentiate holiday types: Some holidays increase spending (Christmas), while others may decrease it (Labor Day)
2. Handling Day-of-Week Effects #
Day-of-week patterns are crucial for credit card sales:
- Weekday vs. weekend spending: Consumer behavior differs significantly
- Payday effects: Spending often increases around common paydays (1st, 15th, last day of month)
- Different day-of-week patterns by industry: Restaurants peak on weekends, while business services peak mid-week
3. Handling Seasonality #
Multiple seasonal patterns affect credit card sales:
- Weekly seasonality: Capture with 7-day cycles
- Monthly seasonality: Capture with 30-day cycles (bill payment patterns)
- Quarterly seasonality: Capture with 90-day cycles (business reporting cycles)
- Annual seasonality: Capture with 365-day cycles (holiday shopping, summer vacation)
4. Handling Special Events #
Company-specific events can significantly impact sales:
- Marketing campaigns: Include timing and scale of campaigns as features
- Promotions and discounts: Include as binary or continuous features
- Product launches: Include as binary features with potential decay effects
Evaluation and Refinement #
To ensure your model is accurate for quarterly forecasting:
-
Evaluate at multiple levels:
- Daily accuracy (MAPE, MAE, RMSE)
- Weekly aggregation accuracy
- Monthly aggregation accuracy
- Quarterly aggregation accuracy
-
Use time series cross-validation:
- Train on historical data
- Test on one quarter
- Move forward one quarter and repeat
-
Analyze errors by segment:
- By day of week
- By month
- By holiday/non-holiday
- By promotion/non-promotion periods
-
Refine the model iteratively:
- Add features for periods with high error
- Adjust holiday windows
- Tune model hyperparameters
Implementation Recommendations #
For your specific goal of predicting full quarter sales:
- Start with Prophet for its ability to handle multiple seasonalities and holidays
- Add custom quarterly seasonality to capture business cycle effects
- Create a comprehensive holiday calendar specific to your business
- Include day-of-week effects explicitly
- Add external regressors for company-specific events and promotions
- Aggregate daily predictions to quarterly level for final forecasts
- Validate on historical quarters to ensure accuracy at the quarterly level
Monitoring and Updating #
Once deployed, your model should be:
- Regularly updated with new data (at least monthly)
- Monitored for drift in accuracy
- Adjusted for new patterns that emerge
- Supplemented with human judgment for unexpected events
Potential Improvements #
- Incorporate Real-time Data: Use real-time transaction data to improve the timeliness and accuracy of forecasts.
- Leverage Advanced Machine Learning Techniques: Explore deep learning models like LSTM for capturing complex temporal dependencies.
- Integrate External Economic Indicators: Include macroeconomic variables such as GDP growth, unemployment rates, and consumer confidence indices to enhance model robustness.
- Enhance Feature Engineering: Continuously refine feature sets based on emerging patterns and business insights.
- Implement Automated Model Selection: Use automated machine learning (AutoML) tools to streamline model selection and hyperparameter tuning.
- Develop a Feedback Loop: Establish a feedback mechanism to incorporate forecast errors and improve model accuracy over time.
By following this comprehensive approach, you’ll be able to build a robust model for predicting credit card sales that accounts for seasonality, holidays, and day-of-week effects, ultimately providing accurate quarterly sales forecasts for your company.