A Free Marketing Dashboard Built Entirely in Google Sheets
I built my first real marketing dashboard in Google Sheets in 2016. I was running paid social and email for a DTC brand doing $100K a year, and I had zero budget for a BI tool. Six years and one $3M brand later, I was still using a version of that same sheet. This is how to build a marketing dashboard in Google Sheets that actually gets checked every week, not one that dies in a browser tab.
What the problem actually looks like
Most marketing teams have data everywhere and clarity nowhere. Ad spend lives in Meta and Google Ads. Email metrics live in Klaviyo or Mailchimp. Revenue lives in Shopify or a CRM. Nobody looks at all of it in one place, so decisions get made on whichever platform's dashboard was open last.
Then Monday rolls around, someone asks "how are we doing," and three people give three different answers depending on which tool they logged into. That's not a data problem. That's a dashboard problem.
Why it happens
Marketing tools are built to make their own numbers look good. Meta will show you reach and engagement. Google Ads will show you clicks and impressions. None of them default to blended CAC or contribution margin, because that math requires pulling in your Shopify or Stripe numbers too.
On the $2.2B infrastructure project I marketed, we had six agencies each reporting their own metrics in their own format. PR had media impressions. Digital had click-through rates. Community relations had event attendance. Nobody had one number that told the client if the $10M budget was working. That gap between "activity reported" and "outcome measured" is where most marketing dashboards fail before they're even built.
The other reason it happens: people think they need a tool. So they sign up for a $200/month dashboard product, spend two weeks connecting integrations, and then stop maintaining it because nobody on the team actually built it, so nobody trusts it enough to keep it updated.
What we do about it
Here's the actual build, the one I've used on brands from $100K to $3M in revenue.
Step 1: Set up three tabs, not one.
- Raw Data tab: paste exports here weekly. Never format this tab. It's ugly on purpose.
- Calculations tab: this is where formulas live, pulling from Raw Data.
- Dashboard tab: this is the only tab anyone else sees. Clean, five to eight metrics, no clutter.
Step 2: Pick five metrics maximum for the dashboard tab. I use these for a DTC brand:
- Blended CAC (total spend / total new customers)
- ROAS by channel
- Email revenue as % of total revenue
- Contribution margin per order
- Week-over-week revenue trend
Step 3: Use IMPORTRANGE to pull data automatically where you can. If your ad platforms export to Google Sheets via Supermetrics or a native connector, set that up once. If not, manual weekly paste takes ten minutes. Don't let "no clean API" stop you from starting.
Step 4: Build the blended CAC formula correctly. This is the one people get wrong constantly:
Blended CAC = (Total Ad Spend + Total Tool Costs) / New Customers Acquired
Not just ad spend divided by conversions from the ad platform. That number is fake. Meta will tell you it drove 50 conversions. Your Shopify data might show 35 new customers that week. Use the Shopify number. Platforms overcount conversions by 20% to 40% depending on attribution window settings. I've seen it as high as 60% on brands using 7-day click, 1-day view windows.
Step 5: Use conditional formatting, not charts, for the first version. Green if CAC is under target, red if it's over. Charts look nice in a pitch deck. Conditional formatting gets read in eight seconds by someone scrolling on their phone before a Monday meeting.
Step 6: Set a recurring calendar block to update it. Fifteen minutes every Monday morning. Not "when I get to it." The dashboard that gets updated inconsistently gets ignored entirely within six weeks. I've watched this happen on three different teams.
Total cost: zero dollars. Total time to build the first version: about three hours. Time to update weekly once it's built: fifteen minutes.
The most common mistake
People build a dashboard with 25 metrics because they're afraid of leaving something out. Nobody reads a 25-metric dashboard. It gets opened once, gets overwhelming, gets closed, never gets opened again.
Pick five numbers that actually drive a decision. If a metric changing wouldn't change what you do next week, it doesn't belong on the dashboard. Put it in the Calculations tab if you want it archived, not on the tab people actually look at.
What it costs to ignore
On the infrastructure project, before we consolidated reporting into one shared sheet, the client asked me directly why PR spend was still increasing when engagement had flattened for two months. I didn't have a fast answer. That's a bad meeting. That's a client starting to wonder what they're paying for.
After we built one shared dashboard across all six agencies, that same question took thirty seconds to answer with a number on screen.
On the DTC side, a brand without a blended CAC view will keep spending on a channel that platform reporting says is winning, while blended numbers say it's underwater. I've seen brands burn $30K to $50K over a quarter this way, chasing a channel's own inflated ROAS number instead of the real one. A free Google Sheet catches that in week one.
FAQ
Do I need Supermetrics or a paid connector to build this?
No. You can build a fully functional dashboard with manual weekly copy-paste. Connectors save time once you're past 10,000 rows of data or managing multiple accounts, but they're not required to start.
How is this different from just using each platform's native dashboard?
Native dashboards can't blend data across platforms. Only a shared sheet can show you CAC calculated from actual Shopify orders divided by actual total spend across every channel, not each platform's own inflated conversion count.
How often should the dashboard actually get updated?
Weekly, on a fixed day, every time. Monthly updates hide problems for three to four weeks before anyone notices. Weekly is the shortest interval that catches a bad trend before it becomes a bad quarter.
The takeaway: start with three tabs, five metrics, and fifteen minutes a week. You don't need a tool. You need a habit and one honest formula for CAC.