How I Used BigQuery to Fix a Company’s Marketing Step-by-Step

Share NOW

Read the Master Article: BigQuery Alternatives, BigQuery Competitors: The Ultimate Data Warehouse Deathmatch

Step 1: Pulling the Data—Finding the Hidden Gold

First thing I did? Ripped an export from Google Ads and Google Analytics into BigQuery. Their PPC team had been running ads like blindfolded drunks at a dartboard – spending big but had no clue which clicks actually led to revenue.

I needed to answer a basic but critical question:

Which keywords and audiences actually made the company money?

So, I ran this SQL query in BigQuery to merge ad click data with actual conversions:

SELECT
search_term,
campaign_name,
SUM(cost) AS total_spend,
SUM(conversions) AS total_conversions,
SUM(revenue) AS total_revenue,
(SUM(revenue) / SUM(cost)) AS ROAS
FROM `company_ads_data.google_ads_performance`
GROUP BY search_term, campaign_name
ORDER BY ROAS DESC;

What I Found:

  • 50% of ad spend was wasted on non-converting keywords.
  • Hyper-specific brand+product searches (e.g., “3D instructions for Grill 2023”) had three times the ROAS but were getting no budget.
  • The biggest CPC bleed was on generic queries like “how to build a table” – people who weren’t looking for a structured 3D guide.

Step 2: Slashing Wasted Spend

I took a chainsaw to their ad strategy.

  • Paused every keyword with an ROAS below 1.0.
  • Shifted budget into high-converting, product-specific searches.
  • Rebuilt their negative keyword list to block low-intent searches.

Results: Within four months, CPC dropped by 22%, and overall ROAS doubled.

Step 3: Finding SEO Gaps with BigQuery + Search Console Data

Next up: Why was their organic traffic dead?

I ran this SQL query in BigQuery to pull search terms from Google Search Console:

SELECT
query,
COUNT(*) AS impressions,
SUM(clicks) AS total_clicks,
SUM(clicks) / COUNT(*) AS click_through_rate,
AVG(position) AS avg_position
FROM `company_analytics.search_console_data`
GROUP BY query
ORDER BY impressions DESC;

What I Found:

  • The company was showing up for thousands of assembly-related searches… but their CTR sucked because their meta descriptions and titles were weak.
  • Some product-specific searches (like “assembly guide for grill”) were getting tons of impressions but ranking below position five.

Step 4: SEO Overhaul—Stealing Back Organic Traffic

  • Rewrote meta titles and descriptions to improve CTR.
  • Optimized content for high-impression, low-rank keywords by adding missing information users were searching for.
  • Linked key landing pages internally to pass authority and boost rankings.

Results: Organic traffic jumped 40% in six months.

Step 5: Optimizing the Conversion Funnel with BigQuery Data

Final problem: People were landing on the company’s site and bouncing like crazy.

I ran this SQL query in BigQuery to analyze drop-off points:

SELECT
page_url,
COUNT(*) AS total_visits,
SUM(CASE WHEN event_type = ‘conversion’ THEN 1 ELSE 0 END) AS total_conversions,
(SUM(CASE WHEN event_type = ‘conversion’ THEN 1 ELSE 0 END) / COUNT(*)) AS conversion_rate
FROM `company_analytics.website_behavior`
GROUP BY page_url
ORDER BY conversion_rate ASC;

What I Found:

  • The highest drop-off was on pages that didn’t immediately show how the company was better than YouTube tutorials.
  • The signup CTA was below the fold, meaning most people never saw it.

Fixes:

  • Moved the CTA above the fold.
  • Added social proof (logos of brands using the company) to build trust.
  • Created a “Try Before You Buy” feature that let users demo an instruction set before committing.

Results: Conversion rates jumped 30%.

Final Scorecard: BigQuery x Execution = Revenue

  • CPC down 22%
  • Organic traffic up 40%
  • Conversion rates up 30%
  • ROAS doubled

This is how you stop guessing and start running marketing like a tiger.