Calculate Blended ROAS & CAC in Excel: True Marketing ROI

Updated March 2025 15 min read Advanced
Shopify Facebook Ads Google Ads

TL;DR

  • Platform ROAS is inflated - Facebook claims credit for sales Google actually drove
  • Blended ROAS = Total Revenue / Total Ad Spend across all channels
  • Blended CAC = Total Ad Spend / New Customers acquired
  • True profitability requires accounting for COGS, not just revenue

Prerequisites

  • Ad spend data from all platforms (Facebook, Google, TikTok, etc.)
  • Order-level data with attribution source (UTM parameters or default source)
  • New customer vs. returning customer designation
  • Cost of Goods Sold data
Calculate Blended ROAS & CAC in Excel: True Marketing ROI

The Attribution Lie Costing You Thousands

You log into Facebook Ads Manager: "5.2 ROAS - Amazing!"
You check Google Ads: "4.8 ROAS - Crushing it!"

But your bank account tells a different story. You spent $20K on ads and made $65K in revenue. That's 3.25 ROAS.

What happened?

The Attribution Problem:
- Customer sees Facebook ad (Facebook tracks them)
- Customer Googles your brand (Google tracks them)
- Customer visits site directly (no attribution)
- Customer buys

Facebook says: "We drove that sale!" (Claims $65 revenue)
Google says: "Actually, we did!" (Claims $65 revenue)
Direct says: "It was us!" (Claims $65 revenue)

One sale, three platforms claiming credit, $195 of "attributed" revenue from a $65 sale.

This is why platform ROAS is a vanity metric. Blended ROAS tells the truth.

Platform ROAS vs. Blended ROAS

Platform ROAS (What Dashboards Show)


Formula: Attributed Revenue / Ad Spend on That Platform

Problems:
- Double/triple counting of sales
- Last-click attribution ignores awareness channels
- Doesn't account for organic/direct that resulted from ads
- Makes every channel look amazing even when losing money overall

Blended ROAS (Reality)


Formula: Total Revenue / Total Ad Spend (All Channels)

Benefits:
- Can't cheat the math
- Shows true return on marketing investment
- Accounts for attribution overlap
- Guides actual business decisions

Real Example:


Your Ad Spend:
- Facebook: $10K
- Google: $8K
- TikTok: $2K
- Total: $20K

Platform-Reported ROAS:
- Facebook: $52K revenue / $10K = 5.2 ROAS
- Google: $38K revenue / $8K = 4.75 ROAS
- TikTok: $10K revenue / $2K = 5.0 ROAS
- Sum: $100K "attributed" revenue

Actual Revenue: $65K (from Shopify)

Blended ROAS: $65K / $20K = 3.25 ROAS

The platforms claimed $100K but you only made $65K. The $35K difference is attribution overlap.

