Orders Table

orders_table

The Orders table contains detailed data on each order, including transaction dates, product details, customer information, and sale amounts. Querying this table can provide insights into sales trends, customer buying behavior, and product performance.

📘
Note
event_date is a required field for queries on this table, and refers to the date of the order.

Dimensions

Dimensions are immutable properties that can be used for grouping data.

Title	ID	Type	Description
Event Hour	`event_hour`	string	The hour of the day the order was placed, according to a 24-hour clock. Based on the time zone of the shop at the moment of purchase. Example values: `07`, `16`, `21`
Event Date	`event_date`	date	The date the order was placed. Based on the time zone of the shop at the moment of purchase.
Event Day	`event_date.day`	date	The day on which the event occurred. Derived from `event_date`.
Event Week	`event_date.week`	date	The Sunday of the week during which the event occurred. Derived from `event_date`.
Event Month	`event_date.month`	date	The month during which the event occurred. Derived from `event_date`.
Event Quarter	`event_date.quarter`	date	The first month of the quarter during which the event occurred. Derived from `event_date`.
Event Year	`event_date.year`	date	The year during which the event occurred. Derived from `event_date`.
Amazon Fulfillment Channel	`amazon_fulfillment_channel`	string	The fulfillment channel of the Amazon order. Example values: `FBA`, `FBM`
Amazon Fulfillment Status	`amazon_fulfillment_status`	string	The fulfillment status of the Amazon order. Example values: `Pending`, `Unshipped`, `PartiallyShipped`, `Shipped`, `Canceled`, `Unfulfillable`, `InvoiceUnconfirmed`, `PendingAvailability`
Amazon Is Business Order	`amazon_is_business_order`	boolean	Indicates if the order is a business order on Amazon. Possible values: `true`, `false`
Amazon Marketplace ID	`amazon_marketplace_id`	string	The Amazon marketplace ID for the marketplace associated with the order. See list of marketplace IDs.
Canceled Date	`cancelled_at`	timestamp	The time at which the order was canceled. Formatted according to the ISO 8601 international standard. NULL if the order was not canceled. Example value: `2023-06-15T19:26:30.000Z`
Order Created Date	`created_at`	timestamp	The time at which the order record was created. Formatted according to the ISO 8601 international standard. Example value: `2022-06-15T19:26:30.000Z`
Customer Email	`customer_email`	string	The customer's email address. Example value: `[email protected]`
Customer First Name	`customer_first_name`	string	The customer's first name. Example value: `John`
Customer City	`customer_from_city`	string	The city of the customer's shipping address. Example value: `San Francisco`
Customer Country	`customer_from_country_code`	string	The country code of the customer's shipping address, according to ISO 3166-1 alpha-2. See list of country codes. Example value: `US`
Customer State	`customer_from_state_code`	string	The state or province of the customer's shipping address. Example value: `CA`
Customer ID	`customer_id`	string	The unique identifier for the customer, assigned by the main sales platform. Example value: `5209503793328`
Customer Last Name	`customer_last_name`	string	The last name of the customer. Example value: `Doe`
Customer Tags	`customer_tags`	repeated string	Tags associated with the customer, set in the main sales platform. Example values: `Active Subscriber`, `PayPal_user`, `Login with FB`
Discount Code	`discount_code`	string	The discount code or promotion name used in the order. Set by the seller in the main sales platform. Example values: `WELCOME10`, `Replacement for defective item`, `807QRWMNK2`
Discount Type	`discount_type`	string	The type of discount received by the customer in the order. Example values: `fixed_amount`, `percentage`
Fulfillment Status	`fulfillment_status`	string	The fulfillment status of the order. Example values: `fulfilled`, `partial`, `restocked`
Integration Shipping Status	`integration_shipping_status`	string	The shipping status of the order, according to the integrated shipping provider. Example values: `LabeledCreated`, `Completed`
Is First Order in Subscription	`is_first_order_in_subscription`	boolean	True if the order is the first in a subscription series. Possible values: `true`, `false`
Is New Customer	`is_new_customer`	boolean	True if this is the customer's first order. Possible values: `true`, `false` Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies.
Is Subscription Order	`is_subscription_order`	boolean	True if the order is part of a subscription. Possible values: `true`, `false`
Order ID	`order_id`	string	The unique order ID, assigned by the main sales platform. Example value: `4930225799243`
Order Name	`order_name`	string	The name of the order as assigned by the seller on the main sales platform. Example values: `#2188464`, `Ex-2163819-9`
Sales Platform	`platform`	string	The sales platform where the order was processed. Example values: `Shopify`, `BigCommerce`, `WooCommerce`, `Amazon`
Products	`products_info`	record repeated	Details about the products associated with the order (at the level of the variant), including name, product ID, variant ID, type, SKU, etc.
Product ID	`products_info.product_id`	string	The unique ID of the product as set within the main sales platform. Example value: `3891505496131`
Product Name	`products_info.product_name`	string	The name of the product as set within the main sales platform. Example values: `Green Soft Socks 3-Pack`, `Paring Knife Travel Case`, `Gift Subscription`
Product Type	`products_info.product_type`	string	The product type as set within the main sales platform. Example values: `Jackets`, `Coffee Mugs`, `Books`
Product SKU	`products_info.product_sku`	string	The SKU (Stock Keeping Unit) for the product as set within the main sales platform. Example values: `GP-BACK-02`, `A02630`, `POP200NAVY`
Product Variant ID	`products_info.variant_id`	string	The unique identifier for a specific variant of a product as set within the main sales platform. Example value: `29275946877001`
Is Gift Card	`products_info.is_gift_card`	boolean	True if the product purchased is a gift card. Possible values: `true`, `false`
Product Price	`products_info.product_name_price`	numeric	The price of a single product (to the buyer), before discounts. By default shown in the shop's currency. Note: Tax will be included in the product price if the seller has configured the sale to require taxes in the main sales platform.
Product Discount	`products_info.discount_amount_for_product`	numeric	The amount discounted from the product price. By default shown in the shop's currency.
Product Units Sold	`products_info.product_name_quantity_sold`	numeric	The total number of individual units sold for the product.
Product Cost	`products_info.single_product_cost`	numeric	The cost of a single product (to the seller). By default shown in the shop's currency.
Shipping Provider	`shipping_provider_id`	string	The integrated shipping provider that manages and processes the order's shipment. Example values: `shipbob`, `shipstation`
Shop Timezone	`shop_timezone`	string	The designated timezone of the shop according to the timezone database.
Source	`source_name`	string	The origin of the order as designated by the seller on the main sales platform. Example values: `web`, `pos`, `tiktok`
Tags	`tags`	repeated string	Tags associated with the order, assigned by the seller on the main sales platform. Example values: `Upsell`, `Order Credits`, `discount_eligible`
Transactions	`transactions`	record repeated	Details about transactions associated with the order, including payment information and status.
Transaction Created Date	`transactions.created_at`	timestamp	The time at which the transaction record was created. Formatted according to the ISO 8601 international standard. Example value: `2024-12-25 11:30:09`
Transaction Processed Date	`transactions.processed_at`	timestamp	The time at which the transaction was processed. Formatted according to the ISO 8601 international standard. Example value: `2024-12-25 11:30:09`
Transaction Payment Gateway	`transactions.payment_gateway_name`	string	The payment gateway used for the transaction. Example values: `shopify_payments`, `paypal`, `gift_card`
Transaction Currency	`transactions.currency`	string	The currency in which the transaction was processed. Example values: `USD`, `EUR`
Transaction Status	`transactions.status`	string	The status of the transaction. Example values: `SUCCESS`, `FAILURE`, `AWAITING_RESPONSE`, `PENDING`, `ERROR`
Transaction Amount	`transactions.amount`	numeric	The amount of the transaction.
Transaction Fees	`transactions.fees`	numeric	The fees associated with the transaction.

