Daasity Data Model: Unified Order Schema (UOS)

This article provides a detailed description of the Unified Order Schema (UOS) within the Daasity Data Model. This article will help you understand the purpose of each table and column in this data model.

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)

The embedded diagram houses the ERD for the Unified Order Schema (UOS) integration illustrating the different tables and keys to join across tables.  Hover over the embedded diagram to reveal controls to zoom and scroll.

Unified Order Schema Tables

• Customer Email Lookup
• Customer Household Lookup
• Customer Phone Lookup
• Customers
• Employees
• Fulfillments
• Inventory Levels
• Locations
• Order Discount Codes
• Order Household Lookup
• Order Item Fulfillments
• Order Line Items
• Order Payments
• Order Shipping Service
• Orders
• Product Variants
• Products
• Refund Line Items
• Refunds
• Sales Report
• Transactions
• Unique Customers

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

Column	Description
EMAIL	Email address
FIRST_NAME	First Name
LAST_NAME	Last Name
PHONE_NUMBER	Phone Number
PLATFORM	Indicates which source platform (Amazon, Shopify, Magento, Lightspeed, etc.) the email address and associated information was sourced from
UPDATED_AT	Date the record was last updated from the integration schema

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

Column	Description
CUSTOMER_ID	Customer ID from the Customer and Order tables enabling a householded individual to reference back to the customer in a souce system
UNIQUE_CUSTOMER_ID	Daasity generated unique customer id representing a householded individual

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_email_lkp

Table Type: Householding

Column	Description
EMAIL	Email address
FIRST_NAME	First Name
LAST_NAME	Last Name
PHONE_NUMBER	Phone Number
PLATFORM	Indicates which source platform (Amazon, Shopify, Magento, Lightspeed, etc.) the email address and associated information was sourced from
UPDATED_AT	Date the record was last updated from the integration schema

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

Column	Description
ADDRESS1	Address line 1
ADDRESS2	Address line 2
CITY	City
CLEAN_PHONE_NUMBER	The phone number stripped of all non-numeric characters
COMPANY	Company
COUNTRY	Country
COUNTRY_CODE	Two letter country code
CREATED_AT	Date the customer was created in the source system
CUSTOMER_ACCOUNT_STATUS	Status of the customer account that relate to the ability for a customer to access their account - usually values like active, pending, inactive
CUSTOMER_ID	Non-householded unique Daasity identifier for the customer record - comprised of the source system and customer identifier from the source system
CUSTOMER_NOTES	Notes from the source system for the customer
CUSTOMER_TAGS	Tags from the source system for the customer
DO_NOT_SHARE_FLAG	Flag indicating if the customer has requested to opt-out of data sharing such as marketing co-ops, etc.
EMAIL	Email address
FIRST_NAME	First Name
LAST_NAME	Last Name
MARKETING_OPT_OUT_FLAG	Flag indicating if the customer has requested to opt-out of marketing
PHONE_NUMBER	Phone Number
STATE	State or Province
STATE_CODE	Two letter code for the State or Province
STORE_CUSTOMER_ID	Identifier for the customer in the source system
UPDATED_AT	Date the record was last updated from the integration schema
VERIFIED_EMAIL	Flag indicating if the email address has been verified
ZIPCODE	Zip or postal code

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

Column	Description
EMPLOYEE_ID	Unique Daasity identifier for the employee - comprised of the source system and the employee id from the source system
FIRST_NAME	First Name
LAST_NAME	Last Name
STORE_EMPLOYEE_ID	Identifier for the employee in the source system

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

