---
id: 7c7f6b9c-3e65-4f9e-8d6b-52b91c9f4d41
name: Calculate menu item profitability
kind: Insight
cron: 0 0 */4 * * *
analysis_window: 30d
comparison_periods: [7d, 28d]
---

Detect menu items that require pricing or promotion adjustments based on profitability and demand patterns.

## Known Tables & Columns

### `curated.pos_transaction_items`
- item (integer) - menu item identifier — references itm.id
- bohname (varchar) - menu item name — for item name join itm on gnditem.item = itm.id and use itm.longname
- price (decimal) - current selling price
- cost (decimal) - estimated cost of item — not in gnditem, join itm on gnditem.item = itm.id and use itm.cost
- revid (integer) - revenue center identifier — for revenue center name join rev on gnditem.revid = rev.id and use rev.name

### `curated.pos_catalog_items`
- id (integer, PK) - menu item identifier
- longname (varchar) - menu item name — alternatively shortname for abbreviated name or chitname for the name printed on the guest check
- category (varchar) - menu category — not directly in itm; join itmgrpl on itmgrpl.itemid = itm.id, then join itmgrp on itmgrpl.id = itmgrp.id and use itmgrp.desc
- price (decimal) - base configured price
- donotshow (varchar) - whether the item is active on the menu — no explicit active flag exists; donotshow = 'Y' indicates the item is hidden/inactive, so treat donotshow != 'Y' (or NULL) as active

### `curated.pos_modifiers`
- id (integer, PK) - modifier identifier
- item01–item54 (integer) - item(s) associated with the modifier — the mod table stores up to 54 linked items as separate columns (item01, item02, … item54); alternatively use itmxmod.itemid joined on itmxmod.moditem = mod.id for a cleaner item-to-modifier relationship
- longname (varchar) - modifier name (e.g., extra cheese, bacon) — alternatively shortname for abbreviated name
- price01–price54 (bigint) - additional price from modifier — prices are stored as positional columns aligned with their corresponding item01–item54 entries; no single flat price field exists

### `curated.pos_transaction_tenders`
- check (integer) - check identifier
- typeid (integer) - payment method — no tender name directly in gndtndr; join tdr on gndtndr.typeid = tdr.id and use tdr.name for the tender type label (e.g., Cash, Card, Online); tdr.cash, tdr.iscredtcrd, and tdr.debit flags can be used to classify tender types programmatically
- amount (decimal) - payment amount
- date (date) - date of payment

### `curated.pos_transactions`
- check (integer, PK) - check identifier
- dob (date) - date of sale
- openhour + openmin (integer, integer) - when the check was opened — no native timestamp; open time is split across openhour and openmin and must be reconstructed (e.g., make_time(openhour, openmin, 0) or combined with dob for a full timestamp)
- closehour + closemin (integer, integer) - when the check was closed — same pattern as open time; reconstruct from closehour and closemin combined with dob
- count (integer) - number of guests on the check
- revenue (integer) - revenue center identifier — for revenue center name join rev on gndsale.revenue = rev.id and use rev.name
- amount (decimal) - total check value

## Analysis Approach

1. Aggregate menu item performance over the last {AnalysisWindow}.  
   For each `curated.pos_transaction_items`, calculate:
   - total units sold
   - total revenue (`quantity × unit_price`)
   - total cost (`quantity × unit_cost`)
   - total profit (`revenue - cost`)
   - average margin percentage.

2. Rank menu items by **profit contribution** (total profit generated).

3. Identify **high-volume items with strong margins**.  
   These are "star items" — strong candidates for **small price increases** if demand appears stable.

4. Identify **high-margin items with low sales volume**.  
   These items may benefit from **promotion, upselling, or improved menu placement**.

5. Identify **high-volume items with weak margins**.  
   Recommend **price adjustments, portion optimization, or ingredient cost review**.

6. Detect **items frequently ordered together** by analyzing order-level co-occurrence in `curated.pos_order_items`.  
   If two items appear together in a high percentage of the same orders, recommend **combo deals or bundles**.

7. Exclude items with fewer than **20 total units sold** during the analysis window to avoid noise from rarely ordered items.

8. Generate concise recommendations phrased as **direct managerial actions**, such as:
   - price adjustments
   - menu highlighting
   - combo creation
   - upselling opportunities.

## Severity Guidance

- **Low**: Minor optimization opportunity (e.g., small price increase or promotional suggestion).
- **Medium**: Clear profitability imbalance between demand and margin requiring adjustment.
- **High**: Major profit leakage — high-volume item with significantly below-average margin or pricing inconsistency.