Seasonality Detection & Analysis

Learn how to detect and work with seasonal patterns in your time series data.

What is Seasonality?

Seasonality is a repeating pattern that occurs at regular intervals.

Common Examples

Example	Pattern	Period
Retail sales	Higher in December, lower in summer	12 months (yearly)
Website traffic	Higher during 9 AM-5 PM	24 hours (daily)
Restaurant reservations	Higher on weekends	7 days (weekly)
Energy usage	Higher in summer (AC) and winter (heating)	12 months (yearly)
Air travel	Higher on business days (Mon-Fri)	7 days (weekly)
E-commerce	Spikes on paydays, holidays	Variable

Visual Recognition

No Seasonality (random noise)
value
    │   ◦  ◦   ◦    ◦  ◦ ◦
    │  ◦   ◦    ◦  ◦    ◦
    ├──────────────────────► time


Weekly Seasonality (spikes every 7 days)
value
    │  /\    /\    /\    /\
    │ /  \  /  \  /  \  /  \
    ├──────────────────────► time


Seasonal + Trend (pattern with growth)
value
    │      /\      /\
    │     /  \    /  \
    │    /    \  /    \
    │   /      \/      \
    ├──────────────────────► time

---

Types of Seasonality

Single Seasonality

One repeating pattern.

Daily retail sales with weekly pattern
(peaks every 7 days)

Pattern repeats: 52 times per year
Seasonal period: 7 days

Multiple Seasonality

Multiple overlapping patterns at different scales.

E-commerce website traffic:
- Hourly pattern: Higher 9 AM-5 PM (business hours)
- Daily pattern: Higher weekdays than weekends
- Weekly pattern: Monday-Friday peak
- Yearly pattern: Holiday shopping spikes

The 4 patterns combine to create complex data

Sub-Annual Seasonality

Patterns within a year.

Seasonal period: 7 (weekly)
Seasonal period: 24 (hourly)
Seasonal period: 4 (quarterly)

Annual Seasonality

Patterns yearly.

Seasonal period: 12 (monthly data repeating yearly)
Seasonal period: 365 (daily data repeating yearly)

---

Detecting Seasonality

Method 1: Visual Inspection

-- Plot your data visually
SELECT
    date,
    sales,
    DAYNAME(date) as day_of_week,
    MONTH(date) as month
FROM sales_data
ORDER BY date;

Look for:

• Repeating peaks and valleys
• Patterns that recur at fixed intervals
• Waves that return at regular intervals

Method 2: Autocorrelation Function (ACF)

Shows correlation between values at different time lags.

If data has seasonality with period 7:
lag 7 will show HIGH correlation
lag 14 will show HIGH correlation (2 periods)
lag 6 will show LOW correlation (not a period)

Method 3: AnoFox Forecast Detection

-- Automatic seasonality detection
SELECT * FROM TS_DETECT_SEASONALITY(
    'your_table',
    'date_column',
    'value_column',
    {'method': 'auto'}  -- Detects common periods
);

Output:

is_seasonal | detected_period | strength | p_value
-----------+-----------------+----------+--------
true       | 7               | 0.87     | 0.001

---

Understanding Seasonality Results

is_seasonal

Is there a significant seasonal pattern?

• true = Pattern detected
• false = No pattern found

detected_period

How often does the pattern repeat?

• 7 = Weekly (repeats every 7 days)
• 12 = Monthly (repeats every 12 months)
• 24 = Hourly (repeats every 24 hours)

strength

How strong is the seasonality (0 to 1)?

• 0.0 = No seasonality
• 0.5 = Moderate seasonality
• 0.9+ = Strong seasonality

Example interpretation:

• Strength 0.15 = Weak seasonality (noise-like)
• Strength 0.50 = Clear seasonality
• Strength 0.85 = Very strong seasonality

p_value

Statistical significance.

• p_value < 0.05 = Seasonality is statistically significant
• p_value > 0.05 = Could be noise, not real seasonality

---

Seasonality in Forecasting

Why Seasonality Matters

Models that ignore seasonality produce bad forecasts:

Actual sales:  100 90 110 95 105 100 110 95 105 100
Without seasonality detection:
Forecast:      100 100 100 100 100 100 100 100 100
Forecast misses the weekly oscillation!

With seasonality detection:
Forecast:      110 95 105 100 110 95 105 100 110 95
Forecast captures the weekly pattern!

Seasonal Period Parameter

When forecasting, tell the model the seasonal period:

SELECT * FROM TS_FORECAST(
    'sales_data',
    'date',
    'sales',
    'AutoETS',
    28,
    {'seasonal_period': 7}  -- Weekly seasonality
);

Common seasonal periods:

Daily data:
  - 7 (weekly pattern)
  - 365 (yearly pattern)

Hourly data:
  - 24 (daily pattern)
  - 168 (weekly = 24 * 7)

Monthly data:
  - 12 (yearly pattern)

---

Decomposing Seasonality

Break down a time series into components:

STL Decomposition

Separates:

1. Trend: Long-term direction
2. Seasonal: Repeating pattern
3. Remainder: Noise/irregular variation

SELECT * FROM TS_STL_DECOMPOSITION(
    'sales_data',
    'date',
    'sales',
    {'seasonal_period': 7}
);

Output schema:

date | sales | trend | seasonal | remainder
-----|-------|-------|----------|----------
2024-01-01 | 100 | 98 | 5 | -3
2024-01-02 | 95 | 98 | -2 | -1
2024-01-03 | 108 | 99 | 8 | 1
...

