Data model: wide_order

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: Web Order Smart Order Table service Quick service Retail Kiosk Pizza Grocery Donation Gas station	Quick Service
order_option	varchar	Required 'Unknown'	Identification of order option, e.g. To-Go, Eat-In, Delivery, etc. If it is a custom order option, then value reflects the custom created name. If it is not recognised it is `Unknown`.	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: Voided Returned Comped N/A - regular state (open/ closed) Unknown - custom value	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 - regular product Gift Coupon Donation Store credit Shipping Delivery charge Bag charge Unknown - item type is not recognised	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 it always reflects the newest name if product_id is NULL then manually entered product name will be shown.	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: Group/Linked Combo Split Combo	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