Column	Description
CONVERTED_CURRENCY	The currency of the amounts for this record
CREATED_AT	Date the fulfillment was created
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
DELIVERY_DATE	Date the shipment was delivered
ESTIMATED_ARRIVAL_DATE	Date the fulfillment (shipment) is expected to arrive
FULFILLMENT_COST	Cost to fulfill this shipment
FULFILLMENT_DATE	Date the shipment was fulfilled (ie. Picked and packed)
FULFILLMENT_ID	Daasity ID for the fulfillment - usually comprised of the source system and fulfillment id but for some commerce system must be generated
FULFILLMENT_LOCATION	Location where the shipment was fulfilled
FULFILLMENT_LOCATION_ID	Location ID where the shipment was fulfilled
FULFILLMENT_STATUS	Status of the fulfillment - usually Fulfilled, Partially Fulfilled, Unfulfilled or sometimes Null
ORDER_ID	Order related to the fulfillment
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
SHIPMENT_STATUS	Status of the shipment
SHIPPING_ADDRESS1	Shipping Address Line 1
SHIPPING_ADDRESS2	Shipping Address Line 2
SHIPPING_CITY	Shipping City
SHIPPING_COMPANY	Shipping Company
SHIPPING_COST	Cost to ship the fulfillment
SHIPPING_COUNTRY	Shipping Country
SHIPPING_COUNTRY_CODE	Two letter country code
SHIPPING_FIRST_NAME	Shipping First Name
SHIPPING_LAST_NAME	Shipping Last Name
SHIPPING_LATITUDE	Lattitude of the delivery address to enable geo-mapping
SHIPPING_LONGITUDE	Longitude of the delivery address to enable geo-mapping
SHIPPING_NAME	Shipping Name
SHIPPING_PHONE_NUMBER	Shipping Phone Number
SHIPPING_PHONE_NUMBER_CLEAN	The shipping phone number stripped of all non-numeric characters
SHIPPING_STATE	Shipping State
SHIPPING_STATE_CODE	Two letter code for the State or Province
SHIPPING_ZIPCODE	Shipping zip or postal code
STORE_FULFILLMENT_ID	The fulfillment id from the source system (if applicable)
TRACKING_COMPANY	Name of the company used to ship/deliver the shipment
TRACKING_NUMBER	Number that can be used to track the shipment
UPDATED_AT	Date the record was last updated from the integration schema

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

Column	Description
AVAILABLE_QUANTITY	Inventory that is available
BACK_ORDER_QUANTITY	Inventory that is back-ordered to fulfill current orders
COMMITTED_QUANTITY	Inventory that is committed to orders and unavailable for fulfillment
IN_TRANSIT_QUANTITY	Inventory that is in-transit to the fulfillment location
INVENTORY_DATE	Date of the inventory levels
INVENTORY_ITEM_ID	ID of the item from the source system
INVENTORY_LEVEL_ID	Unique ID for each record
INVENTORY_QUANTITY	Inventory quanity
LISTING_SKU	SKU that is used to list or sell the item
LOCATION_ID	ID for the location where the inventory is located
LOCATION_NAME	Name of the location where the inventory is located
ON_ORDER_QUANTITY	Inventory that is on order and expected to be delivered to the fulfillment location
SKU	SKU that is used to fulfill the item
STORE_INVENTORY_ID	ID of the inventory item in the source system
VARIANT_ID	Product variant the inventory item is associated with

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

Column	Description
ADDRESS1	Address line 1
ADDRESS2	Address line 1
CITY	City
COUNTRY	Country
CREATED_AT	Date the store or warehouse was created in the source system
DELETED_AT	Date the store or warehouse was removed from the source system
LOCATION_ID	Daasity ID of the location (store or warehouse) in the source system - comprised of the source system and location id
LOCATION_NAME	Name of the location (store or warehouse)
PHONE_NUMBER	Phone Number
STATE	State or Province
STORE_LOCATION_ID	ID of the location (store or warehouse) in the source system
UPDATED_AT	Date the store or warehouse information was last updated in the source system
ZIPCODE	Zip or postal code

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

Column	Description
CONVERTED_CURRENCY	The currency of the amounts for this record
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
DISCOUNT_AMOUNT	Amount of the discount applied
DISCOUNT_CODE	Code that was entered for the discount
DISCOUNT_TYPE	Type of discount (could be dollar, percent, line, order, etc.)
ORDER_DISCOUNT_ID	Unique ID for the order and discount
ORDER_ID	Order related to the discount
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema

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

Column	Description
CUSTOMER_ID	Daasity ID for the customer from the source system
ORDER_CODE	Customer facing code for the order
ORDER_DATE	Date of the order was placed
ORDER_ID	ID of the related Order
UNIQUE_CUSTOMER_ID	Unique Daasity identifier for the householded customer

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

Column	Description
FULFILLMENT_ID	Daasity ID for the fulfillment - usually comprised of the source system and fulfillment id but for some commerce system must be generated
ITEM_FULFILLMENT_STATUS	Status of the fulfillment for the item - usually Fulfilled or Unfulfilled
ORDER_ITEM_FULFILLMENT_ID	Daasity ID to identify the item to be fulfilled - usually the source system and source order item fulfillment id if it exists
ORDER_LINE_ID	ID of the line item of the order the item fulfillment is related to
ORDERED_QUANTITY	Number of units that were ordered
REMAINING_TO_FULFILL	Number of units that remain to be fulfilled
SKU	SKU that is used to fulfill the item
STORE_ORDER_ITEM_FULFILLMENT_ID	The item fulfillment id from the integration schema
VARIANT_ID	ID used to relate the item to the product catalog

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

