Data model: wide_order_item
Description
wide_order_item data model adds more resolution and visibility to business performance when wide_order data model is not enough. It allows Revel Data Connector users to make decisions based on specific item/product performance or a category of products.
Revel Data Connector users using this data model can answer questions like:
- What is the percentage of total sales for a specific item and what are the trends?
- What is the item price, unit cost, and margin for each item at a specific time?
- What is the item performance in a specific channel, geographic area and how do they compare?
- How did profitability change compared to prior time periods?
- What items contributed the most to total revenue over time?
- Which products are mostly discounted and why, what items are voided the most?
- Which employee gives the most discounts, comps, voids?
- What are item return reasons and trends?
- How big is the contribution of a specific product category to overall business performance?
- Speed of service: which dish takes the longest to be prepared in the kitchen?
- Table service: what are the sales or revenue of each table in the restaurant?
- etc.
Data Objects
Resource | Data type | Mode | Description | Example |
---|---|---|---|---|
order_id | int4 | Required | Unique ID for an Order | 5 |
order_created_at | timestamp | Required | Order creation date and time in UTC format | 2018-11-08 16:37:41 |
order_updated_at | timestamp | Required | Order last update date and time in UTC format | 2018-11-08 16:45:36 |
created_by_employee_first_name | varchar | 'N/A' | First name of employee who created an order. If order wasn’t created by employee, then value is N/A | John |
created_by_employee_last_name | varchar | 'N/A' | Last name of employee who created an order. If order wasn’t created by employee, then value is N/A | Doe |
created_by_employee_is_active | bool | Nullable | Identification of whether employee is active. If order wasn’t created by employee, then value is NULL | TRUE |
last_updated_by_employee_first_name | varchar | 'N/A' | First name of last employee who updated an order. If order wasn’t updated by employee, then value is N/A | John |
last_updated_by_employee_is_active | bool | Nullable | Identification of whether employee is active. If order wasn’t updated by employee, then value is NULL | TRUE |
last_updated_by_employee_last_name | varchar | 'N/A' | Last name of last employee who created an order. If order wasn’t created by employee, then value is N/A | Doe |
order_source | varchar | Required 'Unknown' | Source channel of the order or pos mode if applicable:
| Quick Service |
order_option | varchar | Required 'Unknown' | Identification of order option, e.g. To-Go, Eat-In, Delivery, etc.
| To Go |
order_status | varchar | Required | Status of the order | Closed |
is_order_paid | bool | Required | Order payment status | TRUE |
order_item_id | int4 | Required | Unique ID for an order item | 3 |
product_name_override | varchar | ‘N/A’ | Overriden product name. | Ice Cream Gift |
unit_price | numeric | Required | Unit price of the product in the order. It does not include price for modifiers or combos, monetary value. | 7.000000 |
unit_cost | numeric | Required | Unit cost of the product, it does not include price for modifiers or combos, monetary value. | 4.000000 |
unit_of_measure | varchar | 'N/A' | Unit, weighted, etc. | Unit |
quantity | int4 | Required | Number of ordered products(s) | 1 |
weight | float | Nullable | Number of weight for the product, which is weighted unit_of_measure contains types of data, e.g. Oz. | 7.0 |
item_status | varchar | 'N/A'/'Unknown’ | The status of the item: | Returned |
item_status_reason | varchar | 'N/A'/'Unknown’ | Reason for the item status (e.g. void) | Return to inventory |
item_type | varchar | Required/‘Unknown’ | Identification of the type of an item: | Normal |
total_price | numeric | Required | Total price for the products(s) in the Order, monetary value. Formula: unit_price +modifier_amount * quantity | 11.600000 |
total_cost | numeric | Required | Total price for the products(s) in the Order, monetary value. Formula: unit_cost +modifier_amount * quantity | 11.600000 |
tax_rate | float | Required | % of the tax rate. | 10.0 |
tax_amount | numeric | Required | Tax amount per product, , monetary value. Formula: tax rate * unit price * quantity | 0.400000 |
modifiers_amount | numeric | Required | Total amount of all the modifiers of single product, monetary value. | 1.600000 |
modifiers_cost | numeric | Required | Total cost of all the modifiers of single product, monetary value. | 0.170000 |
discount_reason | varchar | 'N/A' | Reason for discounting an item. | Basic Prcnt Type 1 |
discount_amount | numeric | Required | Amount of the discount, monetary value | 1.500000 |
discount_description | varchar | 'N/A' | Description, which contains discount reason and %. | {"discount_id": 68, "name": "Basic Prcnt Type 1", "qty": 1} |
service_fee_amount | numeric | Required | Service fee amount, include taxed and untaxed fee amounts, monetary value | 1.080000 |
service_fee_taxed_amount | numeric | Required | Service fee taxed amount, monetary value. | 0.670000 |
comps_amount | numeric | Required | Price reductions for defective or damaged goods, monetary value. | 6.990000 |
returns_amount | numeric | Required | The return of goods for a refund of payment, monetary value. | 1.130000 |
voids_amount | numeric | Required | Price of the voided product, monetary value. | 1.630000 |
exchanges_amount | numeric | Required | Price of the exchanged product, monetary value. | 2.750000 |
void_reason | varchar | 'N/A' | Reason for voiding an item. | Order error |
return_reason | varchar | 'N/A' | Reason for returning an item. | Wrong item |
is_valid_record | bool | Required | Identifies if a record is valid and should be trusted or not | TRUE |
product_id | int4 | Nullable | Unique ID of the product. | 102 |
product_sku | varchar | ‘N/A’ | Unique SKU of the product. At least 4 characters. | AB123 |
product_name | varchar | ‘N/A’ | Name of the product
| Beef Sandwich |
is_product_active | bool | Nullable | Identification if product is active, e.g. TRUE or FALSE | TRUE |
category_name | varchar | ‘N/A’ | Product category | Salad |
is_category_active | bool | Nullable | Identification if product’s category is active, e.g. TRUE or FALSE | TRUE |
sub_category_name | varchar | ‘N/A’ | Product sub-category | Hot Salad |
is_sub_category_active | bool | Nullable | Identification if product’s sub-category is active, e.g. TRUE or FALSE | TRUE |
product_class_name | varchar | ‘N/A’ | Product class name. | Drinks |
is_product_class_active | bool | Nullable | Identification if product’s class is active, e.g. TRUE or FALSE | TRUE |
combo_type | varchar | ‘N/A’ | Combo type:
| Split Combo |
product_group_name_list | varchar | ‘N/A’ | List of product’s group names | |Product Group A| |
establishment_id | int4 | Required | Establishment unique Key | 1 |
establishment_name | varchar | Required | Establishment Name | Company-1 |
company_name | varchar | Required | Company name | Company |
company_url | varchar | Required | Company url address | company.revelup.com |
establishment_country | varchar | Required | Establishment Country | US |
establishment_state | varchar | 'N/A' | Establishment State. | GA |
establishment_city | varchar | Required | Establishment City. | Atlanta |
establishment_latitude | float | Nullable | Establishment Latitude | 34.6843294 |
establishment_longitude | float | Nullable | Establishment Longitude | -86.4109566 |
establishment_post_code | varchar | Required | Establishment Post Code | 30314 |
is_establishment_active | bool | Required | Identification if establishment is active, e.g. TRUE or FALSE | TRUE |
establishment_group_name_list | varchar | Required | List of establishment’s group names | |Group1|Group2|Group3| |
timezone | varchar | Required | The configured timezone of the establishment | US/Eastern |
establishment_division | varchar | 'N/A' | Establishment division. If establishment does not belong to a division, then value is N/A . | Division - 1 |
establishment_sub_division | varchar | 'N/A' | Establishment sub-division. If establishment does not belong to a sub-division, then value is N/A . | Sub-division - 1 |
pos_station_name | varchar | Required | POS station name. | POS1 |
revenue_center_name | varchar | 'N/A' | Name of a revenue center a POS station is linked to. | Bar Area |
table_name | varchar | 'N/A' | Table name. | Table 1 |
section_name | varchar | 'N/A' | Name of the section a table is in. | Section 1 |
capacity | varchar | Nullable | Number of people a table can accommodate. | 4 |
kds_start_time | timestamp | Nullable | Item start on KDS date and time in UTC format | |
kds_completed_time | timestamp | Nullable | Item completed on KDS date and time in UTC format | |
expedite_completed_time | timestamp | Nullable | Item completed on expedite date and time in UTC format | |
time_on_kds_in_sec | int8 | Nullable | Time difference between kds_start_time and kds_completed_time in seconds | |
time_on_expedite_in_sec | int8 | Nullable | Time difference between kds_completed_time and expedite_completed_time in seconds | |
total_kitchen_time_in_sec | int8 | Nullable | Total time an item spent on kds and expedite: time_on_kds + time_on_expedite in seconds |
Updated 8 months ago