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.

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 the order was placed. 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: 2022-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.
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
channel	string	The platform through which the ad was delivered.
campaign_id	string	The unique campaign ID for the marketing campaign associated with the ad. Assigned by the publisher platform. Example value: 120210439890990004
ad_set_id	string	The unique ad set ID for the ad set within a campaign. Assigned by the publisher platform. Example value: 120210439890740003
ad_id	string	The unique ad ID, assigned by the publisher platform. Example value: 120210439891110007
campaign_name	string	The name of the marketing campaign associated with the ad.
ad_set_name	string	The name of the ad set within a campaign.
ad_name	string	The name of the specific ad.
utm_medium	string	The UTM medium parameter captured by Triple Pixel from the ad URL. Example values: sponsored, paid, profile_link
landing_page	string	The URL of the page where a visitor lands after clicking on an ad.
device	string	The type of device used by the visitor (e.g. desktop, tablet, mobile, wearable) for the session.
browser	string	The web browser of the session where the order occurred.
session_country	string	The country from which the session originated.
session_city	string	The city from which the session originated.
model	string	The attribution model.Triple Attribution is applied by default. Example values: Triple Attribution, Last Click, First Click, Linear All, Triple Attribution + Views, Linear Paid Note: Total Impact is not available in the Pixel Orders table. Since Total Impact is an aggregated model, it cannot be applied on a per-order level.
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, lifetime Note: group by attribution_window is unavailable on the Pixel Orders table.
triple_id	string	The unique ID assigned by Triple Whale to track users across sessions.
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.

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 (not including currency conversion fees). Edit these in: Store > Gateway Costs
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 (MSP), or edit in: Store > Cost of Goods. Note that COGS for Pixel data is set at attribution time and does not get updated, so may vary from COGS in the Orders table.
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: Store > Cost of Goods
Discounts	discount_amount	numeric	The total discount amount applied to the order, including shipping discounts.
Pixel Purchases	orders_quantity	numeric	The total number of Pixel-reported purchases (orders) within the selected timeframe.

Derived

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

Title	ID	Type	Description
Pixel Purchases > $0	orders_with_amount	formula	The total number of Pixel-reported purchases with a revenue greater than zero.
Pixel New Customer Purchases	new_customer_orders	formula	Number of Pixel-reported purchases placed by New Customers.
Pixel Returning Customer Purchases	returning_customer_orders	formula	Number of Pixel-reported purchases placed by Returning Customers.
Pixel Average Order Value	aov	formula	Pixel AOV = (Pixel Conversion Value - Shipping Price - Taxes) / Number of Pixel Purchases
Pixel New Customers %	new_customers_percent	formula	Percentage of Pixel-reported purchases by New Customers.
Pixel Returning Customers %	returning_customers_percent	formula	Percentage of Pixel-reported purchases by Returning Customers.
Pixel New Customer Revenue	new_customer_revenue	formula	Amount of Pixel-reported revenue derived from New Customers.
Pixel Returning Customer Revenue	returning_customer_revenue	formula	Amount of Pixel-reported revenue derived from New Customers.
Pixel True Average Order Value	true_aov	formula	Pixel True AOV = (Pixel-Reported Order Revenue - Shipping Price - Taxes) / Number of Pixel-Reported Orders > $0
Unique Customers	unique_customers	formula	Number of unique customers within the selected timeframe.