Column	Description
CONVERTED_CURRENCY	The currency of the amounts for this record
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
DISCOUNT_AMOUNT	Amount of the discount applied at the item level
GIFT_CARD_FLAG	Flag to indicate if the item is a gift card
LISTING_SKU	SKU that is used to list or sell the item
ORDER_ID	ID used to relate the item to the order
ORDER_LINE_ID	Primary key for the order line - usually the source system and the order line id from the integration schema
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
PLATFORM_COMMISSION_FEES	Item level commission fees for order from the source system - i.e. Amazon Seller Fees at the item level
PRICE	Item level price charged for the item
PRODUCT_ID	ID used to relate the item to the product catalog at the style level
PRODUCT_NAME	Name of the product at the time of purchase
QUANTITY	Number of units purchased
REFUND_FLAG	Flag to indicate if the item was refunded
SHIPPED_ITEM_FLAG	Flag to indicate if the was has been shipped
SKU	SKU for the item that was purchased
SKU_COST	Cost of the SKU at the time the item was purchased
STORE_ORDER_LINE_ID	The ID of the order line from the integration schema
TAX_AMOUNT	Tax amount at the item level
VARIANT_ID	ID used to relate the item to the product catalog at the item level
VARIANT_NAME	Name of the item (variant) at the time of the purchase
VENDOR	Name of the vendor that the SKU was purchased from (if applicable)
WEIGHT	Weight of the SKU (per single unit)

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

Column	Description
ORDER_ID	ID used to relate the payment to the Order
PAYMENT_GATEWAY_NAME	Name of the payment method
PAYMENT_ID	Primary key for the order payment method

Order Shipping Service

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

Table Name: uos.order_shipping_service

Table Type: Secondary

Column	Description
CARRIER_IDENTIFIER	The carrier code for the shipping method
ORDER_ID	ID used to relate the shipping service to the Order
ORDER_SHIPPING_LINE_ID
SHIPPING_CODE	Code used in fulfillment for the shipping method
SHIPPING_DISCOUNT	Discount applied to shipping
SHIPPING_LINE_ID
SHIPPING_TITLE	Title / name of the shipping method from the source system

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

Column	Description
AMOUNT_CHARGED	The total amount the customer was charged
BILLING_ADDRESS1	Address line 1
BILLING_ADDRESS2	Address line 2
BILLING_CITY	City
BILLING_COMPANY	Company
BILLING_COUNTRY	Country
BILLING_COUNTRY_CODE	Two letter country code
BILLING_FIRST_NAME	First Name
BILLING_LAST_NAME	Last Name
BILLING_LATITUDE	Lattitude of the billing address to enable geo-mapping
BILLING_LONGITUDE	Longitude of the billing address to enable geo-mapping
BILLING_NAME	Full Name
BILLING_PHONE_NUMBER	Phone Number
BILLING_PHONE_NUMBER_CLEAN	The phone number stripped of all non-numeric characters
BILLING_STATE	State or Province
BILLING_STATE_CODE	Two letter code for the State or Province
BILLING_ZIPCODE	Zip or postal code
BROWSER_IP	IP of the Browser that placed the order (if applicable)
BUSINESS_ORDER_FLAG	Flag to indicate if the order was placed by a business
CANCEL_DATE	Date the order was cancelled
CANCEL_REASON	Reason the order was cancelled
CART_DISCOUNT_AMOUNT	Total discount amount at the cart level
CART_TOKEN	Token to recreate the cart
CONVERTED_CURRENCY	The currency of the amounts for this record
CREATED_AT	Date the order was created
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
CUSTOMER_ID	Daasity ID for the customer
CUSTOMER_LANGUAGE	Language the customer used to place the order
EMAIL	Email address
EMPLOYEE_ID	ID used to relate to the employee if an employee was used to place the order
EXPEDITED_SHIPPING_ORDER_FLAG	Flag to indicate if the order has expedited shipping
FINANCIAL_STATUS	Financial status of the payment - usually paid, pending, voided
FULFILLMENT_STATUS	Fulfillment status of the order - usually fulfilled, partially fulfilled or blank
LOCATION_ID	ID of the location where the order was placed (if applicable)
ORDER_CODE	Customer facing code for the order
ORDER_DATE	Date the order was placed
ORDER_ID	Daasity ID for the order - usually the sourcey system and the order id from the source system
ORDER_NOTES	Notes placed on the order
ORDER_SOURCE	Identifies how the order was generated (web, manual entry, POS, etc.)
ORDER_TAGS	Tags on the order
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
PLATFORM_COMMISSION_FEES	Order level commission fees for order from the source system - i.e. Shopify fees or Amazon order level fees
PRIME_ORDER_FLAG	Flag to indicate if the order was a prime order
PRODUCT_AMOUNT	Total amount for all the items purchased in the order
REFERRING_SITE	URL that was responsible for the incoming traffic
REFUND_AMOUNT	Total refund amount
REFUND_FLAG	Flag to indicate if the order had a refund
REPLACEMENT_ORDER_FLAG	Flag to indicate if the order had a replacement
SALES_CHANNEL	Identifies where the order was generated - online, specific app connected to the store, point of sales, etc.
SHIPPING_AMOUNT	Total amount charged for shipping
STORE_ORDER_ID	ID for the order from the integration schema
TAX_AMOUNT	Total amount charged for tax
TAX_RATE	Tax rate applied to the order
TAXES_INCLUDED_FLAG	Flag to indicate if tax was included in the order (i.e. VAT)
TOTAL_WEIGHT	Total weight of the order
UPDATED_AT	Date the order was last updated in the source system

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

