The Menu Item Analysis Report shows menu item sales, price, and cost by location, date, and menu item. It contains the quantities sold, selling prices, and theoretical cost for each item.
Column reference
| Column | Type | Description |
|---|---|---|
| tenant_id | NUMBER(38,0) | R365 internal tenant identifier |
| location_id | VARCHAR | Location identifier sales occurred at |
| operational_date | DATE | Operational (business) date the sales occurred on |
| menu_item_id | VARCHAR | Identifier of the menu item |
| location_name | VARCHAR | Name of the location |
| location_number | VARCHAR | Location number |
| legal_entity_id | VARCHAR | Identifier of the location's legal entity |
| legal_entity_name | VARCHAR | Name of the location's legal entity |
| recipe_item_id | VARCHAR | Identifier of the recipe item backing this menu item, if any |
| menu_item_name | VARCHAR | Name of the menu item |
| menu_item_category1_id | VARCHAR | Identifier of the menu item's first-level category |
| menu_item_category1_name | VARCHAR | Name of the menu item's first-level category |
| menu_item_category2_id | VARCHAR | Identifier of the menu item's second-level category |
| menu_item_category2_name | VARCHAR | Name of the menu item's second-level category |
| menu_item_category3_id | VARCHAR | Identifier of the menu item's third-level category |
| menu_item_category3_name | VARCHAR | Name of the menu item's third-level category |
| is_entree | BOOLEAN | Shows whether the menu item is an entree: true if it is, false if it is not |
| cogs_category | NUMBER(38,0) | Cost-of-goods-sold category assigned to the menu item |
| plu_id | VARCHAR | Price look-up (PLU) identifier for the menu item |
| sku | VARCHAR | SKU assigned to the menu item |
| pos_menu_item_id | VARCHAR | POS system identifier for the menu item |
| target_margin | NUMBER(38,10) | Target margin configured for the menu item |
| total_quantity | NUMBER(38,5) | Total quantity sold. Additive; rolls up exactly across any date/location range. |
| total_sales | NUMBER(38,2) | Total sales dollars. Additive; rolls up exactly across any date/location range. |
| each_price | NUMBER(38,2) | Realized price per unit sold for the day. On days with zero-dollar sales, the catalog price is used. This value is not additive. To get a price over a date range, divide total sales by total quantity instead of averaging this column. |
| each_cost_theoretical | NUMBER(38,4) | Theoretical cost per unit, calculated from the current recipe tree. This value is not additive. For commissary-received or prepped recipes where this value is zero, use the each_cost_recipe column instead. |
| each_cost_recipe | NUMBER(38,4) | Per-unit cost from the recipe item's own stored base cost. Not additive. |
| each_cost_estimated | NUMBER(38,4) | Per-unit estimated cost from the menu item's configured estimated cost. Not additive. |
| each_margin_theoretical | NUMBER(38,2) | each_price minus each_cost_theoretical |
| each_margin_recipe | NUMBER(38,2) | each_price minus each_cost_recipe |
| each_margin_estimated | NUMBER(38,2) | each_price minus each_cost_estimated |
| theo_cost_theoretical | NUMBER(38,4) | each_cost_theoretical times total_quantity |
| theo_cost_recipe | NUMBER(38,4) | each_cost_recipe times total_quantity |
| theo_cost_estimated | NUMBER(38,4) | each_cost_estimated times total_quantity |
| total_profit_theoretical | NUMBER(38,2) | total_sales minus theo_cost_theoretical |
| total_profit_recipe | NUMBER(38,2) | total_sales minus theo_cost_recipe |
| total_profit_estimated | NUMBER(38,2) | total_sales minus theo_cost_estimated |
| cost_pct_theoretical | NUMBER(38,12) | each_cost_theoretical / each_price, 0 when either operand is 0 |
| cost_pct_recipe | NUMBER(38,12) | each_cost_recipe / each_price, 0 when either operand is 0 |
| cost_pct_estimated | NUMBER(38,10) | each_cost_estimated / each_price, 0 when either operand is 0 |