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
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.
Notes
event_dateis 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 |
|---|---|---|
| Subscription Filter | subscription_filter | Filters data based on subscription type. Possible values: none, subscription_first_order, subscription_recurring_order, subscription_order, non-subscription_order, non_subscription_recurring_order |
| Country Filter | country_filter | Filters data by country. |
| Use Click Date | use_click_date | Uses date the click occurred, rather than the date the purchase event occurred, for the value of event_date.Possible values: True, False |
Dimensions
Dimensions are immutable properties that can be used for grouping data.
| ID | Type | Description |
|---|---|---|
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 of the event. |
event_date.day | date | The day on which the ad was run or the order was placed. Derived from event_date. |
event_date.week | date | The Sunday of the week during which the ad was run or the order was placed. Derived from event_date. |
event_date.month | date | The month during which the ad was run or the order was placed. Derived from event_date. |
event_date.quarter | date | The first month of the quarter during which the ad was run or the order was placed. Derived from event_date. |
event_date.year | date | The year during which the ad was run or the order was placed. Derived from event_date. |
channel | string | The platform through which the ad was delivered. Example values: facebook-ads, google-ads, tiktok-ads |
campaign_id | string | The unique ID for the marketing campaign associated with the ad. Assigned by the publisher platform. Example values: 23852438666180053, 531011533, 20620285829 |
ad_set_id | string | The unique ad set ID for the ad set associated with the ad click. 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. Example values: Catalog Retargeting, US Generic Search, Breakfast of Champions Campaign |
ad_set_name | string | The name of the ad set within a campaign. Example values: Broad Audience, Bracelets, Wellness |
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 |
campaign_status | string | The status of the campaign. Example values: active, paused |
ad_set_status | string | The status of the adset. Example values: active, paused |
ad_status | string | The status of the ad. Example values: active, paused |
ad_copy | string | The full text (copy) of the ad. |
ad_image_url | string | The URL of the ad image. Example value: https://www.example.com/ad-image.jpg |
ad_type | string | The format of the ad. Example values: video, image, copy |
video_url | string | The URL of the ad video. Example values: https://www.youtube.com/embed/example |
campaign_daily_budget | string | The daily budget for the campaign. Example values: 8000, 10000, 15000 |
adset_daily_budget | string | The daily budget for the ad set. Example values: 1750, 600, 4000 |
campaign_lifetime_budget | string | The lifetime budget for the campaign. |
adset_lifetime_budget | string | The lifetime budget for the ad set. |
campaign_bid_strategy | string | The bidding strategy for the campaign. Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP |
adset_bid_strategy | string | The bidding strategy for the ad set. Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP |
ad_bid_amount | string | The bid amount for the ad. Example values: 15, 28, 30 |
adset_bid_amount | string | The bid amount for the ad set. Example values: 25, 35, 40 |
model | string | The attribution model used. Example values: Triple Attribution, Last Click, First Click, Linear All, Total Impact, Triple Attribution + Views, Linear PaidNote: group by model will not work on certain complex queries, since the Pixel Joined view is an emulator and does not run standard SQL behind the scenes. |
attribution_window | string | The time frame for attributing conversions to the ad. Example values: 1_day, 7_days, 14_days, 28_days, lifetimeNote: group by attribution_window will not work on certain complex queries, since the Pixel Joined view is an emulator and does not run standard SQL behind the scenes. |
Measures
Measures are numeric fields that can be aggregated and/or combined to calculate new metrics.
| Title | ID | Type | Description |
|---|---|---|---|
| Pixel Conversion Value (CV) | order_revenue | numeric | Pixel-reported conversion value (order revenue), after adjustments such as discounts, shipping, refunds, fees, and taxes. |
| Pixel Gross Sales | gross_product_sales | numeric | Pixel-reported gross sales, before adjustments such as discounts, shipping, refunds, fees, and taxes. |
| 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. |
| Pixel New Customer Purchases | new_customer_orders | numeric | Number of Pixel-reported purchases placed by New Customers. |
| Pixel New Customer Revenue | new_customer_order_revenue | numeric | Amount of Pixel-reported revenue derived from New Customers. |
| Pixel Cost of Goods | cogs | numeric | The cost of goods for Pixel-reported purchases within the selected time frame, minus the cost of goods of refunded items in this time frame. |
| 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, minus the cost of goods of refunded items in this time frame. |
| Channel-Reported Ad Spend | spend | numeric | Channel-Reported Ad Spend (without Custom Expenses) |
| Clicks | clicks | numeric | Number of clicks |
| Outbound Clicks | outbound_clicks | numeric | Number of outbound clicks |
| Impressions | impressions | numeric | Number of impressions |
| Channel-Reported Conversion Value | channel_reported_conversion_value | numeric | Channel-reported revenue generated from conversions |
| Channel-Reported Purchases | channel_reported_conversions | numeric | Number of channel-reported conversions |
| Channel-Reported Onsite Conversion Value | channel_reported_onsite_conversion_value | numeric | Channel-reported revenue from 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) |
| One-Day View Conversion Value | one_day_view_conversion_value | numeric | The total number of channel-reported purchases made within one day of viewing the ad. |
| One-Day View Purchases | one_day_view_purchases | numeric | The total value of channel-reported purchases made within one day of viewing the ad. |
| Channel-Reported Visits | channel_reported_visits | numeric | Number of channel-reported visits |
| Opened | opened | numeric | The channel-reported number of times the email/sms was opened by recipients. |
| 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. |
| SMS Messages Delivered | delivered | numeric | The channel-reported number of messages that successfully reached the recipients' phones. |
| Subscribed to Mailing List | subscribed_to_list | numeric | The channel-reported number of recipients subscribed to any communication. |
| Unsubscribed | unsubscribed | numeric | The channel-reported number of recipients who unsubscribed from any communication. |
| Pixel Sessions | sessions | numeric | The Pixel-reported number of online store sessions. |
| Pixel New Visitors | new_visitors | numeric | The Pixel-reported number of first-time visitors to the site. |
| Pixel Unique Visitors | unique_visitors | numeric | The Pixel-reported number of unique visitors to the site. |
| Pixel Page Views | session_page_views | numeric | The Pixel-reported number of pages viewed. |
| 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 Bounces | bounces | numeric | The Pixel-reported number of single-page sessions where the visitor left the site after a single page view. |
| Pixel Add to Carts | add_to_carts | numeric | The Pixel-reported number of Add To Carts from unique customers. |
| Pixel Email Signups | email_signups | numeric | TThe Pixel-reported total number of users who signed up for the mailing list during a session. |
| Website Purchases | website_purchases | numeric | Pixel-reported purchases 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 Return on Ad Spend | pixel_roas | formula | Pixel ROAS = Pixel Conversion Value / Ad Spend |
| Channel-Reported Return on Ad Spend | channel_reported_roas | formula | Channel-Reported ROAS = Channel-Reported Conversion Value / Ad Spend |
| Pixel Returning Customer Purchases | returning_customer_orders | formula | Number of Pixel-reported orders placed by Returning Customers. |
| Pixel Returning Customer Revenue | returning_customer_revenue | formula | Amount of Pixel-reported revenue derived from Returning Customers. |
| Pixel AOV | pixel_aov | formula | Pixel AOV = Pixel Conversion Value / Number of Pixel Purchases |
| Pixel New Customer AOV | pixel_nc_aov | formula | Pixel NC AOV = New Customer Pixel Conversion Value / Number of New Customer Pixel Purchases |
| Channel-Reported AOV | channel_reported_aov | formula | Channel-Reported AOV = Channel-Reported Conversion Value / Number of Channel-Reported Conversions |
| Pixel Profit | pixel_profit | formula | Pixel Profit = Pixel Conversion Value - Ad Spend - Pixel COGS |
| 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 ROAS | ncroas | formula | Pixel NC ROAS = New Customer Pixel Conversion Value / Ad Spend |
| Cost Per Mille | cpm | formula | Cost Per 1000 Impressions. Cost Per Mille (CPM) = Ad Spend / Impressions x 1000 |
| Click-Through Rate | ctr | formula | Click-Through Rate (CTR) = Clicks / Impressions |
| Outbound Click-Through Rate | outbound_ctr | formula | Outbound CTR = Outbound Clicks / Impressions |
| Cost Per Click | cpc | formula | Cost for each click on an ad. |
| Pixel Cost Per Acquisition | pixel_cpa | formula | Pixel CPA = Ad Spend / Number of Pixel Purchases |
| Channel-Reported Cost Per Acquisition | channel_reported_cpa | formula | Channel-Reported CPA = Ad Spend / Number of Channel-Reported Conversions |
| Pixel New Customer Cost Per Acquisition | ncpa | formula | Pixel NCPA = New Customer Pixel Conversion Value / Ad Spend |
| Pixel Bounce Rate | bounce_rate | formula | Pixel Bounce Rate = Number of Bounces / Number of Sessions |
| Pixel Average Session Duration | average_session_duration | formula | Pixel Average Session Duration (in seconds) = Time on Site / (Number of Sessions - Number of Bounces) |
| Pixel Average Page Views Per Session | average_page_views_per_session | formula | Pixel Average Page Views Per Session = Session Page Views / Number of Sessions |
| Pixel Email Signup Rate | email_signup_rate | formula | Pixel Email SUR = Number of Email Signups / Number of Sessions |
| Pixel Conversion Rate | pixel_conversion_rate | formula | Pixel Conversion Rate = Number of Pixel Purchases / Number of Sessions |
| Pixel New Customer Conversion Rate | pixel_nc_conversion_rate | formula | Pixel NC CR = Number of New Customer Pixel Purchases / Number of New Visitors |
| Pixel Conversion Value Delta | pixel_conversion_value_delta | formula | Pixel CV Delta = Pixel Conversion Value - Channel-Reported Conversion Value |
| Pixel Cost Per Add To Cart | pixel_cost_per_add_to_cart | formula | Pixel Cost Per ATC = Ad Spend / Number of Add to Carts |
| Pixel Cost Per Email Signup | pixel_cost_per_email_signup | formula | Pixel Cost Per ESU = Ad Spend / Number of Email Signups |
| Pixel Cost Per New Visitor | pixel_cost_per_new_visitor | formula | Pixel Cost Per New Visitor = Ad Spend / Number of New Visitors |
| Pixel Cost Per Visitor | pixel_cost_per_visitor | formula | Pixel Cost Per Visitor = Ad Spend / Number of Unique Visitors |
| Pixel New Visitor % | pixel_new_visitor_percent | formula | Pixel New Visitor % = Number of New Visitors / Number of Unique Visitors |