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
- View Definition - Order Status
- View Definition - Orders
- View Definition - Order Discount Codes
- Explores
- Transform Code
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 | |
| AOV | Dimension | Can be used to build customized AOV ranges | gross_sales |
| 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]