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

ResourceData typeModeDescriptionExample
order_idint4RequiredUnique ID for an Order5
order_created_attimestampRequiredOrder creation date and time in UTC format2018-11-08 16:37:41
order_updated_attimestampRequiredOrder last update date and time in UTC format2018-11-08 16:45:36
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_is_activeboolNullableIdentification of whether employee is active. If order wasn’t updated by employee, then value is NULLTRUE
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
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
Quick Service
order_optionvarcharRequired
'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_statusvarcharRequiredStatus of the orderClosed
is_order_paidboolRequiredOrder payment statusTRUE
order_item_idint4RequiredUnique ID for an order item3
product_name_overridevarchar‘N/A’Overriden product name.Ice Cream Gift
unit_pricenumericRequiredUnit price of the product in the order. It does not include price for modifiers or combos, monetary value.7.000000
unit_costnumericRequiredUnit cost of the product, it does not include price for modifiers or combos, monetary value.4.000000
unit_of_measurevarchar'N/A'Unit, weighted, etc.Unit
quantityint4RequiredNumber of ordered products(s)1
weightfloatNullableNumber of weight for the product, which is weighted
unit_of_measure contains types of data, e.g. Oz.
7.0
item_statusvarchar'N/A'/'Unknown’The status of the item:
  • Voided
  • Returned
  • Comped
  • N/A - regular state (open/ closed)
  • Unknown - custom value
  • Returned
    item_status_reasonvarchar'N/A'/'Unknown’Reason for the item status (e.g. void)Return to inventory
    item_typevarcharRequired/‘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_pricenumericRequiredTotal price for the products(s) in the Order, monetary value.
    Formula:
    unit_price +modifier_amount * quantity
    11.600000
    total_costnumericRequiredTotal price for the products(s) in the Order, monetary value.
    Formula:
    unit_cost +modifier_amount * quantity
    11.600000
    tax_ratefloatRequired% of the tax rate.10.0
    tax_amountnumericRequiredTax amount per product, , monetary value. Formula:
    tax rate * unit price * quantity
    0.400000
    modifiers_amountnumericRequiredTotal amount of all the modifiers of single product, monetary value.1.600000
    modifiers_costnumericRequiredTotal cost of all the modifiers of single product, monetary value.0.170000
    discount_reasonvarchar'N/A'Reason for discounting an item.Basic Prcnt Type 1
    discount_amountnumericRequiredAmount of the discount, monetary value1.500000
    discount_descriptionvarchar'N/A'Description, which contains discount reason and %.{"discount_id": 68, "name": "Basic Prcnt Type 1", "qty": 1}
    service_fee_amountnumericRequiredService fee amount, include taxed and untaxed fee amounts, monetary value1.080000
    service_fee_taxed_amountnumericRequiredService fee taxed amount, monetary value.0.670000
    comps_amountnumericRequiredPrice reductions for defective or damaged goods, monetary value.6.990000
    returns_amountnumericRequiredThe return of goods for a refund of payment, monetary value.1.130000
    voids_amountnumericRequiredPrice of the voided product, monetary value.1.630000
    exchanges_amountnumericRequiredPrice of the exchanged product, monetary value.2.750000
    void_reasonvarchar'N/A'Reason for voiding an item.Order error
    return_reasonvarchar'N/A'Reason for returning an item.Wrong item
    is_valid_recordboolRequiredIdentifies if a record is valid and should be trusted or notTRUE
    product_idint4NullableUnique ID of the product.102
    product_skuvarchar‘N/A’Unique SKU of the product. At least 4 characters.AB123
    product_namevarchar‘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_activeboolNullableIdentification if product is active, e.g. TRUE or FALSETRUE
    category_namevarchar‘N/A’Product categorySalad
    is_category_activeboolNullableIdentification if product’s category is active, e.g. TRUE or FALSETRUE
    sub_category_namevarchar‘N/A’Product sub-categoryHot Salad
    is_sub_category_activeboolNullableIdentification if product’s sub-category is active, e.g. TRUE or FALSETRUE
    product_class_namevarchar‘N/A’Product class name.Drinks
    is_product_class_activeboolNullableIdentification if product’s class is active, e.g. TRUE or FALSETRUE
    combo_typevarchar‘N/A’Combo type:
    • Group/Linked Combo
    • Split Combo
    Split Combo
    product_group_name_listvarchar‘N/A’List of product’s group names|Product Group A|
    establishment_idint4RequiredEstablishment unique Key1
    establishment_namevarcharRequiredEstablishment NameCompany-1
    company_namevarcharRequiredCompany nameCompany
    company_urlvarcharRequiredCompany url addresscompany.revelup.com
    establishment_countryvarcharRequiredEstablishment CountryUS
    establishment_statevarchar'N/A'Establishment State.GA
    establishment_cityvarcharRequiredEstablishment City.Atlanta
    establishment_latitudefloatNullableEstablishment Latitude34.6843294
    establishment_longitudefloatNullableEstablishment Longitude-86.4109566
    establishment_post_codevarcharRequiredEstablishment Post Code30314
    is_establishment_activeboolRequiredIdentification if establishment is active, e.g. TRUE or FALSETRUE
    establishment_group_name_listvarcharRequiredList of establishment’s group names|Group1|Group2|Group3|
    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
    pos_station_namevarcharRequiredPOS station name.POS1
    revenue_center_namevarchar'N/A'Name of a revenue center a POS station is linked to.Bar Area
    table_namevarchar'N/A'Table name.Table 1
    section_namevarchar'N/A'Name of the section a table is in.Section 1
    capacityvarcharNullableNumber of people a table can accommodate.4
    kds_start_timetimestampNullableItem start on KDS date and time in UTC format
    kds_completed_timetimestampNullableItem completed on KDS date and time in UTC format
    expedite_completed_timetimestampNullableItem completed on expedite date and time in UTC format
    time_on_kds_in_secint8NullableTime difference between kds_start_time and kds_completed_time in seconds
    time_on_expedite_in_secint8NullableTime difference between kds_completed_time and expedite_completed_time in seconds
    total_kitchen_time_in_secint8NullableTotal time an item spent on kds and expedite: time_on_kds + time_on_expedite in seconds