Unified Order Schema (UOS)

This page provides a detailed description of the Unified Order Schema (UOS) within the Daasity Data Model and defines each table and column in this schema

Overview

The Unified Order Schema (UOS) is a core data model within the Daasity transformation module that helps accelerate development of analytical capability by normalizing all commerce data: eCommerce, Marketplace, Retail and Wholesale. The normalized schema was designed to support complex fulfillment logic like split shipments and multiple delivery groups and be extensible for edge cases like product customization or personalization.

UOS is split into what we consider core tables, required to support an order across multiple commerce channels, and secondary tables that are required to provide functionality to the business. Core tables are built off the concepts of Customer, Product, Order, Fulfillment and Location which all required components of a consumer product purchase.

For product, Brands will use different naming conventions for their merchandising hierarchy from Class, Category, Sub-Category to Style or Product (which can have different meaning) and Item or SKU. We’ve taken the Shopify approach and label tables as Product (want some might call a Style) and Product Variant (what some might call an Item or SKU)

Entity Relationship Diagram (ERD)

Click on this link to view the ERD for the Unified Order Schema (UOS) integration illustrating the different tables and keys to join across tables.

Unified Order Schema Tables

TIP: Many of the tables contain the following fields which can be used to track the data flow from the source system to the integration schema within the database and then to UOS, our normalized order schema

  • __loaded_at: defines when the record was last loaded into this table

  • __synced_at: defines when the record was last replicated from the source system into the integration tables

  • __shop_id: defines which integration the data was replicated from (this value is from the source itself)

  • __uos_integration_id: the ID of the integration within the Daasity platform

  • __uos_source: the general source of the data (Shopify, Amazon, Magento, etc._)h

NOTE: We define tables with three different types:

  1. Core - you need to populate this table if you want to have a well-defined order model even if the source data is not structured this way which is especially true for Amazon

  2. Householding - these tables are important to enable customer house-holding

  3. Secondary - these tables are nice to have for proper Customer/Order analytics

Customer Email Lookup

Purpose: Enables you to lookup an email address across all integrations to identify which integrations have an email address and the name and phone number associated with the email address

Table Name: uos.customer_email_lkp

Table Type: Householding

Customer Household Lookup

Purpose: Enables you to see the results of customer house-holding by being able to lookup each Customer ID that has been associated with a house-holded customer

Table Name: uos.customer_hshld_lkp

Table Type: Householding

Customer Phone Lookup

Purpose: Enables you to lookup a phone number across all integrations to identify which integrations have a phone number and the name and email address associated with the email address

Table Name: uos.customer_phone_lkp

Table Type: Householding

Customers

Purpose: Enables you to capture the most recent customer information from the source system which may be different from the customer information at the Order level. For some systems (ex: Amazon) the information will need to be derived from the order data.

Table Name: uos.customers

Table Type: Core

Employees

Purpose: Enables you to support retail and other sales channels where tracking an employee's interaction with sales

Table Name: uos.employees

Table Type: Core

Fulfillments

Purpose: Enables you to support split-shipment and multiple recipients within a single order by keeping shipping and tracking information with the shipment and not the order.

Table Name: uos.fulfillments

Table Type: Core

Inventory Levels

Purpose: Enables you to track inventory at the SKU, day and location level and can be used to build an inventory history table

Table Name: uos.inventory_levels

Table Type: Core

Locations

Purpose: Enables you to support retail because you order needs to be assigned to a retail location and also to support multiple warehouses by linking to fulfillments

Table Name: uos.locations

Table Type: Core

Order Discount Codes

Purpose: Enables you to support systems that allow for stackable discounts - orders where more than one discount code can be used

Table Name: uos.order_discount_codes

Table Type: Secondary

Order Household Lookup

Purpose: Enables you to see the results of customer house-holding by being able to lookup each order and see the customer from the order system and the house-holded customer

Table Name: uos.order_hshld_lkp

Table Type: Householding

Order Item Fulfillments

Purpose: Enables an item in an order to be linked to a fulfillment. Can be used even if multiple fulfillments are required to fulfill an order line

Table Name: uos.order_item_fulfillments

Table Type: Core

Order Line Items

Purpose: Enables you to capture the items that are sold across multiple sales channels and easier map items across systems as you have a SKU and Listing SKU

Table Name: uos.order_line_items

Table Type: Core

Order Payments

Purpose: Enables you to support multiple payment methods for an order and split an order into multiple payments

Table Name: uos.order_payments

Table Type: Secondary

Order Shipping Service

Purpose: Enables you to support multiple shipping services for an order

Table Name: uos.order_shipping_service

Table Type: Secondary

Orders

Purpose: Enables you to normalize orders from multiple systems and has key fields that are critical for certain sales channels like location and employee.

Table Name: uos.orders

Table Type: Core

Product Variants

Purpose: Enables you to separately track what your customer actually buys and want to have fulfilled across multiple systems and to better have a single view of all the different systems by including the SKU and Listing SKU. Also contains the cost so you can quickly calculate current product Gross margin

Table Name: uos.product_variants

Table Type: Core

Products

Purpose: Enables you to include a parent for each product variant which is important for products that have size and color

Table Name: uos.products

Table Type: Secondary

Refund Line Items

Purpose: Enables you to track the items that were refunded and has a quantity field as a customer may not return all the units of an item purchased

Table Name: uos.refund_line_items

Table Type: Secondary

Refunds

Purpose: Enables you to track each individual refund even when there are multiple refunds to an order

Table Name: uos.refunds

Table Type: Secondary

Sales Report

Purpose: Enables you to build a more financial / transaction based table to create data needs for an accounting team that performs analysis based on the date of the order.

The Daasity code replicates the logic in the Shopify Sales Report for all the financial calculations and Transaction Type and Transaction Details

Table Name: uos.sales_report

Table Type: Secondary

Transactions

Purpose: Enables you to track each transaction associated with an order

Table Name: uos.transactions

Table Type: Secondary

Unique Customers

Purpose: Enables you to keep most recent customer information based on the results of customer householding.

Table Name: uos.unique_customers

Table Type: Householding

PreviousUnified Schema IntroductionNextUnified Notification Schema (UNS)

Last updated