Blended Stats Table

blended_stats_tvf

The Blended Stats table function offers a comprehensive snapshot of your business's performance, integrating data from orders, refunds, ads, and custom expenses. Querying this view enables you to assess overall profitability, marketing efficiency, and customer acquisition costs, providing a holistic understanding of your financial health and marketing effectiveness.

📘

Note

event_date is a required field for queries on this table, and refers to the date of the order.

Parameters

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

Title	ID	Description
Include Amazon	include_amazon	Includes Amazon sales data in the results, in addition to the main sales platform (MSP). 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 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 order was placed. Based on the time zone of the shop at the moment of purchase.
Event Day	event_date_day	date	The day on which the ad was run or the order was placed. 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.

Measures

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

Title	ID	Type	Description
Blended Channel-Reported Conversion Value	channel_reported_conversion_value	numeric	Channel-reported conversion value across all marketing channels connected to Triple Whale.
Cost of Goods (COGS)	cogs	numeric	The cost of goods sold within the selected time frame (before refunds). Imported from the main sales platform (MSP), or edit these in: Store > Cost of Goods.
Adjusted Cost of Goods (Adjusted COGS)	cogs_adjusted	numeric	The cost of goods sold within the selected time frame, minus the cost of goods of refunded items in the selected time frame. Imported from the main sales platform (MSP), or edit these in: Store > Cost of Goods.
Discounts	discounts	numeric	The total discount amount applied to the order, including shipping discounts.
Gross Sales	gross_product_sales	numeric	The total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds. Gross Sales = Product Price x Units Sold
Handling Fees	handling_fees	numeric	Handling fees (e.g. boxing, packaging, or “pick & pack” services) paid by the seller for orders in the selected time frame. Edit these in: Store > Cost of Goods
New Customer Orders	new_customer_orders	numeric	The total number of orders placed by new customers.
New Customer Revenue	new_customer_revenue	numeric	The total revenue derived from orders by new customers.
Order Revenue	order_revenue	numeric	The total revenue after adjustments such as discounts, shipping, fees, and taxes (but before refunds). Order Revenue = Gross Sales - Discounts + Shipping + Tax
Orders	orders_count	numeric	The total number of orders placed within the selected timeframe.
Orders > $0	orders_with_amount	numeric	The total number of orders with a revenue greater than zero.
Payment Gateway Costs	payment_gateway_costs	numeric	Payment processing fees (not including currency conversion fees) paid by the seller. Edit these in: Store > Gateway Costs
Refunds	refund_money	numeric	Refunds processed during the selected time period. Note that Triple Whale does not track refunds processed by 3rd-party return-management apps (e.g. Loop), so there may be variance relative to the main sales platform (MSP)'s analytics dashboard.
Refunded Orders	refunded_orders	numeric	The total number of orders refunded in the selected time frame.
Sessions	sessions	numeric	Total 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.
Shipping Costs	shipping_costs	numeric	Shipping costs paid by the seller for orders within the select timeframe. Edit these in: Store > Shipping
Shipping Price	shipping_price	numeric	The total shipping price of the order (paid by the buyer), including shipping taxes and shipping discounts.
Blended Ad Spend	spend	numeric	The reported ad spend of all marketing channels connected to Triple Whale, plus any Custom Expenses marked as Ad Spend.
Taxes	taxes	numeric	Amount customers paid in taxes (excluding refunded taxes).
Total Costs	total_costs	numeric	The total cost to the seller, including COGS, fees, shipping, custom spend, handling, and taxes, adjusted for refunds. Total Costs = COGS - Refunded COGS + Payment Gateway Costs + Shipping Costs + Custom Spend (excl. Custom Ad Spend) + Handling Fees + Taxes
Total Sales	total_sales	numeric	The total sales after deducting refunds, excluding refunded taxes. Total Sales = Order Revenue - Refunds (excluding refunded taxes)

Derived

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

Title	ID	Type	Description
Amazon Total Advertising Cost of Sale (Amazon TACoS)	amazon_tacos	formula	Percentage of Amazon sales revenue spent on ads. Amazon TACoS = Amazon Ad Spend / Amazon Order Revenue
Blended Attributed Return on Ad Spend (BA ROAS)	ba_roas	formula	Revenue attributed to ads relative to ad spend across all channels. Blended-Attributed ROAS = Blended Channel-Reported Conversion Value / Blended Ad Spend
Blended Cost per Acquisition (Blended CPA)	blended_cpa	formula	Average ad cost per non-zero order across all channels. Blended CPA = Blended Ad Spend / Number of Orders > $0
Cash Turnover	cash_turnover	formula	Cash generated after deducting key expenses from order revenue. Cash Turnover = Order Revenue - Shipping Costs - Payment Gateway Costs - Taxes - Blended Ad Spend - Refunds
Cost per Session	cost_per_session	formula	Average ad cost per customer session. Cost Per Session = Blended Ad Spend / Number of Sessions
Gross Profit	gross_profit	formula	Profit after deducting all costs to the seller. Gross Profit = Total Sales - Total Costs
Gross Revenue	gross_revenue	formula	Total revenue before refunds and discounts. Gross Revenue = Gross Sales + Shipping Price + Taxes - Discounts - Refunds
Klaviyo Sales Percentage	klaviyo_sales_percentage	formula	Share of total revenue attributed to Klaviyo. Klaviyo Sales Percentage = Klaviyo CV / Total Order Revenue
Marketing Efficiency Ratio (MER)	mer	formula	Ratio of ad spend to total revenue. MER = Blended Ad Spend / Order Revenue
New Customer Return on Ad Spend (NC ROAS)	nc_roas	formula	Revenue from new customers relative to ad spend. New Customer ROAS = New Customer Revenue / Blended Ad Spend
New Customer Cost Per Acquisition (NCPA)	ncpa	formula	Average ad cost to acquire a new customer. NCPA = Blended Ad Spend / Number of New Customer Orders
Net Margin	net_margin	formula	Percentage of revenue remaining as profit. Net Margin = Net Profit / Order Revenue
Net Profit	net_profit	formula	Remaining revenue after deducting costs. Net Profit = Order Revenue - Refunds - Blended Ad Spend - Total Costs
Net Sales	net_sales	formula	Total sales after refunds and discounts. Net Sales = Gross Sales - Discounts - Refunds
Profit on Ad Spend (POAS)	poas	formula	Profit generated per dollar of ad spend. POAS = (Total Sales - Adjusted COGS + Payment Gateway Costs + Shipping Costs + Handling Fees + Taxes) / Ad Spend
Blended Return on Ad Spend (Blended ROAS)	roas	formula	Revenue generated per dollar spent on ads. Blended ROAS = Order Revenue / Blended Ad Spend