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_dateis a required field for queries on this table.
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. Possible values: True, False |
Dimensions
Dimensions are immutable properties that can be used for grouping data.
| ID | Type | Description |
|---|---|---|
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 of the event. |
event_date.day | date | The day on which the ad was run or the order was placed. Derived from event_date. |
event_date.week | date | The Sunday of the week during which the ad was run or the order was placed. Derived from event_date. |
event_date.month | date | The month during which the ad was run or the order was placed. Derived from event_date. |
event_date.quarter | date | The first month of the quarter during which the ad was run or the order was placed. Derived from event_date. |
event_date.year | date | The year during which the ad was run or the order was placed. Derived from event_date. |
Measures
Measures are numeric fields that can be aggregated and/or combined to calculate new metrics.
| Title | ID | Type | Description |
|---|---|---|---|
| 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 |
| Total Sales | total_sales | numeric | Total Sales = Order Revenue - Refunds (excluding refunded taxes) |
| 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. |
| 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 new customers. |
| Shipping Price | shipping_price | numeric | The total shipping price of the order (to buyer), including shipping taxes and shipping discounts. |
| Shipping Costs | shipping_costs | numeric | Shipping costs paid by the seller for orders within the select timeframe. Edit these in: Store > Shipping |
| Taxes | taxes | numeric | Amount customers paid in taxes (excluding refunded taxes). |
| Payment Gateway Costs | payment_gateway_costs | numeric | Payment processing fees. Edit these in: Store > Gateway Costs |
| Refunded Orders | refunded_orders | numeric | The total number of orders refunded. |
| 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 Shopify's analytics dashboard. |
| Discounts | discounts | numeric | The total discount amount applied to the order, including 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. |
| 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" |
| Blended Channel-Reported Conversion Value | channel_reported_conversion_value | numeric | Channel-reported conversion value across all marketing channels connected to Triple Whale. |
| Total Costs | total_costs | numeric | Total 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.
| Title | ID | Type | Description |
|---|---|---|---|
| Blended Return on Ad Spend | roas | formula | Blended ROAS = Order Revenue / Blended Ad Spend |
| New Customer Return on Ad Spend | nc_roas | formula | New Customer ROAS = New Customer Revenue / Blended Ad Spend |
| Marketing Efficiency Ratio | mer | formula | MER = Blended Ad Spend / Order Revenue |
| Blended Cost per Acquisition | blended_cpa | formula | Blended CPA = Blended Ad Spend / Number of Orders > $0 |
| New Customer Cost Per Acquisition | ncpa | formula | New Customer CPA = New Customer Revenue / Blended Ad Spend |
| Net Profit | net_profit | formula | Net Profit = Order Revenue - Refunds - Blended Ad Spend - Total Costs |
| Gross Profit | gross_profit | formula | Gross Profit = Total Sales - Total Costs |
| Net Margin | net_margin | formula | "The percentage of total order revenue that is net profit. Net Margin = Net Profit / Order Revenue" |
| Cash Turnover | cash_turnover | formula | Cash Turnover = Order Revenue - Shipping Costs - Payment Gateway Costs - Taxes - Blended Ad Spend - Refunds |
| Blended Attributed Return on Ad Spend | ba_roas | formula | Blended-Attributed ROAS = Blended Channel-Reported Conversion Value / Blended Ad Spend |
| Gross Revenue | gross_revenue | formula | Gross Revenue = Gross Sales + Shipping Price + Taxes - Discounts - Refunds |
| Net Sales | net_sales | formula | Net Sales = Gross Sales - Discounts - Refunds |
| Profit on Ad Spend | poas | formula | POAS = (Total Sales - COGS + Payment Gateway Costs + Shipping Costs + Handling Fees + Taxes) / Ad Spend |