Google Marketing Intelligence Project

AI-Powered Advertising Performance & SEO Optimization System

Project Overview

Build an internal system to track both paid advertising (Google Ads) and organic search performance (Google Search Console), using AI to optimize marketing spend and SEO strategy based on actual business results.

Key Questions We'll Answer:

Paid Advertising (Google Ads):

Organic Search (Search Console):

Combined Analysis:

Business Problem

Current Situation:

What We Need:

Current Status

✅ Phase 1 Completed - Google Search Console Integration

Search Console integration is LIVE and working!

Infrastructure:

Data Collection:

Dashboard UI:

What's Working:

✅ Phase 1 Complete - Dual-Model AI Analysis System

IMPLEMENTED AND LIVE!

AI Services:

Features:

Cost: ~$0.05-0.11 per week for dual-model analysis

Why Dual-Model?

Technical Implementation:

Email Notifications:

Lessons Learned:

⏳ Phase 2 Waiting - Google Ads

Waiting on:

Ready to build when approved:

🔮 Phase 3 Future - Combined Analysis

Not yet started:

What We'll Build

Phase 1: Google Search Console Integration (CAN START NOW!)

Database Tables - Search Console:

# search_console_queries
- id
- query (search term people used)
- clicks (integer)
- impressions (integer)
- ctr (decimal - click-through rate)
- position (decimal - average ranking position)
- device_type (desktop, mobile, tablet)
- country
- date
- timestamps

# search_console_pages
- id
- page_url (which page on our site)
- clicks (integer)
- impressions (integer)
- ctr (decimal)
- position (decimal)
- date
- timestamps

# search_console_site_analytics
- id
- report_date
- total_clicks
- total_impressions
- average_ctr
- average_position
- top_queries (jsonb - array of top 10)
- top_pages (jsonb - array of top 10)
- ai_analysis (jsonb - SEO recommendations)
- timestamps

Background Jobs - Search Console:

Phase 2: Google Ads Integration (READY TO BUILD)

Database Tables - Google Ads:

# google_ads_campaigns
- id
- campaign_id (from Google Ads)
- campaign_name
- campaign_type (search, display, etc.)
- location (Los Angeles, Orange County, etc.)
- status
- currency_code
- login_customer_id
- timestamps

# google_ads_keywords
- id
- campaign_id (foreign key)
- keyword_text
- match_type
- spend_micros (integer)
- clicks (integer)
- impressions (integer)
- conversions (integer)
- average_cpc_micros (integer)
- cost_per_conversion_micros (integer)
- date
- timestamps

# google_ads_daily_reports
- id
- report_date
- location
- total_spend
- total_clicks
- total_impressions
- total_conversions
- new_customers_count (from our system)
- anomaly_flags (jsonb) # e.g., spend_jump: true
- ai_analysis (jsonb - stores AI recommendations)
- timestamps

Shared Infrastructure:

# google_api_credentials (encrypted)
- id
- service (google_ads, search_console)
- credential_type (oauth, developer_token)
- access_token (encrypted)
- refresh_token (encrypted)
- expires_at
- timestamps

Background Jobs - Google Ads:

Phase 3: Unified Marketing Dashboard

Admin Dashboard Routes:

/admin/marketing/dashboard          (combined view)
/admin/marketing/organic            (Search Console data)
/admin/marketing/organic/queries    (SEO keywords)
/admin/marketing/organic/pages      (top pages)
/admin/marketing/paid               (Google Ads data)
/admin/marketing/paid/campaigns     (ad campaigns)
/admin/marketing/paid/keywords      (paid keywords)
/admin/marketing/comparison         (organic vs paid analysis)
/admin/marketing/reports            (AI insights)
/admin/marketing/experiments        (AI learning & outcomes)

Key Views:

  1. Marketing Dashboard Overview (Combined)

  2. Organic Performance (Search Console)

  3. Paid Performance (Google Ads)

  4. Keyword Comparison View

  5. AI Recommendations

Phase 3: AI Integration

AI Analysis Features:

  1. Automated Weekly Reports

  2. Negative Keyword Detection

  3. Budget Optimization

  4. Interactive Q&A (future)

AI Provider Options:

