Repository Docs & Knowledge Base

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.