Seasonality Routing — Test Sheet

SQL examples predate the v6 unique_id migration

The SQL snippets on this page use the legacy unique_id column, which was dropped from every table in the v6 migration. All series are now keyed by item_id + site_id — replace unique_id selection / filtering / joining / ORDER BY / GROUP BY with item_id, site_id (and the built-in item_name / site_name columns where applicable). This page is retained as a historical test artifact; do not copy its SQL verbatim.

Date: 2026-05-03 Pipeline tested: theBicycle, pipeline_id=4 Scope: Characterization params + forecasting method groups + seasonal period logic

---

TOC

1. What changed and why
2. Test 1 — Characterization params: min_strength
3. Test 2 — Seasonal period in code: max not first
4. Test 3 — Seasonal group methods in DB
5. Test 4 — recommended_methods updated after re-characterize
6. Test 5 — Forecast runs new methods for seasonal series
7. Test 6 — Best method distribution shifts
8. Test 7 — Specific series shows seasonal forecast shape
9. How to drive the test (step by step)

---

What changed and why

The overview forecast looked flat even though 391/575 series (76%) were flagged seasonal. Root cause: AutoETS was the only statistical competitor in the seasonal method group. AutoETS auto-selects ETS model variants by AIC — with period=52 (annual) it almost always picks a trend-only model because annual seasonal fitting needs 2+ full cycles (104+ weekly observations) and the AIC strongly favours simpler models on noisier data.

Three layers were fixed:

Layer	File / Table	Change
Characterization sensitivity	scenario.config_parameters ids 37, 36191, 36192	min_strength 0.3 → 0.2
Seasonal period selection	files/forecasting/statistical_models.py	seasonal_periods[0] → max(seasonal_periods): uses 52 (annual) not 4 (monthly) as primary period
Seasonal method group	scenario.config_parameters ids 39, 40, 41	Removed AutoETS + TimesFM; added AutoARIMA + AutoTheta to the seasonal group

Pipeline re-run required after this sheet: Characterize → Forecast → Best-method.

---

Test 1 — Characterization params: min_strength

Objective: Confirm min_strength=0.2 in all three characterization parameter sets. A series passes as seasonal when seasonal_strength >= min_strength. Before the fix this was 0.3, causing ~30% fewer seasonal detections.

-- Run against thebicycle (PostgreSQL)
SELECT id, name,
       parameters_set->'seasonality'->>'min_strength' AS min_strength,
       parameters_set->'seasonality'->'test_periods'  AS test_periods
FROM scenario.config_parameters
WHERE parameter_type = 'characterization'
ORDER BY id;

id	name	Before	After (expected)
37	Default	0.3	0.2
36191	TEST Alpha characterization	0.3	0.2
36192	TEST Beta characterization	0.3	0.2

Test_periods should be [4, 13, 26, 52] on all three rows.

---

Test 2 — Seasonal period in code: max not first

Objective: Confirm statistical_models.py uses the largest detected period (52 = annual) as the primary season_length, not the smallest (4 = monthly).

This is a code change, not a DB change. Verify by inspecting the file:

grep -n "max(_all_seasonal_periods)" files/forecasting/statistical_models.py

Expected line (around line 185-195):

season_length = max(_all_seasonal_periods) if _all_seasonal_periods else 1

Also verify MSTL gets all periods, not just one:

grep -n "_mstl_season_length" files/forecasting/statistical_models.py

Expected: _mstl_season_length = sorted([max(2, p) for p in _all_seasonal_periods])

Spot-check after re-running forecast — Chain Lube at Cycles Paris (unique_id=1_301):

-- ClickHouse: check MSTL forecast amplitude (seasonal = high amplitude)
SELECT method,
       round(min(point_forecast), 1) AS fc_min,
       round(max(point_forecast), 1) AS fc_max,
       round(max(point_forecast) - min(point_forecast), 1) AS amplitude
FROM PIPE_forecast_point_values
WHERE pipeline_id=4 AND scenario_id=4 AND unique_id='1_301'
  AND method IN ('MSTL', 'AutoARIMA', 'AutoETS', 'AutoTheta')
GROUP BY method
ORDER BY method;

method	Before (period=4 primary)	After (period=52 primary)
MSTL	amplitude ~8 (intra-month only)	amplitude ≥15 (annual + intra-month)
AutoARIMA	not in seasonal group	now in seasonal group
AutoETS	flat (trend only, ~0.5 range)	not in seasonal group

---

Test 3 — Seasonal group methods in DB

Objective: Confirm AutoETS and TimesFM are removed from the seasonal group and AutoARIMA + AutoTheta are present.

-- Run against thebicycle (PostgreSQL)
SELECT id, name,
       parameters_set->'method_selection'->'seasonal' AS seasonal_methods
FROM scenario.config_parameters
WHERE parameter_type = 'forecasting'
ORDER BY id;

id	name	Before	After (expected)
39	Default	["MSTL","AutoETS","NHITS","PatchTST","TimesFM"]	["MSTL","AutoARIMA","AutoTheta","NHITS","PatchTST"]
40	Fast movers	["MSTL","AutoETS","NHITS","PatchTST","TimesFM","LightGBM"]	["MSTL","AutoARIMA","AutoTheta","NHITS","PatchTST","LightGBM"]
41	Slow movers	["MSTL","AutoETS","NHITS","PatchTST","TimesFM","LightGBM"]	["MSTL","AutoARIMA","AutoTheta","NHITS","PatchTST","LightGBM"]

Standard and complex groups are unchanged — AutoETS stays for non-seasonal series.

---

Test 4 — recommended_methods updated after re-characterize

