Google Ads Integration - Implementation Guide

Phase 2: Paid Advertising Analytics

Status: ✅ API Approval Received - Implementation In Progress

Last Updated: 2025-11-24

🎉 What's Been Completed

✅ Task 1: Gem Installation

• Added: google-ads-googleads v28.0 to Gemfile
• Installed: Successfully bundled with all dependencies
• Location: Gemfile:62

✅ Task 2: Database Schema

Created three migration files following the Search Console pattern:

1. google_ads_campaigns (20251124000000_create_google_ads_campaigns.rb)

- campaign_id (string, unique, indexed)
- campaign_name (string, indexed)
- campaign_type (string)
- campaign_status (string, indexed)
- location (string, indexed)
- currency_code (string, default: "USD")
- login_customer_id (string)

2. google_ads_keywords (20251124001000_create_google_ads_keywords.rb)

- google_ads_campaign_id (foreign key)
- keyword_text (string, indexed)
- match_type (string)
- spend_micros (bigint, indexed) # Google Ads uses micros (1 million = $1)
- clicks (integer)
- impressions (integer)
- conversions (integer, indexed)
- average_cpc_micros (bigint)
- cost_per_conversion_micros (bigint)
- date (date, indexed)

3. google_ads_daily_reports (20251124002000_create_google_ads_daily_reports.rb)

- report_date (date, unique, indexed)
- location (string, indexed)
- total_spend (decimal, indexed)
- total_clicks (integer)
- total_impressions (integer)
- total_conversions (integer)
- new_customers_count (integer)
- top_campaigns (jsonb array)
- top_keywords (jsonb array)
- anomaly_flags (jsonb hash)
- ai_analysis (jsonb hash) # For ConsensusAiService results

📋 Next Steps (Remaining Tasks)

Step 3: Create Models (NEXT)

Create three Active Record models with validations, scopes, and associations:

GoogleAdsCampaign

# app/models/google_ads_campaign.rb
class GoogleAdsCampaign < ApplicationRecord
  has_many :google_ads_keywords, dependent: :destroy

  validates :campaign_id, presence: true, uniqueness: true
  validates :campaign_name, presence: true

  scope :active, -> { where(campaign_status: 'ENABLED') }
  scope :by_location, ->(location) { where(location: location) }
  scope :recent, -> { order(updated_at: :desc) }
end

GoogleAdsKeyword

# app/models/google_ads_keyword.rb
class GoogleAdsKeyword < ApplicationRecord
  belongs_to :google_ads_campaign

  validates :keyword_text, presence: true
  validates :date, presence: true

  scope :for_date_range, ->(start_date, end_date) {
    where(date: start_date..end_date)
  }
  scope :high_spend_zero_conversions, -> {
    where('spend_micros > ? AND conversions = 0', 50_000_000) # $50+
      .order(spend_micros: :desc)
  }

  # Helper to convert micros to dollars
  def spend_dollars
    spend_micros / 1_000_000.0
  end

  def average_cpc_dollars
    average_cpc_micros / 1_000_000.0
  end
end

GoogleAdsDailyReport

# app/models/google_ads_daily_report.rb
class GoogleAdsDailyReport < ApplicationRecord
  validates :report_date, presence: true, uniqueness: true

  scope :recent, -> { order(report_date: :desc) }
  scope :for_date_range, ->(start_date, end_date) {
    where(report_date: start_date..end_date)
  }
  scope :with_ai_analysis, -> { where.not(ai_analysis: nil) }

  # Calculate cost per acquisition
  def cpa
    return 0 if total_conversions.zero?
    total_spend / total_conversions
  end

  # Check if analysis is stale (older than 7 days)
  def stale_analysis?
    return true if ai_analysis.blank?
    analyzed_at = ai_analysis.dig('analyzed_at')
    return true if analyzed_at.nil?
    Time.parse(analyzed_at) < 7.days.ago
  end
end

Step 4: Create GoogleAdsService

Mirror the SearchConsoleService pattern with OAuth and API calls:

# app/services/google_ads_service.rb
require 'google/ads/google_ads'