Measures

Measures are numeric fields that can be aggregated and/or combined to calculate new metrics.

Title	ID	Type	Description
Amazon Estimated Fees	`amazon_estimated_fees`	numeric	The estimated fees to be charged by Amazon for the order, based on products purchased. Note that this amount is an estimate and is not final until Amazon reports on actual fees.
Cost of Goods (COGS)	`cost_of_goods`	numeric	The cost of goods for orders within the selected time frame (before refunds). Imported from the main sales platform, or edit these in Cost Settings > Cost of Goods.
Order-Level Custom Expenses	`custom_expenses`	numeric	Additional custom expenses associated with individual orders. Import these using the Enrich Orders Data API endpoint.
Discounts	`discount_amount`	numeric	The total discount amount applied to the order, including shipping discounts.
Estimated Shipping Costs	`estimated_shipping_costs`	numeric	Estimated shipping costs paid by the seller for orders within the select timeframe. Edit these in: Cost Settings > Shipping
Gross Sales	`gross_product_sales`	numeric	The total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds. Gross Sales = Product Price x Units Sold
Handling Fees	`handling_fees`	numeric	Handling fees (e.g. boxing, packaging, or “pick & pack” services) paid by the seller for orders in the selected time frame. Edit these in Cost Settings > Cost of Goods.
Order Revenue	`order_revenue`	numeric	The total revenue after adjustments such as discounts, shipping, fees, and taxes (but before refunds). Order Revenue = Gross Sales - Discounts + Shipping + Tax
Payment Gateway Costs	`payment_gateway_costs`	numeric	Payment processing fees (not including currency conversion fees) paid by the seller. Edit these in Cost Settings > Gateway Costs.
Units Sold	`product_quantity_sold_in_order`	numeric	The total number of individual product units sold in the order.
Refunds	`refund_money`	numeric	Refund value processed during the selected time period, based on the order date (not including refunded cost of goods, taxes, and shipping). Note that Triple Whale does not track refunds from third-party return management apps (e.g., Loop), so totals may vary from the main sales platform.
Shipping Costs	`shipping_costs`	numeric	Shipping costs paid by the seller for orders within the select timeframe. Edit these in Cost Settings > Shipping.
Shipping Price	`shipping_price`	numeric	The total shipping price of the order (paid by the buyer), including shipping taxes and shipping discounts.
Taxes	`taxes`	numeric	Amount customers paid in taxes (excluding refunded taxes).

