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.

Dimensions

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

NameTypeDescriptionSource
event_datedateThe date the order was placed. Based on the time zone of the shop at the moment of purchase.Shopify, Amazon
event_date.daydateThe day on which the order was placed. Derived from event_date.Shopify, Amazon
event_date.weekdateThe Sunday of the week during which the order was placed. Derived from event_date.Shopify, Amazon
event_date.monthdateThe month during which the order was placed. Derived from event_date.Shopify, Amazon
event_date.quarterdateThe first month of the quarter during which the order was placed. Derived from event_date.Shopify, Amazon
event_date.yeardateThe year during which the order was placed. Derived from event_date.Shopify, Amazon
event_hourstringThe 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
Shopify, Amazon
created_attimestampThe time at which the order record was created. Formatted according to the ISO 8601 international standard.

Example value: 2022-06-15T19:26:30.000Z
Shopify, Amazon
cancelled_attimestampThe 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
Shopify, Amazon
shop_timezonestringThe timezone of the shop. Time zone designations are according to the tz database.Shopify, Amazon
order_idstringThe unique order ID. Assigned by the shop platform.

Example value: 4930225799243
Shopify, Amazon
order_namestringThe name of the order. Assigned by the seller in the shop platform.

Example values: #2188464, Ex-2163819-9
Shopify, Amazon
source_namestringThe name of the source. Assigned by the seller in the shop platform.

Example values: web, 1520611
Shopify, Amazon
customer_idstringThe unique customer ID. Assigned by the shop platform.

Example value: 5209503793328
Shopify, Amazon
is_new_customerbooleanTrue if this is the customer's first order (calculated by Triple Whale based on customer ID).Shopify
customer_first_namestringThe customer’s first name.Shopify, Amazon
customer_last_namestringThe customer’s last name.Shopify, Amazon
customer_emailstringThe customer’s email address.Shopify, Amazon
customer_from_country_codestringThe country of the shipping address, as a two-character country code. See list of country codes.Shopify, Amazon
customer_from_state_codestringThe state/province of the shipping address.Shopify, Amazon
customer_from_citystringThe city of the shipping address.Shopify, Amazon
integration_shipping_statusstringThe shipping status of the order.ShipBob, ShipStation
discount_codestringThe discount code or promotion name used in the order.

Note: If an order has multiple discount codes, only the first code is retrieved.
Shopify, Amazon
discount_typestringThe type of discount customer received in the order (e.g., product discount or shipping discount).Shopify, Amazon
products_inforecord repeatedInformation about the product(s) associated with the order. There may be multiple products per order.Shopify, Amazon
products_info.product_idstringThe unique ID of the product in the shop (not order-specific). Assigned by the shop platform.

Example value: 3891505496131
Shopify, Amazon
products_info.product_namestringName of the product.Shopify, Amazon
products_info.product_skustringStock keeping unit of the product.Shopify, Amazon
products_info.variant_idstringThe unique ID of the product variant in the shop (not order-specific). Assigned by the shop.

Example value: 29275946877001
Shopify, Amazon
products_info.is_gift_cardbooleanTrue if the product purchased is a gift card.Shopify, Amazon
products_info.product_name_pricenumericThe 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.
Shopify, Amazon
products_info.discount_amount_for_productnumericThe amount discounted from the product price in this order. By default shown in the shop's currency.Shopify, Amazon
products_info.product_name_quantity_soldnumericThe quantity of the product sold in the order.Shopify, Amazon
products_info.single_product_costnumericThe 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.
Shopify, Triple Whale Admin
tagsrepeated stringThe tags associated with the order.Shopify, Amazon
customer_tagsrepeated stringTags associated with the customer.

Example values: Active Subscriber, PayPal_user, Login with FB
Shopify
is_subscription_orderbooleanTrue if the order is a subscription order.Recharge
is_first_order_in_subscriptionbooleanTrue if the order is the first in a subscription order.Recharge
platformstringThe sales platform where the order was processed.

Example values: shopify, amazon
Shopify, Amazon
amazon_marketplace_idstringThe Amazon marketplace ID for the marketplace associated with the order. See list of marketplace IDs.Amazon
amazon_fulfillment_statusstringThe fulfillment status of the Amazon order (Pending, Unshipped, PartiallyShipped, Shipped, Canceled, Unfulfillable, InvoiceUnconfirmed, PendingAvailability).Amazon
amazon_fulfillment_channelstringThe fulfillment channel of the Amazon order (FBA, FBM).Amazon

Measures

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

NameTypeDescriptionSource
product_quantity_sold_in_ordernumericThe total number of product units sold in the order.Shopify, Amazon
order_revenuenumericThe total revenue of the products in the order, after adjustments such as discounts, shipping, returns, fees, and taxes.