Phase 4: AI Learning & Continuous Optimization

Goal: Transform the AI from a stateless analyzer into a learning system that gets smarter over time by measuring the real-world impact of its recommendations.

Core Concept: Every AI Recommendation Becomes an Experiment

Instead of just generating suggestions, we:

  1. Log every AI recommendation with context
  2. Measure before/after performance over a defined window (21-30 days)
  3. Evaluate whether the change improved, stayed neutral, or worsened performance
  4. Learn by distilling successful patterns into an evolving "AI Playbook"
  5. Apply the playbook to all future AI prompts to bias toward proven winners

Database Tables - AI Learning:

# ai_recommendations
- id
- source            # 'search_console', 'google_ads', 'combined'
- scope_type        # 'campaign', 'keyword', 'page_url', 'geo'
- scope_id          # campaign_id, keyword_id, or page_url string
- recommendation_type  # 'title_change', 'meta_change', 'neg_keyword', 'budget_up', 'budget_down', 'bid_adjustment'
- recommendation_text  # human-readable description
- ai_payload        # jsonb – raw structured output from Gemini
- reasoning         # text – AI's explanation for this recommendation
- auto_applied      # boolean – was this applied automatically?
- applied_at        # datetime – when was it applied (null if not applied)
- applied_by        # user_id or 'system'
- created_at
- updated_at

# ai_recommendation_outcomes
- id
- ai_recommendation_id  # FK to ai_recommendations
- window_start_date     # date when measurement started
- window_end_date       # date when measurement ended
- ctr_before            # decimal
- ctr_after             # decimal
- impressions_before    # integer
- impressions_after     # integer
- clicks_before         # integer
- clicks_after          # integer
- cost_before           # decimal (for ads, null for organic)
- cost_after            # decimal
- conversions_before    # integer
- conversions_after     # integer
- revenue_before        # decimal
- revenue_after         # decimal
- outcome               # enum: 'improved', 'neutral', 'worse'
- improvement_score     # decimal – calculated % improvement
- evaluated_at          # datetime
- notes                 # text – optional human notes
- timestamps

# ai_playbooks
- id
- version            # integer, incremental (starts at 1)
- source             # 'seo', 'ads', 'combined'
- summary            # text – human-readable summary of learnings
- rules              # jsonb – { good_patterns: [...], bad_patterns: [...], recommendations: [...] }
- action_sample_size # integer – how many evaluated actions were used to build this playbook
- date_range_start   # date – what period of outcomes this covers
- date_range_end     # date
- created_at
- updated_at

# Unified metrics tables (for cross-channel analysis)
# page_daily_metrics
- id
- page_url
- date
- organic_clicks
- organic_impressions
- organic_ctr
- organic_position
- paid_clicks         # null if no ads running for this page
- paid_impressions
- paid_cost
- paid_conversions
- paid_revenue
- created_at
- updated_at

# keyword_daily_metrics
- id
- keyword_text
- match_type          # null for organic, 'exact'/'phrase'/'broad' for paid
- date
- organic_clicks
- organic_impressions
- organic_ctr
- organic_position
- paid_clicks         # null if not bidding on this keyword
- paid_impressions
- paid_cost
- paid_conversions
- paid_revenue
- created_at
- updated_at

Background Jobs - AI Learning:

# Daily Jobs (run automatically every night)
SearchConsoleSyncJob          # 4am - pull organic data
GoogleAdsSyncJob              # 4am - pull paid data
PageDailyMetricsJob           # 5am - aggregate Ads + SEO into unified metrics
KeywordDailyMetricsJob        # 5am - same for keywords
AiDailyInsightsJob            # 6am - generate quick daily insights (optional)

# Weekly Jobs (run Monday mornings)
SeoWeeklyAnalysisJob          # 7am - SEO-only analysis
AdsWeeklyAnalysisJob          # 7:30am - Ads-only analysis
AiOutcomeEvaluationJob        # 8am - label past recommendations (21-30 days old) as improved/neutral/worse
AiPlaybookRefreshJob          # 8:30am - send labeled actions to Gemini, update playbook
AiWeeklyReportJob             # 9am - uses latest playbook + unified metrics to generate email report

AI Prompt Structure with Playbook Integration:

