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

The process should evaluate item performance using historical sales and cost data to determine which menu items deliver both **strong demand and strong margin contribution**.

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

## Known Tables & Columns

### `curated.pos_transaction_items`
- item (integer) - menu item identifier — references itm.id  
- price (decimal) - selling price  
- revid (integer) - revenue center identifier  

### `curated.pos_catalog_items`
- id (integer) - menu item identifier  
- longname (varchar) - item name  
- cost (decimal) - item cost  

### `curated.pos_transactions` (optional context)
- sales frequency and transaction patterns  
- quantity sold signals  

## Analysis Approach

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

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

### Stage 2 — Profitability Calculation
Use `curated.pos_catalog_items` to determine item cost.

Compute:
- profit per item (price − cost)
- total profit contribution (profit × quantity sold).

### Stage 3 — Popularity Assessment
Determine item popularity by comparing:
- item sales volume vs overall item average/median
- frequency of ordering over the 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 the **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.