Pixel Keywords Joined Table

pixel_keywords_joined_tvf

The Pixel Keywords Joined table connects paid search keyword data with revenue and customer behavior insights, showing how specific keywords contribute to purchases, acquisition, and ROAS. This unified view enables deeper analysis of keyword performance across attribution models and supports smarter bidding and search strategy decisions.

🚧
Key Concepts When Using Pixel Keywords Joined Table
Aggregation and Attribution

The Keywords table is the core of the Pixel Keywords 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 keyword level, with Triple Attribution model and Lifetime attribution window applied by default.

Metrics

Channel-Reported data (e.g., channel_reported_conversion_value) comes directly from ad platforms, reflecting channel-specific data.

Pixel-Reported data (e.g., 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.

Simulated Query Behavior

group by attribution_window and group by model will not work on certain complex queries, because the Pixel Keywords 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.

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 Account ID	`account_id`	string	The unique ID for the ad account associated with the ad. Assigned by the ad platform. Example values: `act_2168791943307744`, `18ce55770zl`, `1986494684391480`
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 ID	`adset_id`	string	The unique ID for the ad set associated with the ad click. Assigned by the ad platform. Example value: `120210439890740003`
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 ID	`campaign_id`	string	The unique ID for the marketing campaign associated with the ad. Assigned by the ad platform. Example values: `23852438666180053`, `531011533`, `20620285829`
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`, `Direct`
Keyword ID	`keyword_id`	string	The unique ID assigned to the keyword by the ad platform.
Keyword Match Type	`keyword_match_type`	string	How closely a user’s search query must match the keyword. Possible values: `EXACT`, `PHRASE`, `BROAD`
Keyword Status	`keyword_status`	string	The status of the keyword. Possible values: `ENABLED`, `PAUSED`, `REMOVED`
Keyword System Serving Status	`keyword_system_serving_status`	string	Indicates whether the keyword is currently eligible to serve. Possible values: `ELIGIBLE`, `RARELY_SERVED`
Keyword Text	`keyword_text`	string	The text of the keyword as entered in the ad platform. Example values: `running shoes`, `cheap flights`, `best coffee`
Attribution 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`

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.
Address Details Submitted	`addresses`	numeric	The number of times users submitted address details during checkout.
Ad Set Daily Budget	`adset_daily_budget`	numeric	The daily budget (in cents) set for the ad set within a campaign. By default shown in the shop's currency. Example values: `1750`, `6000`, `40000`
Ad Set Lifetime Budget	`adset_lifetime_budget`	numeric	The total budget (in cents) set for the ad set within a campaign over its lifetime. By default shown in the shop's currency. Example values: `5000`, `10000`, `20000`
Pixel Bounces	`bounces`	numeric	The Pixel-reported number of single-page sessions where the visitor left the site after a single page view.
Campaign Daily Budget	`campaign_daily_budget`	numeric	The daily budget (in cents) for the campaign. Example values: `8000`, `10000`, `15000`
Campaign Lifetime Budget	`campaign_lifetime_budget`	numeric	The lifetime budget (in cents) for the campaign. Example values: `5000`, `10000`, `20000`
Channel-Reported Conversion Value	`channel_reported_conversion_value`	numeric	Revenue generated from channel-reported conversions (purchases).
Checkouts Started	`checkouts`	numeric	The number of checkout processes started.
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.
Add Contact Info	`contacts`	numeric	The number of times users submitted contact details during checkout.
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.
Keyword CPC Bid	`keyword_cpc_bid`	numeric	The bid amount set for the keyword (cost-per-click). By default shown in the shop's currency. Example values: `1500`, `10,000`, `28,000`
Keyword Effective CPC Bid	`keyword_effective_cpc_bid`	numeric	The effective CPC bid after platform bid adjustments are applied. By default shown in the shop's currency. Example values: `1500`, `10,000`, `28,000`
Keyword Quality Score	`keyword_quality_score`	numeric	The quality score assigned to the keyword by the ad platform (0 – 10, higher is better).
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 Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. Also 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. 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 Purchases	`new_customer_orders`	numeric	Number of Pixel-reported orders placed 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 Visitors	`new_visitors`	numeric	The Pixel-reported number of first-time visitors to the site.
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.
Add Payment Info	`payments`	numeric	The number of times users submitted payment details during checkout.
Refunds	`refund_money`	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.
Pixel Session Page Views	`session_page_views`	numeric	The Pixel-reported number of pages viewed during a website session.
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.
Shipping Details Submitted	`shipping`	numeric	The number of times users submitted shipping details during checkout.
Ad Spend	`spend`	numeric	Channel-reported ad spend, excluding any custom expenses marked as ad spend.
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.
Website Purchases	`website_purchases`	numeric	The total number of 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 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
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 Incremental Return on Ad Spend	`iroas`	formula	The additional revenue generated per dollar spent on advertising by comparing conversions between an exposed group (audience that sees the ad) and a holdout group (control group that does not see the ad). Isolates the incremental ROAS of an ad campaign on driving purchases.
Pixel New Customer Cost Per Acquisition	`ncpa`	formula	The cost to acquire a new customer, as reported by Pixel. 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 NCPA = Blended Ad Spend / Number of New Customer Orders
Pixel New Customer ROAS	`ncroas`	formula	Revenue from new customers relative to ad spend. 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 NC ROAS = New Customer Pixel Conversion Value / Ad Spend
Pixel New Customers Percent	`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
Pixel AOV	`pixel_aov`	formula	The Pixel-reported average order value. Pixel AOV = Pixel Conversion Value / Number of Pixel Purchases
Pixel Conversion Rate	`pixel_conversion_rate`	formula	The percentage of website sessions that result in purchases, as reported by Pixel. Conversion Rate = Number of Website 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. 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 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. 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 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. 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	`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	`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

Updated 24 days ago