The Google Sheets QUERY function transforms raw marketing data into actionable insights without complex pivot tables or manual filtering. This guide covers 15 production-ready formulas for campaign analysis, performance tracking, and ROI calculation, from filtering Facebook Ads by CPA to calculating week-over-week growth across channels. Each formula includes working code and real-world context you can implement today.
Why QUERY is Essential for Marketing Analytics
Most marketers waste hours copying, pasting, and filtering data in spreadsheets. The QUERY function eliminates this completely.
QUERY uses SQL-like syntax from the Google Visualization API Query Language to filter, sort, calculate, and aggregate data in seconds. Instead of creating five different sheets with manual filters, you write one formula that updates automatically when your source data changes.
The practical difference: A PPC manager analyzing 500 campaigns can identify all campaigns with CPA >$50 and CTR <2% in one formula instead of manually filtering columns and cross-referencing metrics.
What makes QUERY powerful for marketing?
- Handles thousands of rows without performance issues
- Updates automatically when source data changes
- Combines multiple conditions in one formula
- Creates custom calculated fields on the fly
- Works with any data source (manual entry, CSV imports, or automated connectors)
For marketers managing Google Sheets, this is the single most versatile function available. According to Google's official documentation, QUERY can perform operations that typically require multiple functions or pivot tables.
15 Essential QUERY Formulas for Marketing
1. Filter Campaigns by Cost Per Acquisition (CPA)
Identify campaigns that need immediate optimization based on acquisition cost thresholds.
=QUERY(A2:F, "SELECT A, B, C, D, E, F WHERE F > 50 ORDER BY F DESC", 1)
Breaking it down:
A2:F= Your data range (Campaign Name, Channel, Spend, Conversions, Revenue, CPA)WHERE F > 50= Shows only campaigns where CPA (column F) exceeds $50ORDER BY F DESC= Sorts highest CPA first1= Includes header row
Your Google Ads sheet has 200 campaigns. Instead of manually filtering, this formula instantly shows which campaigns are eating budget with poor conversion efficiency. Adjust the threshold (50) based on your target CPA.
2. Calculate ROAS by Marketing Channel
Compare return on ad spend across Facebook, Google, LinkedIn, TikTok, etc.
=QUERY(A2:D, "SELECT A, SUM(C)/SUM(B) WHERE B > 0 GROUP BY A LABEL SUM(C)/SUM(B) 'ROAS'", 1)
This formula groups your data by channel (column A), then divides total revenue by total spend to calculate ROAS. The WHERE B > 0 clause excludes channels with zero spend. The LABEL parameter names the calculated column "ROAS" for clarity.
Manual ROAS calculations break when you add new campaigns. This formula automatically recalculates as you update your data, giving you real-time channel comparison.
3. Identify High-Performing Keywords (CTR + Conversion Rate)
=QUERY(A2:G, "SELECT A, D, F WHERE D > 3 AND F > 5 ORDER BY F DESC", 1)
This returns keywords with CTR above 3% AND conversion rate above 5%, sorted by conversion rate. According to 2025 Google Ads benchmarks, the average search ad CTR is approximately 6.66% across industries, though this varies significantly by sector.
SEO and PPC teams can quickly identify "unicorn keywords" that deserve more budget allocation or content investment. These are your money makers.
4. Compare Week-over-Week Performance
Track trends and spot anomalies by comparing current week to previous week.
=QUERY(A2:E, "SELECT A, SUM(D), SUM(E) WHERE B >= date '2025-12-02' AND B <= date '2025-12-08'
GROUP BY A", 1)
Date handling in QUERY: Use the format date 'YYYY-MM-DD' for proper date filtering. This formula filters data for the last 7 days, groups by Campaign (A), and sums Clicks (D) and Conversions (E).
Create two versions, one for current week, one for previous week, then add a column calculating percentage change. Update the date range weekly or reference cells with date '"&TEXT(A1,"yyyy-mm-dd")&"' for dynamic dates.
5. Segment Performance by Device Type
Understand how mobile, desktop, and tablet users behave differently.
=QUERY(A2:F, "SELECT B, COUNT(A), AVG(E), SUM(F) GROUP BY B LABEL COUNT(A) 'Campaigns',
AVG(E) 'Avg CPC', SUM(F) 'Total Conv'", 1)
This groups by Device (B), counts number of campaigns per device, calculates average CPC, and sums total conversions.
Why segment by device: Mobile CPC is often 30-40% lower than desktop, but conversion rates can vary dramatically by industry. This formula reveals if you're overspending on low-converting devices.
6. Analyze Geographic Performance (Top Cities/Regions)
Find which locations generate the best ROI.
=QUERY(A2:E, "SELECT A, SUM(C), SUM(D), SUM(D)/SUM(C) WHERE C > 100 GROUP BY A ORDER BY
SUM(D)/SUM(C) DESC LIMIT 10", 1)
Shows Location (A), Total Spend, Total Revenue, ROAS, but only for locations with meaningful spend (>$100). The LIMIT 10 clause returns only the top 10 locations sorted by ROAS.
An e-commerce brand discovers Austin converts at 2.8x ROAS while Chicago is at 1.1x. This data justifies reallocating budget or creating location-specific campaigns.
7. Filter by Conversion Rate Threshold
Quickly identify underperforming campaigns that need pause or optimization.
=QUERY(A2:F, "SELECT A, B, E, F WHERE F < 2 AND E > 1000 ORDER BY E DESC", 1)
Returns campaigns with conversion rate below 2% (F < 2) that have significant traffic (E > 1000 clicks). Sorts by clicks to prioritize high-traffic problems.
A campaign with 10 clicks and 0 conversions isn't statistically significant. Filtering for 1000+ clicks ensures you're optimizing campaigns with real data, not noise.
8. Calculate Average CTR Across Campaign Types
Benchmark search vs. display vs. video campaigns.
=QUERY(A2:E, "SELECT B, AVG(D), AVG(E) GROUP BY B LABEL AVG(D) 'Avg Impressions',
AVG(E) 'Avg CTR%'", 1)
Groups by Campaign Type (B) and calculates average impressions and CTR per type. This is useful for comparing search (typically 3-6% CTR depending on industry) vs. display (0.5-1% CTR).
According to multiple 2025 industry reports, search ads average 3.17% CTR while display ads average 0.46% across industries. This formula helps you compare your performance to these benchmarks.
9. Find Top Spending Campaigns (Budget Analysis)
=QUERY(A2:D, "SELECT A, SUM(C) GROUP BY A ORDER BY SUM(C) DESC LIMIT 20 LABEL SUM(C)
'Total Spend'", 1)
Shows top 20 campaigns by total spend. Helps identify if budget is concentrated in a few campaigns.
If your top 5 campaigns consume 80% of budget, you're highly dependent on those campaigns. This formula helps identify concentration risk in your media spend.
10. Identify Negative ROAS Campaigns
Stop the bleeding by pausing campaigns that lose money.
=QUERY(A2:E, "SELECT A, B, D, E, E/D WHERE D > 0 AND E/D < 1 ORDER BY D DESC
LABEL E/D 'ROAS'", 1)
Calculates ROAS (Revenue/Spend) on the fly and shows only campaigns where ROAS < 1 (losing money). Sorts by spend to prioritize big budget drains.
A campaign spending $500/day at 0.6 ROAS loses $200/day. Over 30 days, that's $6,000 in losses. This formula catches these fast.
11. Segment by Ad Format (Image vs. Video vs. Carousel)
Which creative formats drive performance?
=QUERY(A2:F, "SELECT C, COUNT(A), AVG(E), SUM(F) GROUP BY C ORDER BY AVG(E) DESC LABEL COUNT(A)
'Ad Count', AVG(E) 'Avg CTR', SUM(F) 'Total Conv'", 1)
Groups by Ad Format (C), counts total ads, averages CTR, sums conversions. Reveals which formats work best for your specific audience and campaign goals.
12. Calculate Average Order Value by Traffic Source
Find which channels bring high-value customers.
=QUERY(A2:E, "SELECT A, SUM(D)/SUM(C) GROUP BY A ORDER BY SUM(D)/SUM(C) DESC LABEL
SUM(D)/SUM(C) 'AOV'", 1)
Divides Total Revenue (D) by Total Orders (C) to calculate average order value per source. Sorts to show highest AOV sources first.
If organic search has $85 AOV while paid social has $45 AOV, you might prioritize SEO investment even if paid social has lower CPA. Customer lifetime value matters as much as acquisition cost.
13. Find Campaigns Exceeding Budget Thresholds
Monitor campaigns approaching or exceeding monthly budgets.
=QUERY(A2:D, "SELECT A, SUM(C), D WHERE SUM(C) > D GROUP BY A, D LABEL SUM(C) 'Actual Spend',
D 'Budget'", 1)
Compares actual spend (C) to budget (D) and shows only campaigns where actual > budget. Prevents overspend surprises before they damage monthly P&L.
14. Analyze Performance by Time of Day/Day of Week
Optimize ad scheduling and bid adjustments.
=QUERY(A2:F, "SELECT B, AVG(D), AVG(E), AVG(F) GROUP BY B ORDER BY AVG(F) DESC LABEL AVG(D)
'Avg CPC', AVG(E) 'Avg CTR', AVG(F) 'Avg Conv Rate'", 1)
Groups by Hour or Day (B), calculates average metrics per time period. Identifies high-performing hours/days for dayparting strategies.
Many B2B campaigns see 40-50% higher conversion rates during business hours (9am-5pm weekdays). This formula quantifies when to increase bids.
15. Create Multi-Metric Pivot-Style Summaries
Build executive dashboards with aggregated metrics across multiple dimensions.
=QUERY(A2:G, "SELECT A, B, SUM(D), SUM(E), SUM(F), SUM(E)/SUM(D), SUM(F)/SUM(D) WHERE D > 0
GROUP BY A, B ORDER BY SUM(E) DESC LABEL SUM(D) 'Clicks', SUM(E) 'Conversions',
SUM(F) 'Revenue', SUM(E)/SUM(D) 'Conv Rate', SUM(F)/SUM(D) 'Rev/Click'", 1)
Groups by Channel (A) and Campaign Type (B). Calculates total clicks, conversions, revenue, plus calculated metrics (conversion rate, revenue per click). Creates comprehensive performance overview.
Instead of maintaining 5-10 separate reports, this single formula generates executive-ready summaries that update automatically when source data changes.
Advanced QUERY Tips for Marketing Data
Combining Multiple Data Sources
When you have data from Google Ads in one sheet and Facebook Ads in another, use QUERY with {} array notation:
=QUERY({GoogleAds!A2:E; FacebookAds!A2:E}, "SELECT Col1, SUM(Col4) GROUP BY Col1", 1)
This combines data from multiple sheets before querying. You can do this manually, use platform exports, or automate with tools like Dataslayer if pulling from multiple advertising platforms into one consolidated sheet. For more on Google Sheets automation, see our guide on Google Sheets AI features.
Handling Date Ranges Dynamically
Instead of hardcoding dates, reference cells:
=QUERY(A2:E, "SELECT A, SUM(C) WHERE B >= date '"&TEXT(F2,"yyyy-mm-dd")&"' AND B <= date '"
&TEXT(F3,"yyyy-mm-dd")&"' GROUP BY A", 1)
Put start date in F2 and end date in F3. The formula updates automatically when you change those dates.
Avoiding Common QUERY Errors
"Unable to parse query string": Usually caused by mixing column references (A, B, C) vs. generic columns (Col1, Col2, Col3). Use Col1 notation when combining sheets with {}.
Empty results: Check that your WHERE conditions aren't too restrictive. Remove conditions one at a time to identify the issue.
Date formatting issues: Google Sheets stores dates as numbers. Use date '2025-12-09' format in QUERY, not cell references with raw dates.
For marketers working with larger datasets, consider our guide on BigQuery for Marketers when Sheets starts hitting performance limits.
Comparison Table: QUERY vs. Manual Analysis
Key takeaway: QUERY has higher initial learning curve but pays off with automatic updates and zero maintenance time.
FAQ: Google Sheets QUERY Function for Marketing
What is the Google Sheets QUERY function and why should marketers use it?
The QUERY function uses SQL-like syntax to filter, sort, aggregate, and analyze data in Google Sheets without manual manipulation. Marketers should use it because it transforms hours of copy-paste-filter work into a single formula that updates automatically. When you're analyzing hundreds of campaigns across multiple platforms, QUERY becomes essential for maintaining sanity and accuracy. The function handles complex multi-condition filtering (show me campaigns with CPA > $50 AND CTR < 2% AND impressions > 1000) that would require multiple manual steps otherwise.
Can QUERY function handle data from multiple advertising platforms?
Yes, but with important caveats. QUERY works perfectly with any data already in Google Sheets, regardless of source. You can manually export CSVs from Facebook Ads, Google Ads, LinkedIn Ads, etc., paste them into different sheets, and use QUERY's array notation {Sheet1!A2:E; Sheet2!A2:E} to combine them. The challenge is ensuring consistent column structure and handling different date formats. For automated multi-platform analysis, you'll need to either write Google Apps Script to fetch API data or use dedicated connectors that export to Sheets automatically.
What's the difference between QUERY and FILTER functions in Google Sheets?
FILTER is simpler and faster for basic filtering, while QUERY is more powerful for complex analysis. Use FILTER when you need to show rows meeting simple conditions: =FILTER(A2:E, C2:C>100) shows rows where column C exceeds 100. Use QUERY when you need aggregation (GROUP BY), calculations (SUM, AVG), sorting, or multiple conditions with AND/OR logic. FILTER returns the exact columns you feed it; QUERY lets you select specific columns, create calculated fields, and reshape data. For marketing analysis, QUERY is usually the better choice because you typically need to sum spending, calculate ROAS, group by campaign type, and sort by performance, all tasks FILTER can't handle.
How do I fix "Unable to parse query string" errors in QUERY?
This error indicates syntax problems in your QUERY statement. The most common causes:
- Incorrect column references: use letter notation (A, B, C) for single-sheet queries or Col notation (Col1, Col2, Col3) when combining multiple sheets.
- Missing quotes around text: string comparisons need quotes:
WHERE A = 'Facebook'notWHERE A = Facebook. - Date formatting issues: dates must use
date '2025-12-09'format. - Reserved words: if column headers use SQL reserved words like "date" or "order," wrap them in backticks. To debug, simplify your formula to just
SELECT *, then add conditions one at a time until the error reappears.
Can I use QUERY with real-time data from advertising platforms?
QUERY itself doesn't fetch real-time data, it only analyzes data already in your spreadsheet. To get near-real-time marketing data, you have three options:
- Manual exports: download CSVs from advertising platforms daily and paste into your sheet.
- Google Sheets add-ons or connectors: tools that authenticate with platform APIs and populate your sheet automatically on schedules you define.
- Google Apps Script: write custom code to call advertising APIs. Once data is in your sheet through any of these methods, QUERY formulas update automatically. The "real-time" part depends on your data source update frequency.
What are the performance limitations of QUERY with large marketing datasets?
QUERY handles 10,000-50,000 rows comfortably in most Google Sheets, but performance degrades beyond that. Specific limitations:
- Sheet cell limits: Google Sheets caps at 10 million cells total.
- Calculation timeouts: complex QUERY formulas with multiple aggregations on 50,000+ rows may timeout.
- Memory constraints: combining data from multiple sheets with
{}notation multiplies memory usage. For enterprise-scale marketing data (millions of rows), consider migrating to Google BigQuery, or use Sheets only for summary-level data after pre-aggregating in a database.
How do I make QUERY formulas easier to maintain and share with my team?
Maintainability requires upfront planning. Best practices:
- Use named ranges: instead of
A2:G, name your data range "CampaignData". - Document column structure: add a comments row explaining what each column contains.
- Store WHERE conditions in cells: put threshold values in separate cells and reference them using concatenation.
- Add error handling: wrap formulas in IFERROR:
=IFERROR(QUERY(...), "No data matches your filters"). - Create a formula library sheet: document your team's most-used QUERY formulas with explanations.
- Protect formula rows: use Google Sheets protection to prevent accidental deletion.
Conclusion: From Data Overload to Data Clarity
The 15 QUERY formulas in this guide solve real problems marketing teams face daily: identifying budget-draining campaigns, calculating ROI by channel, comparing performance across time periods, and building automated reports that update without manual intervention.
Start with formulas 1-5 (filter by CPA, calculate ROAS, identify high performers, compare week-over-week, segment by device). These solve 80% of common marketing analysis needs. Once comfortable, layer in geographic analysis, ad format comparison, and multi-metric summaries.
The goal isn't to memorize SQL syntax: it's to stop spending 10 hours weekly on manual reporting and redirect that time toward strategy, testing, and optimization.
Want to automate data collection from multiple advertising platforms into Google Sheets? Try Dataslayer free for 15 days to connect Google Ads, Facebook Ads, LinkedIn Ads, TikTok Ads, and 50+ other platforms to Sheets, Looker Studio, BigQuery, or Power BI without manual exports. Learn more about our MCP integration for AI-powered data analysis.
Pick one formula from this list, apply it to your current campaign data, and time how long it takes versus your manual process. That time savings, multiplied by 52 weeks, is your annual ROI from learning QUERY.
