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 Sales - Refunds - Discounts | 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]