MER
mer
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 tablecustom_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;
Updated 16 days ago