View: Order Line Revenue

This article will help you learn about the fields available in the Order Line Revenue 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

View Definition

This view is linked to the [drp.order_line_revenue] 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
Converted Currency Dimension The Currency Code the amount was converted to converted_currency
Currency Conversion Rate Dimension The daily rate use to convert from the original to the converted currency currency_conversion_rate
Original Currency Dimension The Currency Code the amount was converted from original_currency
Store Customer ID Dimension The Customer ID from the Source System store_customer_id
Store Order ID Dimension The Order ID from the Source System store_order_id
Store Order Line ID Dimension The Order Line ID from the Source System store_order_line_id
Gift Card Flag Dimension If the Item is a Gift Card gift_card_flag
Listing SKU Dimension Product SKU on the store (i.e., Amazon, Shopify, etc.) that the purchase was made from listing_sku
Product Name Dimension The name of the product when purchased product_name
Product Type Dimension Product Type as identified in the Store (i.e. Shopify) purchase was made product_type
SKU Dimension The SKU of the product from the Source System (i.e. Shopify) sku
SKU Cost per Unit Dimension The cost of the item (1 unit) unit_sku_cost
Variant Name Dimension The name of the Item (Product Variant) when purchased variant_name
Variant Price Dimension The price of the item (Product Variant) when purchased product_price
Number of SKUs per Order Dimension The number of different variants (SKUs) that were placed in the order num_sku_per_order
Average Order Discount Amount Measure Average discount per order -- Total Discount Amount divided by Valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(discount_amount), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Order Gross Margin Measure Average order value using gross margin -- Total Gross Margin divided by Valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(gross_margin), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Order Gross Sales Measure Average order value using gross sales -- Total Gross Sales divided by Valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(gross_sales), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Order Net Sales Measure Average order value using net sales -- Total Net Sales divided by Valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(net_sales), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Order Product Cost Measure The sum of all product costs / by the total valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(sku_cost), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Order Product Sales Measure Average product sales per order -- Total Product Sales divided by Valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(product_sales_amount), 0) / COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) END
Average Unit Gross Margin Measure Average unit value using gross margin -- Total Gross Margin divided by Valid units CASE WHEN COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) = 0 THEN 0 ELSE COALESCE(SUM(gross_margin), 0) / COALESCE(SUM(CASE WHEN valid_order_flag = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) END
Average Unit Gross Sales Measure Average gross Sales unit -- Total gross Sales divided by Valid order items CASE WHEN COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) = 0 THEN 0 ELSE COALESCE(SUM(gross_sales), 0) / COALESCE(SUM(CASE WHEN valid_order_flag = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) END
Average Unit Net Sales Measure Average net Sales per unit -- Total Net Sales divided by Valid order items CASE WHEN COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) = 0 THEN 0 ELSE COALESCE(SUM(net_sales), 0) / COALESCE(SUM(CASE WHEN valid_order_flag = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) END
Average Unit Product Cost Measure Average Product Cost per valid uni CASE WHEN COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) = 0 THEN 0 ELSE COALESCE(SUM(sku_cost), 0) / COALESCE(SUM(CASE WHEN valid_order_flag = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) END
Average Unit Product Sales Measure Average product sales per unit -- Total Product Sales divided by Valid order items CASE WHEN COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) = 0 THEN 0 ELSE COALESCE(SUM(product_sales_amount), 0) / COALESCE(SUM(CASE WHEN valid_order_flag = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0) END
Units Per Order Measure Units per order -- Total feature items divided by valid orders CASE WHEN COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) = 0 THEN 0 ELSE COALESCE(SUM(num_feature_items), 0) * 1.00 / (COUNT(DISTINCT CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END) * 1.00)
Count - Customers Measure Count the number of householded customers COUNT(DISTINCT(unique_customer_id))
Count - Daasity Valid Items Measure Count the number of valid items according to the Daasity logic of valid (removes orders that are free, cancelled, voided, and fraud COALESCE(SUM(CASE WHEN valid_order_flag_daasity = TRUE THEN num_feature_items + num_free_items ELSE 0 END ), 0)
Count - Daasity Valid Orders Measure Count the number of valid orders according to the Daasity logic of valid (removes orders that are free, cancelled, voided, and fraud) COUNT(DISTINCT( CASE WHEN valid_order_flag_daasity = TRUE THEN order_id ELSE NULL END)
Count - Items Measure Count the number of feature + free items regardless of order status COALESCE(SUM(num_feature_items + num_free_items), 0)
Count - Orders Measure Count the number of orders regardless of order status COUNT(DISTINCT(order_id))
Percent - Discount of Potential Sales Measure Percentage of discount amount on the full product sales amount (Units & Product Price) CASE WHEN COALESCE(SUM(potential_sales), 0) = 0 THEN 0 ELSE COALESCE(SUM(discont_amount), 0) / COALESCE(SUM(potential_sales), 0) END
Percent - Attribution Contribution Percentage Measure Percentage of attribution contribution margin on net sales total_attribution_contribution_margin/NULLIF(total_net_sales,0)
Percent - First Click Contribution Percentage Measure Percentage of first click contribution margin on net sales total_first_click_contribution_margin/NULLIF(total_net_sales,0)
Percent - Last Click Contribution Percentage Measure Percentage of last click contribution margin on net sales  total_last_click_contribution_margin/NULLIF(total_net_sales,0)
Percent - Refund of Gross Measure Percentage of refund amount on total gross sales CASE WHEN COALESCE(SUM(gross_sales), 0) = 0 THEN 0 ELSE COALESCE(SUM(refund_amount), 0) / COALESCE(SUM(gross_sales), 0)
Total Amazon Gross Sales Measure Total Gross sales of Amazon orders using Shopify definition: Product Sales COALESCE(SUM(CASE WHEN LOWER(order_line_revenue.__uos_source) = 'asc' THEN gross_sales ELSE 0 END), 0)
Total Attribution Contribution Margin Measure  Margin of Orders: Net Margin less Attribution Marketing Costs COALESCE(SUM(attribution_net_contribution), 0)
Total Gross Margin Measure Total Gross Margin of orders: Net Sales less SKU cost COALESCE(SUM(gross_margin), 0)
Total Gross Sales Measure Total Gross sales of orders using Shopify definition: Product Sales COALESCE(SUM(gross_sales), 0)
Total Net Sales Measure Total Net sales of orders: Gross Sales less discounts less refunds as of the order date COALESCE(SUM(net_sales), 0)
Total Non-Recurring Gross Sales Measure Total Gross Sales (Product Sales) for non-subscription orders COALESCE(SUM(CASE WHEN ( order_status.subscription_order_flag = 1 ) = TRUE
THEN 0 ELSE order_line_revenue.gross_sales END ), 0)
Total Recurring Gross Sales Measure Total Gross sales (Product Sales) for recurring orders COALESCE(SUM(CASE WHEN ( order_status.subscription_order_flag = 1 ) = TRUE
THEN order_line_revenue.gross_sales ELSE 0 END ), 0)
Total Attribution Marketing Cost Measure Total Attribution Marketing cost of orders COALESCE(SUM(attribution_marketing_cost), 0)
Total Fulfillment Cost Measure Total fulfillment cost of orders COALESCE(SUM(fulfillment_cost), 0)
Total Product Cost Measure Cost of product as listed in Shopify OR the BSD SKU Cost Sheet COALESCE(SUM(sku_cost), 0)
Total Shipping Cost Measure Total shipping cost of orders COALESCE(SUM(shipping_cost), 0)
Total Cart Discount Amount Measure Total discount applied at the cart level (order) COALESCE(SUM(non_product_discount_amount), 0)
Total Discount Amount Measure Total discount amount COALESCE(SUM(discount_amount), 0)
Total Feature Product Amount Measure Total product sales including product discounts COALESCE(SUM(product_sales_amount), 0)
Total Gift Card Sales Amount Measure Total Gift Card amount($) purchased COALESCE(SUM(gift_card_sales_amount), 0)
Total Product Discount Amount Measure Total discount applied to the product (line level) COALESCE(SUM(product_discount_amount), 0)
Total Refund Amount Measure Total refund amount allocated based on Product Sales. Calculated as of the original order date COALESCE(SUM(refund_amount), 0)
Total Shipping Amount Charged Measure Total Shipping Amount charged to and paid by customers COALESCE(SUM(shipping_amount), 0)
Total Tax Amount Measure Tax amount allocated based on Product Sales COALESCE(SUM(tax_amount), 0)

Explores

This view is part of the following explores:

  • Inventory Levels
  • Loop Returns
  • Order & Order Line Revenue
  • Shipstation
  • Subscribers

Transformation Code

This view is a Daasity derived table generated by the following code:

  • [4310_BAS_ORL_order_line_revenue.sql]