Marketing Efficiency Ratio (MER)

Overview

Marketing Efficiency Ratio (MER) indicates how much revenue is generated relative to total marketing expenditure.

πŸ“˜

MER = (Ad Spend + Custom Ad Spend) / Order Revenue

The calculation is based on data from the Ads table, the Custom Spend table, and the Orders table.

Insights and Actions

Marketing Efficiency Ratio (MER) measures revenue generation efficiency against marketing spend, critical for evaluating and optimizing marketing investment returns. Efficiently leveraging MER data can guide strategic business decisions:

  • Assess Marketing Performance: A higher MER indicates more revenue generated per dollar spent on marketing, highlighting efficient ad spend.
  • Optimize Marketing Spend: Use MER insights to reallocate budgets towards high-performing channels, maximizing revenue generation.
  • Campaign Evaluation: Analyze MER by campaign to identify which marketing efforts offer the best return, refining future strategies.
  • Strategic Planning: MER trends inform strategic decisions, helping adjust marketing approaches to improve overall efficiency and profitability.
  • Budget Justification: MER can justify marketing budget adjustments by demonstrating the revenue impact of increased or optimized spend.

Example Use

Prompt

What's my MER for the last 7 days?

Response

Query

WITH
  orders AS (
    SELECT
      SUM(order_revenue) AS order_revenue
    FROM
      orders_table
    WHERE
      platform IN ('stripe', 'shopify', 'amazon')
      AND event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  ),
  ads AS (
    SELECT
      SUM(spend) AS spend
    FROM
      ads_table
    WHERE
      event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  ),
  custom_spendings AS (
    SELECT
      COALESCE(SUM(amount), 0) AS total_custom_spend
    FROM
      custom_spend_table
    WHERE
      event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
      AND is_ad_spend = TRUE
  )
SELECT
  COALESCE(
    (
      ads.spend + COALESCE(custom_spendings.total_custom_spend, 0)
    ) / NULLIF(orders.order_revenue, 0),
    0
  ) AS mer
FROM
  orders,
  ads,
  custom_spendings;