Stop Believing Your Ad Dashboards: Build a Google Script to Find True ROAS and CAC

You are staring at a 4.2x ROAS in Meta Ads Manager and a 3.8x ROAS in Google Ads. Your bank account tells a different story. The cash is not piling up. This gap is not random noise. It is a systematic measurement error built into every ad platform dashboard. The headlines in your dashboard are a sales pitch, not a fact. Let me show you why and how to fix it with a free Google Script that pulls raw data from both platforms and joins it with your actual CRM revenue.

Why Platform ROAS and CAC Are Systematically Biased

The default attribution windows are the first lie. Meta defaults to a 28-day click, 1-day view window. Multiple agency studies, including Tinuiti from 2023 to 2025, show this inflates reported ROAS by 30 to 50 percent compared to a 7-day click-only model. That 4.2x becomes a 2.8x overnight when you use a fair window. Google Ads uses a last-click model that overcounts branded search by roughly 40 percent and undercounts assist channels. Google's own Data-Driven Attribution documentation (2022 update) confirms this. The model rewards itself.

Then Apple's App Tracking Transparency landed in 2021. Meta's reported conversion match rate dropped by about 30 percent. Instead of fixing the reporting, the platform masked the signal loss with algorithmic estimates. A 2024 to 2025 analysis by the agency Optimize found a median discrepancy of 22 percent between platform-reported ROAS and server-side tracked ROAS. The platforms are grading their own homework on a curve. A CXL Institute case study from 2025 concluded that true ROAS is typically 15 to 30 percent lower than platform-reported ROAS when you account for overlapping attribution between Meta and Google. If you are allocating budget based on those numbers, you are burning money.

What You Will Build

You will set up a Google Apps Script that fetches daily cost and conversion data from the Meta Ads API and the Google Ads API using a consistent 7-day click-only attribution window on both sides. It then joins that data with revenue from your CRM (Stripe, Shopify, or any SQL source) to calculate true ROAS and true CAC. The output lands in a Google Sheet with discrepancy columns so you can see exactly how much each platform is inflating its numbers. You then use those real metrics to reallocate budget.

Prerequisites

• A Google account (for Google Sheets and Apps Script)
• Admin access to a Meta Ads account and a Google Ads account
• A Google Cloud project with the Google Ads API enabled
• Basic familiarity with JavaScript and REST APIs
• Your CRM export capability (CSV or API)

Step 1: Setting Up API Access for Meta and Google Ads

You need raw data, not dashboard summaries. Both platforms offer APIs for this.

Meta Ads API Token Setup

Go to the Facebook Developers portal. Create a new app of type "Business". Under Products, add "Marketing API". Generate a long-lived access token (60 days valid) with the ads_management and ads_read permissions. Copy your Ad Account ID from Ads Manager. It looks like act_123456789. Store both in a secure place; you will hardcode them into your script (or use script properties).

// Example: Fetching cost and conversions for last 30 days
var url = 'https://graph.facebook.com/v19.0/act_AD_ACCOUNT_ID/insights' +
  '?fields=cost,actions' +
  '&time_range[since]=2026-06-01&time_range[until]=2026-06-30' +
  '&level=account' +
  '&access_token=YOUR_LONG_LIVED_TOKEN';

Google Ads API Setup

Enable the Google Ads API in your Google Cloud project. Create OAuth 2.0 credentials (Desktop app type). Download the JSON client secret. In your Google Sheet, go to Extensions > Apps Script. Enable the "Google Ads API" advanced service (you will need to link your Cloud project). Also enable "URL Fetch" and "Sheets" services. You will authenticate using OAuth 2.0 flow inside the script. For simplicity, you can use the ScriptApp.getOAuthToken() method with appropriate scopes.

Step 2: Building the Google Script to Fetch Ad Platform Data

Create a new script in your Google Sheet. We will write two functions: one for Meta, one for Google. Both fetch daily cost and conversions (with a 7-day click-only window) and write to a sheet.

