4310_ORL_BAS_order_line_revenue Transformation

Target Database Table

drp.order_line_revenue.

Script Description and Logic

Pulls fields from at the order line level from

  • uos.refunds
  • uos.transactions
  • drp.order_status
  • uos.refund_line_items
  • uos.order_hshld_lkp
  • uos.orders
  • drp.order_cpo_cpa
  • drp.order_item_fulfillment_cost 

Order lines from all integrations are included in the primary source tables and are reported by unique order line id, shop id and integration id combination.

A join is performed between drp_staging.uos_orders_to_update, uos.order_line_items, uos.orders, drp_staging.refund_line_items, drp.order_status and uos.order_hshld_lkp, to return a list of order line items that have been updated in the last seven days and initial line item totals. The line items to update and their aggregates are then stored in a staging table.

Refunds and line level refunds are rolled up to the order level.

Discounts, taxes, number of items per order and gift card usage are then rolled up to the order level.

The line item staging table is then combined with the the order level rollups to determine the amounts to be allocated at the line level for discounts, taxes, number of items per order, gift card usage and refunds. The line level amounts to be allocated are then stored in a staging table as oli_line_rollup_amounts. Next the oli_line_rollup_amounts table values are allocated to the line level, first and last click marketing cost is then pulled from drp.order_cpo_cpa, and shipping and fulfillment costs are pulled in from drp.order_item_fulfillment_cost.

This line level allocation is then staged as oli_allocated_amount. Totals are then calculated from oli_allocated_amount where an order is flagged as valid in order_status. The unique combinations of order line id and shop id are then placed in a staging table in preparation for incremental functionality.

Incremental functionality is included such that only the records that require an update or are new get inserted into the final order_line_revenue table. The records in staging are compared to those in the final table. Any order lines that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.order_line_revenue.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos order_line_items database Primary source
uos orders database Primary source
uos refund_line_items database Primary source
drp order_status database Primary source
uos transactions database Primary source
uos refunds database Primary source
drp order_cpo_cpa database Primary source
drp order_item_fulfillment_cost database Primary source
uos order_hshld_lkp database Lookup
drp_staging uos_orders_to_update database Lookup
drp_staging line_items database Staging for line items
drp_staging refund_line_items database Rollup of refund_line_items to the order line level
drp_staging oli_refund_rollup database Contains a rollup of refunds to the order level
drp_staging oli_refund_line_rollup database Contains a rollup of refund lines to the order level
drp_staging oli_line_discount_rollup database Contains a rollup of order line level discount amount to the order level
drp_staging oli_line_tax_rollup database Contains a rollup of order line level tax amount to the order level
drp_staging oli_giftcard_amount_used database Determines the amount paid by gift card and amount paid by other processes
drp_staging oli_giftcard_percentage database Calculates the percentage of an order paid by gift card
drp_staging oli_num_order_items database Calculates the number of items in each order
drp_staging oli_line_rollup_amounts database Contains a line level allocation of all rollup amounts
drp_staging oli_allocated_amounts database Contains an allocation of oli_line_rollup_amounts to the line level.
drp_staging order_line_revenue database Pre-insert

SQL Flow

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

 4310_ORL_BAS_order_line_revenue Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
