---
id: b2d4f6a1-7c9e-4d3a-a2f7-6e1b8c4f5a12
name: Generate monthly sales forecast
kind: Forecast
cron: 0 0 6 1 * *
analysis_window: 730d
comparison_periods: [30d, 365d]
---

Generate accurate **monthly sales forecasts** to support long-term inventory planning, budgeting, and strategic decision-making.

Forecast should estimate **total expected revenue for each of next 12 months** using historical order data and relevant external signals.

Output must contain **forecast data only**, without explanations or descriptions of methodology.

## Known Tables & Columns

### `curated.pos_orders`
- `created_at` (timestamp) - local order timestamp
- `created_at_utc` (timestamp) - UTC timestamp when normalization is needed
- `business_date` (date) - business trading date for monthly bucketing
- `total` (decimal) - order revenue
- `destination` (varchar) - destination or fulfillment context
- `service_channel` (varchar) - service-channel grouping when populated
- `fulfillment_channel` (varchar) - fulfillment grouping when populated

### `curated.pos_order_items` (optional enrichment)
- `order_id` (varchar) - join key to `curated.pos_orders.order_id`
- `item_id` (integer) - menu item identifier
- `quantity` (integer) - sold quantity
- `unit_price` (decimal) - observed sold unit price
- `category_name` (varchar) - sold item category when populated

## Analysis Approach

1. Extract historical orders from last **24 months** using `curated.pos_orders`.

2. Aggregate historical revenue by:
   - month
   - year
   - service or fulfillment channel when that split materially improves forecast quality.

3. Use `business_date` as primary monthly bucket. Use `created_at` or `created_at_utc` only for finer event alignment.

4. Identify recurring **seasonal demand patterns**, such as:
   - high and low sales months
   - seasonal demand cycles across year
   - long-term growth or decline trends.

5. Detect **recent anomalies** within historical period such as:
   - unusual spikes or drops in revenue
   - temporary demand changes caused by promotions or events.

6. Use `curated.pos_order_items` only when item or category mix materially explains long-term demand shifts.

7. Enrich dataset using **external signals**, including:
   - national holidays
   - seasonal demand periods
   - major local events (sports tournaments, festivals, tourism peaks).

8. Build baseline monthly forecast for future months based on historical seasonality and long-term trends.

9. Adjust forecasts based on:
   - known future holidays
   - recurring seasonal demand patterns
   - detected long-term trends.

10. Produce final **monthly revenue forecast for next 12 months**.

11. Output constraints:
    - return **forecast data only**
    - **do not include explanations, reasoning, or methodology**
    - output must contain **exactly 12 forecast entries**.

12. Minimize database usage:
    - prefer aggregated summaries
    - avoid requesting more than **3 queries** unless necessary.

## Output Format

Return structured list containing **12 monthly forecast values** for next 12 months.

No explanations or narrative text should be included in output.