Repository Docs & Knowledge Base

id: c2a4f6d1-9b7e-4c2b-8d3f-1e6a7b9d5c21 name: Recommend optimal pricing adjustments

Detect pricing optimization opportunities by analyzing demand patterns, price sensitivity, and external factors to maximize revenue and margin.

Known Tables & Columns

curated.pos_transaction_items

• ITEM (varchar) - menu item identifier
• ITEM_NAME (varchar) - menu item name
• QTY (int) - quantity sold
• PRICE (decimal) - selling price
• NET_SALES (decimal) - total revenue from item
• BUSINESS_DATE (date) - transaction date
• HOUR (int) - hour of sale
• REVN_CENTER (varchar) - revenue center (Dining Room, Bar, Delivery)

curated.pos_hourly_sales

• ITEM (varchar) - menu item identifier
• DATE (date) - transaction date
• HOUR (int) - hour of sale
• QTY_SOLD (int) - quantity sold
• SALES_AMOUNT (decimal) - total sales
• REVN_CENTER (varchar) - revenue center

curated.pos_transactions

• DATE (date) - transaction date
• REVN_CENTER (varchar) - revenue center
• TOTAL_REVENUE (decimal) - total revenue
• TOTAL_ORDERS (int) - number of orders

Analysis Approach

1. Analyze item-level sales for the last 90 days to determine demand levels and revenue contribution for each menu item.

1. Identify demand drivers:
2. high-demand and low-demand items based on sales volume
3. demand patterns by hour of day
4. demand patterns by day of week
5. differences between weekdays and weekends
6. demand shifts across revenue centers.

1. Establish a baseline demand for each item using normal (non-holiday, typical weather) periods.

1. Detect deviations from baseline caused by:
2. holidays and special periods (e.g., Christmas, Easter, New Year, long weekends)
3. weather conditions (rain, temperature, wind impact on dine-in vs delivery).

1. Estimate price sensitivity (elasticity proxy):
2. compare changes in price vs changes in quantity sold over time
3. identify items with low sensitivity (stable demand despite price variation)
4. identify items with high sensitivity (demand strongly affected by price).

1. Identify pricing opportunities:
2. peak-hour pricing for high-demand, low-sensitivity items
3. off-peak discounts for low-demand periods
4. weather-based pricing adjustments (e.g., delivery during rain. Use get_weather_forecast tool to check weather for Orlando. Get current weather conditions and forecast.)
5. holiday/event-based pricing strategies
6. revenue center-specific pricing differences.

1. Validate that each item has sufficient data (minimum 30 units sold) to ensure reliable recommendations.

1. Avoid overly aggressive pricing changes unless supported by strong and consistent demand patterns.

1. Balance price increase vs expected volume drop, prioritizing overall revenue and margin improvement.

1. Generate concise pricing recommendations including:
2. item name
3. context (time, day, weather, event, revenue center)
4. demand insight
5. recommended price adjustment (percentage, discount, bundle, dynamic pricing)
6. expected impact (revenue, volume, margin)
7. short reasoning.

1. Minimize database usage:
2. prefer aggregated summaries
3. do not request more than 3 queries unless necessary
4. reuse previously retrieved data when possible.

Severity Guidance

• Low: Small pricing optimization opportunity with limited impact.
• Medium: Clear pricing inefficiency across specific time periods or segments.
• High: Strong, consistent demand imbalance or pricing inefficiency with significant revenue upside.