Overview

Marketing Efficiency Ratio (MER) evaluates the total advertising spend as a proportion of the revenue generated from all orders.

📘

MER = Blended Ad Spend ÷ Order Revenue

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

Detailed Breakdown

The formula above is derived from the following components:

• Blended Ad Spend = spend + custom_ad_spend

  • spend = SUM(spend) --> Ads table
  • custom_ad_spend = SUM(IF(is_ad_spend, amount, 0)) --> Custom Spend table

• Order Revenue = SUM(order_revenue) --> Orders table.

  • order_revenue is the total revenue after adjustments such as discounts, shipping, fees, and taxes.

Insights and Actions

Example Use

Prompt

What's my MER for the last 7 days?

Response

Query

SELECT
  COALESCE(
    SUM(bst.spend) / NULLIF(SUM(bst.order_revenue), 0),
    0
  ) AS mer
FROM
  blended_stats_tvf () bst
WHERE
  bst.event_date BETWEEN CURRENT_DATE() - 7 AND CURRENT_DATE()  - 1;