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_dateis a required field for queries on this table.
Dimensions
Dimensions are immutable properties that can be used for grouping data.
| ID | Type | Description | Source |
|---|---|---|---|
event_date | date | The date the order was placed. Based on the time zone of the shop at the moment of purchase. | Shopify, Amazon |
event_date.day | date | The day on which the order was placed. Derived from event_date. | Shopify, Amazon |
event_date.week | date | The Sunday of the week during which the order was placed. Derived from event_date. | Shopify, Amazon |
event_date.month | date | The month during which the order was placed. Derived from event_date. | Shopify, Amazon |
event_date.quarter | date | The first month of the quarter during which the order was placed. Derived from event_date. | Shopify, Amazon |
event_date.year | date | The year during which the order was placed. Derived from event_date. | Shopify, Amazon |
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 | Shopify, Amazon |
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 | Shopify, Amazon |
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 | Shopify, Amazon |
shop_timezone | string | The timezone of the shop. Time zone designations are according to the tz database. | Shopify, Amazon |
order_id | string | The unique order ID. Assigned by the shop platform. Example value: 4930225799243 | Shopify, Amazon |
order_name | string | The name of the order. Assigned by the seller in the shop platform. Example values: #2188464, Ex-2163819-9 | Shopify, Amazon |
source_name | string | The name of the source. Assigned by the seller in the shop platform. Example values: web, 1520611 | Shopify, Amazon |
customer_id | string | The unique customer ID. Assigned by the shop platform. Example value: 5209503793328 | Shopify, Amazon |
is_new_customer | boolean | True if this is the customer's first order (calculated by Triple Whale based on customer ID). | Shopify |
customer_first_name | string | The customer’s first name. | Shopify, Amazon |
customer_last_name | string | The customer’s last name. | Shopify, Amazon |
customer_email | string | The customer’s email address. | Shopify, Amazon |
customer_from_country_code | string | The country of the shipping address, as a two-character country code. See list of country codes. | Shopify, Amazon |
customer_from_state_code | string | The state/province of the shipping address. | Shopify, Amazon |
customer_from_city | string | The city of the shipping address. | Shopify, Amazon |
integration_shipping_status | string | The shipping status of the order. | ShipBob, ShipStation |
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. | Shopify, Amazon |
discount_type | string | The type of discount customer received in the order (e.g., product discount or shipping discount). | Shopify, Amazon |
products_info | record repeated | Information about the product(s) associated with the order. There may be multiple products per order. | Shopify, Amazon |
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 | Shopify, Amazon |
products_info.product_name | string | Name of the product. | Shopify, Amazon |
products_info.product_sku | string | Stock keeping unit of the product. | Shopify, Amazon |
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 | Shopify, Amazon |
products_info.is_gift_card | boolean | True if the product purchased is a gift card. | Shopify, Amazon |
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. | Shopify, Amazon |
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. | Shopify, Amazon |
products_info.product_name_quantity_sold | numeric | The quantity of the product sold in the order. | Shopify, Amazon |
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. | Shopify, Triple Whale Admin |
tags | repeated string | The tags associated with the order. | Shopify, Amazon |
customer_tags | repeated string | Tags associated with the customer. Example values: Active Subscriber, PayPal_user, Login with FB | Shopify |
is_subscription_order | boolean | True if the order is a subscription order. | Recharge |
is_first_order_in_subscription | boolean | True if the order is the first in a subscription order. | Recharge |
platform | string | The sales platform where the order was processed. Example values: shopify, amazon | Shopify, Amazon |
amazon_marketplace_id | string | The Amazon marketplace ID for the marketplace associated with the order. See list of marketplace IDs. | Amazon |
amazon_fulfillment_status | string | The fulfillment status of the Amazon order (Pending, Unshipped, PartiallyShipped, Shipped, Canceled, Unfulfillable, InvoiceUnconfirmed, PendingAvailability). | Amazon |
amazon_fulfillment_channel | string | The 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.
| Title | ID | Type | Description | Source |
|---|---|---|---|---|
| Units Sold | product_quantity_sold_in_order | numeric | The total number of individual product units sold in the order. | Shopify, Amazon |
| 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 | Shopify, Amazon |
| 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 | Shopify, Amazon |
| Shipping Price | shipping_price | numeric | The total shipping price of the order, including shipping taxes and shipping discounts. | Shopify, Amazon |
| 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. | Shopify, Amazon |
| Taxes | taxes | numeric | Amount customers paid in taxes (excluding refunded taxes) | Shopify, Amazon |
| Shipping Costs | shipping_costs | numeric | Shipping costs paid by the seller for orders within the select timeframe. Edit these in: Store > Shipping | Shopify, ShipBob, ShipStation, Triple Whale Admin |
| 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 | Shopify, ShipBob, ShipStation, Triple Whale Admin |
| Payment Gateway Costs | payment_gateway_costs | numeric | Payment processing fees. Edit these in: Store > Gateway Costs | Shopify, Amazon, Triple Whale Admin |
| 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. | Shopify, Amazon |
| Handling Fees | handling_fees | numeric | Cost of boxing, packaging, or “pick & pack” services. Edit these in: Store > Cost of Goods | Triple Whale Admin |
| Discounts | discount_amount | numeric | The total discount amount applied to the order, including shipping discounts. | Shopify, Amazon |
| 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. | Amazon |
Derived
Derived fields are metrics that are pre-calculated using multiple measures or advanced formulas.
| Title | ID | Type | Description | Source |
|---|---|---|---|---|
| Orders | orders_count | formula | The total number of orders placed within the selected timeframe. | Shopify, Amazon |
| Orders > $0 | orders_with_amount | formula | The total number of orders with a revenue greater than zero. | Shopify, Amazon |
| 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. | Shopify |
| 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. | Shopify |
| Average Order Value (AOV) | aov | formula | AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders | Shopify, Amazon |
| New Customers % | new_customers_percent | formula | Percentage of orders placed by New Customers. | Shopify, Amazon |
| Returning Customers % | returning_customers_percent | formula | Percentage of orders placed by Returning Customers. | Shopify, Amazon |
| 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 Shopify some remain unidentified (“N/A”), so there may be variance between the two. | Shopify, Amazon |
| 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. | Shopify, Amazon |
| True Average Order Value (True AOV) | true_aov | formula | True AOV = (Order Revenue - Shipping Price - Taxes) / Number of Orders > $0 | Shopify, Amazon |
| Unique Customers | unique_customers | formula | Number of unique customers within the selected timeframe. | Shopify |