Orders table

The orders table contains information about each order in your store. Although this table is good for generating order lists and joining with the customers table, use the sales table instead for financial or other metrical tasks.

The orders table contains deleted and test orders, but it does not contain information about returns. This table contains one row per order.

Columns

Anchor link to section titled "Columns"
Column Data type Description
order_id numeric The unique numeric identifier for the order. This ID is unique across Shopify stores. This column is the primary key for this table. In your Shopify admin, this ID is used internally. Most merchants are familiar with the other ID that appears on orders in the Shopify admin. This ID can be found in the Name column.
api_client_id numeric The ID of the API client that called the Shopify API. This column is a foreign key reference to the api_client_id column in the API clients table.
billing_address_id numeric The ID of the customer's billing address. This column is a foreign key reference to the address_id column in the addresses table. Contains null if the customer didn't or couldn't specify a billing address. For example, orders made using the Shopify Point of Sale (POS) usually don't have a billing address associated with them.
customer_id numeric The ID of the customer. This column is a foreign key reference to the customer_id column in the customers table.
location_id numeric The ID of the location of the Shopify POS system. This column is a foreign key reference to location_id column in the locations table. The location_id is null for orders not placed through Shopify POS, such as those placed through the online store.
shipping_address_id numeric The ID of the customer's shipping address. This column is a foreign key reference to the address_id column in the addresses table. Contains null if the customer couldn't or didn't specify a shipping address. For example, orders made using the Shopify POS usually don't have a shipping address associated with them.
shop_id numeric The ID of the store. This column is a foreign key reference to the shop_id column in the shops table.
user_id numeric The ID of the staff member who created the order. This column is a foreign key reference to the ID in the users table. This column applies to Shopify POS orders and to orders that were converted from draft orders.
name varchar The identifier of the order that the merchant and customer sees. This is the ID that appears on the order in the Shopify admin. For example, #1001. By default, this identifier is unique to one store. If you have multiple stores, then use the order_id column to guarantee uniqueness across multiple stores.
total_price numeric The total price of the order, including shipping and taxes. This column includes gift card sales, but does not include returns. This value may not be accurate for API-imported orders. Do not use this column for financial calculations. Instead, use the total_price column in the sales table.
total_price_fx numeric This column contains the same data as the total_prices column, but the values are converted to a fixed currency (USD).
discount_code varchar The discount code that was applied to the order.
fulfillment_status varchar Contains the fulfillment status of the order. Valid values include:
  • Fulfilled: Every line item in the order has been fulfilled.
  • Partial: At least one line item in the order has been fulfilled.
  • Unfulfilled: None of the line items in the order has been fulfilled
See the line items table for information about the fulfillment of individual line items.
cancel_reason varchar Contains the reason why the order was canceled. If the order was not canceled, this value is null. If the order was canceled, the value will be one of the following:
  • customer: Customer changed or canceled the order.
  • fraud: Order was fraudulent.
  • inventory: Items in the order were not in inventory.
  • declined: Order was canceled because the payment has been declined.
  • other: Order was canceled for a reason not in the list above.
processed_at timestamp The date (ISO 8601) and time (UTC) when the order was created. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
canceled_at timestamp If the order was canceled, then this column contains the date (ISO 8601) and time (UTC) when the order was canceled. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
deleted_at timestamp If the order was deleted, then this column contains the date (ISO 8601) and time (UTC) when the order was deleted. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
test boolean True when the order is a test order, False otherwise.

Remarks

Anchor link to section titled "Remarks"

Do not use the orders table for financial metrics. Use the sales for financial calculations instead.

Money columns and conversion rates

Anchor link to section titled "Money columns and conversion rates"

All money columns, such as total_price and total_price_fx, are stored as decimals to two digits of precision, and should be interpreted in the currency of the store.

The conversion rates used in these calculations are taken at 9:20 AM UTC everyday. These rates are calculated based on the mid-point between the buy and sell rates from the global currency markets.

Examples

Anchor link to section titled "Examples"

The following example lists orders that are unfulfilled:

The following example returns the rate of orders by repeat customers:

The following example returns the average total price of orders:

The following SQL query returns order cancellations:

Anchor link to section titled "Related information"