View: Fulfillments

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:

  • 0-1
  • 1-3
  • 3-5
  • 5-10
  • 10-20
  • > 20
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:

  • 0-1
  • 1-3
  • 3-5
  • 5-10
  • 10-20
  • > 20
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]