function fetchMetaData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Meta_Raw');
  if (!sheet) sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Meta_Raw');
  
  var token = 'YOUR_META_TOKEN';
  var adAccountId = 'act_YOUR_ID';
  var baseUrl = 'https://graph.facebook.com/v19.0/' + adAccountId + '/insights';
  
  var params = {
    'fields': 'campaign_name,cost,actions,impressions,clicks,date_start',
    'time_range': JSON.stringify({'since':'2026-06-01','until':'2026-06-30'}),
    'level': 'campaign',
    'action_attribution_windows': JSON.stringify(['7d_click']),
    'access_token': token
  };
  
  var queryString = Object.keys(params).map(function(k) {
    return k + '=' + encodeURIComponent(params[k]);
  }).join('&');
  
  var response = UrlFetchApp.fetch(baseUrl + '?' + queryString);
  var data = JSON.parse(response.getContentText());
  
  // Write data to sheet (simplified)
  data.data.forEach(function(row) {
    sheet.appendRow([
      row.date_start,
      row.campaign_name,
      'Meta',
      row.cost ? parseFloat(row.cost) : 0,
      row.actions ? (row.actions.find(function(a) { return a.action_type === 'purchase'; }) || {value: 0}).value : 0,
      // revenue not available from API; will join later
    ]);
  });
}

For Google Ads, use the advanced service:

function fetchGoogleData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Google_Raw');
  if (!sheet) sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Google_Raw');
  
  var customerId = '1234567890'; // without dashes
  var query = "SELECT campaign.name, metrics.cost_micros, metrics.conversions, segments.date " +
              "FROM campaign WHERE segments.date BETWEEN '2026-06-01' AND '2026-06-30' " +
              "AND campaign.status = 'ENABLED'";
  
  var rows = AdsApp.search(query);
  while (rows.hasNext()) {
    var row = rows.next();
    sheet.appendRow([
      row.segments.date,
      row.campaign.name,
      'Google',
      row.metrics.costMicros / 1000000,
      row.metrics.conversions
    ]);
  }
}

Normalize attribution windows: Meta's action_attribution_windows parameter enforces 7-day click-only on the API side. Google's API by default returns conversions based on the account-level attribution model. You need to set your Google Ads account to use "Data Driven" or at least "Last Click" with 7-day window. Better yet, import server-side conversions with a consistent 7-day click window. This ensures the comparison is fair.

Step 3: Calculating True ROAS and CAC with CRM Revenue Join

Platforms report only their own attributed conversions. The true cost includes both platforms. The true revenue comes from your CRM, not from the ad platform's pixel. You must join on transaction ID or at least on date and campaign. The most reliable method is to pass a transaction_id parameter via UTM and store it in your CRM at checkout. If you do not have that, you can aggregate by date across platforms and subtract returns.

function calculateTrueROAS() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('True_ROAS');
  if (!sheet) sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('True_ROAS');
  sheet.getRange(1,1,1,8).setValues([['Date','Total Cost','Total Revenue (CRM)','Meta Reported ROAS','Google Reported ROAS','True ROAS','True CAC','Discrepancy %']]);
  
  // Assume you have a CRM revenue sheet with Date and Revenue columns
  var crmSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CRM_Revenue');
  var crmData = crmSheet.getDataRange().getValues();
  var crmMap = {};
  crmData.slice(1).forEach(function(r) { crmMap[r[0]] = r[1]; }); // Date -> Revenue
  
  var rawSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Consolidated_Ad_Costs');
  var rawData = rawSheet.getDataRange().getValues();
  
  var daily = {};
  rawData.slice(1).forEach(function(r) {
    var date = r[0];
    var platform = r[1];
    var cost = r[2];
    var reportedROAS = r[3]; // from platform's own reporting (need to fetch too)
    if (!daily[date]) daily[date] = {totalCost:0, metaCost:0, googleCost:0, metaReportedROAS:[], googleReportedROAS:[]};
    daily[date].totalCost += cost;
    if (platform === 'Meta') { daily[date].metaCost += cost; daily[date].metaReportedROAS.push(reportedROAS); }
    else { daily[date].googleCost += cost; daily[date].googleReportedROAS.push(reportedROAS); }
  });
  
  var rowNum = 2;
  Object.keys(daily).sort().forEach(function(date) {
    var cost = daily[date].totalCost;
    var revenue = crmMap[date] || 0;
    var trueROAS = cost > 0 ? revenue / cost : 0;
    var avgMetaROAS = daily[date].metaReportedROAS.length > 0 ? daily[date].metaReportedROAS.reduce(function(a,b){return a+b;})/daily[date].metaReportedROAS.length : 0;
    var avgGoogleROAS = daily[date].googleReportedROAS.length > 0 ? daily[date].googleReportedROAS.reduce(function(a,b){return a+b;})/daily[date].googleReportedROAS.length : 0;
    var trueCAC = cost / (revenue > 0 ? 1 : 1); // replace with unique purchaser count from CRM
    var discrepancy = avgMetaROAS > 0 ? ((avgMetaROAS - trueROAS) / avgMetaROAS) * 100 : 0;
    
    sheet.getRange(rowNum,1,1,8).setValues([[date, cost, revenue, avgMetaROAS, avgGoogleROAS, trueROAS, trueCAC, discrepancy]]);
    rowNum++;
  });
}

