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.