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.

TitleIDDescription
Include Amazoninclude_amazonIncludes 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.

TitleIDTypeDescription
Event Hourevent_hourstringThe 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 Dateevent_datedateThe date the order was placed. Based on the time zone of the shop at the moment of purchase.
Event Dayevent_date_daydateThe day on which the ad was run or the order was placed. Derived from event_date.
Event Weekevent_date_weekdateThe Sunday of the week during which the event occurred. Derived from event_date.
Event Monthevent_date_monthdateThe month during which the event occurred. Derived from event_date.
Event Quarterevent_date_quarterdateThe first month of the quarter during which the event occurred. Derived from event_date.
Event Yearevent_date_yeardateThe 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.

TitleIDTypeDescription
Blended Channel-Reported Conversion Valuechannel_reported_conversion_valuenumericChannel-reported conversion value across all marketing channels connected to Triple Whale.
Cost of Goods (COGS)cogsnumericThe cost of goods sold within the selected time frame (before refunds).

Imported from the main sales platform, or edit these in Cost Settings > Cost of Goods.
Adjusted Cost of Goods (Adjusted COGS)cogs_adjustednumericThe 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, or edit these in Cost Settings > Cost of Goods.
DiscountsdiscountsnumericThe total discount amount applied to the order, including shipping discounts.
Gross Salesgross_product_salesnumericThe total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds.

Gross Sales = Product Price x Units Sold
Handling Feeshandling_feesnumericHandling fees (e.g. boxing, packaging, or “pick & pack” services) paid by the seller for orders in the selected time frame.

Edit these in Cost Settings > Cost of Goods.
New Customer Ordersnew_customer_ordersnumericThe total number of orders placed by new customers.
New Customer Revenuenew_customer_revenuenumericThe total revenue derived from orders by new customers.
Order Revenueorder_revenuenumericThe total revenue after adjustments such as discounts, shipping, fees, and taxes (but before refunds).

Order Revenue = Gross Sales - Discounts + Shipping + Tax
Ordersorders_countnumericThe total number of orders placed within the selected timeframe.
Orders > $0orders_with_amountnumericThe total number of orders with a revenue greater than zero.
Payment Gateway Costspayment_gateway_costsnumericPayment processing fees (not including currency conversion fees) paid by the seller.

Edit these in Cost Settings > Gateway Costs.
Refundsrefund_moneynumericRefund value processed during the selected time period, based on the refund 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.
Refunded Ordersrefunded_ordersnumericThe total number of orders refunded in the selected time frame.
SessionssessionsnumericTotal 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 Costsshipping_costsnumericShipping costs paid by the seller for orders within the select timeframe.

Edit these in Cost Settings > Shipping.
Shipping Priceshipping_pricenumericThe total shipping price of the order (paid by the buyer), including shipping taxes and shipping discounts.
Blended Ad SpendspendnumericThe reported ad spend of all marketing channels connected to Triple Whale, plus any Custom Expenses marked as Ad Spend.
TaxestaxesnumericAmount customers paid in taxes (excluding refunded taxes).
Total Coststotal_costsnumericThe 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 Salestotal_salesnumericThe 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.

TitleIDTypeDescription
Amazon Total Advertising Cost of Sale (Amazon TACoS)amazon_tacosformulaPercentage of Amazon sales revenue spent on ads.

Amazon TACoS = Amazon Ad Spend / Amazon Order Revenue
Blended Attributed Return on Ad Spend (BA ROAS)ba_roasformulaRevenue 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_cpaformulaAverage ad cost per non-zero order across all channels.

Blended CPA = Blended Ad Spend / Number of Orders > $0
Cash Turnovercash_turnoverformulaCash generated after deducting key expenses from order revenue.

Cash Turnover = Order Revenue - Shipping Costs - Payment Gateway Costs - Taxes - Blended Ad Spend - Refunds
Cost per Sessioncost_per_sessionformulaAverage ad cost per customer session.

Cost Per Session = Blended Ad Spend / Number of Sessions
Gross Profitgross_profitformulaProfit after deducting all costs to the seller.

Gross Profit = Total Sales - Total Costs
Gross Revenuegross_revenueformulaTotal revenue before refunds and discounts.

Gross Revenue = Gross Sales + Shipping Price + Taxes - Discounts - Refunds
Klaviyo Sales Percentageklaviyo_sales_percentageformulaShare of total revenue attributed to Klaviyo.

Klaviyo Sales Percentage = Klaviyo CV / Total Order Revenue
Marketing Efficiency Ratio (MER)merformulaRatio of ad spend to total revenue.

MER = Blended Ad Spend / Order Revenue
New Customer Return on Ad Spend (NC ROAS)nc_roasformulaRevenue from new customers relative to ad spend.

New Customer ROAS = New Customer Revenue / Blended Ad Spend
New Customer Cost Per Acquisition (NCPA)ncpaformulaAverage ad cost to acquire a new customer.

NCPA = Blended Ad Spend / Number of New Customer Orders
Net Marginnet_marginformulaPercentage of revenue remaining as profit.

Net Margin = Net Profit / Order Revenue
Net Profitnet_profitformulaRemaining revenue after deducting costs.

Net Profit = Order Revenue - Refunds - Blended Ad Spend - Total Costs
Net Salesnet_salesformulaTotal sales after refunds and discounts.

Net Sales = Gross Sales - Discounts - Refunds
Profit on Ad Spend (POAS)poasformulaProfit 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)roasformulaRevenue generated per dollar spent on ads.

Blended ROAS = Order Revenue / Blended Ad Spend