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_quantityandorder_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 primarily comes from the shop platform (e.g., Shopify, BigCommerce, WooCommerce) as well as via onsite channel shops (e.g. Meta Shop and TikTok Shop).
- Onsite purchase data (e.g. Meta Shop and TikTok Shop) is also included in the Pixel Orders table, but is only available at the channel level.
- The Pixel Joined and Pixel Orders tables may show different order counts for ads on platforms with onsite shops (e.g. Meta, TikTok) due to channel-reported orders that are tracked in the Pixel Orders table but not Pixel Joined table (e.g. organic orders).
Note
event_dateis 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 |
| date | The date the order was placed. Based on the time zone of the shop at the moment of purchase. |
Event Day |
| date | The day on which the event occurred. Derived from |
Event Week |
| date | The Sunday of the week during which the event occurred. Derived from |
Event Month |
| date | The month during which the event occurred. Derived from |
Event Quarter |
| date | The first month of the quarter during which the event occurred. Derived from |
Event Year |
| date | The year during which the event occurred. Derived from |
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: |
Ad ID |
| string | The unique ad ID. Assigned by the ad platform. Example value: |
Ad Name |
| string | The name of the ad. Example values: |
Ad Set ID |
| string | The unique ID for the ad set associated with the ad click. Assigned by the ad platform. Example value: |
Ad Set Name |
| string | The name of the ad set within a campaign. Example values: |
Attribution Window |
| string | The time frame for attributing conversions to the ad. By default Example values: Note: |
Web Browser |
| string | The web browser of the session where the order originated. Example values: |
Campaign ID |
| string | The unique ID for the marketing campaign associated with the ad. Assigned by the ad platform. Example values: |
Campaign Name |
| string | The name of the marketing campaign associated with the ad. Example values: |
Cancelled Date |
| 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: |
Channel |
| string | The platform through which the ad was delivered. Example values: |
Click Timestamp |
| timestamp | The time at which the ad was clicked. Formatted according to the ISO 8601 international standard. Example value: |
Order Created Date |
| timestamp | The time at which the order record was created. Formatted according to the ISO 8601 international standard. Example value: |
Customer Email |
| string | The customer's email address. Example value: |
Customer First Name |
| string | The customer's first name. Example value: |
Customer City |
| string | The city of the customer's shipping address. Example value: |
Customer Country |
| string | The country code of the customer's shipping address, according to ISO 3166-1 alpha-2. See list of country codes. Example value: |
Customer State |
| string | The state or province of the customer's shipping address. Example value: |
Customer ID |
| string | The unique identifier for the customer, assigned by the main sales platform. Example value: |
Customer Last Name |
| string | The last name of the customer. Example value: |
Customer Tags |
| repeated string | Tags associated with the customer, set in the main sales platform. Example values: |
Device |
| string | The type of device used by the visitor for the session. Example values: |
Discount Code |
| string | The discount code or promotion name used in the order. Set by the seller in the main sales platform. Example values: |
Discount Type |
| string | The type of discount received by the customer in the order. Example values: |
Fulfillment Status |
| string | The fulfillment status of the order. Example values: |
Integration Shipping Status |
| string | The shipping status of the order, according to the integrated shipping provider. Example values: |
Is First Order in Subscription |
| boolean | True if the order is the first in a subscription series. Possible values: |
Is New Customer |
| boolean | True if this is the customer's first order. Possible values: 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 |
| boolean | True if the order is part of a subscription. Possible values: |
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: |
Attribution Model |
| string | The attribution model used. By default Example values: Note that |
Order ID |
| string | The unique order ID, assigned by the main sales platform. Example value: |
Order Name |
| string | The name of the order as assigned by the seller on the main sales platform. Example values: |
Products |
| 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 |
| string | The unique ID of the product as set within the main sales platform. Example value: |
Product Name |
| string | The name of the product as set within the main sales platform. Example values: |
Product Type |
| string | The product type as set within the main sales platform. Example values: |
Product SKU |
| string | The SKU (Stock Keeping Unit) for the product as set within the main sales platform. Example values: |
Product Variant ID |
| string | The unique ID for a specific variant of a product as set within the main sales platform. Example value: |
Is Gift Card |
| boolean | True if the product purchased is a gift card. Possible values: |
Product Price |
| numeric | The price of a single product (to the buyer). |
Product Discount |
| numeric | The discount amount for the product. |
Product Units Sold |
| numeric | The total number of individual units sold for the product. |
Product Cost |
| numeric | The cost of a single product (to the seller). |
Session City |
| string | The city of the session where the order originated. Example values: |
Session Country |
| string | The country of the session where the order originated. Example values: |
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: |
Shipping Provider |
| string | The integrated shipping provider that manages and processes the order's shipment. Example values: |
Shop Timezone |
| string | The designated timezone of the shop according to the tz database. |
Source |
| string | The origin of the order as designated by the seller on the main sales platform. Example values: |
Tags |
| repeated string | Tags associated with the order, assigned by the seller in the main sales platform. Example values: |
Transactions |
| record repeated | Details about transactions associated with the order, including payment information and status. |
Transaction Created Date |
| timestamp | The time at which the transaction record was created. Formatted according to the ISO 8601 international standard . Example value: |
Transaction Processed Date |
| timestamp | The time at which the transaction was processed. Formatted according to the ISO 8601 international standard . Example value: |
Transaction Payment Gateway |
| string | The payment gateway used for the transaction. Example values: |
Transaction Currency |
| string | The currency in which the transaction was processed. Example values: |
Transaction Status |
| string | The status of the transaction. Example values: |
Transaction Amount |
| numeric | The amount of the transaction. |
Transaction Fees |
| numeric | The fees associated with the transaction. |
Triple Pixel ID |
| string | The unique ID assigned by Triple Pixel to track visitors across sessions. Example value: |
UTM Medium |
| string | The UTM medium parameter captured by Triple Pixel from the ad URL Example values: |
Measures
Measures are numeric fields that can be aggregated and/or combined to calculate new metrics.
Title | ID | Type | Description |
|---|---|---|---|
Cost of Goods (COGS) |
| 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 |
| numeric | Additional custom expenses associated with individual orders. Import these using the Enrich Orders Data API endpoint. |
Discounts |
| numeric | The total discount amount applied to the order, including shipping discounts. |
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 |
| numeric | The total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds. Gross Sales = Product Price x Units Sold |
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 |
| 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 |
| 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 |
| numeric | Payment processing fees (not including currency conversion fees) paid by the seller. Edit these in Cost Settings > Gateway Costs. |
Units Sold |
| numeric | The total number of individual product units sold in the order. |
Refunds |
| 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 |
| numeric | Shipping costs paid by the seller for orders within the select timeframe. Edit these in Cost Settings > Shipping. |
Shipping Price |
| numeric | The total shipping price of the order, including shipping taxes and shipping discounts. |
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 |
| formula | The Pixel-reported average order value. Pixel AOV = Pixel Conversion Value / Number of Pixel Purchases |
Pixel New Customer Purchases |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| formula | Number of unique customers who placed orders within the selected timeframe. |
Updated 11 days ago