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_dateis 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, and are applied before aggregation.
Title | ID | Description |
|---|---|---|
Include Amazon |
| Includes Amazon sales data in the results, in addition to the main sales platform (MSP). Possible values: |
Dimensions
Dimensions are immutable properties that can be used for grouping data.
Title | ID | Type | Description |
|---|---|---|---|
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: |
Event Date |
| date | The date the order was placed. Based on the time zone of the shop at the moment of purchase. |
Event Day |
| date | The day on which the ad was run or the order was placed. Derived from |
Event Week |
| date | The Sunday of the week during which the event occurred. Derived from |
Event Month |
| date | The month during which the event occurred. Derived from |
Event Quarter |
| date | The first month of the quarter during which the event occurred. Derived from |
Event Year |
| date | The year during which the event occurred. Derived from |
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 |
| numeric | Channel-reported conversion value across all marketing channels connected to Triple Whale. |
Cost of Goods (COGS) |
| numeric | The 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_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, or edit these in Cost Settings > Cost of Goods. |
Discounts |
| numeric | The total discount amount applied to the order, including shipping discounts. |
Gross Sales |
| numeric | The total revenue, before adjustments such as discounts, shipping, fees, taxes, and refunds. Gross Sales = Product Price x Units Sold |
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 Cost Settings > Cost of Goods. |
New Customer Orders |
| numeric | The total number of orders placed by new customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. |
New Customer Revenue |
| numeric | The total revenue derived from orders by new customers. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. |
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 |
| numeric | The total number of orders placed within the selected timeframe. |
Order-Level Custom Expenses |
| numeric | Additional custom expenses associated with individual orders. Import these using the Enrich Orders Data API endpoint. |
Orders > $0 |
| numeric | The total number of orders with a revenue greater than zero. |
Payment Gateway Costs |
| numeric | Payment processing fees (not including currency conversion fees) paid by the seller. Edit these in Cost Settings > Gateway Costs. |
Refunds |
| numeric | Refund 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 Orders |
| numeric | The total number of orders refunded in the selected time frame. |
Sessions |
| numeric | Total 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 Costs |
| numeric | Shipping costs paid by the seller for orders within the select timeframe. Edit these in Cost Settings > Shipping. |
Shipping Price |
| numeric | The total shipping price of the order (paid by the buyer), including shipping taxes and shipping discounts. |
Blended Ad Spend |
| numeric | The reported ad spend of all marketing channels connected to Triple Whale, plus any Custom Expenses marked as Ad Spend. |
Taxes |
| numeric | Amount customers paid in taxes (excluding refunded taxes). |
Total Costs |
| numeric | The total cost to the seller, including COGS, fees, shipping, custom spend, handling, taxes, and order-level custom expenses, adjusted for refunds. Total Costs = COGS - Refunded COGS + Payment Gateway Costs + Shipping Costs + Custom Spend (excl. Custom Ad Spend) + Handling Fees + Taxes + Order-Level Custom Expenses |
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) |
| 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) |
| 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) |
| formula | Average ad cost per non-zero order across all channels. Blended CPA = Blended Ad Spend / Number of Orders > $0 |
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 - Order-Level Custom Expenses |
Cost per Session |
| formula | Average ad cost per customer session. Cost Per Session = Blended Ad Spend / Number of Sessions |
Gross Profit |
| formula | Profit after deducting all costs to the seller. Gross Profit = Total Sales - Total Costs |
Gross Revenue |
| formula | Total revenue including shipping and taxes, after subtracting discounts and refunds. Gross Revenue = Gross Sales + Shipping Price + Taxes - Discounts - Refunds |
Klaviyo Sales Percentage |
| formula | Share of total revenue attributed to Klaviyo. Klaviyo Sales Percentage = Klaviyo CV / Total Order Revenue |
Marketing Efficiency Ratio (MER) |
| formula | Ratio of ad spend to total revenue. MER = Blended Ad Spend / Order Revenue |
New Customer Return on Ad Spend (NC ROAS) |
| formula | Revenue from new customers relative to ad spend. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. New Customer ROAS = New Customer Revenue / Blended Ad Spend |
New Customer Cost Per Acquisition (NCPA) |
| formula | Average ad cost to acquire a new customer. Note that Triple Whale classifies all customers as either new or returning, while the main sales platform may leave some unclassified, potentially leading to discrepancies. NCPA = Blended Ad Spend / Number of New Customer Orders |
Net Margin |
| formula | Percentage of revenue remaining as profit. Net Margin = Net Profit / Order Revenue |
Net Profit |
| formula | Remaining revenue after deducting costs. Net Profit = Order Revenue - Refunds - Blended Ad Spend - Total Costs |
Net Sales |
| formula | Total sales after refunds and discounts. Net Sales = Gross Sales - Discounts - Refunds |
Profit on Ad Spend (POAS) |
| formula | Profit generated per dollar of ad spend. POAS = (Total Sales - Total Costs) / Ad Spend |
Blended Return on Ad Spend (Blended ROAS) |
| formula | Revenue generated per dollar spent on ads. Blended ROAS = Order Revenue / Blended Ad Spend |
Updated 4 days ago