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

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

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

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

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

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

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

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

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

10. Produce clear pricing recommendations including:
   - item name
   - demand insight
   - suggested price adjustment
   - brief reasoning understandable for restaurant managers.

11. Minimize database usage:
   - prefer aggregated summaries instead of raw data
   - 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.