How to Analyze Google Ads ROAS with SQL
Google Ads will happily tell you your ROAS is 4.2x. Open Stripe, add up what you actually collected, divide by what you spent, and you get 1.8x. Both numbers are "correct" inside their own systems. Only one of them reflects money that hit your bank account.
The gap comes from how Google Ads calculates conversion value. It uses attribution models and pixel fires to estimate revenue. A user clicks your ad, triggers a purchase event three days later, and Google records the full order value as conversion revenue. But the pixel fires on the thank-you page regardless of whether the charge succeeds in Stripe. Failed payments, refunds, disputes, trial-to-paid dropoffs. None of that feeds back into Google Ads by default.
The fix: stop trusting Google's conversion value and compute ROAS from the source data yourself. Ad spend from Google Ads, actual revenue from Stripe, joined on UTM parameters.
The tables
Three tables. Google Ads campaign spend: google_ads.campaigns(campaign_id, campaign_name, cost, impressions, clicks, date). Users with UTM attribution captured at signup: users(id, email, utm_source, utm_medium, utm_campaign, created_at). Actual charges from Stripe: stripe.charges(customer_id, amount, created).
The users table bridges the two. A user signs up with utm_source=google and utm_campaign=brand_exact, then pays via Stripe. That UTM field is the join key Google Ads doesn't give you natively.
True ROAS: spend vs. actual revenue
Start with the aggregate number — total spend versus total collected revenue from users who came through Google Ads.
WITH google_users AS (
SELECT id AS user_id, utm_campaign, created_at
FROM users
WHERE utm_source = 'google' AND utm_medium = 'cpc'
),
actual_revenue AS (
SELECT
gu.utm_campaign,
SUM(sc.amount) / 100.0 AS revenue -- Stripe stores cents
FROM google_users gu
JOIN stripe.charges sc ON sc.customer_id = gu.user_id
WHERE sc.amount > 0
GROUP BY gu.utm_campaign
),
ad_spend AS (
SELECT
campaign_name,
SUM(cost) / 1e6 AS spend -- Google Ads API stores micros
FROM google_ads.campaigns
WHERE date >= '2026-01-01'
GROUP BY campaign_name
)
SELECT
s.campaign_name,
ROUND(s.spend, 2) AS spend,
ROUND(COALESCE(r.revenue, 0), 2) AS revenue,
CASE
WHEN s.spend > 0
THEN ROUND(COALESCE(r.revenue, 0) / s.spend, 2)
ELSE 0
END AS true_roas
FROM ad_spend s
LEFT JOIN actual_revenue r ON r.utm_campaign = s.campaign_name
ORDER BY spend DESCTwo unit traps. Stripe amounts are in cents, so divide by 100. Google Ads API costs are in micros (millionths of the currency unit), so divide by 1,000,000. I've seen dashboards showing 450x ROAS because somebody forgot the micros conversion. Nobody questioned it for two weeks.
The LEFT JOIN from spend to revenue is deliberate. Campaigns that spent money but generated zero revenue should show up as ROAS 0, not vanish from the report.
Fastero
Connect your database. Ask questions. Get dashboards.
Postgres, BigQuery, Snowflake, and 10+ sources — live-connected, AI-powered, no dashboard builder learning curve.
Try free →Campaign-level ROAS by month
The aggregate hides trends. A campaign that returned 3x in Q1 and 0.5x in Q2 averages to a decent-looking number while actively burning money.
WITH google_users AS (
SELECT id AS user_id, utm_campaign, DATE_TRUNC('month', created_at) AS signup_month
FROM users
WHERE utm_source = 'google' AND utm_medium = 'cpc'
),
monthly_revenue AS (
SELECT
gu.utm_campaign,
gu.signup_month,
SUM(sc.amount) / 100.0 AS revenue
FROM google_users gu
JOIN stripe.charges sc ON sc.customer_id = gu.user_id
WHERE sc.amount > 0
GROUP BY gu.utm_campaign, gu.signup_month
),
monthly_spend AS (
SELECT
campaign_name,
DATE_TRUNC('month', date) AS spend_month,
SUM(cost) / 1e6 AS spend,
SUM(clicks) AS clicks
FROM google_ads.campaigns
GROUP BY campaign_name, DATE_TRUNC('month', date)
)
SELECT
s.campaign_name,
s.spend_month,
s.clicks,
ROUND(s.spend, 2) AS spend,
ROUND(COALESCE(r.revenue, 0), 2) AS revenue,
ROUND(COALESCE(r.revenue, 0) / NULLIF(s.spend, 0), 2) AS roas,
ROUND(s.spend / NULLIF(s.clicks, 0), 2) AS cpc
FROM monthly_spend s
LEFT JOIN monthly_revenue r
ON r.utm_campaign = s.campaign_name
AND r.signup_month = s.spend_month
ORDER BY s.spend_month DESC, s.spend DESCNow you can see the trajectory. I include CPC alongside ROAS because a campaign might look fine on ROAS while its cost-per-click has doubled, meaning it's converting harder to compensate. That usually doesn't last.
Keyword-level profitability
Campaign-level tells you which buckets work. Keyword-level tells you which search terms generate paying customers. You'll need a keyword cost table, google_ads.keywords(campaign_id, keyword, cost, clicks, date), from the search terms report.
WITH google_users AS (
SELECT id AS user_id, utm_campaign, utm_term, created_at
FROM users
WHERE utm_source = 'google' AND utm_medium = 'cpc'
AND utm_term IS NOT NULL
),
keyword_revenue AS (
SELECT
gu.utm_term AS keyword,
COUNT(DISTINCT gu.user_id) AS paying_users,
SUM(sc.amount) / 100.0 AS revenue
FROM google_users gu
JOIN stripe.charges sc ON sc.customer_id = gu.user_id
WHERE sc.amount > 0
GROUP BY gu.utm_term
),
keyword_spend AS (
SELECT
keyword,
SUM(cost) / 1e6 AS spend,
SUM(clicks) AS clicks
FROM google_ads.keywords
WHERE date >= '2026-01-01'
GROUP BY keyword
)
SELECT
ks.keyword,
ks.clicks,
ROUND(ks.spend, 2) AS spend,
COALESCE(kr.paying_users, 0) AS paying_users,
ROUND(COALESCE(kr.revenue, 0), 2) AS revenue,
ROUND(COALESCE(kr.revenue, 0) / NULLIF(ks.spend, 0), 2) AS roas,
ROUND(ks.spend / NULLIF(COALESCE(kr.paying_users, 0), 0), 2) AS cost_per_paying_user
FROM keyword_spend ks
LEFT JOIN keyword_revenue kr ON kr.keyword = ks.keyword
WHERE ks.spend > 10 -- filter out noise
ORDER BY ks.spend DESCThis requires utm_term={keyword} in your tracking template. If you're not passing it, you're flying blind at the keyword level. Fix that before anything else.
cost_per_paying_user is often more useful than ROAS for keywords. A keyword with $2,000 spend, 3 paying users, and $5,000 revenue looks great at 2.5x ROAS. But $667 per paying customer on an $800 ACV means $133 of margin per customer. That's tight.
The double-counting problem
Here's a gotcha that biases your numbers. A user clicks Ad A on Monday, doesn't convert, clicks Ad B on Thursday, and signs up. Google Ads attributes a conversion to both campaigns (depending on your attribution model). If you report Google's conversion numbers, you've double-counted the revenue.
The SQL approach above avoids this naturally because users.utm_campaign captures the last click only. One user, one UTM, one revenue attribution. Campaign B gets all the credit even if campaign A did the awareness work. For budget allocation decisions, that's the right trade-off. You want to know which campaigns close, not which campaigns "assist."
Time-lagged ROAS: cohorted view
A click today might not convert for two weeks. Measure ROAS within the same month the spend happened and you'll systematically undercount revenue from longer-cycle campaigns. The fix is cohort-based ROAS: group users by signup month, then measure revenue over fixed windows after signup.
WITH google_users AS (
SELECT
id AS user_id,
utm_campaign,
created_at AS signup_date,
DATE_TRUNC('month', created_at) AS cohort_month
FROM users
WHERE utm_source = 'google' AND utm_medium = 'cpc'
),
cohort_revenue AS (
SELECT
gu.utm_campaign,
gu.cohort_month,
COUNT(DISTINCT gu.user_id) AS signups,
SUM(CASE WHEN sc.created < gu.signup_date + INTERVAL '30 days'
THEN sc.amount ELSE 0 END) / 100.0 AS rev_30d,
SUM(CASE WHEN sc.created < gu.signup_date + INTERVAL '60 days'
THEN sc.amount ELSE 0 END) / 100.0 AS rev_60d,
SUM(CASE WHEN sc.created < gu.signup_date + INTERVAL '90 days'
THEN sc.amount ELSE 0 END) / 100.0 AS rev_90d
FROM google_users gu
LEFT JOIN stripe.charges sc
ON sc.customer_id = gu.user_id AND sc.amount > 0
GROUP BY gu.utm_campaign, gu.cohort_month
),
monthly_spend AS (
SELECT
campaign_name,
DATE_TRUNC('month', date) AS spend_month,
SUM(cost) / 1e6 AS spend
FROM google_ads.campaigns
GROUP BY campaign_name, DATE_TRUNC('month', date)
)
SELECT
cr.utm_campaign,
cr.cohort_month,
cr.signups,
ROUND(ms.spend, 2) AS spend,
ROUND(cr.rev_30d, 2) AS rev_30d,
ROUND(cr.rev_60d, 2) AS rev_60d,
ROUND(cr.rev_90d, 2) AS rev_90d,
ROUND(cr.rev_30d / NULLIF(ms.spend, 0), 2) AS roas_30d,
ROUND(cr.rev_60d / NULLIF(ms.spend, 0), 2) AS roas_60d,
ROUND(cr.rev_90d / NULLIF(ms.spend, 0), 2) AS roas_90d
FROM cohort_revenue cr
JOIN monthly_spend ms
ON ms.campaign_name = cr.utm_campaign
AND ms.spend_month = cr.cohort_month
WHERE cr.cohort_month <= CURRENT_DATE - INTERVAL '90 days'
ORDER BY cr.cohort_month DESC, ms.spend DESCThe WHERE cohort_month <= CURRENT_DATE - INTERVAL '90 days' filter matters. Without it, recent cohorts show artificially low 90-day ROAS because they haven't had 90 days yet. Same principle as trailing cohorts in retention analysis. Incomplete windows produce misleading zeros.
The pattern this reveals: some campaigns have a 30-day ROAS of 0.8x but a 90-day ROAS of 2.5x. Those campaigns attract users who take longer to convert but stick around. Killing them based on 30-day numbers is a mistake I've watched teams make repeatedly.
Budget allocation: where to shift spend
With per-campaign true ROAS in hand, rank campaigns and decide where to shift budget.
WITH campaign_performance AS (
-- Use the campaign-level ROAS query from above
SELECT
s.campaign_name,
SUM(s.cost) / 1e6 AS total_spend,
SUM(s.clicks) AS total_clicks,
COALESCE(SUM(r.revenue), 0) AS total_revenue
FROM (
SELECT campaign_name, cost, clicks
FROM google_ads.campaigns
WHERE date >= CURRENT_DATE - INTERVAL '90 days'
) s
LEFT JOIN (
SELECT gu.utm_campaign, SUM(sc.amount) / 100.0 AS revenue
FROM users gu
JOIN stripe.charges sc ON sc.customer_id = gu.id
WHERE gu.utm_source = 'google' AND gu.utm_medium = 'cpc'
AND gu.created_at >= CURRENT_DATE - INTERVAL '90 days'
AND sc.amount > 0
GROUP BY gu.utm_campaign
) r ON r.utm_campaign = s.campaign_name
GROUP BY s.campaign_name
)
SELECT
campaign_name,
ROUND(total_spend, 2) AS spend_90d,
total_clicks,
ROUND(total_revenue, 2) AS revenue_90d,
ROUND(total_revenue / NULLIF(total_spend, 0), 2) AS roas,
CASE
WHEN total_revenue / NULLIF(total_spend, 0) >= 3.0 THEN 'SCALE'
WHEN total_revenue / NULLIF(total_spend, 0) >= 1.0 THEN 'MAINTAIN'
WHEN total_revenue / NULLIF(total_spend, 0) >= 0.5 THEN 'OPTIMIZE'
ELSE 'CUT'
END AS action
FROM campaign_performance
ORDER BY roas DESCThe thresholds (3x for SCALE, 1x for MAINTAIN, 0.5x for OPTIMIZE, below that CUT) aren't universal. Adjust them for your margins. A SaaS product with 85% gross margins can tolerate a lower ROAS threshold than an e-commerce business running at 30% margins.
One caveat: this query won't tell you whether a SCALE campaign can actually absorb more budget. Doubling spend on a branded search campaign that already captures 90% impression share just raises your CPCs. The ranking tells you where to look; the decision to scale still requires checking impression share in the Google Ads UI.
Putting it together
Five queries, five angles: aggregate true ROAS, campaign-level monthly trends, keyword-level profitability, cohorted time-lag view, and the allocation ranking to act on it.
Fastero connects to both Google Ads and Stripe natively, so you can run these queries against your live data without building an ETL pipeline first. Same approach works for Meta Ads too. Set up a dashboard with the campaign-level query, schedule it to refresh daily, and add a Slack alert when any campaign's ROAS drops below your floor.
Try Fastero free — connect Google Ads and Stripe, query your real ROAS in SQL, and stop trusting attribution models with your budget. No credit card required.