Why a dashboard needs numbers no ad platform can give you
Open any advertising account and you will find a return figure. The search platform reports its return on ad spend, the social platform reports its own, the email tool shows revenue per send and the affiliate network adds a fourth view. Each of these numbers is calculated by the system that wants more of your budget, each uses its own rules for what counts as a sale, and none of them knows what the others have claimed. Add them up and the total is regularly larger than what the shop actually took in.
A marketing dashboard therefore starts somewhere else: with the totals of the business. How much did we spend on marketing in total? How much revenue came in, in total? How many customers bought for the first time? Three divisions on these totals give you the marketing efficiency ratio (MER), the blended customer acquisition cost (blended CAC) and the acquisition MER. They are simple, they cannot be double counted, and they answer the question the owner of the business really asks: is marketing as a whole paying off?
This free lesson shows you how to calculate all three in Excel, month by month and for a whole year, and how to avoid the one mistake that spoils most year figures. The example company is Fernhollow Outfitters, an online shop for outdoor gear. The company, its customers and every number in this lesson and in the course are fictional, and all benchmarks mentioned are illustrative.
MER: the marketing efficiency ratio
The definition fits on one line: MER = total revenue ÷ total marketing spend, both for the same period. If the shop sold goods for $289,380 in June and spent $68,900 on marketing, the MER is 4.2. Every marketing dollar came with $4.20 of revenue.
Note the careful wording: came with, not caused. MER makes no claim about which dollar produced which sale. It includes customers who would have bought without any advertising, orders from people who typed the shop's address from memory and sales to regulars who have been buying for years. That is its weakness and its strength. It is a weakness because MER cannot tell you which channel to cut. It is a strength because nobody can inflate it: revenue comes from the shop system or the bank, spend from the invoices, and both are facts.
You will meet MER under other names: blended ROAS, total ROAS or ecommerce ROAS. Some teams prefer the inverse, marketing spend ÷ revenue, and call it the marketing cost ratio or cost of sales; a MER of 4.0 is the same as a cost ratio of 25 %. Use whichever your company is used to, but put the formula on the dashboard so that nobody has to guess.
Blended CAC: what a new customer really costs
The second ratio looks at people instead of dollars: blended CAC = total marketing spend ÷ total new customers. In June Fernhollow Outfitters won 1,040 customers who had never ordered before. $68,900 ÷ 1,040 = $66.25. That is what one new customer cost in June, averaged over every channel.
The word blended matters. A platform CAC divides the spend on one platform by the customers that platform claims. A customer who clicked a search ad on Monday and a social ad on Wednesday before buying is claimed by both, so the platform CACs look better than reality. The blended CAC counts every customer once, because it takes the customer count from the shop system. It also divides all marketing spend by the new customers, including spend aimed at existing customers, which makes it a conservative, slightly pessimistic figure. For a number that decides how much you can afford to pay for growth, conservative is the right side to err on.
Be strict about what a new customer is: a person whose first ever order falls into the period. Most shop systems can export a first-order flag or an order number per customer. Guest checkouts and second email addresses blur the count a little; that is acceptable as long as the rule stays the same every month.
Acquisition MER: taking repeat customers out of the picture
MER has a blind spot. A shop with many loyal customers shows a high MER even if its advertising has stopped working, because the regulars keep ordering. The acquisition MER (also written aMER or new-customer ROAS) removes them: acquisition MER = new-customer revenue ÷ total marketing spend. In June, first orders brought $102,900, so the acquisition MER is 102,900 ÷ 68,900 = 1.49. For every marketing dollar, $1.49 of first-order revenue came in.
An acquisition MER around or even below 1 is not automatically a problem. It only says that first orders alone do not pay for the marketing. Whether later orders make up for it is the payback question, which the course treats with cohorts. The value of the number on a dashboard is its direction: when MER is stable but the acquisition MER falls month after month, the business is living off its existing customers.
A fully worked example: Fernhollow Outfitters in 2026
Here are the twelve months of the lab: marketing spend, total revenue, new customers and new-customer revenue, followed by the three calculated columns. The spend column contains everything the company paid for marketing in the month: advertising on all platforms, the email tool, affiliate commissions and the agency fee.
| Month | Marketing spend $ | Total revenue $ | New customers | New-customer revenue $ | MER | Blended CAC $ | Acquisition MER |
|---|---|---|---|---|---|---|---|
| Jan | 42,000 | 151,200 | 620 | 55,800 | 3.6 | 67.74 | 1.33 |
| Feb | 39,500 | 146,150 | 590 | 51,900 | 3.7 | 66.95 | 1.31 |
| Mar | 46,800 | 182,520 | 720 | 66,200 | 3.9 | 65.00 | 1.41 |
| Apr | 52,400 | 214,840 | 815 | 77,400 | 4.1 | 64.29 | 1.48 |
| May | 61,300 | 263,590 | 960 | 93,100 | 4.3 | 63.85 | 1.52 |
| Jun | 68,900 | 289,380 | 1,040 | 102,900 | 4.2 | 66.25 | 1.49 |
| Jul | 72,500 | 282,750 | 1,010 | 99,000 | 3.9 | 71.78 | 1.37 |
| Aug | 66,200 | 251,560 | 905 | 87,800 | 3.8 | 73.15 | 1.33 |
| Sep | 54,800 | 219,200 | 800 | 76,000 | 4.0 | 68.50 | 1.39 |
| Oct | 49,600 | 193,440 | 705 | 66,300 | 3.9 | 70.35 | 1.34 |
| Nov | 88,400 | 380,120 | 1,360 | 136,000 | 4.3 | 65.00 | 1.54 |
| Dec | 79,300 | 325,130 | 1,150 | 112,700 | 4.1 | 68.96 | 1.42 |
| Year | 721,700 | 2,899,880 | 10,675 | 1,025,100 | 4.02 | 67.61 | 1.42 |
In the lab the month names are in column A from row 4 to row 15, spend is in column B, total revenue in C, new customers in D and new-customer revenue in E. The three formulas, written for row 4 and copied down to row 15, are:
- MER (F4):
=C4/B4. Total revenue divided by marketing spend. For January: 151,200 ÷ 42,000 = 3.6. - Blended CAC (G4):
=B4/D4. Marketing spend divided by new customers. For January: 42,000 ÷ 620 = $67.74. - Acquisition MER (H4):
=E4/B4. New-customer revenue divided by marketing spend. For January: 55,800 ÷ 42,000 = 1.33.
All three use plain relative references, so they adjust row by row when you copy them down. Read the table like a short story of the year. Spend more than doubles from February ($39,500) to November ($88,400), the month of the big autumn promotions. MER nevertheless stays in a narrow band between 3.6 and 4.3. The blended CAC is lowest in May ($63.85) and highest in August ($73.15), when the summer peak is over but the budget has not yet been reduced.
The year figure: sum first, divide second
Now the question every report eventually asks: what was the MER of the whole year? There are two ways to answer, and only one is correct. The tempting way is to average the twelve monthly MERs: =AVERAGE(F4:F15) gives 3.98. The correct way divides the year totals: =SUM(C4:C15)/SUM(B4:B15) = 2,899,880 ÷ 721,700 = 4.02.
Why are they different? An average of ratios gives every month the same weight. February, with $39,500 of spend, counts exactly as much as November with $88,400. But November's dollars are more than twice as many, and they happened to be efficient ones. The true year MER is a weighted average, weighted by spend, and dividing the totals does this weighting automatically.
The gap of about 0.035 looks harmless. Multiply it by the year's spend and it stands for about $25,100 of revenue that the averaged figure cannot account for. In businesses with stronger seasons, or when one month is very small, the two numbers drift much further apart. The rule holds for every ratio on every dashboard: a ratio of a period is the sum of the numerator divided by the sum of the denominator.
| Year figure | Correct: from totals | Wrong: average of months | Formula to use |
|---|---|---|---|
| MER | 4.02 | 3.98 | =SUM(C4:C15)/SUM(B4:B15) |
| Blended CAC | $67.61 | $67.65 | =SUM(B4:B15)/SUM(D4:D15) |
The same applies to the blended CAC of the year: total spend of $721,700 divided by 10,675 new customers is $67.61, while the average of the twelve monthly CACs is $67.65. This time the two are almost identical. That is luck, not a rule: the cheap and the expensive months happen to balance out. Only the calculation from the totals is right every time.
How to read the three numbers together
No single ratio tells the truth on its own. Read them as a set, always against the previous period and against a target:
- MER stable, acquisition MER falling: revenue from existing customers is covering for weaker acquisition. Look at the new-customer count before the customer base starts to shrink.
- MER falling, blended CAC stable: new customers still cost the same, but they or the regulars spend less. Check average order value and repeat orders, not the ad accounts.
- Blended CAC rising while spend rises: this is normal within limits. The first dollars reach the easiest customers; additional budget buys more expensive ones. The question is whether the higher CAC is still below what a customer is worth.
- Everything improves in a promotion month: compare with the same month of last year before celebrating. Seasonal demand lifts all three ratios without any change in marketing quality.
For Fernhollow Outfitters the year ends with a MER of 4.02, a blended CAC of $67.61 and an acquisition MER of 1.42. In words: four dollars of revenue per marketing dollar, of which about $1.42 are first orders, and a little under $68 to win a new customer.
What counts as marketing spend?
The ratios are only comparable from month to month if the spend figure is defined once and then left alone. A practical definition for a small or medium-sized shop:
- Include media spend on all advertising platforms, affiliate and creator payments, the cost of email and messaging tools, agency and freelancer fees and the cost of producing ads.
- Decide once whether salaries of the in-house marketing team and discounts given to customers are part of it. Many teams report a media-only MER and a fully loaded MER side by side.
- Exclude shipping, product costs and payment fees. They belong to the margin, not to marketing.
- Use the same calendar for spend and revenue. Platform invoices dated on the third of the following month must be booked into the month in which the ads ran.
Common mistakes and how to spot them
- Reversed division.
=B4/C4returns 0.28 instead of 3.6. A MER below 1 for a healthy shop almost always means spend and revenue are swapped. - Averaging the monthly ratios.
=AVERAGE(F4:F15)for the year MER. Compare it with SUM ÷ SUM; if the two differ, the average is the wrong one. - Mixing all customers into CAC. Dividing spend by all orders or all customers gives a cost per order, a much smaller and less useful number. CAC needs first-time customers only.
- Revenue with and without tax. If revenue includes sales tax in one export and not in another, MER jumps for no reason. Pick net revenue after discounts and returns if you can, and say so on the sheet.
- Different periods. Spend by invoice date against revenue by order date shifts money between months. Both must follow the same calendar.
- Comparing MER with a platform ROAS. They answer different questions. A platform ROAS of 6 and a MER of 4 can both be correct at the same time.
Quick reference: formulas of this lesson
| Metric | Definition | Excel formula (row 4) |
|---|---|---|
| MER | Total revenue ÷ total marketing spend | =C4/B4 |
| Blended CAC | Total marketing spend ÷ new customers | =B4/D4 |
| Acquisition MER | New-customer revenue ÷ total marketing spend | =E4/B4 |
| Year MER | Sum of revenue ÷ sum of spend | =SUM(C4:C15)/SUM(B4:B15) |
| Year blended CAC | Sum of spend ÷ sum of new customers | =SUM(B4:B15)/SUM(D4:D15) |
| Marketing cost ratio | 1 ÷ MER | =B4/C4 |
Where these numbers are used
- Monthly management report. MER and blended CAC are the two marketing lines that belong on the first page, next to revenue and margin.
- Budget planning. A revenue target divided by the expected MER gives the marketing budget needed; a customer target multiplied by the blended CAC does the same from the customer side.
- Sanity check for platform reports. When every platform reports improving returns but MER falls, the platforms are claiming the same customers more often, not winning more of them.
- Investor and bank updates. Blended CAC, together with what a customer brings in over time, is the standard short description of how efficiently a company grows.
What you will build in the rest of this course
Lesson 1 is free. The other nineteen lessons each come with their own live lab and build, step by step, the complete reporting workflow around these three numbers.
From raw exports to a channel table (lessons 2 and 3). You roll raw campaign rows up into one line per channel with SUMIFS, clean inconsistent source names with LOWER and TRIM, map them to channel groups with XLOOKUP and measure how much spend is still unmapped.
Targets, pacing and week-over-week reporting (lessons 4 to 6). You compare KPIs with targets in the right direction, set status bands, check in the middle of a month whether revenue is on pace with EOMONTH, and separate real changes from weekly noise with a trailing four-week average.
Making a table readable (lessons 7 and 8). You add sparklines, a heat map and highlight rules, and build a scorecard that follows a single month selector with previous-month and year-to-date figures.
Honest numbers (lessons 9 to 11 and 15). You measure how much the platforms over-claim and calibrate their numbers to the real total, split revenue into new and returning customers, derive the break-even MER from the margin, fix total lines that average averages and reconcile exports with the finance figure after removing duplicates.
Looking ahead and explaining change (lessons 12 to 14 and 16 to 18). You forecast a quarter with FORECAST.LINEAR, flag unusual days with z-scores, find the campaigns that explain a weekly change, check with cohorts whether the CAC pays back, and test bigger budgets and reallocations in what-if grids.
The report itself (lessons 19 and 20). You let the sheet write the routine sentences of the weekly report and finish with a complete four-week dashboard: raw data, channel roll-up, blended scorecard with targets and a combo chart.
Summary
Three blended ratios form the top line of a marketing dashboard. MER is total revenue ÷ total marketing spend, =C4/B4. Blended CAC is total marketing spend ÷ new customers, =B4/D4. Acquisition MER is new-customer revenue ÷ total marketing spend, =E4/B4. For a year or any longer period, divide the totals instead of averaging the monthly ratios. At Fernhollow Outfitters that gives a year MER of 4.02 and a blended CAC of $67.61. The numbers cannot be double counted, they are easy to audit, and they set the frame that every channel report has to fit into. All data in this lesson are fictional.
Frequently asked questions
What is MER in marketing?
MER stands for marketing efficiency ratio. It is total revenue divided by total marketing spend for the same period. A MER of 4 means that every dollar spent on marketing came with four dollars of revenue across all channels and all customers. It is also called blended ROAS.
What is the difference between MER and ROAS?
ROAS is usually reported per platform or campaign and only counts the revenue that platform attributes to its own ads. MER divides all revenue of the business by all marketing spend. ROAS helps to compare campaigns inside one platform, MER shows whether marketing as a whole pays off and cannot be double counted.
How do I calculate blended CAC in Excel?
Divide the total marketing spend of a period by the number of customers who ordered for the first time in that period, for example =B4/D4. For a whole year use =SUM(B4:B15)/SUM(D4:D15) instead of averaging the monthly values.
Why should I not average monthly MER values to get the year MER?
An average of ratios gives every month the same weight, whether it had a small or a large budget. The year MER is the sum of all revenue divided by the sum of all spend, which weights each month by its spend automatically. In the example of this lesson the average is 3.98 while the correct year MER is 4.02.
What is acquisition MER or aMER?
Acquisition MER is the revenue from new customers divided by total marketing spend. It removes repeat customers from the picture and shows what the marketing spend returns in first orders only. It is typically much lower than MER and is watched for its trend.
What is a good MER?
There is no universal value, because it depends on the gross margin. The break-even MER is 1 divided by the gross margin: with a 40 percent margin the business needs a MER above 2.5 just to cover its marketing spend from the margin. Compare your MER with your own break-even level and your own history rather than with other companies.
Keep going: Marketing Dashboards & KPI Reporting: Blended CAC and MER, Channel Roll-Ups, Targets and Pacing, Sparklines & the Weekly Report Narrative
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Channel Roll-Ups from Raw Exports: SUMIFS for Spend and Revenue, ROAS per Channel and Share of Spend
- Cleaning Raw Exports: Clean Keys with LOWER and TRIM, a Channel Mapping Table with XLOOKUP and the Unmapped Share
- Targets on a Dashboard: Variance, Attainment for Higher- and Lower-Is-Better KPIs, Status Bands and a Highlight Rule
- Pacing Against a Monthly Target: Days in Month with EOMONTH, Expected to Date, Pace, Run-Rate Projection and Required Daily Revenue
- Week-over-Week Reporting: Conversion Rate, WoW Change, a Trailing 4-Week Average and the Deviation from Recent Normal
- Sparklines and Conditional Formats: Trend per Channel, a Heat Map of Weekly ROAS and a Rule That Flags Falling Channels
- A Scorecard Driven by One Selector Cell: XLOOKUP by Month, Previous Month with EDATE, MoM Change and Year-to-Date Figures
- Platform Numbers vs Reality: Over-Attribution Factor, Proportional Calibration and a Calibrated CAC per Channel
- Reading MER Honestly: New vs Returning Revenue, Break-Even MER and Contribution After Marketing
- The Total Line Done Right: Weighted Conversion Rate and Order Value with SUMPRODUCT Instead of an Average of Averages
- Trend Forecasts for a Dashboard: SLOPE, FORECAST.LINEAR, R² and the Projected Attainment of a Quarter Target
- Is Today Unusual? Mean, Standard Deviation, z-Scores and a Row-Highlight Rule for Daily CPA
- Explaining the Change: Top Movers with RANK.EQ, LARGE and XLOOKUP and the Net Change of All Campaigns
- Data Quality Checks: Row Keys with TEXT, Duplicate Highlighting, De-Duplicated Totals and Reconciliation with Finance
- Is the CAC Worth It? Cohort Payback Month, Six-Month Margin to CAC and a Payback Staircase with a Formula Rule
- What-If Grid for a Bigger Budget: New MER by Extra Spend and Marginal ROAS, Mixed References and the Break-Even Marginal ROAS
- Budget Reallocation: Average vs Marginal ROAS, Giving and Receiving Channels with MIN, MAX and XLOOKUP and the New Blended MER
- The Weekly Report Narrative: Sentences from Numbers with TEXT, a Direction Word with a Flat Band and a Headline Count
- Final Project: A Four-Week Marketing Dashboard with Channel Roll-Up, Blended MER and CAC, Target Status and a Combo Chart