Sales schema

You can see information about your customers’ orders based on criteria such as sales by month, sales by channel, or sales by staff.

Using SINCE and UNTIL will filter orders by the date that the sale or refund event took place. A query that filters with SINCE -1m UNTIL today will return all sales and refunds that occurred in the last month.

Example sales query - Sales over time

  1. SHOW orders, gross_sales, discounts, refunds, net_sales, shipping, taxes, total_sales
  2. OVER month(happened_at) AS month
  3. FROM sales
  4. SINCE -11m
  5. UNTIL today ORDER
  6. BY month ASC

Sale properties

The following properties show information about the dollar value totals that are included in a sale:

Properties Type Description
Aggregate properties - used in the SHOW clause
average_order_value price The average value of the orders.
discounts price Equates to line item discount + order level discount share for a collection of sales. This is the total dollar value reduction applied to a sale in the form of discounts on products, collections, or entire orders. Discounts that are applied to an entire order are proportionally applied to the sales for the order. This property reflects the pre-tax discount amount. Discounts are created using discount codes, not the compare at price.
gift_card_discounts price The value of discounts on gift card sales. For example, for a $50 gift card sold at a $10 discount for $40, this field will contain $10.
gift_card_gross_sales price The face value of gift cards sold. For example, for a $50 gift card sold at a $10 discount for $40, this field will contain $50. Use this field to measure the increase in liabilities due to gift cards being sold, but be aware that it will not balance against payments.
gift_cards_issued price The net amount that gift cards sold for, after discounts. For example, for a $50 gift card sold at a $10 discount for $40, this field will contain $40. Use this field to balance against payments.
gross_sales price Equates to quantity x product price (before taxes, shipping, discounts, and returns) for a group of sales. Canceled, pending, and unpaid orders are included. Test and deleted orders are not included.
net_sales price Equates to gross sales - discounts - returns.
pending_sales price Shows the amount that gift cards were sold for. See gift_cards_issued.
refunds price The value of the tender given back to the customer in exchange for returned products. Returns are not counted as separate orders.
shipping price Equates to shipping charges - shipping discounts - refunded shipping amounts.
taxes price The total amount of taxes based on the orders. Canceled, pending, and unpaid orders are included. Test and deleted orders are not included. If orders are partially or fully refunded, then the net amount for those orders in the time period of the report is still included.
total_sales price Total sales includes taxes and shipping.

Order properties

The following properties show information about the order that a sale is attributed to:

Properties Type Description
Aggregate properties - used in the SHOW clause
returned_item_quantity number The number of items returned.
net_quantity number Equates to the number of items sold - the number of items returned.
ordered_item_quantity number The quantity of items that were ordered.
orders number The number of orders that were placed on a given date. Canceled, pending, and unpaid orders are included. Test and deleted orders are not included.
Non-aggregate properties - used in the BY clause
order_id number The unique numeric identifier for the order.
order_name number The order number.
financial_status caseless_string The payment status of the order.
sale_kind string Whether the sale transaction is an order or a return.
adjustment caseless_string Whether there is an adjustment written to account for a refund discrepancy, for example, where the value of restocked items doesn’t equal the value of refunded payments.
cancelled caseless_string Whether or not the order is canceled.

Product properties

The following properties show information about the product included in a sale:

Properties Type Description
Non-aggregate properties - used in the BY clause
product_title string The title of the product.
product_vendor string The vendor of the product.
product_type string The product’s type
product_id number The unique numeric identifier for the product.
product_price price The price of the product variant.
variant_title string The title of the product variant.
variant_sku string The SKU of the product variant.
variant_id number The unique numeric identifier for the product variant.

Customer properties

The following properties show information about the customer that a sale was made to:

Properties Type Description
Non-aggregate properties - used in the BY clause
customer_email string The email address entered by the customer.
customer_name string The first and last names entered by the customer.
customer_id number The unique numeric identifier for the customer.
customer_type string Shows First-time if this is the customer’s first order, and Returning if this is not the customer’s first order.
billing_country string The country from the customer’s billing address.
billing_region string The state or province from the customer’s billing address.
billing_company string The company from the customer’s billing address.
billing_city string The city from the customer’s billing address.
shipping_region string The state or province from the customer’s shipping address.
shipping_city string The city from the customer’s shipping address.
shipping_country string The country from the customer’s billing address.

Referral traffic properties

The following properties show information about the referral that led to a sale:

Properties Type Description
Non-aggregate properties - used in the BY clause
referrer_host string The full domain of the referrer.
referrer_name string The domain of the referrer in a human-readable form
referrer_path string The URL path of the referrer.
referrer_url string The full URL of the referrer.
referrer_source string The referrer type (search or unknown).

Marketing campaign properties

The following properties show information about the marketing campaign that a sale can be attributed to:

Properties Type Description
Non-aggregate properties - used in the BY clause
utm_campaign_content string A/B testing and content-targeted ads to differentiate ads or links that point to the same URL.
utm_campaign_term string The keywords associated with an ad.
utm_campaign_medium string The ad medium, such as email or cost-per-click (cpc).
utm_campaign_source string Identifies a search engine, a newsletter name, or other source.
utm_campaign_name string The name that you give a promotion or campaign to identify it.

Sales channel properties

The following properties show information about the sales channel that a sale was placed through:

Properties Type Description
Non-aggregate properties - used in the BY clause
channel_provider_name string The sales channel that the order was placed through.
pos_location_name string The name of the point of sale location where the sale was made.
## Employee properties If a sale was a POS sale or from a draft order, then the following properties show information about the employee who made it:
Properties Type Description
Non-aggregate properties - used in the BY clause
employee_id number The unique numeric identifier for the employee who created the order.
employee_name string The name of the employee who created the order.
## Time properties The following properties show sales information by the date and time the sales were made:
Properties Type Description
Non-aggregate properties - used in the BY or OVER clause
year year The year of a sale or return.
month month The month of a sale or return.
week week The week of a sale or return.
day day The day of a sale or return.
hour hour The hour of a sale or return.