Objective: After re-running characterization, seasonal series must have AutoARIMA and AutoTheta in their recommended_methods list — and not AutoETS.

Step: Run characterization step (ProcessRunner → Forecast → Characterize, or CLI).

-- ClickHouse: check recommended_methods for a known seasonal series
SELECT unique_id, has_seasonality, seasonal_strength,
       recommended_methods
FROM PIPE_series_characteristics
WHERE pipeline_id=4 AND scenario_id=4
  AND unique_id IN ('1_301', '1_342', '1_344', '2_305')
ORDER BY unique_id;

unique_id	item	has_seasonality	recommended_methods (expected after)
1_301	Chain Lube / Cycles Paris	1	["MSTL","AutoARIMA","AutoTheta",...]
1_342	(top volume series)	1	["MSTL","AutoARIMA","AutoTheta",...]
1_344		1	["MSTL","AutoARIMA","AutoTheta",...]
2_305		1	["MSTL","AutoARIMA","AutoTheta",...]

None of the above should have AutoETS in the list.

Also verify non-seasonal series are unaffected:

SELECT unique_id, has_seasonality, recommended_methods
FROM PIPE_series_characteristics
WHERE pipeline_id=4 AND scenario_id=4 AND has_seasonality=0
LIMIT 5;

Expected: non-seasonal series still have AutoETS (it's in the standard group).

---

Test 5 — Forecast runs new methods for seasonal series

Objective: After re-running forecast, AutoARIMA and AutoTheta results exist for the 391 seasonal series. AutoETS results for those series should be absent (since it's no longer in their recommended_methods).

-- ClickHouse: method coverage for seasonal vs non-seasonal series
SELECT
    sc.has_seasonality,
    f.method,
    count(DISTINCT f.unique_id) AS series_count
FROM PIPE_forecast_point_values f
JOIN PIPE_series_characteristics sc
    ON f.unique_id = sc.unique_id
    AND sc.pipeline_id = f.pipeline_id
    AND sc.scenario_id = f.scenario_id
WHERE f.pipeline_id=4 AND f.scenario_id=4
GROUP BY sc.has_seasonality, f.method
ORDER BY sc.has_seasonality DESC, series_count DESC;

Expected after fix: - Seasonal series (has_seasonality=1): MSTL + AutoARIMA + AutoTheta dominant; AutoETS absent or minimal - Non-seasonal series (has_seasonality=0): AutoETS + AutoARIMA + AutoTheta present (standard group)

---

Test 6 — Best method distribution shifts

Objective: AutoETS share of best methods drops; MSTL + AutoARIMA share rises. Overall seasonality signal in the aggregate forecast becomes visible.

-- ClickHouse: best method distribution by series count and forecast volume
SELECT
    bm.best_method,
    count() AS series_count,
    round(sum(f.total_fc) / 1000, 1) AS total_volume_k
FROM PIPE_series_best_methods bm
JOIN (
    SELECT unique_id, sum(point_forecast) AS total_fc
    FROM PIPE_forecast_point_values
    WHERE pipeline_id=4 AND scenario_id=4
    GROUP BY unique_id
) f ON bm.unique_id = f.unique_id
WHERE bm.pipeline_id=4
GROUP BY bm.best_method
ORDER BY series_count DESC;

method	Before (series count)	Before (% volume)	After (expected)
MSTL	229	44%	higher — more seasonal series now compare MSTL vs AutoARIMA
AutoETS	185	36%	lower — not a candidate for seasonal series
AutoTheta	91	12%	similar or higher — now in seasonal group
AutoARIMA	57	8%	higher — now competing in 391 seasonal series

---

Test 7 — Specific series shows seasonal forecast shape

Objective: Spot-check top-volume seasonal series that previously had flat AutoETS forecasts. After re-running, their best method should be MSTL or AutoARIMA and the forecast should oscillate seasonally (amplitude > 20% of mean).

Series to check: 1_342 (top volume, previously AutoETS flat)

-- ClickHouse: forecast values for 1_342 after re-run
SELECT method, forecast_week, round(point_forecast, 1) AS fc
FROM PIPE_forecast_point_values
WHERE pipeline_id=4 AND scenario_id=4
  AND unique_id='1_342'
  AND method IN ('MSTL', 'AutoARIMA', 'AutoTheta')
ORDER BY method, forecast_week
LIMIT 30;

-- ClickHouse: best method for 1_342 after re-run
SELECT unique_id, best_method, best_score, runner_up_method
FROM PIPE_series_best_methods
WHERE pipeline_id=4 AND unique_id='1_342';

Expected: - best_method = MSTL or AutoARIMA (not AutoETS) - Forecast values oscillate: max(fc) - min(fc) > 0.20 * avg(fc) over the 26-week horizon

Also check in the UI: 1. Open / → Forecast tab → Demand & Forecast chart 2. The aggregate line should show a declining slope Jun → Nov (summer→winter trough) 3. Open /series/1_342 → Main chart → confirm MSTL or AutoARIMA is highlighted in green

---

How to drive the test

After confirming Test 3 (DB changes verified via SQL above):

1. ProcessRunner → Characterize — re-runs _recommend_methods with the new seasonal group → Verify Test 4 SQL shows AutoARIMA/AutoTheta in recommended_methods for seasonal series

2. ProcessRunner → Forecast — runs the new method set for each series → Verify Test 5 SQL shows new method distribution

3. ProcessRunner → Best Method — re-scores with updated backtest results → Verify Tests 6 & 7 SQL

Total re-run time: ~10-15 min (characterize) + ~30-45 min (forecast) + ~5 min (best-method).