Every AI analysis call follows this pattern:

  1. Load Latest Playbook (if any exists for the relevant scope: seo, ads, or combined)

  2. Build Prompt Including:

  3. Ask Gemini To:

  4. Store Output:

Example Playbook Rules (JSON Structure):

{
  "good_patterns": [
    {
      "pattern": "title_changes_with_numbers",
      "description": "Title changes that add specific numbers (e.g., '7 Best', '$299') increased CTR by avg 23%",
      "sample_size": 12,
      "avg_improvement": 0.23,
      "confidence": "high"
    },
    {
      "pattern": "negative_keywords_high_spend",
      "description": "Adding negative keywords for terms with $200+/mo spend and 0 conversions saved avg $890/mo",
      "sample_size": 8,
      "avg_savings": 890,
      "confidence": "high"
    }
  ],
  "bad_patterns": [
    {
      "pattern": "broad_match_low_volume",
      "description": "Budget increases on broad match keywords with <100 impressions/week resulted in no new conversions",
      "sample_size": 5,
      "avg_waste": 145,
      "confidence": "medium"
    }
  ],
  "recommendations": [
    "Focus title optimization on pages with >1000 impressions/month and <2% CTR",
    "Only add negative keywords for terms with at least $50 spend in last 30 days",
    "Prioritize budget increases on exact match keywords that already convert"
  ]
}

Dashboard View: AI Experiments

New route: /admin/marketing/experiments

Shows table of all AI recommendations with columns:

Filters:

Success Criteria:

After 3 months of operation:

Evaluation Windows by Recommendation Type:

Different changes need different measurement periods:

Recommendation Type Evaluation Window Why
Negative Keywords 7-14 days Immediate spend reduction
Budget Adjustments 14-21 days Need time to see conversion impact
Title/Meta Changes 30-60 days Google re-indexing + ranking stabilization
Bid Adjustments 7-14 days Quick impact on ad position/cost
Content Updates 60-90 days Long tail organic growth

Learning Loop Visualization:

Week 1: AI analyzes data → generates recommendations → stores in DB
   ↓
Week 2-4: Recommendations applied → performance measured
   ↓
Week 5: OutcomeEvaluationJob labels each recommendation (improved/neutral/worse)
   ↓
Week 6: PlaybookRefreshJob sends outcomes to Gemini → updates playbook rules
   ↓
Week 7+: Future AI calls use updated playbook → smarter recommendations
   ↓
(Loop continues, system gets smarter over time)

Phase 4 Rollout Strategy:

Phase 4a: Recommendation Tracking (Week 1-2)

Phase 4b: Outcome Measurement (Week 3-6)

Phase 4c: Playbook Learning (Week 7-10)

Phase 4d: Close the Loop (Week 11+)

Anti-Pattern Safeguards:

To avoid learning from noise:

  1. Minimum Sample Size: Don't create playbook rules until at least 5 similar actions evaluated
  2. Statistical Significance: Calculate confidence intervals on improvements
  3. Recency Weighting: Patterns from last 90 days weighted 2x vs older patterns
  4. External Factors: Flag outliers (major algorithm updates, seasonal spikes)
  5. Human Override: All auto-applied changes have rollback mechanism

Technical Architecture

┌─────────────────────────────────────────────────────────────┐
│                     Google Ads API                          │
│  - Campaign performance data                                │
│  - Keyword metrics                                          │
│  - Geographic performance                                   │
└────────────────────────┬────────────────────────────────────┘
                         │
                         │ OAuth 2.0 + Developer Token
                         │ Daily API calls (~10-15/day)
                         │
                         ▼
