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.

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_hourdateThe hour of the day on which the event occurred, 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 event occurred. Based on the time zone of the shop at the moment of the event.
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
Order Revenueorder_revenuenumericThe total revenue after adjustments such as discounts, shipping, fees, and taxes (but before refunds).

Order Revenue = Gross Sales - Discounts + Shipping + Tax
Total Salestotal_salesnumericTotal Sales = Order Revenue - Refunds (excluding refunded taxes)
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.
New Customer Ordersnew_customer_ordersnumericThe total number of orders placed by new customers.
New Customer Revenuenew_customer_revenuenumericThe total revenue derived from new customers.
Shipping Priceshipping_pricenumericThe total shipping price of the order (to buyer), including shipping taxes and shipping discounts.
Shipping Costsshipping_costsnumericShipping costs paid by the seller for orders within the select timeframe.

Edit these in: Store > Shipping
TaxestaxesnumericAmount customers paid in taxes (excluding refunded taxes).
Payment Gateway Costspayment_gateway_costsnumericPayment processing fees (not including currency conversion fees).

Edit these in: Store > Gateway Costs
Refunded Ordersrefunded_ordersnumericThe total number of orders refunded.
Refundsrefund_moneynumericRefunds 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 Shopify's analytics dashboard.
DiscountsdiscountsnumericThe total discount amount applied to the order, including shipping discounts.
Blended Ad SpendspendnumericThe reported ad spend of all marketing channels connected to Triple Whale, plus any Custom Expenses marked as Ad Spend.
Gross Salesgross_product_salesnumeric"The total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds.

Gross Sales = Product Price x Units Sold"
Blended Channel-Reported Conversion Valuechannel_reported_conversion_valuenumericChannel-reported conversion value across all marketing channels connected to Triple Whale.
Total Coststotal_costsnumericTotal Costs = COGS - Refunded COGS + Payment Gateway Costs + Shipping Costs + Custom Spend (excl. Custom Ad Spend) + Handling Fees + Taxes

Derived

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

TitleIDTypeDescription
Blended Return on Ad SpendroasformulaBlended ROAS = Order Revenue / Blended Ad Spend
New Customer Return on Ad Spendnc_roasformulaNew Customer ROAS = New Customer Revenue / Blended Ad Spend
Marketing Efficiency RatiomerformulaMER = Blended Ad Spend / Order Revenue
Blended Cost per Acquisitionblended_cpaformulaBlended CPA = Blended Ad Spend / Number of Orders > $0
New Customer Cost Per AcquisitionncpaformulaNew Customer CPA = New Customer Revenue / Blended Ad Spend
Net Profitnet_profitformulaNet Profit = Order Revenue - Refunds - Blended Ad Spend - Total Costs
Gross Profitgross_profitformulaGross Profit = Total Sales - Total Costs
Net Marginnet_marginformula"The percentage of total order revenue that is net profit.

Net Margin = Net Profit / Order Revenue"
Cash Turnovercash_turnoverformulaCash Turnover = Order Revenue - Shipping Costs - Payment Gateway Costs - Taxes - Blended Ad Spend - Refunds
Blended Attributed Return on Ad Spendba_roasformulaBlended-Attributed ROAS = Blended Channel-Reported Conversion Value / Blended Ad Spend
Gross Revenuegross_revenueformulaGross Revenue = Gross Sales + Shipping Price + Taxes - Discounts - Refunds
Net Salesnet_salesformulaNet Sales = Gross Sales - Discounts - Refunds
Profit on Ad SpendpoasformulaPOAS = (Total Sales - COGS + Payment Gateway Costs + Shipping Costs + Handling Fees + Taxes) / Ad Spend