Building Your Blended ROAS Calculator in Excel

  1. 3

    Aggregate All Ad Spend by Day

    Create table with columns: Date, Facebook Spend, Google Spend, TikTok Spend, Other Spend, Total Spend. Export daily spend from each platform and consolidate. Use SUMIF or pivot tables.

  2. 3

    Calculate Daily Blended ROAS

    Simple formula: =DailyRevenue / DailyAdSpend. No attribution, no clicks, no sessions. Just dollars in, dollars out. Plot this over time to see true performance trends.

  3. 3

    Identify New vs. Returning Customers

    In Shopify export, check if First Purchase Date = Order Date. New customers are true acquisition. Returning customers are retention (shouldn't count toward CAC).

  4. 3

    Calculate Blended CAC

    CAC = Total Ad Spend / New Customers Acquired. Formula: =TotalSpend / COUNTIFS(Orders, FirstPurchase=TRUE). This is what you actually pay to acquire a customer across all channels.

  5. 3

    Build Contribution Margin ROAS

    Revenue ROAS is vanity. Profit ROAS matters. Formula: (Revenue - COGS) / Ad Spend. If COGS is 40% of revenue, your 3.25 ROAS is actually 1.95 profit ROAS.

  6. 3

    Create Rolling 7/30/90 Day ROAS

    Daily ROAS is noisy. Use =SUMIFS for revenue and spend over last 7/30/90 days, then divide. Rolling averages smooth volatility and show true trends.

  7. 3

    Build Break-Even Analysis

    Calculate minimum ROAS needed to be profitable. If COGS=40%, fees=5%, you need 1.82 ROAS just to break even. Anything above is profit. Shows how much room you have.

,,,,,,,

The CAC Payback Period

Blended CAC alone doesn't tell the full story. You need to know how long it takes to recover acquisition cost.

Formula:


CAC Payback = CAC / (Monthly Revenue per Customer × Gross Margin %)

Example:


- Blended CAC: $50
- Average customer spends $30/month (including repeat purchases)
- Gross margin: 60% ($18 profit per month)

Payback Period = $50 / $18 = 2.8 months

What's Good?


- Under 3 months: Excellent, fast payback
- 3-6 months: Good, sustainable
- 6-12 months: Requires working capital but manageable
- 12+ months: Risky, needs strong retention

The Excel Calculation:


```excel
=CAC / (AVERAGE(MonthlyRevenue) * GrossMarginPercent)
```

If payback is too long, you either need to:
1. Lower CAC (improve conversion, better targeting)
2. Increase initial AOV (upsells, bundles)
3. Improve retention (faster repeat purchases)

⚠️
High blended ROAS early on often includes lots of direct/organic traffic from word-of-mouth, not ads. As you scale ads, blended ROAS will compress. Don't assume 5.0 ROAS will hold as you 10x spend.

Advanced: Incrementality Testing

Blended ROAS still doesn't tell you which channels actually work. Some of your sales would have happened anyway.

The Incrementality Test:

Week 1-2: Run all channels normally (baseline)
Week 3-4: Turn off Facebook entirely
Week 5-6: Back to normal

Measure:
- Did revenue drop by more than Facebook's attributed revenue?
- If yes: Facebook is incremental (driving new sales)
- If no: Facebook is getting credit for sales that would have happened anyway

Excel Incrementality Model:

1. Calculate average daily revenue WITHOUT Facebook: $2,000
2. Compare to average WITH Facebook: $2,800
3. Incremental revenue FROM Facebook: $800/day
4. Incremental ROAS: $800 / (Facebook Daily Spend)

This is the truest measure of channel performance.

When Blended ROAS Misleads

Scenario 1: Launch Phase


Early customers are often warm (friends, existing audience, influencers). Your 6.0 ROAS isn't scalable. As you exhaust warm audience, expect 3.0-4.0.

Scenario 2: Seasonal Business


Q4 e-commerce naturally has better ROAS (holiday shopping intent). Don't compare December (4.5 ROAS) to February (2.8 ROAS) and panic.

Scenario 3: Organic Growth Spike


You get featured in major press. Sales spike, ad spend stays same, ROAS spikes to 8.0. This is temporary. Don't use it to justify increased ad spend.

Scenario 4: Retention Business


If 60% of revenue is repeat customers, your "marketing ROAS" includes retention. Separate new customer acquisition ROAS from total ROAS.

Get automated blended ROAS tracking that combines all your ad platforms with actual revenue data.

Track True Marketing ROI

See real profitability, not platform lies

Common Mistakes to Avoid

Learn from these common pitfalls to ensure success

❌

Comparing platform ROAS to blended ROAS and getting confused

Solution: They measure different things. Platform ROAS will always be higher (2-3x) due to attribution overlap. Only use blended ROAS for business decisions.

❌

Not accounting for organic/direct traffic influenced by ads

Solution: Some 'direct' traffic is actually people who saw ads and came back later. Blended ROAS captures this, platform ROAS doesn't.

❌

Using gross revenue instead of contribution margin for profitability

Solution: 5.0 revenue ROAS with 50% gross margin = 2.5 profit ROAS. Always calculate both.

❌

Not separating new customer acquisition from repeat customer revenue

Solution: CAC should only include new customers. Repeat customer revenue is retention, not acquisition.

❌

Making decisions on daily blended ROAS instead of rolling averages

Solution: Daily data is noisy. Use 30-day rolling average for strategic decisions.

How to Verify Your Numbers

Ensure accuracy with these verification steps

✓

Your blended ROAS matches actual bank deposits divided by actual ad spend

✓

You can explain why your blended ROAS is lower than platform ROAS

✓

You know your break-even ROAS and current margin above it

✓

You've calculated CAC payback period and know if it's sustainable

✓

You're making channel allocation decisions based on blended metrics, not platform dashboards

Frequently Asked Questions

My blended ROAS is 2.8 but Facebook says 5.2. Which is right?
Blended ROAS (2.8) is reality. Facebook's 5.2 includes attribution overlap with other channels. Trust the blended number for business decisions.
What's a good blended ROAS for e-commerce?
3.0-4.0 is solid. Above 4.0 is excellent. Below 2.5 is marginal. But it depends on your margins - high-margin businesses can survive on lower ROAS.
Should I stop using platform dashboards entirely?
No. Use them for creative optimization and targeting. But use blended ROAS for budget allocation and profitability decisions.
How do I attribute revenue to channels if everything is blended?
You can't precisely, and that's the point. Focus on: if I increase spend on X, does blended ROAS hold? That's incrementality testing.
What if I only run one ad channel?
Blended ROAS and platform ROAS should be closer (but still different due to organic traffic). The concept still matters - total revenue / total spend.

Ready to Transform Your Reporting?

Start with a free report and see exactly what you'll get every time.

Get 1 Report Free - No Card View Pricing