Build a Google Script to Find True ROAS & CAC for Meta and Google Ads

Your Ad Platform ROAS is a Lie (and It's Costing You Money)

Every morning you open Meta Ads Manager and Google Ads. Meta says your ROAS is 3.8x, Google says 4.2x. You feel good. But your bank account tells a different story. That's because platforms grade their own homework.

A 2023 ReBid analysis found that Meta's default last-click attribution inflates ROAS by 20-40% compared to data-driven models. Google switched to data-driven attribution in early 2024, but legacy campaigns still use last-click unless manually updated. Add in the fact that true CAC includes agency fees, tool subscriptions, and salaries (a ProfitWell 2023 study showed 43% of companies ignore these, understating CAC by 15-30%), and your numbers are fiction.

The fix isn't more expensive tools. It's a free Google Apps Script that pulls raw data from the Meta Marketing API and Google Ads API, forces a uniform attribution window, and calculates honest ROAS and CAC in a single dashboard.

Prerequisites

• A Google account (for Sheets and Apps Script)
• Meta Ads Manager access with a System User token (or long-lived user token) with ads_read permission
• Google Ads account linked to a Google Cloud Project with the Google Ads API enabled and a service account created
• Basic familiarity with JavaScript (the script uses vanilla JavaScript with UrlFetchApp)
• An OAuth2 library installed in Apps Script (Library ID: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMjxT63)

Step 1: Set Up Your Google Sheet and API Access

Create a new Google Sheet. Rename three tabs: Raw_Meta, Raw_Google, Dashboard. This keeps raw data separate from calculations, making debugging easier.

Meta Marketing API Access

Go to the Meta Business Suite, navigate to Business Settings > Users > System Users. Create a system user with ads_read permission. Generate a token. This token lasts 60 days; note that you'll need to refresh it or set up auto-refresh via a long-lived token. Use the v21 endpoint (v22 likely by 2026 but compatible).

Google Ads API Access

Enable the Google Ads API in your Google Cloud Console. Create a service account, download the JSON key. Then in your Google Ads Manager account, link the service account's email as a user with Standard access. In Apps Script, we'll use the OAuth2 library to authenticate with the service account's private key and client email.

Step 2: Write the Script, Fetch Meta Ads Data

Open Extensions > Apps Script. Create a new script file. Here's the skeleton for fetching Meta data:

function fetchMetaData() {
  var token = 'YOUR_META_ACCESS_TOKEN';
  var adAccountId = 'act_123456789';
  var baseUrl = 'https://graph.facebook.com/v21.0/' + adAccountId + '/insights';
  
  var params = {
    'time_range': JSON.stringify({'since':'2026-05-01','until':'2026-05-31'}),
    'fields': 'campaign_name,spend,actions,action_values',
    'action_attribution_windows': ['7d_click'],
    'level': 'campaign',
    'limit': 100
  };
  
  var url = baseUrl + '?' + buildQueryString(params);
  var options = {'method':'GET','headers':{'Authorization':'Bearer ' + token},'muteHttpExceptions':true};
  
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  
  // Handle pagination
  var allRows = [];
  var nextUrl = data.paging ? data.paging.next : null;
  allRows = parseMetaRows(data.data);
  
  while (nextUrl) {
    var nextResponse = UrlFetchApp.fetch(nextUrl, options);
    var nextData = JSON.parse(nextResponse.getContentText());
    allRows = allRows.concat(parseMetaRows(nextData.data));
    nextUrl = nextData.paging ? nextData.paging.next : null;
  }
  
  // Write to Raw_Meta sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw_Meta');
  sheet.clear();
  sheet.getRange(1,1, allRows.length, allRows[0].length).setValues(allRows);
}

function parseMetaRows(rows) {
  return rows.map(function(row) {
    var leads = 0;
    var revenue = 0;
    if (row.actions) {
      var leadAction = row.actions.find(a => a.action_type === 'lead');
      leads = leadAction ? parseInt(leadAction.value) : 0;
    }
    if (row.action_values) {
      var purchaseAction = row.action_values.find(a => a.action_type === 'purchase');
      revenue = purchaseAction ? parseFloat(purchaseAction.value) : 0;
    }
    return [row.campaign_name, parseFloat(row.spend), leads, revenue];
  });
}

// Quick helper to build query string
function buildQueryString(params) {
  return Object.keys(params).map(function(key) {
    return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]);
  }).join('&');
}

Key points: Force the 7-day click attribution window (action_attribution_windows: ['7d_click']) to match Google's default. Pagination is essential; large accounts can return hundreds of rows. Batch writes not needed here because UrlFetchApp can handle up to 50 calls per minute; pagination loops stay under that.

Step 3: Fetch Google Ads Data

Create another function for Google Ads. Use the OAuth2 library for service account authentication.