┌─────────────────────────────────────────────────────────────┐
│         Quarry Rentals Rails Application                    │
│                                                               │
│  ┌──────────────────────────────────────────────────────┐  │
│  │  GoogleAdsService                                     │  │
│  │  - Authenticate with OAuth                            │  │
│  │  - Fetch campaign/keyword data                        │  │
│  │  - Handle rate limiting                               │  │
│  │  - Store in database                                  │  │
│  └──────────────────────────────────────────────────────┘  │
│                                                               │
│  ┌──────────────────────────────────────────────────────┐  │
│  │  GoogleAdsSyncJob (Solid Queue)                       │  │
│  │  - Runs daily at 2am                                  │  │
│  │  - Pulls previous day's data                          │  │
│  │  - Updates historical records                         │  │
│  └──────────────────────────────────────────────────────┘  │
│                                                               │
│  ┌──────────────────────────────────────────────────────┐  │
│  │  AiAnalysisService                                    │  │
│  │  - Analyze performance trends                         │  │
│  │  - Identify optimization opportunities                │  │
│  │  - Generate recommendations                           │  │
│  │  - Uses Gemini or Claude API                          │  │
│  └──────────────────────────────────────────────────────┘  │
│                                                               │
│  ┌──────────────────────────────────────────────────────┐  │
│  │  Admin Dashboard                                      │  │
│  │  - View campaign performance                          │  │
│  │  - Analyze keywords                                   │  │
│  │  - Review AI recommendations                          │  │
│  │  - Generate reports                                   │  │
│  └──────────────────────────────────────────────────────┘  │
│                                                               │
└─────────────────────────────────────────────────────────────┘

API Calls We'll Make

Google Ads API Services:

1. GoogleAdsService.SearchStream

-- Fetch campaign performance
SELECT
  campaign.id,
  campaign.name,
  campaign.status,
  segments.date,
  metrics.cost_micros,
  metrics.clicks,
  metrics.impressions,
  metrics.conversions
FROM campaign
WHERE segments.date DURING LAST_7_DAYS

2. Keyword Performance

-- Fetch keyword metrics
SELECT
  campaign.name,
  ad_group.name,
  ad_group_criterion.keyword.text,
  ad_group_criterion.keyword.match_type,
  metrics.cost_micros,
  metrics.clicks,
  metrics.conversions
FROM keyword_view
WHERE segments.date DURING LAST_7_DAYS
ORDER BY metrics.cost_micros DESC

3. Geographic Performance

-- Fetch performance by location
SELECT
  geographic_view.location_type,
  campaign_criterion.location.geo_target_constant,
  metrics.cost_micros,
  metrics.conversions
FROM geographic_view
WHERE segments.date DURING LAST_7_DAYS

Estimated API Call Volume:

Key Metrics We'll Track

Performance Metrics:

Business Metrics:

Analysis Metrics:

AI Analysis Prompts

Weekly Performance Analysis:

Analyze the following Google Ads performance data for Quarry Rentals' dumpster rental business:

Last 7 Days:
- Total Spend: $3,245
- Conversions: 24
- CPA: $135.21
- New Customers: 18

Campaign Performance:
[Campaign data here]

Keyword Performance:
[Top 20 keywords by spend]

Please provide:
1. Overall performance assessment
2. Best performing campaigns/keywords
3. Worst performing campaigns/keywords
4. Specific recommendations for optimization
5. Negative keywords to consider adding
6. Budget reallocation suggestions

Negative Keyword Identification:

Analyze these keywords that have high spend but zero conversions:

1. "cheap dumpster" - $340 spent, 48 clicks, 0 conversions
2. "dumpster rental free" - $285 spent, 62 clicks, 0 conversions
3. "trash pickup" - $180 spent, 31 clicks, 0 conversions

For each keyword:
1. Why is it not converting?
2. Should it be added as a negative keyword?
3. Are there related terms we should also add?
4. Estimated monthly savings if removed

Consider our business: dumpster and portable sanitation rentals for contractors and homeowners in Southern California.

Budget Optimization:

Given these campaign ROI metrics:

Campaign A (LA Dumpster Rental):
- Spend: $1,890
- Conversions: 15
- Revenue: $7,500
- ROI: 3.97x

Campaign B (OC Roll-Off Service):
- Spend: $1,055
- Conversions: 7
- Revenue: $3,200
- ROI: 3.03x

Campaign C (Porta-Potty Rental):
- Spend: $695
- Conversions: 4
- Revenue: $1,800
- ROI: 2.59x

Total monthly budget: $10,000

How should we reallocate budget across these campaigns to maximize ROI? Provide specific dollar amounts and reasoning.

Implementation Plan

🚀 Phase 1: Can Start NOW - Search Console (While Waiting for Google Ads Approval)

