Net Margin

Overview

Net Margin, expressed as a percentage, measures the profitability of a business after all expenses have been subtracted from revenue.

πŸ“˜

Net Margin = (Total Sales - Blended Ad Spend - Total Costs) / Order Revenue

The calculation is based on data in the Blended Stats table.

Detailed breakdown

The formula above is derived from the following components:

Total Sales = order_revenue - (refund_money - total_refunded_tax)
Blended Ad Spend = spend + custom_ad_spend
Total Costs = cogs - total_refunded_cogs + payment_gateway_costs + shipping_costs + custom_excl_ad_spend + handling_fees + taxes
Order Revenue = SUM(order_revenue) --> Orders table

Where:

order_revenue = SUM(order_revenue) --> Orders table
refund_money = ABS(SUM(total_refunded_price)) --> Refunds table
total_refunded_tax = ABS(SUM(total_refunded_tax)) --> Refunds table
spend = SUM(spend) --> Ads table
custom_ad_spend = SUM(IF(is_ad_spend, amount, 0)) --> Custom Spend table
cogs = SUM(cost_of_goods) --> Orders table
total_refunded_cogs = ABS(SUM(total_refunded_cogs)) --> Refunds table
payment_gateway_costs = SUM(payment_gateway_costs) --> Orders table
shipping_costs = SUM(shipping_costs) --> Orders table
custom_excl_ad_spend = SUM(IF(is_ad_spend = false, amount, 0)) --> Custom Spend table
handling_fees = SUM(handling_fees) --> Orders table
taxes = SUM(taxes) --> Orders table

Insights and Actions

Net Margin is crucial for understanding an e-commerce business's profitability after all expenses. Efficiently leveraging Net Margin data can guide strategic business decisions:

  • Optimize Ad Spend: Cut inefficient advertising and reallocate budgets to improve margins.
  • Reduce Operational Costs: Lower costs through smarter supply chain and shipping strategies.
  • Adjust Pricing: Increase prices on high-demand items to boost margins, while staying competitive.
  • Focus on High-Margin Products: Prioritize marketing and inventory for more profitable products.

Example Use

Prompt

What was my net margin for the last month?

Response

Query

SELECT
  COALESCE(
    (
      SUM(bs.total_sales) - SUM(bs.spend) - SUM(bs.total_costs)
    ) / NULLIF(SUM(bs.order_revenue), 0),
    0
  ) AS net_margin
FROM
  prepared_blended_stats_table AS bs
WHERE
  bs.event_date BETWEEN DATE_ADD(
    DATE_TRUNC(CURRENT_DATE(), MONTH),
    INTERVAL -1 MONTH
  ) AND DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY);