pixel_orders_table
The Pixel Orders table merges detailed transaction data, including the timing of ad clicks, devices used by customers, and key order metrics like sales and orders, with ad performance insights from Triple Whale Pixel. Querying this table allows for an in-depth analysis of ad campaign effectiveness, customer conversion paths, and sales trends, highlighting the direct impact of advertising on purchase behavior.
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 the order was placed. 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: 2022-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 |
click_ts | timestamp | The time at which the ad was clicked. Formatted according to the ISO 8601 international standard. Example value: 2022-06-15T19:26:30.000Z | Triple Pixel |
channel | string | The platform through which the ad was delivered. | Triple Pixel |
campaign_id | string | The unique campaign ID for the marketing campaign associated with the ad. Assigned by the publisher platform. Example value: 120210439890990004 | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain |
ad_set_id | string | The unique ad set ID for the ad set within a campaign. Assigned by the publisher platform. Example value: 120210439890740003 | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Klaviyo, Taboola, Outbrain |
ad_id | string | The unique ad ID, assigned by the publisher platform. Example value: 120210439891110007 | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Taboola, Outbrain |
campaign_name | string | The name of the marketing campaign associated with the ad. | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain |
ad_set_name | string | The name of the ad set within a campaign. | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Klaviyo, Taboola, Outbrain |
ad_name | string | The name of the specific ad. | Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Taboola, Outbrain |
utm_medium | string | The UTM medium parameter captured by Triple Pixel from the ad URL. Example values: sponsored, paid, profile_link | Triple Pixel |
landing_page | string | The URL of the page where a visitor lands after clicking on an ad. | Triple Pixel |
device | string | The type of device used by the visitor (e.g. desktop, tablet, mobile, wearable) for the session. | Triple Pixel |
browser | string | The web browser of the session where the order occurred. | Triple Pixel |
session_country | string | The country from which the session originated. | Triple Pixel |
session_city | string | The city from which the session originated. | Triple Pixel |
model | string | The attribution model.Triple Attribution is applied by default.Example values: Triple Attribution, Last Click, First Click, Linear All, Total Impact, Triple Attribution + Views, Linear PaidNote: group by model is unavailable on the Pixel Orders table. You can group by model (with some limitations) using the Pixel Joined view. | Triple Pixel |
attribution_window | string | The time frame within which attributions are considered. lifetime is applied by default.Example values: 1_day, 7_days, 14_days, 28_days, lifetimeNote: group by attribution_window is unavailable on the Pixel Orders table. You can group by model (with some limitations) using the Pixel Joined view. | Triple Pixel |
triple_id | string | The unique ID assigned by Triple Whale to track users across sessions. | Triple Pixel |
session_id | string | The unique ID for the browsing session. Note: Only sessions with an order are included. For data on all sessions, refer to the Sessions table. | Triple Pixel |
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 |
| Pixel Purchases | orders_quantity | numeric | The total number of Pixel-reported purchases (orders) within the selected timeframe. | Triple Whale, Shopify, Amazon |
Derived
Derived fields are metrics that are pre-calculated using multiple measures or advanced formulas.
| Title | ID | Type | Description | Source |
|---|---|---|---|---|
| Pixel Purchases > $0 | orders_with_amount | formula | The total number of Pixel-reported purchases with a revenue greater than zero. | Shopify, Amazon |
| Pixel New Customer Purchases | new_customer_orders | formula | Number of Pixel-reported purchases placed by New Customers. | Shopify |
| Pixel Returning Customer Purchases | returning_customer_orders | formula | Number of Pixel-reported purchases placed by Returning Customers. | Shopify |
| Pixel Average Order Value | aov | formula | Pixel AOV = (Pixel Conversion Value - Shipping Price - Taxes) / Number of Pixel Purchases | Shopify, Amazon |
| Pixel New Customers % | new_customers_percent | formula | Percentage of Pixel-reported purchases by New Customers. | Shopify, Amazon |
| Pixel Returning Customers % | returning_customers_percent | formula | Percentage of Pixel-reported purchases by Returning Customers. | Shopify, Amazon |
| Pixel New Customer Revenue | new_customer_revenue | formula | Amount of Pixel-reported revenue derived from New Customers. | Shopify, Amazon |
| Pixel Returning Customer Revenue | returning_customer_revenue | formula | Amount of Pixel-reported revenue derived from New Customers. | Shopify, Amazon |
| Pixel True Average Order Value | true_aov | formula | Pixel True AOV = (Pixel-Reported Order Revenue - Shipping Price - Taxes) / Number of Pixel-Reported Orders > $0 | Shopify, Amazon |
| Unique Customers | unique_customers | formula | Number of unique customers within the selected timeframe. | Shopify |