You are spending money based on numbers that are wrong. Meta says your campaign has a 4x ROAS. Google gives you a shiny green checkmark on a $10 cost per conversion. And you are moving budget toward the platform that looks best on paper while the real cost of acquiring a customer quietly bleeds your margin.

Platform-reported ROAS and your true cost per lead are not the same number. The gap is where your profit disappears. This guide shows you how to build a simple Google Sheets dashboard that reveals the real number, so you can stop chasing fake winners and start moving budget where it actually works.

What you'll be able to do: In under an hour, export your ad data, connect it to your CRM, and calculate accurate CPL and CAC per channel. You will see exactly which platform is lying to you and where to shift spend tomorrow morning.

What you need: Access to Meta Ads Manager and Google Ads (read-only permissions are fine). A CRM or form tool that collects leads (HubSpot, ActiveCampaign, even a simple Google Form with UTM parameters works). Google Sheets. No coding skills required.

1. Why Meta and Google Overstate Your ROAS (And Why It Costs You Money)

Both platforms use last-click attribution as their default. That means they give 100% credit for a conversion to the last ad someone clicked, even if they saw five other ads first. Then they layer on view through conversions, counting users who saw an ad but never clicked, then converted days later by typing your URL directly. This inflates the reported ROAS massively.

But the bigger lie is lead quality. A campaign that drives 100 leads at $10 each in Meta Ads Manager looks like a 4x ROAS if you assign a $40 lead value. But if only 20 of those leads are qualified and 5 become customers, your real numbers are completely different. The platform never accounts for that.

Real world example: A B2B software client ran a whitepaper campaign on Meta. The platform reported $9 per conversion and 3.5x ROAS. When we pulled the CRM data, 60% of those "conversions" never even opened the follow up email. The true cost per lead, defined as a qualified conversation started, was $42. Their real ROAS was 0.8x. They were losing money on every "cheap" lead.

You cannot optimize what the platform tells you. You have to build your own attribution model. That starts with your data.

2. Step 1: Gather the Raw Data You Already Have Access To

You do not need an expensive analytics platform. You need three sources of truth.

First, export your ad platform data. In Meta Ads Manager, pull a report with Date, Campaign Name, Cost, Impressions, Clicks, and Conversions (set to the specific pixel event you care about, usually Lead or Purchase). In Google Ads, use the Campaigns report with the same columns. Export to CSV. Name the files clearly.

Second, pull lead data from your CRM or form tool. This needs to include the date the lead came in, the source (ideally populated by UTM parameters), and the lead status: is it qualified or unqualified? "Qualified" can mean a 5 minute sales conversation or a verified email and industry match. Define it once and stick to it.

If you are not tracking UTMs, stop reading and go set them up now. It takes 10 minutes. In Meta Ads, go to the ad level and append ?utm_source=meta&utm_campaign=campaignname to your URL. In Google Ads, use the auto tagging option or manual UTMs. Without UTMs, your CRM won't know where a lead came from, and your dashboard will be useless.

This export is the foundation. Most people skip it and trust the platform numbers. That is exactly how they get trapped.

3. Step 2: Build Your True CPL Dashboard in Google Sheets (No Formulas Required)

Open a new Google Sheet. Create the following columns across the top row:

  • Date (use the date from your ad platform)
  • Channel (Meta, Google, LinkedIn, etc.)
  • Cost (from the platform export)
  • Leads (platform reported conversions)
  • Qualified Leads (from your CRM)
  • Sales (revenue from closed deals)

Paste your platform data into the first few columns. Then paste your CRM data, aligning by date and channel. If you use the same date format, a simple SUMIFS function can pull qualified leads into the right rows. Example: if your CRM data is in a second sheet called CRMData, use =SUMIFS(CRMData!QualifiedLeads, CRMData!Date, A2, CRMData!Channel, B2).

Now calculate your true CPL. In a new column: =C2 / E2 (Cost divided by Qualified Leads). This is your real cost per qualified lead. Ignore the platform conversion number. This number tells you the truth.