Interpretation:

• Trend column: The underlying trend direction
• Seasonal column: The repeating seasonal effect
• Remainder column: Unpredictable variation/noise

MSTL Decomposition (Multiple Seasonality)

For data with multiple overlapping patterns:

SELECT * FROM TS_MSTL_DECOMPOSITION(
    'traffic_data',
    'timestamp',
    'visitors',
    {'seasonal_periods': [24, 168]}  -- Hourly + weekly
);

---

Working with Seasonal Data

Seasonal Naive Forecast

Simplest seasonal model: repeat last year's values.

SELECT * FROM TS_FORECAST(
    'sales_data',
    'date',
    'sales',
    'SeasonalNaive',
    28,
    {'seasonal_period': 7}  -- Repeat last week
);

How it works:

Last week: Mon=100, Tue=95, Wed=110, Thu=105, Fri=120, Sat=130, Sun=90
Next week forecast: Mon=100, Tue=95, Wed=110, Thu=105, Fri=120, Sat=130, Sun=90

Deseasonalizing Data

Remove seasonality to see trends more clearly:

CREATE TABLE deseasonalized AS
SELECT
    date,
    sales,
    seasonal,
    sales - seasonal as deseasonalized_sales
FROM TS_STL_DECOMPOSITION(
    'sales_data',
    'date',
    'sales',
    {'seasonal_period': 7}
);

---

Multiple Seasonality Example

E-commerce website with daily + weekly + yearly seasonality:

Data observations:
- Visitors spike at 3 PM every day (24-hour seasonality)
- Visitors higher Mon-Fri than weekend (7-day seasonality)
- Visitors spike in November-December (365-day seasonality)

Detection:
TS_DETECT_SEASONALITY_ALL() finds: 24, 7, 365

Forecast with multiple seasonality:

SELECT * FROM TS_FORECAST(
    'traffic_data',
    'timestamp',
    'visitors',
    'MSTL',  -- Model for multiple seasonality
    168,     -- Forecast 1 week (7 days * 24 hours)
    {'seasonal_periods': [24, 7]}  -- Hourly + weekly
);

---

Common Seasonality Mistakes

❌ Wrong Seasonal Period

-- WRONG: Data is hourly, but using monthly period
SELECT * FROM TS_FORECAST(..., {'seasonal_period': 12});

-- RIGHT: Hourly data likely has daily (24) seasonality
SELECT * FROM TS_FORECAST(..., {'seasonal_period': 24});

❌ Not Enough Data for Detected Period

-- WRONG: Only 14 days of data, but detecting 365-day seasonality
-- Can't learn yearly pattern with < 1 year data!

-- RIGHT: Have at least 2 full seasonal cycles
-- For 365-day seasonality, need 2 years of data (730 days)

❌ Ignoring Seasonality

-- WRONG: Ignoring the seasonal_period parameter
SELECT * FROM TS_FORECAST(
    'weekly_sales',
    'date',
    'sales',
    'AutoETS',
    28,
    {}  -- No seasonal_period specified!
);

-- RIGHT: Specify seasonal period
SELECT * FROM TS_FORECAST(
    'weekly_sales',
    'date',
    'sales',
    'AutoETS',
    28,
    {'seasonal_period': 7}
);

---

Handling Changing Seasonality

Problem: Seasonality Changes Over Time

Year 1: Strong weekly pattern (peak on Friday)
Year 2: Work-from-home starts → Pattern shifts (peak on Wednesday)
Year 3: Return-to-office → Pattern reverts

Solutions

1. Retrain regularly (weekly/monthly)

-- Retrain model with recent data every week
-- Use only last 6 months of data to capture current patterns
CREATE TABLE recent_sales AS
SELECT * FROM sales_data
WHERE date >= TODAY() - INTERVAL '6 months';

2. Use adaptive models

-- ETS and MFLES automatically adapt to changing patterns
SELECT * FROM TS_FORECAST(..., 'AutoETS', ..., {...});

3. Monitor seasonality changes

-- Periodically check if seasonality strength changed
SELECT * FROM TS_DETECT_SEASONALITY(...)
WHERE date >= TODAY() - INTERVAL '1 month';

---

Quick Reference

Task	Function	Parameter
Detect seasonality	TS_DETECT_SEASONALITY	method='auto'
Decompose series	TS_STL_DECOMPOSITION	seasonal_period=7
Multiple seasonality	TS_MSTL_DECOMPOSITION	seasonal_periods=[24,168]
Forecast with seasonality	TS_FORECAST	seasonal_period=7
Seasonal naive	TS_FORECAST with 'SeasonalNaive'	seasonal_period=7

---

Next Steps

1. Detecting Patterns in Your Data — Practical guide to detect seasonality
2. Model Selection — Choose models for seasonal data
3. Data Preparation — Handle seasonal data issues
4. API Reference — All seasonality functions

---

Key Takeaways

• ✅ Seasonality = Repeating patterns at fixed intervals
• ✅ Detect automatically with TS_DETECT_SEASONALITY
• ✅ Always specify seasonal_period in forecasts
• ✅ Multiple seasonality possible (hourly + daily + yearly)
• ✅ STL/MSTL decomposition shows components
• ✅ Need 2+ full seasonal cycles of data for accurate detection
• ✅ Retrain periodically if seasonality changes