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

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._)

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

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