Column	Description
BARCODE	Alphanumeric version of barcode
CONVERTED_CURRENCY	The currency of the amounts for this record
COUNTRY_OF_ORIGIN	Country where the item originated or manufactured
CREATED_AT	Date the the item was created in the source system
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
IMAGE_POSITION	Position of the image for the URL that was provided
IMAGE_URL	URL of the image for the item
INVENTORY_ITEM_ID	ID to used to relate to inventory levels
INVENTORY_TRACKED_FLAG	Flag to indicate if inventory is tracked for this item
LISTING_SKU	SKU that is used to list or sell the item
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
PRICE	Price of the item
PRODUCT_ID	ID used to relate the item to the Product (parent)
REQUIRES_SHIPPING_FLAG	Flag to indicate if the product requires shipping
SKU	SKU for the item
SKU_COST	Current cost of the SKU
STORE_VARIANT_ID	Product variant id from the integration schema
UPDATED_AT	Date the item was last updated in the source system
VARIANT_ID	Daasity ID for the item - usually the source system and the product variant id
VARIANT_NAME	Current name of the item
WEIGHT	Weight (numeric) of the item
WEIGHT_UNIT	Units in which the weight is supplied

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

Column	Description
CREATED_AT	Date the product was created
PRODUCT_ID	Daasity ID for the product - usually the source system and the ID of the product in the source system
PRODUCT_NAME	Current name of the product
PRODUCT_TAGS	Tags on the product
PRODUCT_TYPE	Type of product
PUBLISHED_AT	Date the product was published and available purchase
PUBLISHED_SCOPE	Where the product was published (website, retail, etc.)
STORE_PRODUCT_ID	ID of the product from the integration schema
UPDATED_AT	Date the product was last updated in the source system
VENDOR_NAME	Name of the vendor that the product was purchased from (if applicable)

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

Column	Description
CREATED_AT	Date the item refund was created
ORDER_ID	ID used to relate the item refund to the order
ORDER_LINE_ID	ID used to relate the item refund to the order item
QUANTITY	Number of units refunded
REFUND_ID	ID used to relate the item refund to the refund
REFUND_LINE_AMOUNT	Amount refunded
REFUND_LINE_ITEM_ID	Daasity ID of the refund line item - usually the source system and the refund line item
REFUND_LINE_TAX_AMOUNT	Tax amount refunded
STORE_REFUND_LINE_ITEM_ID	ID of the refund line item from the source system

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

Column	Description
CONVERTED_CURRENCY	The currency of the amounts for this record
CREATED_AT	Date the refund was created
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
EMPLOYEE_ID	ID of the employee that processed the refund (if applicable)
ORDER_ID	ID used to relate the refund to the order
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
REFUND_AMOUNT	Total amount of the refund
REFUND_DATE	Date the refund was processed
REFUND_ID	Daasity ID for the refund - usually the source system and id of the refund from the source system
REFUND_SHIPPING_AMOUNT	Shipping amount refunded
REFUND_TAX_AMOUNT	Tax amount refunded
STORE_REFUND_ID	ID of the refund from the source system

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