Gross Product Sales - Discounts - Returns + Shipping + Tax
Shopify, Amazon
gross_product_salesnumericThe total revenue of the products in the order, before adjustments such as discounts, shipping, returns, fees, and taxes.

Product Price x Quantity
Shopify, Amazon
shipping_pricenumericThe total shipping price of the order (to buyer), including shipping taxes and shipping discounts. By default shown in the shop's currency.Shopify, Amazon
refund_moneynumericThe total refund amount of the order. By default shown in the shop's currency.Shopify, Amazon
taxesnumericThe total taxes of the order. By default shown in the shop's currency.Shopify, Amazon
shipping_costsnumericShipping cost to the seller. By default shown in the shop's currency.

Note: The data source varies based on your shipping cost settings in Triple Whale admin.
Shopify, ShipBob, ShipStation, Triple Whale Admin
estimated_shipping_costsnumericEstimated shipping cost to the seller. By default shown in the shop's currency.

Note: The data source varies based on your shipping cost settings in Triple Whale admin.
Shopify, ShipBob, ShipStation, Triple Whale Admin
payment_gateway_costsnumericThe total payment gateway costs of the order. By default shown in the shop's currency.

The data source varies depending on the payment method set in Triple Whale Admin.
Shopify, Amazon, Triple Whale Admin
cost_of_goodsnumericThe total cost of goods sold (COGS) in the order (to seller). By default shown in the shop's currency.Shopify, Amazon
handling_feesnumericThe total handling fees of the order. By default shown in the shop's currency.Triple Whale Admin
discount_amountnumericThe total discount amount applied to the order (including product discounts; excluding shipping discounts, which are accounted for in shipping_price). By default shown in the shop's currency.Shopify, Amazon
amazon_estimated_feesnumericThe estimated fees to be charged by Amazon for the order. By default shown in the shop's currency.Amazon

Derived

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

NameTypeDescriptionSource
orders_countformulaTotal number of orders placed.

Formula:
count(1) orders
Shopify, Amazon
orders_with_amountformulaTotal number of orders with a revenue greater than zero.

Formula:
sum(if(order_revenue > 0, 1, 0)) orders_with_amount
Shopify, Amazon
new_customer_ordersformulaTotal number of orders placed by new customers.

Formula:
sum(if(is_new_customer, 1, 0)) new_customer_orders
Shopify
returning_customer_ordersformulaTotal number of orders placed by returning customers.

Formula:
sum(if(is_new_customer, 0, 1)) returning_customer_orders
Shopify
aovformulaAverage order value, minus shipping and taxes. By default shown in the shop's currency.

Formula:
safe_divide(sum(order_revenue - shipping_price - taxes), count(1)) aov
Shopify, Amazon
new_customers_percentformulaPercentage of orders placed by new customers out of all orders.

Formula:
safe_divide(sum(if(is_new_customer, 1, 0)), count(1)) new_customers_percent
Shopify, Amazon
returning_customers_percentformulaPercentage of orders placed by returning customers out of all orders.

Formula:
safe_divide(sum(if(is_new_customer, 0, 1)), count(1)) returning_customers_percent
Shopify, Amazon
new_customer_revenueformulaTotal revenue generated from new customers. By default shown in the shop's currency.

Formula:
sum(if(is_new_customer, order_revenue, 0)) new_customer_revenue
Shopify, Amazon
returning_customer_revenueformulaTotal revenue generated from returning customers. By default shown in the shop's currency.

Formula:
sum(if(is_new_customer, 0, order_revenue)) returning_customer_revenue
Shopify, Amazon
true_aovformula"True" average order value, minus shipping and taxes, for orders with a positive revenue. By default shown in the shop's currency.

Formula:
safe_divide(sum(order_revenue - shipping_price - taxes), sum(if(order_revenue > 0, 1, 0))) true_aov
Shopify, Amazon
total_costsformulaSum of all costs associated with the orders, including cost of goods sold, shipping, payment gateway costs, handling fees, and taxes. By default shown in the shop's currency.

Formula:
sum(cost_of_goods + shipping_costs + payment_gateway_costs + handling_fees + taxes) total_costs
Shopify, Amazon
gross_marginformulaRatio of gross profit to order revenue.

Formula:
safe_divide(sum(order_revenue - (cost_of_goods + shipping_costs + payment_gateway_costs + handling_fees + taxes)), sum(order_revenue)) gross_margin
Shopify, Amazon
gross_profitformulaTotal profit excluding cost of goods, shipping costs, payment gateway costs, handling fees, and taxes. By default shown in the shop's currency.

Formula:
sum(order_revenue - (cost_of_goods + shipping_costs + payment_gateway_costs + handling_fees + taxes)) gross_profit
Shopify, Amazon
unique_customersformulaNumber of unique customers who have placed an order.

Formula:
count(distinct customer_id) unique_customers
Shopify