View: Transactional Sales Report

This article will help you learn about the fields available in the Transactional Sales Report 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 [uos.sales_report] 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
Transaction Date Dimension Group Date of when the transaction (order or refund) was processed transaction_date
Converted Currency Code Dimension The currency code of all sales metrics that were converted converted_currency
Currency Conversion Rate Dimension The daily rate for the currency conversion currency_conversion_rate
Original Currency Code Dimension The currency from the store (non-converted currency code) original_currency
Email Address Dimension Email Address of the Customer placing the Order email_address
Order ID Dimension The internal ID of the order from the source system store_order_id
Order Line ID Dimension The internal ID of the order line from the source system store_order_line_id
Item Quantity Dimension The quantity of an item purchased or refunded quantity
Order Code Dimension The customer facing Order Number in the source system order_code
Price Dimension The product price at time of purchase price
Product ID Dimension The internal ID of the product from the source system store_product_id
Product Name Dimension The name of the product from the source system product_name
SKU Dimension The SKU of the item purchased or refunded. May also contain a description of a refund discrepancy sku
Shop ID Dimension The Store identifier in the Daasity App (shop_id.shopify.com) __shop_id
Transaction Detail Type Dimension Indicates if at the Order or Item level for the Purchase or Refund transaction_detail_type
Transaction Type Dimension Indicates if a Purchase or Refund transaction transaction_type
Average Order Value  Measure Total Sales / Number of Orders (COALESCE(SUM(gross_sales), 0) + COALESCE(SUM(discount_amount), 0) + COALESCE(SUM(refund_amount), 0) + COALESCE(SUM(shipping_amount), 0) + COALESCE(SUM(tax_amount), 0)) / NULLIF(COUNT(DISTINCT CASE WHEN transaction_type = 'PURCHASE' AND NOT giftcard_only_order THEN order_id ELSE NULL END), 0)
Count Items Measure Count of all items purchased, net of returns COALESCE(SUM(quantity), 0)
Count Items with Discount Measure Count of all items with a discount applied COALESCE(SUM(CASE WHEN discount_amount <> 0 THEN quantity ELSE 0 END ), 0)
Count Orders Measure Total Number of Orders COUNT(DISTINCT CASE WHEN transaction_type = 'PURCHASE' AND NOT giftcard_only_order THEN order_id ELSE NULL END)
Count Refunded Items Measure Total Number of Refunded Items COALESCE(SUM(CASE WHEN transaction_type = 'REFUND' AND transaction_detail_type = 'ITEM REFUND'
AND NOT giftcard_only_order THEN ABS( quantity ) ELSE NULL END), 0)
Count Refunded Orders Measure Total Number of Refund Orders COUNT(DISTINCT CASE WHEN transaction_type = 'REFUND' AND NOT giftcard_only_order THEN order_id ELSE NULL END)
Count Valid Orders Measure Total Number of Valid Orders according to Daasity's definition of valid (excludes Free, Cancelled, Voided, and Fraud orders) COUNT(DISTINCT CASE WHEN transaction_type = 'PURCHASE'
AND NOT giftcard_only_order
AND ( valid_order_flag_daasity = 1 ) = TRUE THEN order_id ELSE NULL END)
Total Discount Amount Measure Sum of all discounts COALESCE(SUM(discount_amount), 0)
Total Gross Margin Measure Total Gross Margin = Net Sales - Shipping Cost - Fulfillment Cost - SKU Cost COALESCE(SUM(net_ssales - shipping_amount - fulfillment_amount - (NVL( sku_cost ,0) * quantity ) ), 0)
Total Gross Sales Measure Sum of all gross sales COALESCE(SUM(gross_sales), 0)
Total Gross Sales - Original Currency Measure Sum of all gross sales in the Original Currency COALESCE(SUM(gross_sales * currency_conversion_rate ), 0)
Total Net Sales Measure Sum of all net sales = Gross - Refunds COALESCE(SUM(net_sales), 0)
Total Refund Amount Measure Sum of all refunds and returns COALESCE(SUM(refund_amount), 0)
Total Sales Measure Gross Sales + Discount + Returns + Shipping + Tax COALESCE(SUM(gross_sales), 0) + COALESCE(SUM(discount_amount), 0) + COALESCE(SUM(refund_amount), 0) + COALESCE(SUM(shipping_amount), 0) + COALESCE(SUM(tax_amount), 0)
Total Shipping Amount Measure Sum of all net shipping revenue (what the customer paid for Shipping) COALESCE(SUM(shipping_amount), 0)
Total SKU Cost Measure Sum of all product costs related to the SKU purchased COALESCE(SUM(sku_cost), 0)
Total Taxes Measure Sum of all net tax charges COALESCE(SUM(tax_amount), 0)

Explores

This view is part of the following explores:

  • Transactional Sales Report

Transformation Code

This view is a part of the Daasity Unified Order Schema (UOS) that enables Daasity to combine different order sources into a single data model.

Growth & Pro support the following code:

  • [0526_UOS_BAS_SHOPIFY_sales_report.sql]
  • [0557_UOS_BAS_AMAZON_sales_report.sql]
  • [0587_UOS_BAS_SKUBANA_sales_report.sql]