Derived

Derived fields are metrics that are pre-calculated using multiple measures or advanced formulas.

Title	ID	Type	Description
Average Order Value (AOV)	`aov`	formula	The average order value, excluding shipping and taxes, as reported by the main sales platform. AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders
New Customer Orders	`new_customer_orders`	formula	The total number of orders placed by new customers within the selected time frame. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies.
New Customer Revenue	`new_customer_revenue`	formula	The total revenue derived from orders by new customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies.
New Customers Percent	`new_customers_percent`	formula	The percentage of purchases made by new customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. New Customers % = Number of New Customer Orders / Total Orders
Orders	`orders`	formula	The total number of orders placed within the selected timeframe.
Non-Zero Orders	`orders_with_amount`	formula	The total number of orders with a revenue greater than zero. Non-Zero Orders = Count of Orders where Order Revenue > $0
Returning Customer Orders	`returning_customer_orders`	formula	Number of orders placed by Returning Customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies.
Returning Customer Revenue	`returning_customer_revenue`	formula	Amount of revenue derived from Returning Customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies.
Returning Customers Percent	`returning_customers_percent`	formula	Percentage of orders placed by Returning Customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. Returning Customers % = Number of Returning Customer Orders / Total Orders
True Average Order Value (True AOV)	`true_aov`	formula	The average non-zero order value, excluding shipping and taxes, as reported by the main sales platform. True AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders > $0
Unique Customers	`unique_customers`	formula	Number of unique customers who placed orders within the selected timeframe.

Updated about 1 month ago