The trick is getting unique purchasers from your CRM. Export a list of orders with date, transaction_id, and refund status. True CAC = total ad cost / number of first-time purchasers within 90 days. If you exclude refunds and returns, you will see CAC can be 2x to 3x higher than what the platform reports. Most platforms only count the initial purchase event; they ignore chargebacks and subscription churn.

Step 4: Interpreting Results for Budget Allocation Decisions

Once your spreadsheet shows the discrepancy percentages, you can move budget away from the platform with the largest inflation gap. In the DTC scenario described earlier, a brand spending $50k on Meta and $30k on Google saw platform-reported ROAS of 4.2x and 3.8x respectively. After building this system, true ROAS came in at 2.8x for Meta and 3.2x for Google. The inflation on Meta was larger because its view-through conversions were double counting with Google's last click credit.

Now consider cohort CAC. Google Ads often delivers higher lifetime value customers in certain verticals due to purchase intent. A seven-day ROAS comparison may favor Meta, but a 90-day true CAC comparison flips the table. Incorporate refund and churn data over 90 days. If you cut Google Ads based on a short window, you are losing your best customers.

A common outcome is shifting budget from 60/40 Meta/Google to 50/50 after adjusting for real CAC. The result is not higher ROAS; it is lower cost per retained customer. That is the only number that pays your bills.

Common Pitfalls

• Using the same conversion window on both platforms: Meta defaults to 28-day click plus 1-day view. Google defaults to 30-day click. Set both to 7-day click-only for fair comparison.
• Ignoring overlapping attribution: A user clicks a Meta ad, then later clicks a Google ad, then buys. Both platforms claim the conversion. Your true ROAS must use total revenue divided by total cost, not the sum of individual platform attributions.
• Not passing transaction IDs consistently: Without a unique transaction ID parameter, you cannot deduplicate user-level conversions. Implement ?transaction_id=ORDERID in your UTM parameters. Many teams skip this step.
• Google Script free quota limits: If you have more than 50 campaigns across two platforms, the 6 minutes per day execution limit and 50,000 URL fetch characters may be insufficient. For larger accounts, use a cloud function like AWS Lambda or a third party tool like Supermetrics.

Next Steps

Once your script is running and you have a few weeks of true ROAS data, start tracking only the five numbers that matter for your weekly review. The discrepancy column will tell you if a platform's algorithmic attribution is getting more or less honest over time. You can also extend this system to include TikTok or LinkedIn Ads by adding similar API fetchers. The architecture is the same: raw cost, raw conversions with a controlled window, and a CRM revenue join.

Building this yourself takes a few hours and saves you from making six-figure allocation mistakes. But not everyone has the time or the interest to become a part-time API engineer. If you would rather see exactly where your site and funnel are leaking leads in minutes, we have built a free AI audit that does the diagnosis without you writing a line of code. Run your free audit here.

Cover photo by Milad Fakurian on Unsplash.