Pixel Joined Table

pixel_joined_tvf

The Pixel Joined table combines essential advertising and transaction data, giving users insights into how campaigns, adsets, 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.

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. Values: 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. 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. 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 specific 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
Adset Bid Amount	adset_bid_amount	string	The bid amount for the adset. Example values: 25, 35, 40
Adset Bid Strategy	adset_bid_strategy	string	The bidding strategy for the adset. Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP, LOWEST_COST_WITH_MIN_ROAS
Adset Daily Budget	adset_daily_budget	string	The daily budget set for the adset within a campaign. By default shown in the shop's currency. Example values: 1750, 6000, 40000
Adset ID	adset_id	string	The unique ID for the adset associated with the ad click. Assigned by the publisher platform. Example value: 120210439890740003
Adset Lifetime Budget	adset_lifetime_budget	string	The total budget set for the adset within a campaign over its lifetime. By default shown in the shop's currency. Example values: 5000, 10000, 20000
Adset Name	adset_name	string	The name of the adset within a campaign. Example values: Broad Audience, Bracelets, Wellness
Adset Status	adset_status	string	The status of the adset. 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 values: 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 (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 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 (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 data in the Orders table.
Pixel New Customer Revenue	new_customer_order_revenue	numeric	Amount of Pixel-reported 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.
Opened	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	received	numeric	The channel-reported number of email/sms messages successfully received by the target audience.
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 (MSP) 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. 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 through Pixel-reported ads. 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 %	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