Repository Docs & Knowledge Base

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.