Repository Docs & Knowledge Base

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.

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 sales into hourly revenue patterns, grouping by:
2. hour of day
3. day of week.

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

1. Detect recent anomalies in the last 90 days such as:
2. sudden sales spikes
3. unusually low demand
4. temporary effects caused by promotions or special events.

1. Enrich the analysis using external signals, including:
2. holidays and public events
3. local events (sports games, festivals)
4. weather conditions.

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

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

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

1. Produce the final hourly revenue forecast for the next 24 hours.

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

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