class GoogleAdsService
  CUSTOMER_ID = ENV.fetch('GOOGLE_ADS_CUSTOMER_ID', '623-323-9884').delete('-')

  attr_reader :client

  def initialize
    @client = Google::Ads::GoogleAds::GoogleAdsClient.new do |config|
      config.client_id = ENV.fetch('GOOGLE_OAUTH_CLIENT_ID')
      config.client_secret = ENV.fetch('GOOGLE_OAUTH_CLIENT_SECRET')
      config.developer_token = ENV.fetch('GOOGLE_ADS_DEVELOPER_TOKEN')
      config.refresh_token = get_refresh_token
      config.login_customer_id = CUSTOMER_ID
    end
  end

  # Sync campaigns for date range
  def sync_campaigns(start_date: 7.days.ago.to_date, end_date: Date.yesterday)
    # Implementation here
  end

  # Sync keyword performance
  def sync_keywords(start_date: 7.days.ago.to_date, end_date: Date.yesterday)
    # Implementation here
  end

  # Sync daily aggregate reports
  def sync_daily_report(date: Date.yesterday)
    # Implementation here
  end

  # Sync all data
  def sync_all(start_date: 7.days.ago.to_date, end_date: Date.yesterday)
    campaigns_count = sync_campaigns(start_date: start_date, end_date: end_date)
    keywords_count = sync_keywords(start_date: start_date, end_date: end_date)

    reports_count = 0
    (start_date..end_date).each do |date|
      sync_daily_report(date: date)
      reports_count += 1
    end

    {
      campaigns: campaigns_count,
      keywords: keywords_count,
      daily_reports: reports_count
    }
  end

  private

  def get_refresh_token
    oauth_credential = OauthCredential.for_provider_and_user(
      provider: "google_ads",
      user_id: "default"
    )

    unless oauth_credential.persisted? && oauth_credential.refresh_token.present?
      raise "No OAuth credentials found. Please authorize at: /admin/oauth/authorize/google_ads"
    end

    oauth_credential.refresh_token
  end
end

Step 5: Create Background Jobs

SyncGoogleAdsDataJob

# app/jobs/sync_google_ads_data_job.rb
class SyncGoogleAdsDataJob < ApplicationJob
  queue_as :default

  def perform
    service = GoogleAdsService.new
    result = service.sync_all(
      start_date: 7.days.ago.to_date,
      end_date: Date.yesterday
    )

    Rails.logger.info "Google Ads sync completed: #{result.inspect}"
  rescue => e
    Rails.logger.error "Google Ads sync failed: #{e.message}"
    raise
  end
end

WeeklyGoogleAdsAnalysisJob

# app/jobs/weekly_google_ads_analysis_job.rb
class WeeklyGoogleAdsAnalysisJob < ApplicationJob
  queue_as :default

  def perform
    # Get last 7 days of data
    reports = GoogleAdsDailyReport.for_date_range(7.days.ago.to_date, Date.yesterday)

    # Prepare data for AI analysis
    analysis_data = {
      total_spend: reports.sum(:total_spend),
      total_clicks: reports.sum(:total_clicks),
      total_conversions: reports.sum(:total_conversions),
      # ... more metrics
    }

    # Use ConsensusAiService (Gemini + Grok)
    consensus_service = ConsensusAiService.new
    result = consensus_service.analyze_google_ads_performance(analysis_data)

    # Store in most recent daily report
    latest_report = reports.last
    latest_report.update!(ai_analysis: result)

    # Send email notification
    AdsInsightsMailer.weekly_report(latest_report).deliver_later

    Rails.logger.info "Google Ads AI analysis completed"
  rescue => e
    Rails.logger.error "Google Ads AI analysis failed: #{e.message}"
    raise
  end
end

Step 6: Create Controller & Dashboard

Admin::GoogleAdsController

# app/controllers/admin/google_ads_controller.rb
module Admin
  class GoogleAdsController < ApplicationController
    def index
      @date_range = parse_date_range

      @has_data = GoogleAdsKeyword.exists?
      return render :no_data unless @has_data

      # Latest analytics
      @latest_report = GoogleAdsDailyReport.recent.first

      # Latest AI analysis
      @latest_ai_analysis = GoogleAdsDailyReport.with_ai_analysis.recent.first

      # Campaign performance
      @campaigns = GoogleAdsCampaign.active.recent.limit(10)

      # Top spending keywords
      @top_keywords = GoogleAdsKeyword
        .for_date_range(@date_range[:start], @date_range[:end])
        .group(:keyword_text)
        .select('keyword_text, SUM(spend_micros) as total_spend, SUM(conversions) as total_conversions')
        .order('total_spend DESC')
        .limit(10)

      # Negative keyword candidates (high spend, zero conversions)
      @negative_keyword_candidates = GoogleAdsKeyword
        .for_date_range(@date_range[:start], @date_range[:end])
        .high_spend_zero_conversions
        .limit(10)

      # Daily trends
      @daily_trends = GoogleAdsDailyReport
        .for_date_range(30.days.ago.to_date, Date.yesterday)
        .order(report_date: :asc)
    end

    def sync_now
      begin
        service = GoogleAdsService.new
        result = service.sync_all(start_date: 7.days.ago.to_date, end_date: Date.yesterday)

        redirect_to admin_google_ads_index_path,
                    notice: "Successfully synced #{result[:campaigns]} campaigns, #{result[:keywords]} keywords, and #{result[:daily_reports]} daily reports."
      rescue StandardError => e
        redirect_to admin_google_ads_index_path,
                    alert: "Sync failed: #{e.message}"
      end
    end

    def analyze_now
      begin
        WeeklyGoogleAdsAnalysisJob.perform_later

        redirect_to admin_google_ads_index_path,
                    notice: "AI analysis started. Results will appear in a few moments."
      rescue StandardError => e
        redirect_to admin_google_ads_index_path,
                    alert: "Analysis failed: #{e.message}"
      end
    end

    private

    def parse_date_range
      start_date = params[:start_date].present? ? Date.parse(params[:start_date]) : 30.days.ago.to_date
      end_date = params[:end_date].present? ? Date.parse(params[:end_date]) : Date.yesterday

      { start: start_date, end: end_date }
    rescue ArgumentError
      { start: 30.days.ago.to_date, end: Date.yesterday }
    end
  end
