Data model: wide

Description

wide_order data model has high level information required to do revenue analytics on an order level. It helps understand how each of the factors in the Data Objects list is affecting the business. The way data is structured in this data model, ensures that every business can easily focus on what matters the most. And it doesn't matter if business goals may be different from brand to brand. This approach allows analysts and decision makers to identify areas that are small enough to be able to focus on, and will have a significant enough impact to care about.

Revel Data Connector users using this data model can answer questions like:

What are my sales trends over the selected time period?
How are my sales compared to previous time periods?
Is there a seasonal variance to the business?
How does all of the establishments' performance compare?
How does one geographic area compare to other areas?
What are the revenue trends for divisions of establishments?
Which employees make the biggest contribution to business performance?
What are the trends by revenue centre or specific POS?
What order options customers prefer and what are the order channels?
What is the average ticket size for every channel?
What are service fee trends?
Speed of service: how long does each stage of the order (time to payment, time in the kitchen, time to close) take?
Table service: what are the sales or revenue of each table in the restaurant?
etc.

Data Objects

Column name	Data type	Mode	Description	Example
order_id	int4	Required	Unique identifier for an order.	332
gross_amount	numeric	Required	Gross amount of sales, monetary value. Formula: net_amount + discount_amount + coupons_amount	219.980000
net_amount	numeric	Required	Amount of sales without discounts and coupons, monetary value.	209.480000
tax_amount	numeric	Required	Total tax amount per order, monetary value.	0.130000
tax_excluded_amount	numeric	Required	Tax excluded amount per order, monetary value.	0.130000
discount_amount	numeric	Required	Total amount of discounts per order, monetary value.	10.500000
order_discount_amount	numeric	Required	Amount of order-level discounts per order, monetary value.	8.5
coupons_amount	numeric	Required	Total amount of coupons per order, monetary value.
service_fee_amount	numeric	Required	Service fee amount for an order before discounts, allowance and returns. Including cash, credit card, debit card and trade credit sales. Monetary value.	9.980000
sales_amount	numeric	Required	Sales total amount for an order before discounts, comps and returns. Pre-tax. Including cash, credit card, debit card and trade credit sales. Monetary value.	133.000000
gift_card_amount	numeric	Required	Total amount of gift cards sold per order, monetary value.	25.600000
donation_amount	numeric	Required	Total amount of donations per order, monetary value.	10.500000
store_credit_amount	numeric	Required	Total amount of store cred	12.540000
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_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
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
order_created_at	timestamp	Required	Order creation date and time in UTC format.	2019-10-15 07:13:31
order_updated_at	timestamp	Required	Order last update date and time in UTC format.	2019-10-15 07:20:32
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	Web Order
order_option	varchar	Required ‘Unknown’	Identification of order option, e.g. To-Go, Eat-In, Delivery or 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	Last order status, e.g. open or closed.	Open
is_order_paid	bool	Required	Identification if order is paid.	TRUE
is_receiptless_return	bool	Required	Identification if order is receiptless return. Such orders are created, when there is a need to return money to the customer.	TRUE
is_valid_record	bool	Required	Identifies, if a record is valid and should be trusted or not.	TRUE
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
establishment_id	int4	Required	Establishment unique Key.	1
establishment_name	varchar	Required	Establishment Name.	Company - 1
establishment_city	varchar	Required	Establishment City.	San Francisco
establishment_state	varchar	'N/A'	Establishment State.	CA
establishment_country	varchar	Required	Establishment Country.	US
company_name	varchar	Required	Company name.	Company
company_url	varchar	Required	Company url address.	company.revelup.com
establishment_latitude	float	Nullable	Establishment Latitude.	37.7979623
establishment_longitude	float	Nullable	Establishment Longitude.	-152.0074200
establishment_post_code	float	Required	Establishment Post Code.	94100
is_establishment_active	bool	Required	Indicates if establishment is active.	TRUE
establishment_group_name_list	varchar	Required	Establishment group name list.	\|group1\|group67\|Group34\|
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
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
order_opened_time	timestamp	Nullable	Order opened date and time in UTC format. If an order was opened several times, shows the first open time.
payment_completed_time	timestamp	Nullable	Order paid date and time in UTC format. Reflects the last payment related to an order. Null if an order was not paid.
order_closed_time	timestamp	Nullable	Order closed date and time in UTC format. If an order was closed several times, shows the first closed time. Null for an open order.
time_to_payment_in_sec	int8	Nullable	Time difference between order_opened_time and payment_completed_time in seconds. Null if the order was not paid.
time_to_close_in_sec	int8	Nullable	Time difference between order_opened_time and order_closed_time in seconds. Null for an open order.
max_item_time_on_kds_in_sec	int8	Nullable	Maximum time in seconds that an item from an order spent on kds. Voided, comped, returned and deleted items are not included.
max_item_time_on_expedite_in_sec	int8	Nullable	Maximum time in seconds that an item from an order spent on expedite. Voided, comped, returned and deleted items are not included.
avg_item_time_on_kds_in_sec	int8	Nullable	Average time in seconds that all items from an order spent on kds. Voided, comped, returned and deleted items are not included.
avg_item_time_on_expedite_in_sec	int8	Nullable	Average time in seconds that all items from an order spent on expedite. Voided, comped, returned and deleted items are not included.