View: Orders

This article will help you learn about the fields available in the Orders view, the field types, descriptions and how the fields are calculated

Key Topics

Click on the links below to take you to the section where you can learn more about this View and where it is used and how it is created

NOTE:  This View article differs from most of the other View articles because the Orders View pulls fields from several different tables in the Daasity data model:

  • [drp.order_status]
  • [uos.order_discount_codes]
  • [uos.orders]

View Definition - Order Status

Several fields in the Order view are linked to the [drp.order_status] table which generated as part of the Daasity transformation process and has the following fields available/visible for use.

Field Name Field Type Description Source / Calculation
Order Cancelled Dates Dimension Group Date the order was cancelled order_cancel_date
Order Dates Dimension Group Date the order was processed order_date
Order Refunded Dates Dimension Group The date of the first refund  refund_date
Email Address Dimension Customer email address email
Business Order Flag Dimension If the order was flagged as a business order in Amazon CASE WHEN business_order_flag = 1 THEN 'Yes' ELSE 'No' END
Cancelled Flag Dimension If the Order Status is: Cancelled CASE WHEN cancelled_flag = 1 THEN 'Yes' ELSE 'No' END
Daasity Valid Order Flag Dimension Orders with a status of cancelled, fraud, voided, and free orders are NOT valid according to Daasity logic CASE WHEN valid_order_flag_daasity = 1 THEN 'Yes' ELSE 'No' END
Discount Flag Dimension If a valid order and the discount_amount is greater than 0 CASE WHEN discount_flag = 1 THEN 'Yes' ELSE 'No' END
Expedited Shipping Order Flag Dimension the order was flagged with expedited shipping in Amazon CASE WHEN expedited_shipping_flag = 1 THEN 'Yes' ELSE 'No' END
First Order Flag Dimension If a valid order and is the first order CASE WHEN first_order_flag = 1 THEN 'Yes' ELSE 'No' END
Fraud Flag Dimension If the Order Status is: Fraud CASE WHEN fraud_flag = 1 THEN 'Yes' ELSE 'No' END
Free Order Flag Dimension the Order Status is: Free Order CASE WHEN free_order_flag = 1 THEN 'Yes' ELSE 'No' END
Last Order Flag Dimension If a valid order and is the last order CASE WHEN last_order_flag = 1 THEN 'Yes' ELSE 'No' END
Paid Shipping Flag Dimension If a valid order and the shipping_amount is greater than 0 CASE WHEN paid_shipping_flag = 1 THEN 'Yes' ELSE 'No' END
Prime Order Flag Dimension If the order was flagged as a Prime Order in Amazon CASE WHEN prime_order_flag = 1 THEN 'Yes' ELSE 'No' END
Refund Flag Dimension If the Order Status is: Partially Refunded, Refunded CASE WHEN refund_flag = 1 THEN 'Yes' ELSE 'No' END
Subscription Order Flag Dimension If the order_tag contains 'subscription' CASE WHEN subscription_order_flag = 1 THEN 'Yes' ELSE 'No' END
Cancel Reason Dimension The reason an order was cancelled from the Source System cancel_reason
Days Since First Purchase Dimension The time in days of this order from the first valid order CASE
WHEN time_from_first_purchase_seconds IS NULL THEN NULL
WHEN time_from_first_purchase_seconds = 0 THEN 0
WHEN CEIL(-1.00 * time_from_first_purchase_seconds/86400) = 0 THEN 1
ELSE CEIL(-1.00 * time_from_first_purchase_seconds/86400) END
Days Since Previous Purchase Dimension The time in days of this order from the last valid order CASE
WHEN time_from_previous_purchase_seconds IS NULL THEN NULL
WHEN time_from_previous_purchase_seconds = 0 THEN 0
WHEN CEIL(-1.00 * time_from_previous_purchase_seconds/86400) = 0 THEN 1
ELSE CEIL(-1.00 * time_from_previous_purchase_seconds/86400) END
Order Sequence Number Dimension The sequential order number for all orders order_sequence_number
Valid Order Sequence Number Dimension The sequential order number of a valid order valid_order_sequence_number
Valid Order Subscription Sequence Number Dimension valid_order_sub_sequence_number The sequential order number of a subscription order
AVS Result Code Dimension Result Code of Address Verification avs_result_code
Credit Card BIN Dimension Credit Card BIN - the first 6 digits of the credit card: used to identify type of card and issuer credit_card_bin
Credit Card Type Dimension The type of credit card from the transaction credit_card_company
CVV Result Code Dimension Result Code of CVV Check cvv_result_code
Payment Gateway Dimension The Payment Gateway used for the largest amount of the order if multiple Payment Gateways payment_gateway
Transaction Source Dimension The application in the Source System where the order originated transaction_source
Transaction Type Dimension The type of transaction transaction_type
Financial Status Dimension The financial status from the Source System financial_status
Fulfillment Status Dimension The fulfillment status from the Source System fulfillment_status
Order Status Dimension Possible Values: Fraud, Voided, Cancelled, Refunded, Partially Refunded, Free Order (no sales price), Full Gift Card (Entire order paid with a gift card), Partial Gift Card (Part of the order paid with a gift card), Gift Card Purchase, and Full Payment order_status