Week 1: Search Console Foundation

  1. Set up OAuth 2.0 credentials in Google Cloud Console
  2. Verify quarryrents.com property in Search Console
  3. Add google-api-client gem to Gemfile
  4. Create database migrations for Search Console tables
  5. Create models: SearchConsoleQuery, SearchConsolePage, SearchConsoleSiteAnalytic
  6. Set up encrypted credentials storage

Week 2: Search Console API Integration

  1. Create SearchConsoleService to handle API authentication
  2. Build methods to fetch search query data (keywords ranking)
  3. Build methods to fetch page performance data
  4. Build methods to fetch site-wide analytics
  5. Test with production quarryrents.com data
  6. Create background job for daily organic data sync

Week 3: Organic Dashboard UI

  1. Create admin marketing routes and controller
  2. Build organic performance overview page
  3. Build search queries (SEO keywords) page
  4. Build top pages performance page
  5. Add charts/graphs for rankings and CTR
  6. Style with Tailwind CSS

Week 4: SEO AI Analysis

  1. Create SeoAnalysisService (using Gemini API)
  2. Implement weekly SEO performance analysis
  3. Identify page 2 ranking opportunities (positions 11-20)
  4. Identify low-CTR high-impression queries
  5. Add AI SEO recommendations to dashboard
  6. Set up automated weekly SEO email reports

⏳ Phase 2: After Google Ads Approval - Paid Advertising

Week 5: Google Ads Foundation

  1. Confirm Google Ads API production access approved
  2. Add google-ads-ruby gem
  3. Create database migrations for Google Ads tables
  4. Create models: GoogleAdsCampaign, GoogleAdsKeyword, GoogleAdsDailyReport
  5. Update credentials storage for Ads API

Week 6: Google Ads API Integration

  1. Create GoogleAdsService to handle API authentication
  2. Build methods to fetch campaign data
  3. Build methods to fetch keyword data
  4. Build methods to fetch geographic data
  5. Test with production account data
  6. Create background job for daily paid ads sync

Week 7: Paid Ads Dashboard UI

  1. Add paid advertising routes
  2. Build campaign performance page
  3. Build paid keywords analysis page
  4. Add ad spend charts and conversion tracking
  5. Style with Tailwind CSS

Week 8: Paid Ads AI Analysis

  1. Create AdsAnalysisService
  2. Implement weekly ad performance analysis
  3. Implement negative keyword detection
  4. Implement budget optimization recommendations
  5. Add AI ad recommendations to dashboard

🔗 Phase 3: Combined Analysis - The Power Move

Week 9: Unified Marketing Intelligence

  1. Create MarketingComparisonService
  2. Build keyword comparison view (organic vs paid)
  3. Identify cannibalization (paying for keywords we rank well for)
  4. Identify gaps (no organic presence, ads working well)
  5. Build unified marketing dashboard

Week 10: Advanced AI - Cross-Channel Optimization

  1. Create unified AI analysis combining both channels
  2. "Stop paying for keywords ranking #1-3 organically"
  3. "Improve SEO for high-converting paid keywords"
  4. "These keywords need both SEO + ads support"
  5. Budget reallocation across channels

Week 11: Polish & Launch

  1. Testing and bug fixes
  2. User documentation
  3. Deploy to production
  4. Monitor API usage and costs
  5. Gather feedback and iterate

🧠 Phase 4: AI Learning & Continuous Optimization

Week 12-13: Recommendation Tracking (Phase 4a)

  1. Create database migrations for:
  2. Create models with validations and associations
  3. Update SeoAnalysisService and AdsAnalysisService to log all recommendations
  4. Build AiRecommendation model with scopes (pending, applied, evaluated)
  5. Start collecting recommendation data (no evaluation yet)

Week 14-15: Unified Metrics Aggregation

  1. Create PageDailyMetricsJob (aggregates SEO + Ads data per page)
  2. Create KeywordDailyMetricsJob (aggregates SEO + Ads data per keyword)
  3. Backfill last 30 days of unified metrics
  4. Update all AI analysis services to work from unified metrics
  5. Build dashboard views for unified metrics