Use conditional formatting to color cells green when CPL is below your target, red when it is above. Add a simple chart (select the Cost and Qualified Leads columns, Insert Chart) to see which channel trends cheaper over time. No complex scripting, no add ons, just native Sheets.

If you want to automate the data refresh, set up a Zapier connection that pushes new row data from Meta Ads or your CRM into this sheet. It takes 15 minutes and you never copy paste again.

4. Step 3: Calculate True Customer Acquisition Cost (CAC) From Your Dashboard

The true CPL is useful, but CAC is where the real budget decisions happen. CAC = Total Cost / Number of New Customers. Add a column for Close Rate as well: Sales divided by Qualified Leads.

Here is the math that hurts. Channel A: Google Ads cost $50 per qualified lead and closes 10%. True CAC = $500. Channel B: LinkedIn costs $100 per qualified lead but closes 25%. True CAC = $400. The more expensive channel produces a cheaper customer. If you optimized on CPL alone, you would kill the better channel.

This is exactly why platform ROAS is dangerous. Meta might show a $10 cost per lead with a 2% close rate (CAC $500), while Google shows $15 per lead with a 5% close rate (CAC $300). If you trust Meta's conversion number, you over invest in the wrong platform.

Add these columns to your dashboard. Use the same SUMIFS approach to pull sales data from your CRM. If you use a tool like HubSpot, you can export closed won deals by source and date directly.

5. Step 4: Reallocate Budget Based on Actionable Data (The 70/30 Rule)

Now you have real numbers. Time to act. Follow the 70/30 rule: move 70% of your monthly budget to the channel with the lowest true CAC, and keep 30% for testing other channels or new angles. This protects you from platform shifts while maximizing current winners.

Set up a notification in Google Sheets: go to Tools > Notification Rules, set it to email you when any cell in your CPL column changes by more than 20% week over week. When a channel's cost spikes, you catch it immediately instead of a month later.

Review this dashboard weekly for the first month to establish baselines. After that, a monthly check is sufficient as long as your data imports are automated.

Case study: A B2B agency we worked with was spending 60% of their budget on Meta Ads because the platform showed a 3x ROAS. Our dashboard revealed a true CAC of $720 on Meta versus $470 on LinkedIn. They shifted 40% of their Meta budget to LinkedIn over 8 weeks. CAC dropped 35% and overall lead volume remained flat because the LinkedIn leads closed faster. They saved $12,000 in wasted ad spend in three months.

For more on identifying the metrics that actually matter, read the only 5 numbers your business needs.

6. Common Pitfalls and How to Avoid Them (Saving Hours of Headaches)

Three mistakes kill most dashboards before they produce useful data.

Mixing time zones. Meta Ads uses Pacific Time. Google Ads uses your account timezone. Your CRM uses your local time. Always convert all dates to a single timezone in Sheets. Add a column that converts using =DATEVALUE(A2) + (8/24) for Pacific to Eastern, adjust as needed.

Ignoring offline conversions. If your sales team closes deals over the phone, your CRM might not capture the source unless you integrate call tracking. Use a tool like CallRail or import offline conversion data into Google Ads manually. Otherwise your CAC calculation excludes your highest value channel.

Overcomplicating from day one. Start with three columns: cost, qualified leads, customers. That's enough to find your best channel. Add more columns later. Analysis paralysis stops most people from building anything.

Avoid these common GA4 and Meta Pixel setup mistakes early to save hours later.

Where to Go Next

This dashboard is the minimum viable tracking system for any serious ad spender. Once you have it working, you can expand to lifetime value (LTV), payback period, and channel level ROAS that actually ties to real revenue. But first, get the foundation right.

If you would rather have this built for you, stop guessing and get a complete picture. See exactly where your site and funnel are leaking leads, in minutes with our free AI audit. It tells you what your platform reports won't.

Cover photo by Google DeepMind on Unsplash.