Repository Docs & Knowledge Base

id: 8f1d7e6a-4e5f-4f1c-9f8a-3c0c9d2a61c7 name: Identify demand drivers kind: Insight cron: 0 0 6 * * 1 analysis_window: 90d comparison_periods: [7d, 28d]

Detect menu items whose prices should be adjusted based on demand patterns, seasonal factors, and external conditions such as weather or holidays.

Known Tables & Columns

curated.pos_transaction_items

• item (integer) - menu item identifier — references itm.id
• bohname (varchar) - menu item name — for item name join itm on gnditem.item = itm.id and use itm.longname
• price (decimal) - current selling price
• cost (decimal) - estimated cost of item — not in gnditem, join itm on gnditem.item = itm.id and use itm.cost
• revid (integer) - revenue center identifier — for revenue center name join rev on gnditem.revid = rev.id and use rev.name

curated.pos_hourly_sales

• item (integer) - menu item identifier — not available in gndslsum; use gnditem.item instead, referencing itm.id
• dob (date) - transaction date
• starthour (integer) - hour of sale — gndslsum has starthour/stophour as a time window rather than a single hour; for a single hour field use gnditem.hour instead
• keyvolume (integer) - quantity sold — closest available field in gndslsum; for explicit quantity use gnditem.quantity instead
• revid (integer) - revenue center identifier — for revenue center name join rev on gndslsum.revid = rev.id and use rev.name
• amount (decimal) - total sales value

curated.pos_transactions

• dob (date) - transaction date
• revid (integer) - revenue center identifier — for revenue center name join rev on gndrevn.revid = rev.id and use rev.name
• amount (decimal) - revenue generated — represents the amount per check row; aggregate with SUM(amount) for total revenue
• check (integer) - total orders count — no explicit order count column; aggregate with COUNT(DISTINCT check) to derive total orders

Analysis Approach

1. Analyze sales data for the last 90 days using curated.pos_hourly_sales to determine demand levels for each menu item.

1. Classify menu items into demand tiers:
2. High-demand items: items consistently sold above the median sales volume.
3. Low-demand items: items with significantly lower sales volume relative to other items.

1. Identify time-based demand patterns:
2. compare item demand by hour of day
3. compare day-of-week trends
4. detect patterns related to weekends vs weekdays.

1. Identify seasonal and special-period patterns, including:
2. public holidays
3. major holiday periods (Christmas, Easter, New Year)
4. long weekends or national holidays.

1. Detect weather-related demand patterns by comparing historical sales patterns during:
2. pleasant weather (increased dine-in demand possible)
3. rainy conditions (lower walk-in traffic but higher delivery demand)
4. extreme temperatures (reduced overall restaurant visits).

1. Compare current and upcoming weather forecasts with historical patterns to determine whether demand may deviate from the normal baseline. Use get_weather_forecast tool to check weather for Orlando. Get current weather conditions and forecast.

1. Analyze revenue center performance using curated.pos_transactions to detect whether demand shifts occur between dine-in, bar, or delivery channels.

1. Identify pricing opportunities:
2. high-demand items during peak periods that may tolerate small price increases
3. low-demand items that may benefit from temporary discounts or promotions
4. delivery-heavy items during bad weather that may support surge pricing or delivery markup.

1. Ignore items with fewer than 20 total units sold in the analysis window to avoid noise.

1. Produce clear pricing recommendations including:
2. item name
3. demand insight
4. suggested price adjustment
5. brief reasoning understandable for restaurant managers.

1. Minimize database usage:
2. prefer aggregated summaries instead of raw data
3. do not request more than 3 queries unless necessary.

Severity Guidance

• Low: Small pricing opportunity detected (minor demand imbalance).
• Medium: Consistent demand shift suggesting price adjustment during specific time periods.
• High: Strong demand imbalance caused by external factors (holidays, weather, or peak hours) indicating a significant pricing opportunity.