non_product_discount_amount DECIMAL uos cart_discount_amount if valid_order_flag = 1, then (total_line_item_price / total_price_minus_product_discount)*(cart_discount_amount)
product_discount_amount DECIMAL uos order_line_items.discount_amount if valid_order_flag = 1, then (discount_amount)
product_sales_amount DECIMAL uos order_line_items.total_line_item_price if valid_order_flag = 1 then (price * quantity) - discount_amount, same as gross in Shopify
potential_product_sales_amount DECIMAL uos order_line_items.total_line_item_price if valid_order_flag = 1 then (price * quantity) + discount_amount
num_feature_items INT uos order_line_items.quantity if valid_order_flag = 1 then pull quantity where price of the product is not = 0
num_free_items INT uos order_line_items.quantity if valid_order_flag = 1 then pull quantity where price of the product is = 0
num_gift_card_items INT uos order_line_items.quantity if gift_card_flag = TRUE then count quantity, these are not valid orders
num_units INT uos order_line_items.quantity if valid_order_flag = 1 then (total_feature_items + total_free_items)
discount_amount DECIMAL uos order_line_items.discount_amount if valid_order_flag = 1 then (line_discount_amount + line_allocated_discount_amount), based on retail price
shipping_amount DECIMAL uos orders.shipping_amount if valid_order_flag = 1 and num_items_per_order > 0 then (quantity * 1 / num_items_per_order)
tax_amount DECIMAL uos orders.tax_amount
if valid_order_flag = 1 then (line_tax_amount + line_allocated_tax_amount + refund_line_tax_amount), based on retail price
refund_amount DECIMAL uos refunds.refund_amount if valid_order_flag = 1 then (refund_line_amount + line_allocated_refund_amount)
gross_amount_charged DECIMAL uos order_line_items.price if valid_order_flag = 1 then (product - discount + tax + shipping)
potential_sales DECIMAL uos order_line_items.price if valid_order_flag = 1 then (non-discounted product + shipping)
gross_sales DECIMAL uos order_line_items.price if valid_order_flag = 1 then (product rev)
net_sales DECIMAL uos order_line_items.price if valid_order_flag = 1 then (product - discount - refund)
sku_cost DECIMAL uos order_line_items.sku_cost if valid_order_flag = 1 then sku_cost is pulled in
fulfillment_cost DECIMAL drp order_item_fulfillment_cost.fulfillment_cost if valid_order_flag = 1 then fulfillment_cost is pulled in
shipping_cost DECIMAL drp order_item_fulfillment_cost.shipping_cost if valid_order_flag = 1 then shipping_cost is pulled in
fc_marketing_cost DECIMAL drp order_cpo_cpa.fc_cost_per_order if valid_order_flag = 1 then (fc_cost_per_order * total_featured_items)
lc_marketing_cost DECIMAL drp order_cpo_cpa.lc_cost_per_order if valid_order_flag = 1 then (lc_cost_per_order * total_featured_items)
attribution_marketing_cost DECIMAL N/A N/A N/A
gross_margin DECIMAL uos many WHEN NVL(oaa.valid_order_flag,0) = 1 AND NVL(oaa.gift_card_flag,FALSE) = FALSE THEN
NVL(oaa.total_line_item_price,0.00) -
NVL(oaa.line_discount_amount,0.00) -
NVL(oaa.line_allocated_discount_amount,0.00) +
NVL(oaa.line_allocated_shipping_amount,0.00) -
NVL(oaa.sku_cost,0.00)
ELSE 0
net_margin DECIMAL uos many WHEN NVL(oaa.valid_order_flag,0.00) = 1 THEN
NVL(oaa.total_line_item_price,0.00) -
NVL(oaa.line_discount_amount,0.00) -
NVL(oaa.line_allocated_discount_amount,0.00) +
NVL(oaa.line_allocated_shipping_amount,0.00) -
NVL(oaa.refund_line_amount,0.00) -
NVL(oaa.line_allocated_refund_amount,0.00) -
NVL(oaa.sku_cost,0.00)
ELSE 0
fc_net_contribution DECIMAL uos many WHEN NVL(oaa.valid_order_flag,0.00) = 1 THEN
NVL(oaa.total_line_item_price,0.00) -
NVL(oaa.line_discount_amount,0.00) -
NVL(oaa.line_allocated_discount_amount,0.00) +
NVL(oaa.line_allocated_shipping_amount,0.00) -
NVL(oaa.refund_line_amount,0.00) -
NVL(oaa.line_allocated_refund_amount,0.00) -
NVL(oaa.sku_cost,0.00) -
NVL(oaa.line_allocated_fulfillment_cost,0.00) -
NVL(oaa.line_allocated_shipping_cost,0.00) -
NVL(oaa.fc_marketing_cost,0.00)
ELSE 0
lc_net_contribution DECIMAL uos many WHEN NVL(oaa.valid_order_flag,0.00) = 1 THEN
NVL(oaa.total_line_item_price,0.00) -
NVL(oaa.line_discount_amount,0.00) -
NVL(oaa.line_allocated_discount_amount,0.00) +
NVL(oaa.line_allocated_shipping_amount,0.00) -
NVL(oaa.refund_line_amount,0.00) -
NVL(oaa.line_allocated_refund_amount,0.00) -
NVL(oaa.sku_cost,0.00) -
NVL(oaa.line_allocated_fulfillment_cost,0.00) -
NVL(oaa.line_allocated_shipping_cost,0.00) -
NVL(oaa.lc_marketing_cost,0.00)
ELSE 0
attribution_net_contribution DECIMAL N/A many WHEN NVL(oaa.valid_order_flag,0.00) = 1 THEN
NVL(oaa.total_line_item_price,0.00) -
NVL(oaa.line_discount_amount,0.00) -
NVL(oaa.line_allocated_discount_amount,0.00) +
NVL(oaa.line_allocated_shipping_amount,0.00) -
NVL(oaa.refund_line_amount,0.00) -
NVL(oaa.line_allocated_refund_amount,0.00) -
NVL(oaa.sku_cost,0.00) -
NVL(oaa.line_allocated_fulfillment_cost,0.00) -
NVL(oaa.line_allocated_shipping_cost,0.00) -
NVL(oaa.attribution_marketing_cost,0.00)
ELSE 0


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