Column	Description
BUSINESS_CHANNEL	Channel (D2C, Wholesale, etc.) for the Order
BUSINESS_UNIT	Business Unit for the Order
CONVERTED_CURRENCY	The currency of the amounts for this record
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
CUSTOMER_ID	Daasity ID for the customer
DISCOUNT_AMOUNT	Total discount amount
EMAIL_ADDRESS	Email address
FULFILLMENT_AMOUNT	Total fulfillment amount
GIFTCARD_ONLY_ORDER	Flag to indicate if the order only contained a Gift Card
GROSS_SALES	Total Gross Sales (Shopify definition where Gross Sales = Price * Units)
LISTING_SKU	SKU that is used to list or sell the item
NET_SALES	Total Net Sales (Gross - Refunds)
ORDER_CODE	Customer facing code for the order
ORDER_DATE	Date the order was placed
ORDER_ID	Daasity ID for the order - usually the sourcey system and the order id from the source system
ORDER_LINE_ID	Primary key for the order line - usually the source system and the order line id from the integration schema
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
PRICE	Price of the item
PRODUCT_NAME	Name of the product at the time of purchase
QUANTITY	Quantity purchased
REFUND_AMOUNT	Total refund amount
REFUND_DATE	Date the refund was processed
SHIPPING_AMOUNT	Total amount charged for shipping
SKU	SKU of the item at time of purchase
SKU_COST	Cost of the SKU at the time the item was purchased
STORE_COUNTRY	Country for the store (sales channel) where the order was placed
STORE_CUSTOMER_ID	ID of the customer from the source system
STORE_INTEGRATION_NAME	Name of the integration for the order from the Daasity system
STORE_NAME	Name of the store from the Daasity system
STORE_ORDER_ID	ID of the order from the integration schema
STORE_ORDER_LINE_ID	ID of the order line from the integration schema
STORE_PRODUCT_ID	ID of the product from the integration schema
STORE_TRANSACTION_ID	ID of the transaction from the integration schema
STORE_TYPE	Type of store (ecommerce, retail, etc.) where the order was placed
STORE_VARIANT_ID	ID of the variant from the integration schema
TAX_AMOUNT	Total amount charged for tax
TRANSACTION_DATE	Date the transaction was processed
TRANSACTION_DETAIL_TYPE	Indicates if the record associated with the order is an Order or Refund and if it is at the Order or Item level
TRANSACTION_ID	ID of the transaction from the integration schema
TRANSACTION_TYPE	Indicates if the record is an Order or Refund record

Transactions

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

Table Name: uos.transactions

Table Type: Secondary

Column	Description
AVS_RESULT_CODE	Result code from Address Verification (AVS) in credit card processing
CONVERTED_CURRENCY	The currency of the amounts for this record
CREATED_AT	Date the transaction was created
CREDIT_CARD_BIN	The first 6 digits of the credit card number
CREDIT_CARD_COMPANY	The type of credit card uses (Amex, Visa, Mastercard, etc.)
CURRENCY_CONVERSION_RATE	Conversion rate used for the currency conversion for this record
CVV_RESULT_CODE	Result code from Card Verification (CVV) in credit card processing
ORDER_ID	ID used to relate the transaction to the Order
ORIGINAL_CURRENCY	The currency of the amounts in the integration schema
STORE_TRANSACTION_ID	ID of the transaction from the source system
TRANSACTION_AMOUNT	Amount of the transaction
TRANSACTION_AUTHORIZATION	Authorization code for the transaction
TRANSACTION_DATE	Date the transaction was processed
TRANSACTION_GATEWAY	Gateway used to process the transaction
TRANSACTION_ID	Daasity ID for the transaction - usually the source system and transaction id
TRANSACTION_SOURCE	Application that was used to create the transaction (ex: web, app, mobile, etc.)
TRANSACTION_STATUS	Status of the transaction (ex: success, failure, error)
TRANSACTION_TYPE	Type of transaction (ex: authorization, sale, refund, etc.)

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

Column	Description
ADDRESS1	Most recent address for the householded customer
ADDRESS2	Most recent address for the householded customer
CITY	Most recent city for the householded customer
COMPANY	Most recent company (if applicable) for the household customer
COUNTRY	Most recent country for the householded customer
COUNTRY_CODE	The two letter country code of the county
CREATED_AT	The date the householded customer was first created
EMAIL	Most recent email address for the householded customer
FIRST_NAME	Most recent first name for the householded customer
LAST_NAME	Most recent last name for the householded customer
PHONE_NUMBER	Most recent phone number for the householded customer
STATE	Most recent state (province) for the householded customer
STATE_CODE	The two letter state code for the state
UNIQUE_CUSTOMER_ID	Unique Daasity identifier for the householded customer
UPDATED_AT	Date the unique customer was last householded
ZIPCODE	Most recent zip (postal) code for the householded customer