Facebook says your ROAS is 4.2x. Google says 3.8x. WooCommerce says you sold $47,000 last month on $15,000 ad spend—a 3.1x return. Which number do you trust? The answer: WooCommerce, because that’s where actual money changed hands. Facebook and Google combined can overclaim ROAS by 30-50% (Industry attribution analysis, 2024) because both platforms take credit for the same purchase. BigQuery is the only place where you can JOIN ad spend with real revenue and see the truth.

Here’s the query that reveals your actual ROAS—and why GA4 can’t run it.

The ROAS Disagreement Problem#

Every ad platform is an unreliable narrator. Facebook says 85 sales, Google says 60, WooCommerce says 50—and only WooCommerce has the receipt. This isn’t a bug. It’s how attribution works when every platform optimizes for self-reported success.

Facebook uses a 7-day click / 1-day view attribution window. Google uses data-driven attribution across its own properties. Neither can see the other’s touchpoints. A customer clicks a Facebook ad on Monday, Googles your brand on Wednesday, and buys on Friday. Facebook claims the sale. Google claims the sale. You paid for both clicks but only shipped one product.

GA4 cross-channel attribution only works with Google-owned channels natively—Facebook ad spend requires manual import or a BigQuery join (Google Analytics documentation, 2025). Translation: GA4 is Google’s scorecard, not yours.

The result? Your combined platform ROAS looks incredible on paper, but your bank account tells a different story.

Why BigQuery Is the Neutral Ground#

BigQuery doesn’t sell ads. It doesn’t have an attribution model to defend. It’s a data warehouse—a place where numbers sit in tables and SQL queries extract truth.

Here’s what makes BigQuery the right place for this calculation:

• No attribution bias: BigQuery doesn’t claim credit for anything. It stores data and answers queries.
• SQL JOIN capability: You can connect any two datasets that share a common key—campaign ID, UTM parameter, customer email hash.
• Affordable: BigQuery SQL JOIN operations cost $5 per TB processed, and most WooCommerce stores stay comfortably in the free tier (Google Cloud BigQuery Pricing, 2025).
• Persistent: Unlike GA4’s 14-month data retention default, BigQuery keeps your data as long as you want it.

WooCommerce recorded revenue is the single source of truth because actual money changed hands—regardless of which platform claims credit.

Getting Your Data Into BigQuery#

The ROAS query needs two tables: one for ad spend and one for revenue. Here’s how each gets there.

Table 1: Facebook Ad Spend Data

Facebook Ads Manager lets you export campaign performance data including spend, impressions, clicks, and campaign IDs. To get this into BigQuery:

• Manual export: Download CSV from Ads Manager, upload to BigQuery. Works but doesn’t scale.
• Scheduled exports via ETL tools: Services like Supermetrics, Funnel.io, or Coupler.io automate daily Facebook Ads → BigQuery syncing.
• Facebook Marketing API: For developers who want direct control, the Marketing API provides programmatic access to spend data.

Your Facebook spend table should include: date, campaign_id, campaign_name, ad_set_name, spend, impressions, clicks.

Table 2: WooCommerce Revenue Data

This is where most store owners get stuck. WooCommerce order data lives in your WordPress database. Getting it to BigQuery in a useful format requires event-level streaming—not just nightly CSV dumps.

Why event-level? Because you need UTM parameters captured at the moment of purchase. The UTM source, medium, and campaign values link each order back to the Facebook campaign that drove it. Without server-side capture, those UTM values are lost to ad blockers (31.5% of users globally, Statista 2024) and Safari’s 7-day cookie limit.

You may be interested in: Looker Studio and BigQuery: The Free Dashboard Stack WordPress Stores Are Missing

Your WooCommerce revenue table should include: order_id, order_date, revenue, utm_source, utm_medium, utm_campaign, customer_email_hash.

The True ROAS Query#

With both tables in BigQuery, here’s the SQL that reveals what your ads actually return:

SELECT
  fb.campaign_name,
  fb.total_spend,
  wc.total_revenue,
  ROUND(wc.total_revenue / NULLIF(fb.total_spend, 0), 2) AS true_roas,
  wc.order_count
FROM (
  SELECT
    campaign_name,
    SUM(spend) AS total_spend
  FROM `your_project.ads.facebook_spend`
  WHERE date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY campaign_name
) fb
LEFT JOIN (
  SELECT
    utm_campaign,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT order_id) AS order_count
  FROM `your_project.woocommerce.orders`
  WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
    AND utm_source = 'facebook'
  GROUP BY utm_campaign
) wc
ON fb.campaign_name = wc.utm_campaign
ORDER BY fb.total_spend DESC

This query does something GA4 cannot: it matches real dollars spent on Facebook with real dollars earned in WooCommerce, per campaign, with no attribution model in between.

The LEFT JOIN is intentional. It surfaces campaigns where you spent money but WooCommerce recorded zero attributable revenue—campaigns that Facebook might still report as successful based on view-through or modeled conversions.

What the Results Reveal#

When you run this query, expect surprises. Common patterns include:

Campaigns with high Facebook-reported ROAS but zero WooCommerce-attributed revenue. These campaigns may be claiming view-through conversions or overlapping with Google Ads touchpoints. They’re not necessarily worthless—but they’re not delivering what Facebook says they are.

Campaigns with modest Facebook-reported ROAS that actually drive the most revenue. Often, your best campaigns are broad targeting or prospecting campaigns that Facebook undervalues because the conversion path includes multiple touchpoints.

The total revenue gap. Sum Facebook’s reported revenue across all campaigns, then sum your BigQuery-matched WooCommerce revenue. The difference is the overclaim—and it’s typically 30-50% of what platforms report.

From Query to Dashboard#

A SQL query answers the question once. A Looker Studio dashboard answers it every morning. Connect BigQuery as a data source in Looker Studio, and you can build a live ROAS dashboard that updates as new spend and revenue data flows in.

Companies with first-party data strategies achieve 2.9x better customer retention than those relying on platform-reported metrics (Industry research, 2025). Accurate ROAS isn’t just about cutting waste—it’s about knowing which campaigns actually build your customer base.

Making the Data Flow Automatic#

The query above works today with a manual data load. But the real power comes from automated streaming—WooCommerce events flowing to BigQuery in real-time with UTM parameters, customer identifiers, and purchase amounts attached at the server level.

Transmute Engine™ streams WooCommerce events to BigQuery via the Streaming Insert API while simultaneously sending conversion data to Facebook CAPI and Google Ads Enhanced Conversions. Because it runs as a first-party Node.js server on your subdomain, it captures UTM parameters server-side—before ad blockers or cookie restrictions can strip them.

Key Takeaways#

• Facebook and Google overclaim combined ROAS by 30-50% because both take credit for overlapping conversions
• WooCommerce is your single source of truth—actual revenue, not modeled conversions
• GA4 cannot natively join Facebook ad spend with revenue—BigQuery fills this gap with a simple SQL JOIN
• BigQuery queries cost $5/TB and most WooCommerce stores stay in the free tier
• Server-side event streaming preserves UTM parameters that ad blockers and cookie limits would otherwise destroy

Your ad platforms will always overclaim. Your WooCommerce database never lies. See how Seresa connects them in BigQuery →