end

Step 7: Add Routes

# config/routes.rb
namespace :admin do
  namespace :google_ads do
    get '/', to: 'google_ads#index', as: :index
    post '/sync_now', to: 'google_ads#sync_now'
    post '/analyze_now', to: 'google_ads#analyze_now'
  end
end

Step 8: Update Recurring Jobs

# config/recurring.yml
sync_google_ads_data:
  schedule: at 4am every day
  job: SyncGoogleAdsDataJob

weekly_google_ads_analysis:
  schedule: at 8:30am every Monday
  job: WeeklyGoogleAdsAnalysisJob

🔐 OAuth Setup Required

Environment Variables Needed:

GOOGLE_ADS_CUSTOMER_ID="623-323-9884"
GOOGLE_ADS_DEVELOPER_TOKEN="vcq_TYSpAeWx3XR0BAZaDQ"
GOOGLE_OAUTH_CLIENT_ID="<from Google Cloud Console>"
GOOGLE_OAUTH_CLIENT_SECRET="<from Google Cloud Console>"

OAuth Authorization Flow:

1. Add OAuth route for Google Ads (similar to Search Console)
2. User visits /admin/oauth/authorize/google_ads
3. Redirects to Google OAuth consent screen
4. Google redirects back with authorization code
5. Exchange code for access_token + refresh_token
6. Store in oauth_credentials table with provider: 'google_ads'

🧪 Testing Checklist

Once implementation is complete:

• [ ] Run migrations: bin/rails db:migrate
• [ ] Test OAuth authorization flow
• [ ] Test manual sync: GoogleAdsService.new.sync_all
• [ ] Verify data appears in database
• [ ] Check dashboard displays correctly
• [ ] Test AI analysis job
• [ ] Verify email notifications work
• [ ] Deploy to production
• [ ] Monitor Solid Queue jobs

📊 Expected Data Structure

Campaign Example:

{
  campaign_id: "12345678901",
  campaign_name: "LA Dumpster Rental - Search",
  campaign_type: "SEARCH",
  campaign_status: "ENABLED",
  location: "Los Angeles, CA",
  currency_code: "USD"
}

Keyword Example:

{
  keyword_text: "dumpster rental los angeles",
  match_type: "EXACT",
  spend_micros: 340_500_000,  # $340.50
  clicks: 48,
  impressions: 892,
  conversions: 3,
  average_cpc_micros: 7_093_750,  # $7.09
  date: "2025-11-23"
}

🚀 Benefits Once Complete

Immediate Insights:

• Which campaigns are ROI positive vs. negative
• Keywords wasting budget (high spend, zero conversions)
• Geographic performance breakdown
• Daily cost trends and anomalies

AI-Powered Optimization:

• Negative keyword recommendations - "Add 'cheap' and 'free' to save $800/month"
• Budget reallocation - "Increase LA campaign by 25%, decrease OC by 15%"
• Cannibalization detection (Phase 3) - "Stop paying for keywords you rank #1 for organically"
• Performance predictions - "Expected 12% conversion increase with recommended changes"

Time Savings:

• Automated daily data syncing (no manual reports)
• Weekly AI analysis delivered via email
• One dashboard for all advertising metrics
• Historical data for trend analysis

🔄 Integration with Search Console (Phase 3)

Once both Google Ads and Search Console are complete, we can build unified views:

Keyword Comparison:

Keyword: "dumpster rental los angeles"
├── Organic: Rank #2, 450 clicks/month, $0 cost
└── Paid: $340/month, 48 clicks, 3 conversions

💡 AI Recommendation: "You rank #2 organically. Consider reducing paid bid by 50% or adding as negative keyword if organic CTR is high."

Combined Dashboard:

• Total traffic (organic + paid)
• Total marketing spend vs. revenue
• Cannibalization alerts
• Gaps where neither organic nor paid is working

📚 References

• Google Ads API: https://developers.google.com/google-ads/api/docs/start
• Ruby Client: https://github.com/googleads/google-ads-ruby
• Search Console Implementation: app/services/search_console_service.rb
• AI Analysis: app/services/consensus_ai_service.rb

👤 Contact

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

Status: ✅ API Approved - Ready to implement!