---
id: 5f2c9d81-3a6b-4e7d-9c21-1b8f7a3d6e90
name: Identify plowhorse items (high popularity, low profit)
kind: MenuEngineering
cron: 0 45 7 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Identify **plowhorse menu items** to support menu engineering, pricing optimization, and margin improvement.

The process should detect items that generate **high demand but low profitability**, helping optimize pricing or menu restructuring decisions.

The output must contain **only high-popularity, low-profit items**, with no other categories included and no explanations 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)
- sales frequency signals  
- quantity sold patterns  

## 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
- sales frequency
- average selling price.

### Stage 2 — Profitability Calculation
Use `curated.pos_catalog_items` to calculate cost-based profitability:

- profit per item = selling price − cost  
- total profit contribution = profit × quantity sold  

### Stage 3 — Popularity Assessment
Determine popularity by comparing:
- item sales volume vs overall item average or median
- ordering frequency over the last 90 days

### Stage 4 — Plowhorse Classification
Return **only items meeting all conditions**:
- high popularity (above demand threshold)
- low profitability (below margin threshold)

Exclude all other item categories.

## 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 high-popularity, low-profit items**.
- **Do not include explanations, methodology, or intermediate results**.

## Output Format

Return only confirmed plowhorse items.