The Blended Stats view 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.

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

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
total_salesnumericThe total revenue of the order minus refunds (excluding refunded tax).Shopify, Amazon
orders_with_amountnumericTotal number of orders with a revenue greater than zero.Shopify, Amazon
new_customer_ordersnumericTotal number of orders placed by new customers.Shopify, Amazon
new_customer_revenuenumericTotal revenue generated from new customers. By default shown in the shop's currency.Shopify, Amazon
shipping_pricenumericThe total shipping price of the order (to buyer), including shipping taxes and shipping discounts. By default shown in the shop's currency.Shopify, Amazon
shipping_costsnumericShipping cost to the seller. By default shown in the shop's currency. The data source varies based on Triple Whale admin settings.Shopify, ShipBob, ShipStation, Triple Whale Admin
taxesnumericThe total taxes of the order. By default shown in the shop's currency.Shopify, Amazon
payment_gateway_costsnumericThe total payment gateway costs of the order. By default shown in the shop's currency. The data source varies depending on the payment method set in Triple Whale Admin.Shopify, Amazon, Triple Whale Admin
refunded_ordersnumericThe total number of orders refunded.Shopify
refund_moneynumericThe total amount of money refunded.Shopify
spendnumericThe total advertising expenditure, including channel ad spend and custom ad spend. By default shown in the shop's currency.Facebook, Google, TikTok, Twitter, Pinterest, Snapchat, Bing, Amazon, Criteo, Mountain, Yotpo Email and SMS, Taboola, Outbrain
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
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
total_costsnumericSum of all costs associated with the orders, including cost of goods sold (minus refunded cost of goods), shipping, payment gateway costs, handling fees, taxes, and custom expenses (minus custom ad spend).Shopify, Amazon, ShipBob, ShipStation, Triple Whale Admin

Derived

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

NameTypeDescription
roasformulaReturn on Ad Spend: Revenue generated relative to ad spend.

Formula: safe_divide(sum(order_revenue), sum(spend)) roas
nc_roasformulaNew Customer Return on Ad Spend:

Revenue from new customers relative to ad spend.

Formula: safe_divide(sum(new_customer_revenue), sum(spend)) as nc_roas
merformulaMarketing Efficiency Ratio: Total revenue relative to spend on marketing.

Formula: safe_divide(sum(spend), sum(order_revenue)) mer
blended_cpaformulaBlended Cost per Acquisition: Average cost to acquire an order across all marketing.

Formula: safe_divide(sum(spend), sum(orders_with_amount)) as blended_cpa
ncpaformulaNew Customer Cost per Acquisition: Cost to acquire a new customer.

Formula: safe_divide(sum(spend), sum(new_customer_orders)) ncpa
net_profitformulaNet Profit: Profit after net cost of goods sold and ad spend.

Formula: sum(total_sales) - sum(spend) - sum(total_costs) as net_profit
gross_profitformulaGross Profit: Profit after net cost of goods sold.

Formula: sum(total_sales) - sum(total_costs) as gross_profit
net_marginformulaNet Margin: Percentage of profit relative to revenue.

Formula: safe_divide(sum(total_sales) - sum(spend) - sum(total_costs), sum(order_revenue)) * 100 net_margin
cash_turnoverformulaCash Turnover: Revenue after subtracting costs and expenses.

Formula: sum(order_revenue) - sum(shipping_costs) - sum(payment_gateway_costs) - sum(taxes) - sum(spend) - sum(refund_money) as cash_turnover
ba_roasformulaBooked Ad Return on Ad Spend: Channel reported revenue relative to ad spend.

Formula: safe_divide(sum(channel_reported_conversion_value), sum(spend)) as ba_roas
gross_revenueformulaGross Revenue: Total sales revenue minus refunds and discounts.

Formula: sum(gross_product_sales) + sum(shipping_price) + sum(taxes) - sum(discounts) - coalesce(sum(refund_money), 0) as gross_revenue