---
id: 7a4c1e52-b9f2-4a7f-8e21-5c2b0e4d91b3
name: Generate weekly sales forecast
kind: Forecast
cron: 0 0 6 * * 1
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Generate accurate **weekly sales forecasts** to support inventory planning, staffing allocation, and operational decision-making.

Forecast should estimate **total expected revenue for each of next 4 weeks** 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 weekly 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 **90 days** using `curated.pos_orders`.

2. Aggregate historical revenue by:
   - business week
   - week number
   - service or fulfillment channel when that split materially improves forecast quality.

3. Use `business_date` as primary weekly bucket. Use `created_at` or `created_at_utc` only when finer event alignment is needed.

4. Identify recurring demand patterns such as:
   - typical weekly revenue cycles
   - peak and slow weeks
   - trends over recent weeks.

5. Detect **recent anomalies** within last 90 days such as:
   - sudden spikes or drops in revenue
   - temporary demand caused by promotions or events.

6. Use `curated.pos_order_items` only when item or category mix materially explains weekly demand changes.

7. Enrich dataset using **external signals**, including:
   - holidays and public events
   - local events (sports games, festivals)
   - expected weather conditions.

8. Retrieve current conditions and short-term forecast using `get_weather_forecast` to determine whether demand may deviate from historical baselines.

9. Build baseline weekly forecast for upcoming weeks based on historical patterns and recent trends.

10. Adjust forecast based on:
    - upcoming holidays
    - forecasted weather conditions
    - detected short-term trends or anomalies.

11. Produce final **weekly revenue forecast for next 4 weeks**.

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

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

## Output Format

Return structured list containing **4 weekly forecast values** for next 4 weeks.

No explanations or narrative text should be included in output.