View Definition - UOS Orders

Several fields in the Order view are linked to the [uos.orders] table which generated as part of the Daasity transformation process and has the following fields available/visible for use.

 

Field Name Field Type Description Source / Calculation
Bill Address 1 Dimension Billing address line 1 the order is billed to billing_address1
Bill Address 2 Dimension Billing address line 2 the order is billed to billing_address2
Bill City Dimension City the order is billed to billing_city
Bill Cleaned Phone Number Dimension Formatted phone number to contain only digits billing_phone_number_clean
Bill Company Dimension Company the order is billed to billing_company
Bill Country Dimension Country the order is billed to billing_country
Bill Country Code Dimension Country code of the country the order is billed to billing_country_code
Bill First Name Dimension First name of customer the order is billed to billing_first_name
Bill Full Name Dimension Full name of customer the order is billed to billing_name
Bill Last Name Dimension Last name of customer the order is billed to billing_last_name
Bill Location Location Latitude and longitude of the address the order is billed to billing_latitude AND billing_longitude
Bill Phone Number Dimension Phone Number of customer the order is billed to billing_phone_number
Bill State Dimension State the order is billed to billing_state
Bill State Code Dimension State code of the state the order is billed to billing_state_code
Bill Zipcode (5 Digits) Dimension Zipcode the order is billed to LEFT(billing_zipcode, 5)
Browser IP Dimension The Browser IP of the source for the order browser_ip
Cart Token Dimension The ID of the cart associated with the order cart_token
Order Code Dimension The customer facing order number from the Source System  order_code
Order Notes Dimension Any notes that are attached to the order  order_notes
Order Tags Dimension The tags for an order from the Source System  order_tags
Referring Site Dimension The website where the customer clicked a link to the shop referring_site

View Definition - UOS Order Discount Codes

Several fields in the Order view are linked to the [uos.order_discount_codes] table which generated as part of the Daasity transformation process and has the following fields available/visible for use.

 

Field Name Field Type Description Source / Calculation
Discount Code Dimension Discount Code used in the order discount_code
Discount Code Type Dimension The type of discount code (% or $) discount_type

Explores

This view is part of the following explores:

  • Current Inventory Level
  • Loop Returns
  • Order & Order Line Revenue
  • Shipstation
  • Subscription

UOS Order Discount Codes - this data is only available in the Order & Order Line Revenue explore

UOS Orders - this data is only available in the Order & Order Line Revenue and the Shipstation Explores

Transformation Code

This view is a Daasity derived table generated by the following code:

  • Order Status: [2700_OST_BAS_order_status.sql]