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:

1. Trend Component: The long-term direction of sales (growth or decline)
2. 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
3. Holiday Effects: Specific spikes or drops around holidays
4. Special Events: Sales, promotions, or other company-specific events

Data Preparation and Exploration

Before building your model, you should:

1. Gather historical data: Collect at least 2-3 years of daily credit card sales data

2. 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

3. 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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 quarter

3. Machine Learning Approach with Feature Engineering

For more complex patterns, a gradient boosting approach with extensive feature engineering can be effective.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

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:

1. Evaluate at multiple levels:

   • Daily accuracy (MAPE, MAE, RMSE)
   • Weekly aggregation accuracy
   • Monthly aggregation accuracy
   • Quarterly aggregation accuracy

2. Use time series cross-validation:

   • Train on historical data
   • Test on one quarter
   • Move forward one quarter and repeat

3. Analyze errors by segment:

   • By day of week
   • By month
   • By holiday/non-holiday
   • By promotion/non-promotion periods

4. 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:

1. Start with Prophet for its ability to handle multiple seasonalities and holidays
2. Add custom quarterly seasonality to capture business cycle effects
3. Create a comprehensive holiday calendar specific to your business
4. Include day-of-week effects explicitly
5. Add external regressors for company-specific events and promotions
6. Aggregate daily predictions to quarterly level for final forecasts
7. Validate on historical quarters to ensure accuracy at the quarterly level

Monitoring and Updating

Once deployed, your model should be:

1. Regularly updated with new data (at least monthly)
2. Monitored for drift in accuracy
3. Adjusted for new patterns that emerge
4. Supplemented with human judgment for unexpected events

Potential Improvements

1. Incorporate Real-time Data: Use real-time transaction data to improve the timeliness and accuracy of forecasts.
2. Leverage Advanced Machine Learning Techniques: Explore deep learning models like LSTM for capturing complex temporal dependencies.
3. Integrate External Economic Indicators: Include macroeconomic variables such as GDP growth, unemployment rates, and consumer confidence indices to enhance model robustness.
4. Enhance Feature Engineering: Continuously refine feature sets based on emerging patterns and business insights.
5. Implement Automated Model Selection: Use automated machine learning (AutoML) tools to streamline model selection and hyperparameter tuning.
6. 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.

• Credit Card Sales
• Time Series Forecasting
• Prophet
• SARIMA
• Machine Learning
• Seasonality
• Holidays
• Quarterly Forecasting