Repository Docs & Knowledge Base

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.

1. 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.
2. Look for repeated business keys such as transaction identifiers, item identifiers, or revenue-center identifiers that appear in more than one curated table.
3. Treat similarly named fields as join candidates only after confirming that their semantics line up in both tables.

1. Aggregate historical revenue by:
2. month
3. year
4. revenue center.

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

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

1. Enrich the dataset using external signals, including:
2. national holidays
3. seasonal demand periods
4. major local events (sports tournaments, festivals, tourism peaks).

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

1. Adjust forecasts based on:
2. known future holidays
3. recurring seasonal demand patterns
4. detected long-term trends.

1. Produce the final monthly revenue forecast for the next 12 months.

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

1. Minimize database usage:
2. prefer aggregated summaries
3. 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.