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

The forecast should estimate **total expected revenue for each of the next 12 months** 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 **24 months** 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:
   - month
   - year
   - revenue center.

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

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

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

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

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

9. Produce the final **monthly revenue forecast for the next 12 months**.

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

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

## Output Format

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

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