Notebooks Alpha sales model 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.

Ready to start selling with Shopify?

Try it free