---
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. The forecast should estimate expected revenue for each of the **next 24 hours** using historical sales data and relevant external signals.

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

## Known Tables & Columns

### `curated.pos_transaction_items`
- item (integer) - menu item identifier
- bohname (varchar) - menu item name when populated in the curated transaction-item surface
- price (decimal) - current selling price
- cost (decimal) - estimated cost of item when available in the curated item or catalog surfaces
- revid (integer) - revenue center identifier

### `curated.pos_hourly_sales`
- dob (date) - transaction date
- starthour (integer) - start of hourly sales window
- stophour (integer) - end of hourly sales window
- keyvolume (integer) - quantity sold in the curated hourly sales surface
- revid (integer) - revenue center identifier
- amount (decimal) - total sales value

### `curated.pos_transactions`
- dob (date) - transaction date
- revenue (integer) - revenue or reporting identifier available in the transaction surface
- revid2 (integer) - alternate revenue-center identifier when populated
- amount (decimal) - revenue generated — represents the amount per check row; aggregate with SUM(amount) for total revenue
- check (integer) - order identifier — derive total orders using COUNT(DISTINCT check)

## Analysis Approach

1. Extract sales data from the last **90 days** using `curated.pos_hourly_sales`.

2. If the forecast needs item, catalog, or revenue-center enrichment, first inspect the available `curated` tables and columns, then join only on shared identifiers that are explicitly present in both tables.
   - Look for repeated business keys such as transaction identifiers, item identifiers, or revenue-center identifiers that appear in more than one `curated` table.
   - Treat similarly named fields as join candidates only after confirming that their semantics line up in both tables.

3. Aggregate historical sales into **hourly revenue patterns**, grouping by:
   - hour of day
   - day of week.

4. Identify recurring demand patterns such as:
   - weekday vs weekend differences
   - lunch vs dinner peaks
   - late-night demand patterns.

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

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

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

8. Build a **baseline hourly forecast** for the next 24 hours using historical hourly patterns.

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

10. Produce the final **hourly revenue forecast for the next 24 hours**.

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

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

## Output Format

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