---
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_orders`
- `order_id` (varchar) - join key to sold items
- `created_at` (timestamp) - local order timestamp for time-of-day analysis
- `business_date` (date) - business trading date
- `total` (decimal) - order revenue
- `destination` (varchar) - destination or fulfillment context
- `service_channel` (varchar) - dine-in, pickup, delivery, or similar channel label when populated
- `fulfillment_channel` (varchar) - downstream fulfillment grouping when populated

### `curated.pos_order_items`
- `order_id` (varchar) - join key to `curated.pos_orders.order_id`
- `item_id` (integer) - menu item identifier
- `item_name` (varchar) - sold item name
- `quantity` (integer) - sold quantity
- `unit_price` (decimal) - observed sold unit price
- `category_name` (varchar) - sold item category when populated
- `business_date` (date) - item business date

### `curated.pos_menu_items`
- `item_id` (integer) - menu item identifier
- `item_name` (varchar) - configured menu item name
- `category_name` (varchar) - configured category
- `price` (decimal) - current configured price
- `cost` (decimal) - current configured item cost when visible

## Analysis Approach

1. Analyze sold item demand for last **90 days** using `curated.pos_order_items` joined to `curated.pos_orders` on `order_id`.

2. Classify menu items into demand tiers:
   - **High-demand items**: items consistently sold above 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** using `curated.pos_orders.created_at`
   - compare **day-of-week trends** using `business_date`
   - detect patterns related to **weekends vs weekdays**.

4. Identify **seasonal and special-period patterns**, including:
   - public holidays
   - major holiday periods
   - long weekends or national holidays.

5. Detect **weather-related demand patterns** by comparing historical sales patterns during:
   - pleasant weather
   - rainy conditions
   - extreme temperatures.

6. Compare current and upcoming weather forecasts with historical patterns to determine whether demand may deviate from normal baseline. Use `get_weather_forecast` to check weather for Orlando.

7. Analyze demand shifts across service modes using `curated.pos_orders.destination`, `service_channel`, and `fulfillment_channel` to detect whether shifts occur between dine-in, pickup, and delivery contexts.

8. Use `curated.pos_menu_items` only for stable menu metadata such as configured category or current configured price/cost. Use observed sold `unit_price` from `curated.pos_order_items` for pricing-demand analysis.

9. 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 **delivery markup or channel-specific pricing**.

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

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

12. 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 significant pricing opportunity.