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]