Transforms
      Back to home
      1. Help Center
      2. Technical Documentation
      3. Transforms

      2700_OST_BAS_order_status Transformation

      Target Database Table

      drp.order_status.

      Script Description and Logic

      Pulls customer ID, email, and several order details including cancellation, financial, and fulfillent statuses, as well as payment, refund, and discount amounts. These are then used to construct order statuses and flags to classify orders as full payment, fully refunded, fraud, etc. Orders are also tagged as valid or invalid, and valid orders are given a valid order sequence number for LTV and other analyses.

      Source Tables Used In Script

      Schema Table (or Derived Table) Name Table Type Purpose
      drp integration_mapping database Ensure unique records
      drp_staging uos_orders_to_update database Selects order ID's from the last load into uos.orders, as well as id's from other uos tables that were refreshed within 7 days of the last uos.orders refresh
      N/A all_orders derived Pulls customer and order dimensions from uos.orders_to_update, along with counts of feature and free items for an order and shop ID
      N/A discount derived Sums discount amount by order line and shop ID appended with cart discount amount by order and shop ID from uos.orders_to_update
      N/A discount_rollup derived Rolls up cart discount with line discount amounts by order ID
      N/A first_valid_order derived Selects order ID, date, and email for a customer's first valid order
      N/A gift_card_purchase_only derived Determines if the line item purchase was paid for only with a gift card
      N/A gift_card_rank derived Determines whether the order-level purchase was made fully with a gift card, or only partially using a gift card
      N/A gift_card_use_type derived Selects the max gift card rank on an order shop ID level
      N/A last_sync_date derived Find the last load date from uos.orders
      N/A line_item_rollup derived Sums by order and shop ID line items price, number of feature and gift items, and gift card sales amount from the orders_to_update list
      N/A orders_since_last_sync derived Count the number of distinct orders if the last load date is within 7 days of the last load date on uos.orders
      N/A order_status derived Assign a status to an order based on cancel and financial status, and refund and gift card amounts
      N/A payment_type_rows derived Counts the number of different payment types in an order to help determine whether the order is a gift card only order
      N/A refund_rollup derived Rolls up refund amount on the order level, and creates the refund date as the minimum create dates of all refund lines
      N/A subscription_order derived Creates a valid subscription order sequence number where the order has an order tag like 'subscription'
      N/A transaction_rank derived Ranks uos.orders_to_update according to whether the transaction type is sale, authorization, or something else
      N/A transactions derived Ranks transactions based on type to select only the highest ranked transaction for final table inclusion
      N/A valid_order_facts derived Creates ascending and descending order sequence counts for all valid orders from uos.orders_to_update
      uos order_discount_codes database Primary
      uos order_hshld_lkp database Primary
      uos order_line_items database Primary
      uos order_payments database Primary
      uos orders database Primary
      uos refund_line_items database Primary
      uos refunds database Primary
      uos transactions database Primary

      SQL Flow

      To View Larger Image, Right Click and Choose Open Image In New Tab.

      2700_OST_BAS_order_status Transformation 

      Calculated and Derived Fields

      Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
      last_load_date TIMESTAMP uos.orders __loaded_at MAX(__loaded_at)
      total_line_items_price DECIMAL(20,4) uos.order_line_items price SUM(NVL(order_line_items.price,0) * NVL(order_line_items.quantity,0))
      total_feature_items BIGINT uos.order_line_items quantity SUM(CASE WHEN NVL(order_line_items.price,0) = 0 THEN 0 ELSE NVL(order_line_items.quantity,0) END)
      total_gift_items BIGINT uos.order_line_items quantity SUM(CASE WHEN NVL(order_line_items.price,0) = 0 THEN NVL(order_line_items.quantity,0) ELSE 0 END)
      total_free_items BIGINT uos.order_line_items quantity SUM(CASE WHEN NVL(order_line_items.price,0) = 0 THEN NVL(order_line_items.quantity,0) ELSE 0 END)
      gift_card_sales_amount DECIMAL(20,4) uos.order_line_items price SUM(CASE WHEN order_line_items.gift_card_flag = TRUE THEN (NVL(order_line_items.price,0) * NVL(order_line_items.quantity,0)) ELSE 0 END)
      gift_card_purchase_only BOOLEAN uos.order_line_items gift_card_sales_amount CASE WHEN (gift_card_sales_amount > 0 AND total_feature_items = 1) THEN 1 ELSE 0 END
      refund_amount DECIMAL(20,4) uos.refunds refund_amount SUM(NVL(refund_amount,0.00))
      gift_card_rank INT uos.order_payments number_payment_types CASE WHEN (opg.payment_gateway_name = 'gift_card' AND (ptr.number_payment_types = 1)) THEN 1 WHEN (opg.payment_gateway_name = 'gift_card' AND (ptr.number_payment_types > 1)) THEN 2 END
      order_status VARCHAR uos.orders financial_status CASE
      WHEN cancel_reason = 'fraud' THEN 'Fraud'
      WHEN financial_status = 'voided' AND NVL(refund_amount,0) = 0 THEN 'Voided'
      WHEN order_cancel_date IS NOT NULL THEN 'Cancelled'
      WHEN refund_amount = total AND refund_amount > 0 THEN 'Refunded'
      WHEN refund_amount > 0 AND total > refund_amount THEN 'Partially Refunded'
      WHEN total = 0 THEN 'Free Order'
      WHEN percent_gift_card > 0 AND percent_gift_card < 1 THEN 'Partial Gift Card Purchase'
      WHEN percent_gift_card = 1 THEN 'Gift Card Purchase'
      WHEN gift_card_rank = 1 THEN 'Gift Card Usage'
      WHEN gift_card_rank = 2 THEN 'Partial Gift Card Usage'
      ELSE 'Full Payment'
      END
      valid_order_sequence_number BIGINT uos.orders order_date ROW_NUMBER() OVER (PARTITION BY orders.unique_customer_id ORDER BY orders.order_date)
      last_order_sequence_number BIGINT uos.orders order_date ROW_NUMBER() OVER (PARTITION BY orders.unique_customer_id ORDER BY orders.order_date DESC)
      valid_order_sub_sequence_number INT uos.orders order_date ROW_NUMBER() OVER (PARTITION BY orders.unique_customer_id ORDER BY orders.order_date)
      type_rank INT uos.transactions transaction_type CASE WHEN transaction_type = 'sale' THEN 1
      WHEN transaction_type = 'authorization' THEN 2
      ELSE 3
      END
      transaction_rank BIGINT uos.transactions order_id ROW_NUMBER() OVER (PARTITION BY t.order_id ORDER BY t.transaction_amount DESC, tr.type_rank ASC)
      valid_order_flag BIGINT uos.orders financial_status CASE WHEN os.order_status IN ('Full Payment','Partially Refunded','Refunded','Partial Gift Card Usage','Gift Card Usage','Partial Gift Card Purchase') THEN 1 ELSE 0 END
      business_order_flag BIGINT uos.orders business_order_flag CASE WHEN orders.business_order_flag = TRUE THEN 1 ELSE 0 END
      expedited_shipping_order_flag BIGINT uos.orders expedited_shipping_order_flag CASE WHEN orders.expedited_shipping_order_flag = TRUE THEN 1 ELSE 0 END
      prime_order_flag BIGINT uos.orders prime_order_flag CASE WHEN orders.prime_order_flag = TRUE THEN 1 ELSE 0 END
      discount_flag BIGINT uos.orders discount_amount CASE WHEN os.order_status IN ('Full Payment','Partially Refunded','Refunded','Partial Gift Card Usage','Gift Card Usage','Partial Gift Card Purchase','Free Order') AND orders.discount_amount > 0 THEN 1 ELSE 0 END
      cancelled_flag BIGINT uos.orders financial_status CASE WHEN os.order_status = 'Cancelled' THEN 1 ELSE 0 END
      exchange_order_flag BIGINT N/A N/A CAST(0 AS INT)
      refund_flag BIGINT uos.orders financial_status CASE WHEN os.order_status IN ('Partially Refunded','Refunded') THEN 1 ELSE 0 END
      fraud_flag BIGINT uos.orders financial_status CASE WHEN os.order_status = 'Fraud' THEN 1 ELSE 0 END
      free_order_flag BIGINT uos.orders financial_status CASE WHEN os.order_status = 'Free Order' THEN 1 ELSE 0 END
      paid_shipping_flag BIGINT uos.orders financial_status CASE WHEN os.order_status IN ('Full Payment','Partially Refunded','Refunded','Partial Gift Card Usage','Gift Card Usage','Partial Gift Card Purchase') AND orders.shipping_amount > 0 THEN 1 ELSE 0 END
      first_order_flag BIGINT uos.orders financial_status CASE WHEN valid_order_sequence_number = 1 THEN 1 ELSE 0 END
      last_order_flag BIGINT uos.orders financial_status CASE WHEN last_order_sequence_number = 1 THEN 1 ELSE 0 END
      subscription_order_flag BIGINT uos.orders financial_status CASE WHEN orders.valid_order_sub_sequence_number IS NOT NULL THEN 1 ELSE 0 END
      gift_card_sales_flag BIGINT uos.orders financial_status CASE WHEN orders.gift_card_sales_amount > 0 THEN 1 ELSE 0 END
      order_sequence_number BIGINT uos.orders financial_status ROW_NUMBER() OVER (PARTITION BY orders.unique_customer_id ORDER BY orders.order_date)


      All content © Daasity 2021. Do not copy, share or distribute.