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
Customer Email Lookup [
uos.customer_email_lkp]Customer Household Lookup [
uos.customer_hshld_lkp]Customer Phone Lookup [
uos.customer_phone_lkp]Customers [
uos.customers]Employees [
uos.employees]Fulfillments [
uos.fulfillments]Inventory Levels [
uos.inventory_levels]Locations [
uos.locations]Order Discount Codes [
uos.order_discount_codes]Order Household Lookup [
uos.order_hshld_lkp]Order Item Fulfillments [
uos.order_item_fulfillments]Order Line Items [
uos.order_line_items]Order Payments [
uos.order_payments]Order Shipping Service [
uos.order_shipping_service]Orders [
uos.orders]Product Variants [
uos.product_variants]Products [
uos.products]Refund Line Items [
uos.refund_line_items]Refunds [
uos.refunds]Sales Report [
uos.sales_report]Transactions [
uos.transactions]Unique Customers [
uos.unique_customers]
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:
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
Householding - these tables are important to enable customer house-holding
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
| Column | Description |
|---|---|
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_phone_lkp
Table Type: Householding
| Column | Description |
|---|---|
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 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 | Latitude 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 | Latitude 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 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 |
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 |
Last updated