The Pixel Joined view merges crucial advertising and transaction data to provide a comprehensive picture of advertising performance and its direct impact on sales. By querying this view, users can draw insights into how different advertising campaigns, ad sets, and ads drive order revenue, influence customer acquisition, and affect overall return on ad spend (ROAS). This unified perspective aids in optimizing marketing strategies and budget allocation for better ROI.

📘

Note

event_date is a required field for queries on this table.

Dimensions

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

NameTypeDescriptionSource
event_datedateThe date the ad was run or the order was placed. Based on the time zone of the shop at the moment of the event.Shopify, Amazon
event_date.daydateThe day on which the ad was run or the order was placed. Derived from event_date.Shopify, Amazon
event_date.weekdateThe Sunday of the week during which the ad was run or the order was placed. Derived from event_date.Shopify, Amazon
event_date.monthdateThe month during which the ad was run or the order was placed. Derived from event_date.Shopify, Amazon
event_date.quarterdateThe first month of the quarter during which the ad was run or the order was placed. Derived from event_date.Shopify, Amazon
event_date.yeardateThe year during which the ad was run or the order was placed. Derived from event_date.Shopify, Amazon
channelstringThe platform through which the ad was delivered.

Example values: facebook-ads, google-ads, tiktok-ads
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
campaign_idstringThe unique ID for the marketing campaign associated with the ad. Assigned by the publisher platform.

Example values: 23852438666180053, 531011533, 20620285829
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
ad_set_idstringThe unique ad set ID for the ad set associated with the ad click.

Assigned by the publisher platform. Example value: 120210439890740003
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Klaviyo, Taboola, Outbrain
ad_idstringThe unique ad ID. Assigned by the publisher platform.

Example value: 120210439891110007
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Taboola, Outbrain
campaign_namestringThe name of the marketing campaign associated with the ad.

Example values: Catalog Retargeting, US Generic Search, Breakfast of Champions Campaign
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
ad_set_namestringThe name of the ad set within a campaign.

Example values: Broad Audience, Bracelets, Wellness
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Klaviyo
ad_namestringThe name of the specific ad.

Example values: Kids Ad 1, Read Our Latest Blog 03/12/2023, 8 best fall looks
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Taboola, Outbrain
campaign_statusstringThe status of the campaign.

Example values: active, paused
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Klaviyo
ad_set_statusstringThe status of the adset.

Example values: active, paused
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Klaviyo
ad_statusstringThe status of the ad.

Example values: active, paused
Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Outbrain
ad_copystringThe full text (copy) of the ad.Facebook, Google, TikTok
ad_image_urlstringThe URL of the ad image.

Example value: https://www.example.com/ad-image.jpg
Facebook, Google, TikTok
ad_typestringThe format of the ad.

Example values: video, image, copy
Facebook, Google, TikTok
video_urlstringThe URL of the ad video.

Example values: https://www.youtube.com/embed/example
Facebook, Google, TikTok
campaign_daily_budgetstringThe daily budget for the campaign.

Example values: 8000, 10000, 15000
Criteo, Facebook
adset_daily_budgetstringThe daily budget for the ad set.

Example values: 1750, 600, 4000
Criteo, Facebook
campaign_lifetime_budgetstringThe lifetime budget for the campaign.Criteo, Facebook
adset_lifetime_budgetstringThe lifetime budget for the ad set.Criteo, Facebook
campaign_bid_strategystringThe bidding strategy for the campaign.

Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP
Criteo, Facebook
adset_bid_strategystringThe bidding strategy for the ad set.

Example values: LOWEST_COST_WITHOUT_CAP, LOWEST_COST_WITH_BID_CAP, COST_CAP
Criteo, Facebook
ad_bid_amountstringThe bid amount for the ad.

Example values: 15, 28, 30
Facebook
adset_bid_amountstringThe bid amount for the ad set.

Example values: 25, 35, 40
Facebook
modelstringThe attribution model used.

Example values: Triple Attribution, Last Click, First Click, Linear All, Total Impact, Triple Attribution + Views, Linear Paid

Note: 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.
Triple Pixel
attribution_windowstringThe time frame for attributing conversions to the ad.

Example values: 1_day, 7_days, 14_days, 28_days, lifetime

Note: 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.
Triple Pixel

Measures

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

NameTypeDescriptionSource
order_revenuenumericThe total revenue of the products in the order, after adjustments such as discounts, shipping, returns, fees, and taxes.

