---
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.

The forecast should estimate **total expected revenue for each of the next 4 weeks** using historical sales data and relevant external signals.

The output must contain **forecast data 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 historical 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 revenue by:
   - week
   - week number
   - revenue center.

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

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

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

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

8. Build a **baseline weekly forecast** for the upcoming weeks based on historical patterns and recent trends.

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

10. Produce the final **weekly revenue forecast for the next 4 weeks**.

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

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

## Output Format

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

No explanations or narrative text should be included in the output.