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 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 | 219.980000 |
| net_amount | numeric | Required | Amount of sales without discounts, 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 |
| 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 |
| 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 custom order option, then value is Custom. 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 |
Updated 24 days ago