Views
      Back to home
      1. Help Center
      2. Daasity Platform | Business User Analytics
      3. Views

      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]