Data model: wide_order

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?
  • etc.

Data Objects

Column nameData typeModeDescriptionExample
order_idint4RequiredUnique identifier for an order.332
gross_amountnumericRequiredGross amount of sales, monetary value. Formula:

net_amount + discount_amount
219.980000
net_amountnumericRequiredAmount of sales without discounts, monetary value.209.480000
tax_amountnumericRequiredTotal tax amount per order, monetary value.0.130000
tax_excluded_amountnumericRequiredTax excluded amount per order, monetary value.0.130000
discount_amountnumericRequiredTotal amount of discounts per order, monetary value.10.500000
service_fee_amountnumericRequiredService 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_amountnumericRequiredSales 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
created_by_employee_first_namevarchar'N/A'First name of employee who created an order. If order wasn’t created by employee, then value is N/AJohn
created_by_employee_last_namevarchar'N/A'Last name of employee who created an order. If order wasn’t created by employee, then value is N/ADoe
created_by_employee_is_activeboolNullableIdentification of whether employee is active. If order wasn’t created by employee, then value is NULLTRUE
last_updated_by_employee_first_namevarchar'N/A'First name of last employee who updated an order. If order wasn’t updated by employee, then value is N/AJohn
last_updated_by_employee_last_namevarchar'N/A'Last name of last employee who created an order. If order wasn’t created by employee, then value is N/ADoe
last_updated_by_employee_is_activeboolNullableIdentification of whether employee is active. If order wasn’t updated by employee, then value is NULLTRUE
order_created_attimestampRequiredOrder creation date and time in UTC format.2019-10-15 07:13:31
order_updated_attimestampRequiredOrder last update date and time in UTC format.2019-10-15 07:20:32
order_sourcevarcharRequired

‘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_optionvarcharRequired

‘Unknown’
Identification of order option, e.g. To-Go, Eat-In, Delivery or etc.

If it is custom order option, then value is Custom.

If it is not recognised it is Unknown.
To Go
order_statusvarcharRequiredLast order status, e.g. open or closed.Open
is_order_paidboolRequiredIdentification if order is paid.TRUE
is_receiptless_returnboolRequiredIdentification if order is receiptless return. Such orders are created, when there is a need to return money to the customer.TRUE
is_valid_recordboolRequiredIdentifies, if a record is valid and should be trusted or not.TRUE
pos_station_namevarcharRequiredPOS station name.POS1
revenue_center_namevarchar'N/A'Name of a revenue center a POS station is linked to.Bar Area
establishment_idint4RequiredEstablishment unique Key.1
establishment_namevarcharRequiredEstablishment Name.Company - 1
establishment_cityvarcharRequiredEstablishment City.San Francisco
establishment_statevarchar'N/A'Establishment State.CA
establishment_countryvarcharRequiredEstablishment Country.US
company_namevarcharRequiredCompany name.Company
company_urlvarcharRequiredCompany url address.company.revelup.com
establishment_latitudefloatNullableEstablishment Latitude.37.7979623
establishment_longitudefloatNullableEstablishment Longitude.-152.0074200
establishment_post_codefloatRequiredEstablishment Post Code.94100
is_establishment_activeboolRequiredIndicates if establishment is active.TRUE
establishment_group_name_listvarcharRequiredEstablishment group name list.|group1|group67|Group34|
timezonevarcharRequiredThe configured timezone of the establishmentUS/Eastern
establishment_divisionvarchar'N/A'Establishment division. If establishment does not belong to a division, then value is N/A.Division - 1
establishment_sub_divisionvarchar'N/A'Establishment sub-division. If establishment does not belong to a sub-division, then value is N/A.Sub-division - 1