Gross Product Sales - Discounts - Returns + Shipping + Tax
Shopify, Amazon
gross_product_salesnumericThe total revenue of the products in the order, before adjustments such as discounts, shipping, returns, fees, and taxes.

Product Price x Quantity
Shopify, Amazon
orders_quantitynumericThe quantity of orders placed.

Note: This value is calculated based on the attribution model, and may be a fractional number.
Triple Whale, Shopify, Amazon
new_customer_ordersnumericThe total number of orders placed by new customers.Shopify
new_customer_order_revenuenumericThe total order revenue generated from new customers. By default shown in the shop's currency.Shopify, Amazon
cogsnumericThe total cost of goods sold in the order (to seller). By default shown in the shop's currency.Shopify, Amazon
new_customer_cogsnumericThe cost of goods sold for orders placed by new customers. By default shown in the shop's currency.Shopify, Amazon
spendnumericThe total amount spent on the ad. By default shown in the ad platform's currency.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Yotpo Email and SMS, Taboola, Outbrain
clicksnumericThe total number of clicks on the ad.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
outbound_clicksnumericThe total number of clicks from the ad leading to an external website.Facebook
impressionsnumericThe total number of times the ad was displayed.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Taboola, Outbrain
channel_reported_conversion_valuenumericThe total conversion value for all purchases (includes meta_conversion_value). By default shown in the shop's currency.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
channel_reported_conversionsnumericThe number of purchases attributed to the ad.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Klaviyo, Yotpo Email and SMS, Taboola, Outbrain
meta_facebook_ordersnumericThe total number of purchases made on your Facebook or Instagram shop.Facebook
one_day_view_conversion_valuenumericThe total value of purchases made within one day of viewing the ad.Facebook, Criteo
one_day_view_purchasesnumericThe total number of purchases made within one day of viewing the ad.Facebook, Criteo
channel_reported_visitsnumericVisits to the website or landing page from the ad.Criteo, Mountain, Taboola
openednumericThe total number of times the email was opened by recipients.Klaviyo
receivednumericThe total number of messages successfully received by the target audience.Klaviyo
sentnumericThe total number of SMS messages sent in the campaign.Yotpo Email and SMS, Klaviyo
deliverednumericThe total number of messages that successfully reached the recipients' phones.Yotpo Email and SMS, Klaviyo
subscribed_to_listnumericThe total number of recipients subscribed to any list. Not associated with a campaign (campaign_id will be null).Yotpo Email and SMS, Klaviyo
unsubscribed_from_listnumericThe number of recipients who opted out of the mailing list following the campaign.Yotpo Email and SMS, Klaviyo
subscribed_to_marketingnumericThe number of recipients who opted in for marketing communications as a result of the campaign.Yotpo Email and SMS, Klaviyo
unsubscribed_from_marketingnumericThe number of recipients who opted out of marketing communications following the campaign.Yotpo Email and SMS, Klaviyo
unsubscribednumericThe total number of recipients who unsubscribed from any communication as a result of the campaign.Yotpo Email and SMS, Klaviyo
sessionsnumericTotal number of sessions.Triple Pixel
new_visitorsnumericThe count of first-time site visitors, as detected by Triple Pixel's attribution service.Triple Pixel
unique_visitorsnumericThe count of unique visitors to the site.Triple Pixel
session_page_viewsnumericThe count of page views during a session.Triple Pixel
time_on_sitenumericTotal time spent by users on the site during a session, measured in seconds.Triple Pixel
bouncesnumericThe number of single-page sessions, where the visitor left the site after a single page view.Triple Pixel
add_to_cartsnumericThe number of times items were added to the shopping cart during a session.Triple Pixel
email_signupsnumericThe total number of users who signed up for emails during a session.Triple Pixel

Derived

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

NameTypeDescription
pixel_roasformulaReturn on Ad Spend (ROAS) calculated using pixel data.

Formula: safe_divide(SUM(order_revenue), sum(spend)) pixel_roas
channel_reported_roasformulaROAS as reported by the ad channel.

Formula: safe_divide(SUM(channel_reported_conversion_value), sum(spend)) channel_reported_roas
returning_customer_ordersformulaThe total number of orders placed by returning customers.

Formula: sum(orders_quantity) - sum(new_customer_orders) returning_customer_orders
returning_customer_revenueformulaRevenue generated from returning customers.

