---
id: 3c2a6c51-9d8f-4f72-b7d1-6e5b2c0a9f41
name: Generate short-term hourly sales forecast
kind: Forecast
cron: 0 * * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Generate accurate short-term sales forecasts to support operational planning and staffing decisions. Forecast should estimate expected revenue for each of next **24 hours** using historical order data and relevant external signals.

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

## Known Tables & Columns

### `curated.pos_orders`
- `created_at` (timestamp) - local order timestamp for hourly bucketing
- `created_at_utc` (timestamp) - UTC order timestamp when local timestamp needs normalization
- `business_date` (date) - business trading date
- `total` (decimal) - order revenue
- `destination` (varchar) - destination or fulfillment context
- `service_channel` (varchar) - dine-in, pickup, delivery, or similar channel label when populated
- `fulfillment_channel` (varchar) - downstream 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 completed historical orders from last **90 days** using `curated.pos_orders`.

2. Build hourly revenue history from `curated.pos_orders.total`.
   - bucket by hour using `created_at` when present
   - use `created_at_utc` only when local timestamp needs normalization
   - keep `business_date` for day-level alignment and holiday comparisons.

3. Aggregate historical sales into **hourly revenue patterns**, grouping by:
   - hour of day
   - day of week
   - weekend vs weekday
   - service or fulfillment channel when that split materially improves forecast accuracy.

4. Identify recurring demand patterns such as:
   - lunch vs dinner peaks
   - late-night demand patterns
   - channel-specific peaks.

5. Detect **recent anomalies** in last 90 days such as:
   - sudden sales spikes
   - unusually low demand
   - temporary effects caused by promotions or special events.

6. Use `curated.pos_order_items` only when item or category mix materially explains demand shifts that are not visible from order totals alone.

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

8. Retrieve **current weather conditions and forecast** using `get_weather_forecast` to detect potential demand shifts.

9. Build baseline hourly forecast for next 24 hours using historical hourly patterns.

10. Adjust baseline forecast based on:
    - upcoming weather conditions
    - known events or holidays
    - recent anomalies or short-term trends.

11. Produce final **hourly revenue forecast for next 24 hours**.

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

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

## Output Format

Return structured list containing **24 hourly forecast values** for next 24 hours.
No explanations or narrative text should be included in output.