Orders

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.

ID	Type	Description
event_date	date	The date the order was placed. Based on the time zone of the shop at the moment of purchase.
event_date.day	date	The day on which the order was placed. Derived from event_date.
event_date.week	date	The Sunday of the week during which the order was placed. Derived from event_date.
event_date.month	date	The month during which the order was placed. Derived from event_date.
event_date.quarter	date	The first month of the quarter during which the order was placed. Derived from event_date.
event_date.year	date	The year during which the order was placed. Derived from event_date.
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
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
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
shop_timezone	string	The timezone of the shop. Time zone designations are according to the tz database.
order_id	string	The unique order ID. Assigned by the shop platform. Example value: 4930225799243
order_name	string	The name of the order. Assigned by the seller in the shop platform. Example values: #2188464, Ex-2163819-9
source_name	string	The name of the source. Assigned by the seller in the shop platform. Example values: web, 1520611
customer_id	string	The unique customer ID. Assigned by the shop platform. Example value: 5209503793328
is_new_customer	boolean	True if this is the customer's first order (calculated by Triple Whale based on customer ID).
customer_first_name	string	The customer’s first name.
customer_last_name	string	The customer’s last name.
customer_email	string	The customer’s email address.
customer_from_country_code	string	The country of the shipping address, as a two-character country code. See list of country codes.
customer_from_state_code	string	The state/province of the shipping address.
customer_from_city	string	The city of the shipping address.
integration_shipping_status	string	The shipping status of the order.
discount_code	string	The discount code or promotion name used in the order. Note: If an order has multiple discount codes, only the first code is retrieved.
discount_type	string	The type of discount customer received in the order (e.g., product discount or shipping discount).
products_info	record repeated	Information about the product(s) associated with the order. There may be multiple products per order.
products_info.product_id	string	The unique ID of the product in the shop (not order-specific). Assigned by the shop platform. Example value: 3891505496131
products_info.product_name	string	Name of the product.
products_info.product_sku	string	Stock keeping unit of the product.
products_info.variant_id	string	The unique ID of the product variant in the shop (not order-specific). Assigned by the shop. Example value: 29275946877001
products_info.is_gift_card	boolean	True if the product purchased is a gift card.
products_info.product_name_price	numeric	The product's price (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 Triple Whale Admin.
products_info.discount_amount_for_product	numeric	The amount discounted from the product price in this order. By default shown in the shop's currency.
products_info.product_name_quantity_sold	numeric	The quantity of the product sold in the order.
products_info.single_product_cost	numeric	The cost of goods sold for the product. By default shown in the shop's currency. Data sourcing varies as follows: - For Shopify, the single product cost is set in the store and imported directly, or configured in Triple Whale admin under the costs settings tab. - For Amazon, the single product cost is set in Triple Whale admin under the costs settings tab.
tags	repeated string	The tags associated with the order.
customer_tags	repeated string	Tags associated with the customer. Example values: Active Subscriber, PayPal_user, Login with FB
is_subscription_order	boolean	True if the order is a subscription order.
is_first_order_in_subscription	boolean	True if the order is the first in a subscription order.
platform	string	The sales platform where the order was processed. Example values: shopify, amazon
amazon_marketplace_id	string	The Amazon marketplace ID for the marketplace associated with the order. See list of marketplace IDs.
amazon_fulfillment_status	string	The fulfillment status of the Amazon order (Pending, Unshipped, PartiallyShipped, Shipped, Canceled, Unfulfillable, InvoiceUnconfirmed, PendingAvailability).
amazon_fulfillment_channel	string	The fulfillment channel of the Amazon order (FBA, FBM).

Measures

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

Title	ID	Type	Description
Units Sold	product_quantity_sold_in_order	numeric	The total number of individual product units sold in the order.
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
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
Shipping Price	shipping_price	numeric	The total shipping price of the order, including shipping taxes and shipping discounts.
Refunds	refund_money	numeric	Refunds processed during the selected time period. Note that Triple Whale does not track refunds processed by 3rd-party return-management apps (e.g. Loop), so there may be variance relative to Shopify analytics.
Taxes	taxes	numeric	Amount customers paid in taxes (excluding refunded taxes)
Shipping Costs	shipping_costs	numeric	Shipping costs paid by the seller for orders within the select timeframe. Edit these in: Store > Shipping
Estimated Shipping Costs	estimated_shipping_costs	numeric	Estimated shipping costs paid by the seller for orders within the select timeframe. Edit these in: Store > Shipping
Payment Gateway Costs	payment_gateway_costs	numeric	Payment processing fees. Edit these in: Store > Gateway Costs
Cost of Goods (COGS)	cost_of_goods	numeric	The cost of goods sold within the selected time frame, minus the cost of goods of refunded items in this time frame. Imported from Shopify, or edit these in: Store > Cost of Goods.
Handling Fees	handling_fees	numeric	Cost of boxing, packaging, or “pick & pack” services. Edit these in: Store > Cost of Goods
Discounts	discount_amount	numeric	The total discount amount applied to the order, including shipping discounts.
Amazon Estimated Fees	amazon_estimated_fees	numeric	The estimated fees to be charged by Amazon for the order, based on products purchased. This amount is an estimate and is not final until Amazon reports on actual fees.

Derived

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

Title	ID	Type	Description
Orders	orders_count	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.
New Customer Orders	new_customer_orders	formula	Number of orders placed by New Customers. Note that Triple Whale identifies all customers as either new or returning, while in Shopify some remain unidentified (“N/A”), so there may be variance between the two.
Returning Customer Orders	returning_customer_orders	formula	Number of orders placed by Returning Customers. Note that Triple Whale identifies all customers as either new or returning, while in Shopify some remain unidentified (“N/A”), so there may be variance between the two.
Average Order Value (AOV)	aov	formula	AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders
New Customers %	new_customers_percent	formula	Percentage of orders placed by New Customers.
Returning Customers %	returning_customers_percent	formula	Percentage of orders placed by Returning Customers.
New Customer Revenue	new_customer_revenue	formula	Amount of revenue derived from New Customers. Note that Triple Whale identifies all customers as either new or returning, while in your main shop platform some remain unidentified (“N/A”), so there may be variance between the two.
Returning Customer Revenue	returning_customer_revenue	formula	Amount of revenue derived from Returning Customers. Note that Triple Whale identifies all customers as either new or returning, while in Shopify some remain unidentified (“N/A”), so there may be variance between the two.
True Average Order Value (True AOV)	true_aov	formula	True AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders > $0
Unique Customers	unique_customers	formula	Number of unique customers within the selected timeframe.