function fetchGoogleData() {
  var service = OAuth2.createService('GoogleAds')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setClientId('YOUR_CLIENT_ID')
    .setClientSecret('YOUR_CLIENT_SECRET')
    .setPrivateKey('YOUR_PRIVATE_KEY') // from service account JSON
    .setIssuer('SERVICE_ACCOUNT_EMAIL')
    .setScope('https://www.googleapis.com/auth/adwords');
  
  var token = service.getAccessToken();
  
  var query = "SELECT campaign.name, metrics.cost_micros, metrics.conversions, metrics.conversions_value " +
              "FROM campaign WHERE segments.date BETWEEN '2026-05-01' AND '2026-05-31'";
  var url = 'https://googleads.googleapis.com/v15/customers/YOUR_CUSTOMER_ID/googleAds:searchStream';
  var options = {
    'method': 'POST',
    'headers': {
      'Authorization': 'Bearer ' + token,
      'Content-Type': 'application/json'
    },
    'payload': JSON.stringify({'query': query})
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  
  // Parse rows
  var rows = [];
  data.results.forEach(function(result) {
    var campaign = result.campaign;
    var metrics = result.metrics;
    rows.push([
      campaign.name,
      metrics.costMicros / 1e6,  // convert micros to dollars
      metrics.conversions,
      metrics.conversionsValue
    ]);
  });
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw_Google');
  sheet.clear();
  if (rows.length > 0) {
    sheet.getRange(1,1, rows.length, rows[0].length).setValues(rows);
  }
}

Run both functions from the Apps Script editor to populate the raw data tabs. If you have more than 10 campaigns, add pagination for Google Ads using the nextPageToken field in the response.

Step 4: Calculate True ROAS and CAC in the Dashboard

Now the sheet does the heavy lifting. In the Dashboard tab, create these formulas:

| Metric          | Formula (in cell)                                                                      |
|-----------------|----------------------------------------------------------------------------------------|
| Total Meta Spend| =SUM(Raw_Meta!B:B)                                                                     |
| Total Google Spend| =SUM(Raw_Google!B:B)                                                                 |
| Total Leads     | =SUM(Raw_Meta!C:C) + SUM(Raw_Google!C:C)                                              |
| Total Revenue   | =SUM(Raw_Meta!D:D) + SUM(Raw_Google!D:D)                                              |
| Overhead Buffer | 2500  (hardcode your monthly agency fees, tools, salaries)                             |
| True CAC        | =(B2+B3+B6)/B4                                                                          |
| True ROAS       | =B5/(B2+B3+B6)                                                                          |

Why include overhead? True CAC isn't just ad spend. A 2024 First Page Sage benchmark shows average B2B SaaS CAC by channel: LinkedIn $205, Google $98, Facebook $73. But if you're paying an agency $2,000/month and using a $500/month connector, your real CAC is 20-30% higher. Hard-coding the buffer makes it honest.

For Meta leads, ensure your action type is lead (not offsite_conversion). Google Ads conversions field counts any conversion action set up in the account. If you track both leads and purchases, modify the formulas accordingly. Use segments.date for Google and time_range for Meta to keep the same date window.

Step 5: Understanding Your Output

Say Jane runs a DTC brand spending $10k Google, $8k Meta. Platform reports show combined ROAS 4.0x and CPL $35. Her Dashboard shows:

• True Combined ROAS: 3.1x
• True CAC: $51 (including $2,500 overhead)
• Meta CPL: $48 vs reported $32

She reallocates 20% budget to Google. The script reveals Meta's view-through attribution inflating revenue, and the overhead buffer highlights that her profitable campaigns barely break even. Without the script, she'd keep scaling a losing channel.

A 2024 Clix Marketing experiment showed switching Meta attribution from 7-day click to 1-day click reduced reported CPL by 34% while actual customer value stayed flat. Your forced 7-day window eliminates that bias.

Common Pitfalls & Optimization Tips

1. Attribution mismatch: If Google Ads uses a different attribution model (e.g., 30-day click for some conversion actions), your data will still be inconsistent. In the GAQL query, filter by conversion actions with a specific attribution model or use segments.date with the same lookback window.
2. Token expiry: Meta system user tokens last 60 days. Set up a reminder or use a long-lived token (extend to 60 days). For Google, OAuth tokens can be refreshed automatically by the library if you store refresh tokens.
3. Rate limits: Apps Script allows 50 UrlFetchApp calls per minute per user. For accounts with >20 campaigns, pagination loops can hit this. Batch requests by increasing the limit param (max 500), or add a 1-second sleep between pages.
4. Missing revenue data: Meta's action_values only populate if your pixel passes value and currency. If you use CAPI, ensure server events include those parameters. Otherwise, your True ROAS will be understated.
5. Google Ads cost_micros: Always divide by 1,000,000. Newcomers often forget this and get costs that are off by 6 orders of magnitude.

Next Steps

This script is a starting point. Once your data is flowing, consider these enhancements:

• Add a trigger to run the script daily at 2 AM (using Apps Script triggers). Pull yesterday's complete data, not live numbers.
• Include a dimension for campaign objective (awareness vs conversion) to separate brand and performance spend.
• For large accounts (500+ campaigns), route data through BigQuery first using Apps Script to batch load, then query from Sheets. This avoids timeout issues.

If building and maintaining this script feels like a distraction from your core business, we get it. The setup takes 4-8 hours, and you'll spend another hour monthly on token refreshes and debugging. A managed approach can get you the same honest metrics without the maintenance.

Run a free AI audit to see exactly where your site and funnel are leaking leads, in minutes. Or if you're scaling with complex multi-channel ads, our Growth/Scale retainers (from $2,500/mo) include a custom dashboard like this, wired into your accounts. No guesswork, just data you can trust.

Cover photo by ClickerHappy on Pexels.