by Trivas.aiOpen five dashboards at most DTC brands and you'll find the same setup: Meta reporting its own ROAS, Google reporting its own ROAS, TikTok reporting a third number, and nobody in the room who can say what the business actually spent versus what it actually made. Each platform grades its own homework. None of them agree with each other, and none of them agree with Shopify.
That's the problem. Platform-reported ROAS is inflated, often by 20-40%, because of attribution overlap and last-click bias. A customer sees a TikTok ad, clicks a Google search ad three days later, and both platforms claim the sale. Add up all the "attributed revenue" across your ad accounts and you'll frequently get a number bigger than your total store revenue. That's not a rounding error. That's structural.
The fix a lot of scaling DTC brands have landed on is MER: Marketing Efficiency Ratio, or total revenue divided by total ad spend. No attribution windows, no platform pixels, no guessing. Just the two numbers finance actually cares about.
This post covers how to build a marketing efficiency dashboard for DTC brands the right way: what metrics belong on it, the data architecture underneath it, and a real step-by-step build process you can actually follow this week.
The core metric is blended MER, and it needs to be calculated daily, with a rolling 7-day and 30-day view sitting next to the daily number. Daily MER alone will send you chasing noise.
Around that core number, you need a handful of supporting metrics:
Here's a mistake worth naming directly: dropping platform ROAS entirely once you have MER. Don't. Raw ROAS per channel should sit alongside MER, not replace it, because MER catches halo effects that individual platform pixels miss entirely. If TikTok drives brand awareness that shows up as direct traffic and Google branded search two weeks later, MER captures that lift. TikTok's own dashboard never will.
One more distinction worth building in early: MER versus nMER (new-customer MER, using only new customer revenue over spend). Brands still in aggressive paid acquisition mode should track nMER specifically, since blended MER can look healthy while new-customer acquisition is quietly getting more expensive, propped up by a strong returning-customer base.
Before you touch a chart, you need four sources talking to each other: Shopify orders and revenue, ad platform spend from Meta, Google, and TikTok, and GA4 sessions for directional context (not attribution, just context).
The first thing that trips people up is timestamps. Ad platforms report spend by impression date. Shopify reports revenue by order date. These don't line up cleanly, especially around weekends or multi-day purchase cycles, and pulling both into a spreadsheet without correcting for it quietly breaks your MER by several points without anyone noticing.
Second: refunds and discounts. Gross revenue and net revenue are not the same number, and the gap between them can shift MER meaningfully, especially for brands running aggressive discount codes or dealing with high return rates. Pick one (net is the right answer) and lock it everywhere.
Third, and this is the part nobody wants to hear: most teams start in Google Sheets with manual CSV exports from each platform. It works, for a while. Somewhere around $1M/month in spend, the manual pull becomes a full-time job, the CSVs stop matching because someone forgot to re-export Google Ads on a Tuesday, and the "dashboard" becomes three people's slightly different versions of the same spreadsheet.
Step 1: Centralize spend and revenue data in one warehouse. Instead of pulling native reports from each platform separately, get spend and revenue into a single source, whether that's a warehouse you build yourself or a tool like Trivas built on Amazon Redshift. This is the step that actually prevents the version-drift problem later.
Step 2: Standardize your revenue definition. Net of refunds and discounts, every time, on every chart. Write it down somewhere everyone can see it. This single decision prevents more dashboard arguments than anything else on this list.
Step 3: Build the MER calculation itself. Total spend divided by total revenue, both attributed and unattributed, over matching date ranges. The date-range matching is the part people skip and then wonder why their number looks off by 15%.
Step 4: Layer in channel breakdowns. Meta, Google, and TikTok spend share sitting next to your MER trend line lets you trace a MER dip back to "we increased TikTok spend 40% and it hasn't caught up yet" instead of just seeing the number drop with no explanation.
Step 5: Add rolling trend lines. 7-day and 30-day rolling averages, not single-day snapshots. Daily MER is genuinely noisy, and teams that react to a single bad day end up cutting a channel that was actually working, just delayed.
If you're building this on Shopify specifically, the underlying setup matters more than the chart design. Get the Shopify integration right first, revenue definitions and all, before you spend time on formatting.
The most common one: mixing gross and net revenue across different widgets on the same dashboard without realizing it. One chart pulls gross, another pulls net, and now your MER and CAC numbers are quietly inconsistent with each other.
Second: using platform-attributed revenue instead of total store revenue. This defeats the entire point of building a MER dashboard in the first place. If you're still pulling "attributed revenue" from Meta's ad manager into your MER formula, you haven't actually left ROAS-land, you've just renamed it.
Third: ignoring non-paid revenue. Organic, email, and SMS revenue all flow into total store revenue, which is exactly what MER divides by. That's correct, but it means a strong retention program can mask a genuinely broken paid acquisition engine. If your organic and lifecycle channels are carrying the business, your MER can look fine while your paid channels are quietly losing money. This is exactly why nMER matters for scaling brands.
Fourth, and maybe the most common of all: rebuilding the whole thing manually every Monday in a spreadsheet. Every rebuild is a chance for finance's version and marketing's version to drift apart. Three weeks later, nobody agrees on what the "real" number was in early October.
Trivas pulls Shopify, Meta, Google Ads, and GA4 into one Redshift-backed dashboard, so MER updates automatically instead of getting stitched together from five CSV exports every week. That solves the version-drift problem directly: everyone, marketing and finance, is looking at the same number, built from the same revenue definition, every time.
Reporting time drops from a multi-hour weekly spreadsheet pull to a dashboard that's already refreshed when you open it each morning.
The AI Wingman layer sits on top of that and flags when MER drifts outside its normal range, and it points to which channel drove the shift, instead of a marketing lead noticing three weeks later that TikTok spend crept up while conversion held flat. That's the gap between reactive and 20/20 hindsight, one is useful and the other isn't.
If you're the person who has to walk into a leadership meeting with one clean efficiency number rather than five reconciled tools, this is who custom dashboards and the underlying BI reporting setup are built for, and specifically the workflow marketing leaders deal with every week.
MER only works if revenue and spend definitions are locked down and centralized, not stitched together by hand every Monday morning. Get that foundation right and the rest of the dashboard is genuinely straightforward to build.
If you want to sanity-check your own numbers before committing to any of this, run your current ROAS assumptions through a ROAS calculator and see how far off blended reality actually is from what your platforms are reporting.
And if you'd rather see what a dashboard built for your specific stack looks like instead of DIY-ing it in Sheets for another quarter, talk to a founder or start a trial and get it built properly the first time.
explore more insights
3 min read
3 min read
3 min read
.d53b12e5.png&w=3840&q=75 "Trivas.ai")