Formula: sum(order_revenue) - sum(new_customer_order_revenue) returning_customer_revenue
pixel_aovformulaAverage Order Value (AOV) calculated using pixel data.

Formula: safe_divide(sum(order_revenue), sum(orders_quantity)) pixel_aov
pixel_nc_aovformulaAOV for new customers calculated using pixel data.

Formula: safe_divide(sum(new_customer_order_revenue), sum(new_customer_orders)) pixel_nc_aov
channel_reported_aovformulaAOV as reported by the ad channel.

Formula: safe_divide(sum(channel_reported_conversion_value), sum(channel_reported_conversions)) channel_reported_aov
pixel_profitformulaProfit calculated using pixel data.

Formula: sum(order_revenue) - sum(spend) - sum(cogs) pixel_profit
new_customers_percentformulaPercentage of new customers out of total customers.

Formula: safe_divide(sum(new_customer_orders), sum(orders_quantity)) new_customers_percent
returning_customers_percentformulaPercentage of returning customers out of total customers.

Formula: safe_divide(sum(orders_quantity) - sum(new_customer_orders), sum(orders_quantity)) returning_customers_percent
ncroasformulaNew Customer ROAS calculated using pixel data.

Formula: safe_divide(SUM(new_customer_order_revenue), sum(spend)) ncroas
cpmformulaCost Per Mille (CPM), or cost per thousand impressions.

Formula: safe_divide(SUM(spend), sum(impressions)) * 1000 cpm
ctrformulaClick-Through Rate (CTR) calculated using pixel data.

Formula: safe_divide(SUM(clicks), sum(impressions)) ctr
outbound_ctrformulaClick-Through Rate for outbound clicks.

Formula: safe_divide(SUM(outbound_clicks), sum(impressions)) outbound_ctr
cpcformulaCost Per Click (CPC) calculated using pixel data.

Formula: safe_divide(SUM(spend), sum(clicks)) cpc
pixel_cpaformulaCost Per Acquisition (CPA) for all orders, calculated using pixel data.

Formula: safe_divide(SUM(spend), SUM(orders_quantity)) pixel_cpa
channel_reported_cpaformulaCPA as reported by the ad channel.

Formula: safe_divide(SUM(spend), SUM(channel_reported_conversions)) channel_reported_cpa
ncpaformulaNew Customer CPA calculated using pixel data.

Formula: safe_divide(SUM(spend), SUM(new_customer_orders)) ncpa
bounce_rateformulaBounce rate calculated using pixel data.

Formula: safe_divide(sum(bounces), sum(sessions)) bounce_rate
average_session_durationformulaAverage session duration in seconds, excluding bounces.

Formula: safe_divide(sum(time_on_site), sum(sessions) - sum(bounces)) average_session_duration
average_page_views_per_sessionformulaAverage number of page views per session.

Formula: safe_divide(sum(session_page_views), sum(sessions)) average_page_views_per_session
email_signup_rateformulaRate of email signups per session.

Formula: safe_divide(sum(email_signups), sum(sessions)) email_signup_rate
pixel_conversion_rateformulaThe conversion rate as a ratio of orders to sessions.

Formula: safe_divide(sum(orders_quantity), sum(sessions)) pixel_conversion_rate
pixel_nc_conversion_rateformulaThe conversion rate for new customers.

Formula: safe_divide(sum(new_customer_orders), sum(new_visitors)) pixel_nc_conversion_rate
pixel_conversion_value_deltaformulaThe difference between total order revenue and channel-reported conversion value.

Formula: sum(order_revenue) - sum(channel_reported_conversion_value) pixel_conversion_value_delta
pixel_cost_per_add_to_cartformulaThe cost to get an item added to the cart.

Formula: safe_divide(sum(spend), sum(add_to_carts)) pixel_cost_per_add_to_cart
pixel_cost_per_email_signupformulaThe cost for each email signup.

Formula: safe_divide(sum(spend), sum(email_signups)) pixel_cost_per_email_signup
pixel_cost_per_new_visitorformulaThe cost to acquire a new visitor.

Formula: safe_divide(sum(spend), sum(new_visitors)) pixel_cost_per_new_visitor
pixel_cost_per_visitorformulaThe cost for each visitor.

Formula: safe_divide(sum(spend), sum(unique_visitors)) pixel_cost_per_visitor
pixel_new_visitor_percentformulaThe percentage of new visitors among all visitors.

Formula: safe_divide(sum(new_visitors), sum(unique_visitors)) pixel_new_visitor_percent