Repository Docs & Knowledge Base

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.

1. Rank menu items by profit contribution (total profit generated).

1. Identify high-volume items with strong margins.

These are "star items" — strong candidates for small price increases if demand appears stable.

1. Identify high-margin items with low sales volume.

These items may benefit from promotion, upselling, or improved menu placement.

1. Identify high-volume items with weak margins.

Recommend price adjustments, portion optimization, or ingredient cost review.

1. 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.

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

1. Generate concise recommendations phrased as direct managerial actions, such as:
2. price adjustments
3. menu highlighting
4. combo creation
5. 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.