Pixel Joined Table

pixel_joined_tvf

The Pixel Joined table combines essential advertising and transaction data, giving users insights into how campaigns, ad sets, and ads drive revenue, customer acquisition, and ROAS. This unified view supports better marketing strategies and optimized budget allocation for improved ROI.

🚧

Key Concepts When Using Pixel Joined Table

Aggregation and Attribution

• The Ads table is the core of the Pixel Joined table. Additional tables (e.g., Orders, Email/SMS, Refunds) are outer joined dynamically based on which columns are queried.
• Data is aggregated at the ad level, with Triple Attribution model and Lifetime attribution window applied by default.

Metrics

• Channel-Reported data (e.g., channel_reported_conversions, channel_reported_conversion_value) comes directly from ad platforms, reflecting channel-specific data.
• Pixel-Reported data (e.g., orders_quantity, order_revenue) is captured via Triple Whale Pixel and the shop platform (e.g. Shopify, BigCommerce, WooCommerce), providing detailed order insights from e-commerce platforms.

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.

Simulated Query Behavior

• group by attribution_window and group by model will not work on certain complex queries, because the Pixel Joined table functions as a simulated view rather than a standard SQL table. It dynamically processes data based on user queries, which can introduce constraints compared to typical SQL behavior.

📘

Note

event_date is a required field for queries on this table. By default refers to the purchase date.

Parameters

Parameters can be used to customize and filter the data in the table.

Title	ID	Description
City Filter	city_filter	Filters results by city.
Country Filter	country_filter	Filters results by country.
Include Custom Ad Spend	include_custom_ad_spend	Includes custom ad spend in the spend column. Possible: True, False
Order Tags Filter	order_tags_filter	Filters orders by tags. Matches any order containing the specified comma-separated tags.
Subscription Filter	subscription_filter	Filters results by subscription type. Possible values: none, subscription_first_order, subscription_recurring_order, subscription_order, non-subscription_order, non_subscription_recurring_order
Use Click Date	use_click_date	Uses click date instead of purchase date for event_date. Possible values: True, False

Dimensions

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

Title	ID	Type	Description
Event Hour	event_hour	string	The hour of the day on which the ad was run or the order was placed, according to a 24-hour clock. Based on the time zone of the shop at the moment of the event. Example values: 07, 16, 21
Event Date	event_date	date	The date the ad was run or the order was placed. Based on the time zone of the shop at the moment the event occurred.
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.
Ad Bid Amount	ad_bid_amount	string	The bid amount for the ad. Example values: 15, 28, 30
Ad Copy	ad_copy	string	The full text (copy) of the ad.
Ad ID	ad_id	string	The unique ad ID. Assigned by the publisher platform. Example value: 120210439891110007
Ad Image URL	ad_image_url	string	The URL of the ad image. Example value: https://www.example.com/ad-image.jpg
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 Status	ad_status	string	The status of the ad. Example values: active, paused
Ad Type	ad_type	string	The format of the ad. Example values: video, image, copy
Ad Set Bid Amount	adset_bid_amount	string	The bid amount for the ad set. Example values: 25, 35, 40
Ad Set Bid Strategy	adset_bid_strategy	string	The bidding strategy for the ad set. Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP, LOWEST_COST_WITH_MIN_ROAS
Ad Set Daily Budget	adset_daily_budget	string	The daily budget set for the ad set within a campaign. By default shown in the shop's currency. Example values: 1750, 6000, 40000
Ad Set ID	adset_id	string	The unique ID for the ad set associated with the ad click. Assigned by the publisher platform. Example value: 120210439890740003
Ad Set Lifetime Budget	adset_lifetime_budget	string	The total budget set for the ad set within a campaign over its lifetime. By default shown in the shop's currency. Example values: 5000, 10000, 20000
Ad Set Name	adset_name	string	The name of the ad set within a campaign. Example values: Broad Audience, Bracelets, Wellness
Ad Set Status	adset_status	string	The status of the ad set. Example values: active, paused
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
Campaign Bid Strategy	campaign_bid_strategy	string	The bidding strategy for the campaign. Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP, LOWEST_COST_WITH_MIN_ROAS
Campaign Daily Budget	campaign_daily_budget	string	The daily budget for the campaign. Example values: 8000, 10000, 15000
Campaign ID	campaign_id	string	The unique ID for the marketing campaign associated with the ad. Assigned by the publisher platform. Example values: 23852438666180053, 531011533, 20620285829
Campaign Lifetime Budget	campaign_lifetime_budget	string	The lifetime budget for the campaign. Example values: 5000, 10000, 20000
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
Campaign Status	campaign_status	string	The status of the campaign. Example values: active, paused
Channel	channel	string	The platform through which the ad was delivered. Example values: facebook-ads, google-ads, tiktok-ads
Destination URL	destination_url	string	The destination URL where users land after clicking the ad. Example value: https://www.myshop.com/products/productA
Model	model	string	The attribution model used. By default Triple Attribution. Example values: Triple Attribution, Last Click, First Click, Linear All, Total Impact, Triple Attribution + Views, Linear Paid
URL Template	url_template	string	A template URL used to dynamically populate ad URLs with relevant tracking parameters. Example value: utm_source=facebook&utm_campaign={{campaign.name}}
Video URL	video_url	string	The URL of the ad video. Example value: https://www.youtube.com/embed/example

