New Customer Return on Ad Spend (NC ROAS)

Overview

New Customer Return on Ad Spend (NC ROAS) measures the efficiency of your marketing efforts in acquiring new customers.

πŸ“˜

NC ROAS = New Customer Order Revenue / (Ad Spend + Custom Ad Spend)

Detailed breakdown

The formula above is derived from the following components:

New Customer Order Revenue = SUM(IF(is_new_customer, order_revenue, 0)) --> Orders table
Ad Spend = SUM(spend) --> Ads table
Custom Ad Spend = SUM(IF(is_ad_spend, amount, 0)) --> Custom Spend table

Insights and Actions

New Customer Return on Ad Spend (NC ROAS) is key for measuring the success of marketing campaigns in attracting new customers. Efficiently leveraging NC ROAS data can guide strategic business decisions:

  • Adjust Advertising Strategies: If NC ROAS is high, consider increasing your ad spend in successful channels; if low, reassess your targeting and messaging.
  • Improve Customer Segmentation: Use insights from NC ROAS to refine your targeting, focusing on demographics or interests that are more likely to convert into new customers.
  • Evaluate Ad Creative and Placement: Test different ad creatives and placements to see which combinations yield higher NC ROAS, optimizing your approach based on data.
  • Analyze Conversion Path: Dive into the journey of new customers to understand which interactions are most influential in their decision to purchase, and adjust your marketing funnel accordingly.

Example Use

Prompt

What's my NCROAS the last 90 days?

Response

Query

WITH
  orders AS (
    SELECT
      COALESCE(SUM(order_revenue), 0) AS order_revenue,
      COALESCE(
        SUM(
          CASE
            WHEN is_new_customer = TRUE THEN order_revenue
            ELSE 0
          END
        ),
        0
      ) AS nc_order_revenue
    FROM
      orders_table
    WHERE
      platform IN ('stripe', 'shopify', 'amazon')
      AND event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  ),
  ads AS (
    SELECT
      COALESCE(SUM(spend), 0) AS spend
    FROM
      ads_table
    WHERE
      event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 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 90 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
      AND is_ad_spend = TRUE
  )
SELECT
  COALESCE(
    orders.nc_order_revenue / NULLIF(
      ads.spend + COALESCE(custom_spendings.total_custom_spend, 0),
      0
    ),
    0
  ) AS nc_roas
FROM
  orders,
  ads,
  custom_spendings;