Blended Return on Ad Spend (Blended ROAS)

Overview

Blended Return on Ad Spend (Blended ROAS) is the ratio of order revenue to the total advertising expenditure, including both channel ad spend and custom ad spend, across all channels.

πŸ“˜

Blended ROAS = Order Revenue / Blended Ad Spend

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

Detailed Breakdown

The formula above is derived from the following components:

Order Revenue = SUM(order_revenue) --> Orders table
Blended Ad Spend = spend + custom_ad_spend

Where:

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

Insights and Actions

Blended ROAS is crucial for measuring advertising effectiveness, linking ad spend to revenue. It helps optimize ad campaigns, improve budget allocation, influence pricing decisions, refine audience targeting, and evaluate creative content. Efficiently leveraging Blended ROAS data can guide strategic business decisions:

  • Campaign Optimization: Pinpoint and invest in high-performing ads, discontinuing or adjusting less effective ones.
  • Budget Allocation: Direct marketing budgets toward campaigns with the highest ROAS for better efficiency.
  • Pricing Strategy: Use ROAS insights to tweak pricing for enhanced ad performance and revenue.
  • Audience Targeting: Tailor ad targeting based on which segments yield the best ROAS, maximizing profitability.
  • Creative Evaluation: Assess which ad creatives and messages drive higher ROAS to guide future content creation.

Example Use

Prompt

What was my blended ROAS on April 1, 2024?

Response

Query

WITH
  orders AS (
    SELECT
      COALESCE(SUM(order_revenue), 0) AS order_revenue
    FROM
      orders_table
    WHERE
      platform IN ('stripe', 'shopify', 'amazon')
      AND event_date = '2024-04-01'
  ),
  ads AS (
    SELECT
      COALESCE(SUM(spend), 0) AS spend
    FROM
      ads_table
    WHERE
      event_date = '2024-04-01'
  ),
  custom_spendings AS (
    SELECT
      COALESCE(SUM(amount), 0) AS total_custom_spend
    FROM
      custom_spend_table
    WHERE
      event_date = '2024-04-01'
      AND is_ad_spend = TRUE
  )
SELECT
  COALESCE(
    orders.order_revenue / NULLIF(
      ads.spend + COALESCE(custom_spendings.total_custom_spend, 0),
      0
    ),
    0
  ) AS roas
FROM
  orders,
  ads,
  custom_spendings;