This article will help you learn about the fields available in the Fulfillments 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 Fulfillments View pulls fields from two different tables in the Daasity data model:
- [uos.fulfillments]
- [uos.order_shipping_service]
View Definition - Fulfillments
Several fields in the Fulfillment view are linked to the [uos.fulfillments] 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 |
Delivery Date | Dimension Group | Date the shipment provider indicated the order was delivered | delivery_date |
Fulfillment Date | Dimension Group | Date the order fulfilled | fulfillment_date |
Fulfillment Location | Dimension | The location where the items were fulfilled | fulfillment_location |
Fulfillment Status | Dimension | The status of the fulfillment: pending, cancelled, success or NULL | fulfillment_status |
Ship Address 1 | Dimension | Shipping address line 1 the fulfillment is shipping to | shipping_address_1 |
Ship Address 2 | Dimension | Shipping address line 2 the fulfillment is shipping to | shipping_address_2 |
Ship City | Dimension | Shipping City | shipping_city |
Ship Cleaned Phone Number | Dimension | Formatted phone number to contain only digits | shipping_phone_number_clean |
Ship Company | Dimension | Company that shipped the fulfillment(s) | shipping_company |
Ship Country | Dimension | Country fulfillment is shipping to | shipping_country |
Ship First Name | Dimension | First name of recipient the fulfillment is shipping to | shipping_first_name |
Ship Full Name | Dimension | Full name of recipient the fulfillment is shipping to | shipping_name |
Ship Last Name | Dimension | Last name of recipient the fulfillment is shipping to | shipping_last_name |
Ship Location | Dimension | Latitude and longitude of the address the fulfillment is shipping to |
shipping_latitude shipping_longitude |
Ship State | Dimension | State the fulfillment is shipping to | shipping_state |
Ship ZipCode (5 Digits) | Dimension | Zipcode the fulfillment is shipping to | LEFT(shipping_zipcode, 5) |
Ship Country Code | Dimension | Country code of the country fulfillment is shipping to | shipping_country_code |
Ship State Code | Dimension | State code of the state fulfillment is shipping to | shipping_state_code |
Delivery Company | Dimension | The company used to ship the package | tracking_company |
Shipment Status | Dimension | The status of the shipment associated with the fulfillment | shipment_status |
Tracking Number | Dimension | The Tracking Number of the Package | tracking_number |
Transit Time | Dimension | Time in Days from when the shipment was fulfilled to the date it was delivered | DATEDIFF(days, fulfillment_date, delivery_date) |
Transit Time - Weekdays | Dimension | Time in Weekdays from when the shipment was fulfilled to the date it was delivered | CASE WHEN date_part('dow',CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE))=6 then DATEDIFF('day', CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE), CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) - 1 - (FLOOR(DATEDIFF('day', CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE), CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) / 7) * 2) + (CASE WHEN DATE_PART('dow', CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) - DATE_PART('dow', CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) IN (1, 2, 3, 4, 5) AND DATE_PART('dow', CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) != 0 THEN 2 ELSE 0 END) ELSE DATEDIFF('day', CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE), CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) - ((FLOOR(DATEDIFF('day', CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE), CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) / 7) * 2) + CASE WHEN DATE_PART(dow, CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) - DATE_PART(dow, CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) != 0 AND DATE_PART(dow,CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) != 6 THEN 2 ELSE 0 END + CASE WHEN DATE_PART(dow, CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) - DATE_PART(dow, CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) IN (1, 2, 3, 4, 5) AND DATE_PART(dow, CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) = 6 THEN 1 ELSE 0 END + CASE WHEN DATE_PART(dow, CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) != 0 AND DATE_PART(dow, CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) = 0 THEN 1 ELSE 0 END + CASE WHEN DATE_PART(dow, CAST((TO_CHAR(TO_DATE(fulfillment_date ), 'YYYY-MM-DD')) AS DATE)) = 0 AND DATE_PART(dow, CAST((TO_CHAR(TO_DATE(delivery_date ), 'YYYY-MM-DD')) AS DATE)) != 0 THEN 1 ELSE 0 END) END |
Transit Time Buckets | Dimension | Time in Days from when the shipment was fulfilled to the date it was delivered |
Bucket [transit_time] into the following buckets:
|
Transit Time Buckets - Weekdays | Dimension | Time in Weekdays from when the shipment was fulfilled to the date it was delivered |
Bucket [transit_time_weekdays] into the following buckets:
|
Average Transit Time | Measure | Number of Fulfillments | AVERAGE(DATEDIFF(days, fulfillment_date, delivery_date)) |
Count Fulfillments | Measure | Average Time from Fulfillment Creation to Delivery | COUNT(DISTINCT(fulfillment_id) |
View Definition - Order Shipping Service
Several fields in the Fulfillment view are linked to the [uos.order_shipping_service] 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 |
Shipping Service | Dimension | Shipping service title from integration data | shipping_title |
Shipping Service Code | Dimension | Shipping code from integration data | shipping_code |
Explores
This view is part of the following explores:
- Order & Order Line Revenue
- Shipstation
Order Shipping Service - this data is only available in the Order & Order Line Revenue explore
Transformation Code
This view is a part of the Daasity Unified Order Schema (UOS) that enables Daasity to combine different order sources into a single data model.
Growth & Pro support the following code:
- [0518_UOS_BAS_SHOPIFY_order_shipping_service.sql]
- [0523_UOS_BAS_SHOPIFY_fulfillments.sql]
- [0553_UOS_BAS_AMAZON_fulfillments.sql]
- [0578_UOS_BAS_SKUBANA_order_shipping_service.sql]
- [0583_UOS_BAS_SKUBANA_fulfillments.sql]
Pro supports the following code if the optional code is enabled:
- [0590_UOS_BAS_SFCC_fulfillments.sql] (Salesforce Commerce Cloud)
- [0613_UOS_PRO_LIGHTSPEED_fulfillments.sql]