Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.

branches/kds_cdc_improvments/monitor-specs/backend/Kds.Ai.Data/MonitorSpecs/identify-star-items.md 2,708 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.