Pixel Orders Table

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.

🚧

Key Concepts When Using Pixel Orders Table

Aggregation and Attribution

• The Pixel Orders table aggregates data at the order level, and is intended for order-level analysis using Triple Pixel attribution data.
• The Triple Attribution model and Lifetime attribution window applied by default.
• When the Triple Attribution model is applied, there may be duplicates for some fields (e.g. order_quantity and order_revenue). For analyses where you wish to avoid counting duplicates, use the Last Click model or query the Orders table instead.
• The Total Impact model is an aggregated attribution model, and therefore is not available on the Pixel Orders table since it cannot be applied on a per-order basis.

Order Data in Pixel Joined vs. Pixel Orders tables

• Order data in the Pixel Joined table comes from the ad platform (e.g. Facebook, TikTok), and is collected and aggregated according to the ad it's associated with. Orders made via onsite channel shops (e.g. Meta Shop and TikTok Shop) are included in the orders data in the Pixel Joined table.
• In contrast, order data in the Pixel Orders table comes from the main shop platform (e.g. Shopify, BigCommerce, WooCommerce) along with the Triple Pixel. Orders made via onsite channel shops (e.g. Meta Shop and TikTok Shop) are not included in the Pixel Orders table.
• This difference results in potential discrepancies between the two tables. The Pixel Joined table may show higher order counts for ads on platforms with onsite shops (e.g. Meta, TikTok) due to additional channel-reported orders that aren’t tracked in the Pixel Orders table.

📘

Note

event_date is a required field for queries on this table.

Dimensions

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

Title	ID	Type	Description
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.
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 the order was placed. Example values: 07, 16, 21
Ad ID	ad_id	string	The unique ad ID. Assigned by the ad platform. Example value: 120210439891110007
Ad Name	ad_name	string	The name of the ad. Example values: Kids Ad 1, Read Our Latest Blog 03/12/2023, 8 best fall looks
Ad Set ID	adset_id	string	The unique ID for the ad set associated with the ad click. Assigned by the ad platform. Example value: 120210439890740003
Ad Set Name	adset_name	string	The name of the ad set within a campaign. Example values: Broad Audience, Bracelets, Wellness
Attribution Window	attribution_window	string	The time frame for attributing conversions to the ad. By default lifetime. Example values: 1_day, 7_days, 14_days, 28_days, lifetime Note: group by attribution_window is unavailable on the Pixel Orders table.
Web Browser	browser	string	The web browser of the session where the order originated. Example values: Chrome, Firefox, Safari
Campaign ID	campaign_id	string	The unique ID for the marketing campaign associated with the ad. Assigned by the ad platform. Example values: 23852438666180053, 531011533, 20620285829
Campaign Name	campaign_name	string	The name of the marketing campaign associated with the ad. Example values: Catalog Retargeting, US Generic Search, Breakfast of Champions Campaign
Cancelled 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. \n \nExample value: 2022-06-15T19:26:30.000Z
Channel	channel	string	The platform through which the ad was delivered. Example values: facebook-ads, tiktok-ads, Direct
Click Timestamp	click_ts	timestamp	The time at which the ad was clicked. Formatted according to the ISO 8601 international standard. Example value: 2024-10-26 20:03:48
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
Device	device	string	The type of device used by the visitor for the session. Example values: mobile, desktop, tablet, wearable
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
Landing Page	landing_page	string	The URL path (without query parameters) of the page the visitor landed after clicking on an ad or link, indicating the first page viewed in a session. Example value: /myshop/collections/green_sweater
Attribution Model	model	string	The attribution model used. By default Triple Attribution. Example values: Triple Attribution, Last Click, First Click, Linear All, Triple Attribution + Views, Linear Paid Note that Total Impact is not available on the Pixel Orders table.
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
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 ID 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).
Product Discount	products_info.discount_amount_for_product	numeric	The discount amount for the product.
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).
Session City	session_city	string	The city of the session where the order originated. Example values: San Francisco, Tel Aviv, Kyiv
Session Country	session_country	string	The country of the session where the order originated. Example values: United Kingdom, New Zealand, United Arab Emirates
Session ID	session_id	string	The unique session ID assigned by Triple Pixel. Note: Only sessions with an order are included in the Pixel Orders table. For data on all sessions, use the Sessions table. Example value: cltlok4y634IRw62AZ_1710700966152
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 tz 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 in 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.
Triple Pixel ID	triple_id	string	The unique ID assigned by Triple Pixel to track visitors across sessions. Example value: cltlok4y634IRw62AZ
UTM Medium	utm_medium	string	The UTM medium parameter captured by Triple Pixel from the ad URL Example values: sponsored, paid, profile_link

Measures

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

Title	ID	Type	Description
Cost of Goods (COGS)	cost_of_goods	numeric	The cost of goods for Pixel-reported purchases within the selected time frame (before refunds). Imported from the main sales platform, or edit these in Cost Settings > Cost of Goods. Note that COGS for Pixel data is set at attribution time and does not get updated subsequently, so may vary from COGS in the Orders table.
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
Pixel Purchases	orders_quantity	numeric	The total number of Pixel-reported purchases (orders) within the selected timeframe. Note: This value is calculated based on the attribution model, and may be a fractional number.
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, 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
Pixel Average Order Value	aov	formula	The Pixel-reported average order value. Pixel AOV = Pixel Conversion Value / Number of Pixel Purchases
Pixel New Customer Purchases	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.
Pixel 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.
Pixel New Customers Percent	new_customers_percent	formula	The percentage of Pixel-reported 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. Pixel New Customers % = Number of New Customer Pixel Purchases / Total Pixel Purchases
Non-Zero Pixel Purchases	orders_with_amount	formula	The total number of Pixel-reported purchases with a revenue greater than zero. Non-Zero Pixel Purchases = Count of Pixel Purchases where Order Revenue > $0
Pixel Returning Customer Purchases	returning_customer_orders	formula	Number of Pixel-reported 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.
Pixel Returning Customer Revenue	returning_customer_revenue	formula	Pixel-reported conversion value (order 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.
Pixel Returning Customers Percent	returning_customers_percent	formula	The percentage of Pixel-reported purchases made 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. Pixel New Customers % = Number of Returning Customer Pixel Purchases / Total Pixel Purchases
Pixel True Average Order Value	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.