--- title: Orders table description: "Shopify Data Warehouse documentation: Orders table." source_url: html: https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/orders md: https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/orders.md --- # 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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/customers) table, use the [sales](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/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 | 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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/api_clients#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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/api_clients#api_client_id) table. | | [billing\_address\_id](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/addresses#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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/addresses#address_id) 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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/customers#customer_id) | numeric | The ID of the customer. This column is a **foreign key** reference to the customer\_id column in the [customers](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/customers#customer_id) table. | | [location\_id](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/locations#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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/locations#location_id) table. The location\_id is null for orders not placed through Shopify POS, such as those placed through the online store. | | [shipping\_address\_id](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/customers#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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/customers#address_id) 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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/shops#shop_id) | numeric | The ID of the store. This column is a **foreign key** reference to the shop\_id column in the [shops](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/shops#shop_id) table. | | [user\_id](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/users#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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/users#user_id) 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](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/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](https://shopify.dev/docs/api/admin-rest/latest/resources/discountcode) 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 fulfilledSee the [line items](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/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](https://help.shopify.com/manual/checkout-settings/test-orders) order, False otherwise. | ### Remarks Do not use the orders table for financial metrics. Use the [sales](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/sales) for financial calculations instead. ### 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 The following example lists orders that are unfulfilled: ```SQL SELECT order_id, NAME, fulfillment_status FROM shopify.orders WHERE fulfillment_status = 'unfulfilled'; ``` The following example returns the rate of orders by repeat customers: ```SQL SELECT DATE_TRUNC('month', processed_at), (1.0 * SUM(CASE WHEN row_number > 1 THEN 1 END) / COUNT(1)) AS repeat_order_rate FROM (SELECT *, ROW_NUMBER () OVER ( partition BY customer_id ORDER BY processed_at ASC) FROM shopify.orders) WHERE processed_at >= '2016-01-01' AND processed_at <= '2017-12-31' GROUP BY 1; ``` The following example returns the average total price of orders: ```SQL SELECT DATE_TRUNC('month', processed_at), AVG(total_price) AS avg_total_price FROM shopify.orders WHERE processed_at >= '2016-01-01' AND processed_at <= '2017-12-31' GROUP BY 1; ``` The following SQL query returns order cancellations: ```SQL SELECT DATE_TRUNC('month', processed_at), (1.0 * SUM(CASE WHEN canceled_at IS NOT NULL THEN 1 END) / COUNT(1)) AS cancellation_ratio FROM shopify.orders WHERE processed_at >= '2016-01-01' AND processed_at <= '2017-12-31' GROUP BY 1; ``` ### Related information * [Sales table](https://shopify.dev/docs/apps/build/data-warehouse/reference/schema/sales) * [Sales, orders, and line items](https://shopify.dev/docs/apps/build/data-warehouse) * [How returns are recorded](https://shopify.dev/docs/apps/build/data-warehouse) ***