Week 16-17: Outcome Measurement (Phase 4b)

  1. Create AiOutcomeEvaluationJob
  2. Implement before/after comparison logic
  3. Calculate improvement scores for each recommendation type
  4. Build outcome labeling algorithm (improved/neutral/worse)
  5. Test with first batch of 30-day-old recommendations
  6. Manual review and calibration

Week 18-19: Playbook Learning (Phase 4c)

  1. After 50+ evaluated recommendations, create AiPlaybookRefreshJob
  2. Build Gemini prompt for pattern extraction
  3. Implement playbook versioning
  4. Store good_patterns, bad_patterns, and recommendations
  5. Build playbook viewer in admin dashboard
  6. Generate first AI playbook from real outcomes

Week 20: Close the Loop (Phase 4d)

  1. Update all AI analysis prompts to include latest playbook
  2. Modify recommendation generation to reference playbook rules
  3. Track win rate (% of recommendations that improve performance)
  4. Build AI Experiments dashboard (/admin/marketing/experiments)
  5. Set up playbook change alerts
  6. Measure compound improvement (each cohort's win rate)

Week 21: Automation & Refinement

  1. Add auto-apply logic for high-confidence recommendations
  2. Build rollback mechanism for auto-applied changes
  3. Implement statistical significance testing
  4. Add external factor flagging (seasonality, algorithm updates)
  5. Create weekly learning digest email
  6. Fine-tune evaluation windows per recommendation type

📅 Timeline Summary

Immediate (Weeks 1-4): Build Search Console integration + Dual-Model AI Analysis ✅ COMPLETE

After Approval (Weeks 5-8): Add Google Ads integration - complete paid advertising analysis

Combined Power (Weeks 9-11): Unified marketing intelligence with cross-channel AI optimization

Learning Loop (Weeks 12-21): AI that gets smarter over time by measuring and learning from outcomes

Total: 21 weeks (but we get value from Week 2 onwards with organic data!)

Success Criteria

Phase 1 Success:

Phase 2 Success:

Phase 3 Success:

Phase 4 Success (AI Learning):

Business Impact Goals:

Estimated Costs

API Costs:

AI Analysis Costs (Monthly):

Option 1: Gemini API (already integrated)

Option 2: Claude API (better analysis)

Recommendation: Start with Gemini (free/cheap), upgrade to Claude if we need better insights.

Security & Compliance

Data Security:

Compliance:

Rate Limiting:

Resources & Documentation

Google Ads API:

OAuth Setup:

AI Integration:

Next Steps

🚀 Can Start NOW - Search Console

Immediate Actions (No approval needed!):

  1. Verify Search Console Property

  2. Set Up OAuth 2.0 Credentials

  3. Start Building!

Ready to start? Just say the word and we can begin Phase 1!

⏳ After Google Ads Approval - Paid Advertising

When Google approves (3-5 business days):

  1. Notify when approved - Check Google Ads API Center for status
  2. Add google-ads-ruby gem
  3. Create Google Ads database migrations
  4. Build GoogleAdsService
  5. Test API connection with production data
  6. Proceed with Phase 2 implementation

Questions to Answer During Implementation

  1. How often should we sync data? (Daily vs. hourly?)
  2. How far back should we keep historical data? (1 year? Forever?)
  3. Should we track revenue per campaign or just conversions?
  4. Do we need WhatConverts integration for call tracking?
  5. Should AI analysis run daily or just weekly?
  6. Email reports to who? Just Robert or whole team?

Contact Information

Project Owner: Robert O'Neill Email: robert@quarry.app Google Ads MCC ID: 623-323-9884 Developer Token: vcq_TYSpAeWx3XR0BAZaDQ

Google Ads API Status: Waiting for production access approval (3-5 business days) Search Console API Status: Ready to start NOW - no approval needed!

Next Actions:

  1. Immediate: Start Search Console integration (Phase 1)
  2. After approval: Add Google Ads integration (Phase 2)
  3. Final: Combined marketing intelligence (Phase 3)

Last Updated: 2025-11-12 Status: Phase 1 (Search Console + Dual-Model AI) COMPLETE ✅ | Phase 2 (Google Ads) Awaiting Approval | Phase 4 (AI Learning) Planned & Spec'd