---
id: 3c8f2a91-6b4d-4c2e-9f51-8d2a7c1e6f90
name: Identify star items (high popularity, high profit)
kind: MenuEngineering
cron: 0 35 7 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Identify **star menu items** with **high popularity and high profit** to support menu engineering, pricing strategy, and profitability optimization.

Process should evaluate item performance using historical sold-item data and current configured cost data to determine which menu items deliver both **strong demand and strong margin contribution**.

Output must contain **only confirmed star items**, without explanations, intermediate calculations, or methodology.

## Known Tables & Columns

### `curated.pos_order_items`
- `item_id` (integer) - menu item identifier
- `item_name` (varchar) - sold item name
- `quantity` (integer) - sold quantity
- `unit_price` (decimal) - observed sold unit price
- `business_date` (date) - business trading date

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

### `curated.pos_orders` (optional context)
- `order_id` (varchar) - join key to sold items when order-level context is needed
- `created_at` (timestamp) - order timestamp for popularity timing patterns

## Analysis Approach

### Stage 1 - Item Performance Aggregation
Extract item-level sales data from last **90 days** using `curated.pos_order_items`.

Compute per-item metrics including:
- total quantity sold
- total revenue
- sales frequency
- average selling price.

### Stage 2 - Profitability Calculation
Use `curated.pos_menu_items` to determine current configured item cost.

Compute:
- profit per item (`unit_price - cost`)
- total profit contribution (`(unit_price - cost) × quantity sold`).

Treat profitability as **current-cost-based profitability** rather than historical cost-snapshot profitability.

### Stage 3 - Popularity Assessment
Determine item popularity by comparing:
- item sales volume vs overall item average or median
- frequency of ordering over 90-day window.

### Stage 4 - Star Item Classification
Classify items as **Confirmed Star Items** if they meet both:
- high popularity (above defined demand threshold)
- high profitability (above defined margin threshold).

## Output Constraints

- Use historical data from **last 90 days** only.
- Prefer aggregated summaries.
- Avoid more than **2-3 database queries** unless necessary.
- Return **only Confirmed Star Items**.
- **Do not include intermediate steps, explanations, or methodology**.

## Output Format

Return only confirmed star items.