This page was printed on Aug 13, 2022. For the current version, visit https://help.shopify.com/en/manual/reports-and-analytics/shopifyql/notebooks-sales-schema.
Shopify’s Notebooks Alpha offers merchants the sales schema to explore using ShopifyQL. This page outlines the definition, type, and utility of each ShopifyQL column including numeric values, aggregates, and dimensional attributes.
Numeric Values
Numeric values are the base numbers in the sales model. They’re typically aggregated using ShopifyQL functions such as SUM and AVG to create aggregates.
Numeric values
Name
Type
Definition
quantity
Number
The quantity of product, tip, and gift card units that were purchased or returned. Used to calculate ordered_item_quantity, returned_item_quantity, and net_quantity.
amount_before_discounts_before_tax
Price
The sale value before discounts and taxes. Used to calculate gross_sales, duties, additional_fees, total_tips, and gift_card_gross_sales.
line_item_discount_amount
Price
The value of discount applied to the specific line item, excluding allocated order-level discounts. Used to calculate discounts, net_sales, and gift_card_discounts.
allocated_order_discount_amount
Price
The proportionate amount from an order-level discount that applies to the line item, prior to taxes being applied. Used to calculate total_sales, total_order_value, and percent_of_sales_with_staff_help.
amount_after_discounts_before_tax
Price
The sale value after applying discounts, but before taxes. Used to calculate returns and gift_cards_issued.
tax_amount
Price
The amount of tax for the given sale line. Used to calculate taxes.
amount_after_discounts_after_tax
Price
The sale value after applying discounts and taxes. Used to calculate total_sales, total_order_value, and percent_of_sales_with_staff_help.
cost
Price
The cost of the products for the particular sale (item cost * quantity). Used to calculate total_cost, gross_profit, and gross_margin.
Aggregates
Aggregates are pre-defined aggregates of numeric values, to replicate metrics as available throughout Shopify. Aggregates can be grouped or filtered by any of the dimensional attributes.
Sales
Sales
Name
Type
Aggregate
Definition
gross_sales
Price
Sum
The list value of products purchased, ignoring any discounts, returns, shipping, taxes, gift cards, tips, or duties. Orders with tax included in the product price will have the gross sales amount reduced by the tax amount.
discounts
Price
Sum
The gross amount of discounts given to the products. This includes both line item discounts and discounts that are applied to an entire order, which are proportionally applied to the line items for the order. It doesn't include discounts applied to shipping, or subtract discounts applied to returned items. Discounts of gift cards are offered in gift_card_discounts.
returns
Price
Sum
The value given back to the customer in exchange for returned products. This doesn’t include any taxes or shipping returned.
net_sales
Price
Sum
The value the merchant and customer agreed on for the sale of the products. Calculated as the gross value of products sold minus discounts and returns, plus any adjustments. Shipping, taxes, gift cards, and tips are excluded.
shipping
Price
Sum
The amount charged to the customer for delivering an order, net of discounts and returns, prior to any taxes. Orders with taxes exclude the tax from the shipping amount.
duties
Price
Sum
The total value of duties collected to be paid to a third party shipping provider, net of returns and prior to taxes.
taxes
Price
Sum
The taxes collected on the sale of products, shipping, and duty, net of returns.
total_tips
Price
Sum
The total value of tips received.
total_sales
Price
Sum
The payments the merchant intends to collect from the customer. This includes the sales of products, shipping, tips, and duties, excludes gift cards, and is net of returns.
Profit
Profit
Name
Type
Aggregate
Definition
total_cost
Price
Sum
The cost of the items purchased, as recorded in Shopify at the time of the purchase. Returns reduce the total_cost. Used to calculate gross_profit.
gross_profit
Price
Sum
Calculated as gross_sales minus total_cost, filtered to the sales which tracked their cost. Used to benchmark against targets.
gross_margins
Percent
Rate
The ratio of gross_profit to gross_sales, filtered to the sales which tracked their cost. Used to benchmark the blended margin of items sold against targets.
Orders
Orders
Name
Type
Aggregate
Definition
orders
Number
Distinct count
The number of distinct orders that purchased at least one product. It excludes orders that only purchased gift cards, and returns. Includes the addition of items to an order during an order edit. Used to calculate the average_order_value.
average_order_value
Price
Rate
For sales of sale_kind= "order", the total_sales divided by the distinct count of orders that purchased sale_line_type = "product". The calculation does not include returns.
Customers
Customers
Name
Type
Aggregate
Definition
customers
Number
Distinct count
For sales of sale_kind= "order", the distinct count of customers that purchased sale_line_type= "product". Illustrates how many unique customers are purchasing products.
Retail
Retail
Name
Type
Aggregate
Definition
percent_of_sales_with_staff_help
Percent
Rate
The ratio of the amount (after discount, after tax of product, shipping, and unknown line items) associated with POS users, to the amount (after discount after tax of product, shipping, and unknown line items) not associated with a POS user.
Gift cards
Gift cards
Name
Type
Aggregate
Definition
gift_card_gross_sales
Price
Sum
The redeemable value of gift cards that were purchased, before any discounts are applied. Used to measure the increase in liabilities due to gift cards being sold. It doesn't reconcile balance against payments, nor represent all increases in liabilities, such as manually issued gift cards.
gift_card_discounts
Price
Sum
The gross value of discounts on gift card sales. Both line item discounts and discounts that are applied to an entire order are proportionally applied to the sales for the order. It doesn't subtract discounts applied to returned items. Discounts of products are shown in discounts.
gift_cards_issued
Price
Sum
The net amount gift cards are sold for, after discounts. This is approximately the net payments that are expected from the sale of gift cards.
Units
Units
Name
Type
Aggregate
Definition
ordered_item_quantity
Number
Sum
The quantity of product, tip, and gift card units that were purchased, regardless of returns.
returned_item_quantity
Number
Sum
The quantity of product, tip, and gift card units that were returned.
net_quantity
Number
Sum
Quantity of product, tip, and gift card units sold, net of returns. Used in calculating units_per_transaction.
units_per_transaction
Number
Rate
For sales of sale_kind= "order", the net_quantity divided by the distinct count of orders that purchased sale_line_type = "product". This doesn't include returns.
Dimensional attributes
Sales channel
Sales channel
Name
Type
Definition
channel_name
String
The title of the Sales Channel or Shopify App which created the order.
Merchandizing
Merchandizing
Name
Type
Definition
product_id
Id
For the purchase of one of a shop’s products, the product’s ID. Useful to group or filter sales by a particular product.
product_price
Id
The price of the product purchased.
product_title
String
The title of the line item seen on the customer's receipt. For products in Shopify this is the title of the product at the time of purchase. For custom line items this is the title set at the time of purchase.
product_type
String
For the purchase of products, the product's type at the time of the order.
product_vendor
String
For the purchase of products, the product's vendor at the time of the order.
variant_sku
String
The SKU of the line item, derived from the product variant.
variant_title
String
The title of the line item's product variant.
Online store marketing
Online store marketing
Name
Type
Definition
marketing_event_type
String
For Online Store orders, the type of marketing that led to creation of the order. Examples include "newsletter", "ad", "abandoned_cart", and "search".
referrer_host
String
For Online Store orders, the domain of the referrer that concluded in the sale. Orders without a referrer have an empty string.
referrer_name
String
For Online Store orders, the humanized second-level domain of referrer that concluded in the sale. Orders without a referrer have an empty string.
referrer_path
String
For Online Store orders, the URL path of the referrer that concluded in the sale, excluding any parameters. Orders without a referrer path have an empty string.
referrer_source
String
For Online Store orders, the type of referrer that concluded in the sale. This includes categories such as "Direct", "Email", "Social", and "Search".
referrer_url
String
For Online Store orders, the URL that referred the customer and concluded in the sale, excluding any URL parameters.
landing_site
String
For Online Store orders, the URL the customer landed on at the beginning of the session that concluded in the sale, excluding any URL parameters.
utm_campaign_content
String
For Online Store orders, the utm_content parameter of the landing page URL of the session that placed the order. Orders without a value default to an empty string.
utm_campaign_medium
String
For Online Store orders, the utm_medium parameter of the landing page URL of the session that placed the order. Orders without a value default to an empty string.
utm_campaign_name
String
For Online Store orders, the utm_campaign parameter of the landing page URL of the session that placed the order. Orders without a value default to an empty string.
utm_campaign_source
String
For Online Store orders, the utm_source parameter of the landing page URL of the session that placed the order. Orders without a value default to an empty string.
utm_campaign_term
String
For Online Store orders, the utm_term parameter of the landing page URL of the session that placed the order. Orders without a value default to an empty string.
Shipping
Shipping
Name
Type
Definition
shipping_city
String
The city of the order's current shipping address. Sales without a shipping city return an empty string.
shipping_country
String
The country of the order's current shipping address. Sales without a shipping country return an empty string.
shipping_postal_code
CaselessString
The postal code of the order's current shipping address. Sales without a shipping postal code return an empty string.
shipping_region
String
The region of the order's current shipping address. This is typically a geographic subdivision smaller than the country, like State or Province, where applicable. Sales without a shipping region return an empty string.
Customer
Customer
Name
Type
Definition
billing_city
String
The city of the order's current billing address. Sales without a billing city return an empty string.
billing_company
String
The company of the order's current billing address. Sales without a billing company return an empty string.
billing_country
String
The country of the order's current billing address. Sales without a billing country return an empty string.
billing_postal_code
CaselessString
The postal code of the order's current billing address. Sales without a billing postal code return an empty string.
billing_region
String
The region of the order's current billing address. This is typically a geographic subdivision smaller than the country, such as State or Province, where applicable. Sales without a billing region return an empty string.
customer_email
String
The current email of the order's customer. Sales without a customer email value return an empty string.
customer_first_order
Boolean
Whether the order associated with the sale is the first order for that customer in this shop. Either true or false, and an input to customer_type.
customer_id
Id
The unique ID associated with a customer.
customer_name
String
The current full name of the order's customer. Sales without a customer name value return an empty string.
customer_repeat_order
Boolean
Indicates whether the order associated with the sale is not the first order the customer has made in this shop. Either true or false, and an input to customer_type.
customer_type
String
Indicates whether the line item is part of the first order of the customer in the shop, or not. Either "First-time", "Returning", or empty string.
Retail
Retail
Name
Type
Definition
name_of_staff_who_helped_with_sale
String
The name of the staff member who had a line item assigned to them. Tracks staff's sales attribution.
pos_location_name
String
The current name of the point-of-sale (POS) location that placed the order.
staff_name
String
The name of the staff member who processed the sale.
Sale
Sale
Name
Type
Definition
sale_line_type
String
The type of the line item. This includes values such as "product", "gift_card", or "unknown". Unknown indicates adjustments to account for a difference in the value of the product returned and money exchanged.
sale_kind
String
Indicates whether the line item is an order or a return. Values include "order" and "return".
adjustment
CaselessString
Indicates whether the sale was created to adjust for a refund discrepancy, as indicated by sale_line_type of "unknown".
Used to filter returns, net_sales, or total_sales to specifically the value of products exchanged, as opposed to the total value exchanged.
cost_tracked
CaselessString
Indicates whether the line item has an associated cost of goods. Used in calculating gross_profit and gross_margin.
Order
Order
Name
Type
Definition
cancelled
CaselessString
Indicates whether the order associated with the sale is cancelled. Either "Yes" or "No".
financial_status
CaselessString
The order's current financial status. Values include "authorized", "paid", "refunded", and empty string.
fulfillment_status
CaselessString
The order's current fulfillment status. Values include "unfulfilled", "fulfilled", and "restocked".
order_id
Id
The unique ID associated with an order.
order_name
String
Sequential name for an order. For example, #1000.
presentment_currency
String
For Online Store orders, the currency the customer was presented with.