Measures

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

Title	ID	Type	Description
Pixel Add to Carts	add_to_carts	numeric	The Pixel-reported number of Add To Carts from unique customers.
Pixel Bounces	bounces	numeric	The Pixel-reported number of single-page sessions where the visitor left the site after a single page view.
Channel-Reported Conversion Value	channel_reported_conversion_value	numeric	Revenue generated from channel-reported conversions (purchases).
Channel-Reported Conversions (Purchases)	channel_reported_conversions	numeric	Number of channel-reported conversions (purchases).
Channel-Reported Onsite Conversion Value	channel_reported_onsite_conversion_value	numeric	Revenue from channel-reported purchases made on the ad channel shop (e.g. Meta Shop, TikTok Shop).
Channel-Reported Onsite Purchases	channel_reported_onsite_purchases	numeric	Number of channel-reported purchases made on the ad channel shop (e.g. Meta Shop, TikTok Shop).
Channel-Reported Visits	channel_reported_visits	numeric	Number of channel-reported visits.
Clicks	clicks	numeric	Number of channel-reported clicks.
Pixel 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 data in the Orders table.
SMS Messages Delivered	delivered	numeric	The channel-reported number of messages that successfully reached the recipients' phones.
Pixel Email Signups	email_signups	numeric	The Pixel-reported total number of users who signed up for the mailing list during a session.
Pixel Gross Sales	gross_product_sales	numeric	Pixel-reported gross sales, before adjustments such as discounts, shipping, fees, taxes, and refunds. Gross Sales = Product Price x Units Sold
Impressions	impressions	numeric	Number of channel-reported impressions.
Pixel New Customer Cost of Goods	new_customer_cogs	numeric	The cost of goods for Pixel-reported purchases by new customers 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 data in the Orders table.
Pixel New Customer Conversion Value	new_customer_order_revenue	numeric	Pixel-reported conversion value (order revenue) derived from New Customers.
Pixel New Customer Purchases	new_customer_orders	numeric	Number of Pixel-reported orders placed by New Customers.
Pixel New Visitors	new_visitors	numeric	The Pixel-reported number of first-time visitors to the site.
One-Day View Conversion Value	one_day_view_conversion_value	numeric	The total value of channel-reported purchases made within one day of viewing the ad.
One-Day View Purchases	one_day_view_purchases	numeric	The total number of channel-reported purchases made within one day of viewing the ad.
Opens	opened	numeric	The channel-reported number of times the email/sms was opened by recipients.
Pixel Conversion Value (CV)	order_revenue	numeric	Pixel-reported conversion value (order 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.
Outbound Clicks	outbound_clicks	numeric	Number of channel-reported outbound clicks.
Received Messages	received	numeric	The channel-reported number of email messages successfully received by the target audience.
Refunds	refund_money	numeric	Refund value processed during the selected time period, based on the order date. 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.
SMS Messages Sent	sent	numeric	The channel-reported number of SMS messages sent in the campaign.
Pixel Page Views	session_page_views	numeric	The Pixel-reported number of pages viewed.
Pixel Sessions	sessions	numeric	The Pixel-reported number of online store sessions. Note that Triple Whale defines and tracks sessions differently from the main sales platform so there may be variance between the two. Learn how Triple Whale defines and tracks sessions.
Ad Spend	spend	numeric	Channel-reported ad spend, excluding any custom expenses marked as ad spend.
Subscribed to Mailing List	subscribed_to_list	numeric	The channel-reported number of recipients subscribed to any mailing list.
Pixel Time on Site	time_on_site	numeric	The Pixel-reported time spent by users on the site during a session, measured in seconds.
Pixel Unique Visitors	unique_visitors	numeric	The Pixel-reported number of unique visitors to the site.
Unsubscribed	unsubscribed	numeric	The channel-reported number of recipients who unsubscribed from any mailing list.
Website Purchases	website_purchases	numeric	Pixel-reported purchases (orders) during a website session. Excludes recurring subscription purchases.

Derived

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

Title	ID	Type	Description
Pixel Average Page Views Per Session	average_page_views_per_session	formula	The average number of pages viewed by users during each session. Pixel Average Page Views Per Session = Session Page Views / Number of Sessions
Pixel Average Session Duration	average_session_duration	formula	The average time users spend on your site per session. Pixel Average Session Duration (in seconds) = Time on Site / (Number of Sessions - Number of Bounces)
Pixel Bounce Rate	bounce_rate	formula	The percentage of single-page visits (bounces) out of total sessions. Pixel Bounce Rate = Number of Bounces / Number of Sessions
Channel-Reported AOV	channel_reported_aov	formula	The average order value as reported by the advertising channel. Channel-Reported AOV = Channel-Reported Conversion Value / Number of Channel-Reported Conversions
Channel-Reported Conversion Rate	channel_reported_conversion_rate	formula	The percentage of visits that convert into orders, as reported by the channel. Channel-Reported Conversion Rate = Number of Channel-Reported Conversions / Number of Visits
Channel-Reported Cost Per Acquisition	channel_reported_cpa	formula	The cost to acquire a single conversion, as reported by the channel. Channel-Reported CPA = Ad Spend / Number of Channel-Reported Conversions
Channel-Reported Return on Ad Spend	channel_reported_roas	formula	Revenue generated per dollar spent on ads, as reported by the channel. Channel-Reported ROAS = Channel-Reported Conversion Value / Ad Spend
Cost Per Click (CPC)	cpc	formula	The average cost for each ad click. Cost Per Click (CPC) = Ad Spend / Clicks
Cost Per Mille (CPM)	cpm	formula	The cost for 1,000 ad impressions. Cost Per Mille (CPM) = Ad Spend / Impressions x 1000
Cost Per Visit (CPV)	cpv	formula	The cost for each visit driven by ads. Cost Per Visit (CPV) = Ad Spend / Number of Visits
Click-Through Rate	ctr	formula	The percentage of impressions that result in a click. Click-Through Rate (CTR) = Clicks / Impressions
Pixel Email Signup Rate	email_signup_rate	formula	The percentage of sessions that result in email signups. Pixel Email SUR = Number of Email Signups / Number of Sessions
Pixel New Customer Cost Per Acquisition	ncpa	formula	The cost to acquire a new customer, as reported by Pixel. Pixel NCPA = New Customer Pixel Conversion Value / Ad Spend
Pixel New Customer ROAS	ncroas	formula	Revenue from new customers relative to ad spend. Pixel NC ROAS = New Customer Pixel Conversion Value / Ad Spend
Pixel New Customers Percent	new_customers_percent	formula	The percentage of purchases made by new customers, as reported by Pixel. Pixel New Customers % = Number of New Customer Pixel Purchases / Total Pixel Purchases
Outbound Click-Through Rate	outbound_ctr	formula	The percentage of impressions that result in outbound clicks. Outbound CTR = Outbound Clicks / Impressions
Pixel AOV	pixel_aov	formula	The average order value, as reported by Pixel. Pixel AOV = Pixel Conversion Value / Number of Pixel Purchases
Pixel Conversion Rate	pixel_conversion_rate	formula	The percentage of sessions that result in purchases, as reported by Pixel. Pixel Conversion Rate = Number of Pixel Purchases / Number of Sessions
Pixel Conversion Value Delta	pixel_conversion_value_delta	formula	The difference between Pixel-reported conversion value and channel-reported conversion value. Pixel CV Delta = Pixel Conversion Value - Channel-Reported Conversion Value
Pixel Cost Per Add To Cart	pixel_cost_per_add_to_cart	formula	The cost associated with each add-to-cart action as reported by Pixel. Pixel Cost Per ATC = Ad Spend / Number of Add to Carts
Pixel Cost Per Email Signup	pixel_cost_per_email_signup	formula	The cost associated with each email signup as reported by Pixel. Pixel Cost Per ESU = Ad Spend / Number of Email Signups
Pixel Cost Per New Visitor	pixel_cost_per_new_visitor	formula	The cost to acquire a new visitor as reported by Pixel. Pixel Cost Per New Visitor = Ad Spend / Number of New Visitors
Pixel Cost Per Visitor	pixel_cost_per_visitor	formula	The cost to acquire a unique visitor as reported by Pixel. Pixel Cost Per Visitor = Ad Spend / Number of Unique Visitors
Pixel Cost Per Acquisition	pixel_cpa	formula	The cost to acquire a purchase as reported by Pixel. Pixel CPA = Ad Spend / Number of Pixel Purchases
Pixel New Customer Average Order Value	pixel_nc_aov	formula	The average order value for new customers as reported by Pixel. Pixel NC AOV = New Customer Pixel Conversion Value / Number of New Customer Pixel Purchases
Pixel New Customer Conversion Rate	pixel_nc_conversion_rate	formula	The conversion rate for new customers as reported by Pixel. Pixel NC CR = Number of New Customer Pixel Purchases / Number of New Visitors
Pixel New Visitor Percent	pixel_new_visitor_percent	formula	The percentage of all visitors that are new as reported by Pixel. Pixel New Visitor % = Number of New Visitors / Number of Unique Visitors
Pixel Profit	pixel_profit	formula	The profit calculated as conversion value (order revenue) minus ad spend and cost of goods, as reported by Pixel. Pixel Profit = Pixel Conversion Value - Ad Spend - Pixel COGS
Pixel Return on Ad Spend	pixel_roas	formula	Pixel-reported revenue relative to ad spend. Pixel ROAS = Pixel Conversion Value / Ad Spend
Pixel Returning Customer Purchases	returning_customer_orders	formula	Number of Pixel-reported orders placed by Returning Customers. Pixel RC Orders = Pixel Purchases - Pixel New Customer Purchases
Pixel Returning Customer Revenue	returning_customer_revenue	formula	Pixel-reported conversion value (order revenue) derived from Returning Customers. Pixel RC CV = Pixel CV - Pixel NC CV
Pixel New Customers Percent	returning_customers_percent	formula	The percentage of purchases made by new customers, as reported by Pixel. Pixel New Customers % = Number of Returning Customer Pixel Purchases / Total Pixel Purchases