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.
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.