Suggest changes: Automating Google Ads Spend Monitoring with PostgreSQL and Telegram

Edit the text directly. Your proposal will remain pending until it is reviewed and approved. Only the content is displayed here — the title, date, and metadata cannot be changed from this form.

Content

Managing paid marketing campaigns across Google Ads requires constant vigilance. Ad networks frequently experience sudden impression spikes, bidding algorithm volatility, or unexpected budget runaway over weekends and holidays.

Navigating the cumbersome Google Ads web interface just to verify daily spend or spot budget overruns is inefficient. Marketing teams and engineers need real-time alerting delivered straight to their phones where they can query performance metrics interactively.

I built **[google-ads-telegram-bot](https://github.com/PedroCaballero741/google-ads-telegram-bot)** to automate paid spend surveillance. It is a containerized Python service that periodically ingests campaign cost metrics via the **Google Ads API**, persists time-series data into **PostgreSQL**, and provides an interactive **Telegram Bot** for automated daily summaries, threshold breach warnings, and real-time spend queries.

---

## Architectural Pipeline

The system is designed as an autonomous service split between an ingestion scheduler, a relational store, and an asynchronous Telegram bot:

```mermaid
flowchart TB
 subgraph Upstream ["Upstream Marketing Platform"]
 GADS[/"Google Ads REST API (v17)"\]
 GAQL["Google Ads Query Language (GAQL) <code>SELECT campaign.id, metrics.cost_micros, ...</code>"]
 GADS --- GAQL
 end

subgraph Ingestion ["Ingestion Worker & Scheduler"]
 direction TB
 SCHED["APScheduler / Cron Engine Hourly delta sync + daily rollups"]
 BACKOFF["Resilience & Backoff Layer OAuth token refresh & rate-limit throttling"]
 TRANSFORM["Metrics Normalizer Micros to currency, CPC math, impression delta"]

SCHED --> BACKOFF
        BACKOFF -->|Batched stream query| GAQL
        GAQL -->|Raw metric response| TRANSFORM
    end

subgraph Persistence ["Time-Series Data Store"]
 direction TB
 PG[("PostgreSQL 16 Database Alembic schema migrations")]
 TABLES["Normalized Schema: • <code>campaign_snapshots</code> (hourly cost, clicks, impr) • <code>daily_rollups</code> (historical aggregates) • <code>alert_rules</code> (budget caps, thresholds)"]

TRANSFORM -->|Bulk insert snapshots| PG
        PG --- TABLES
    end

subgraph Analytics ["Surveillance & Anomaly Engine"]
 direction TB
 EVAL["Anomaly Evaluator Evaluates live records against baseline"]
 CHECK_RUNAWAY{"Runaway Spend? >70% budget before 12:00"}
 CHECK_ZERO{"Zero Impressions? 0 clicks/impr in 6h window"}
 ALERT_GEN["Alert Event Dispatcher Format Markdown warning payload"]

PG --> EVAL
        EVAL --> CHECK_RUNAWAY
        EVAL --> CHECK_ZERO
        CHECK_RUNAWAY -->|Triggered| ALERT_GEN
        CHECK_ZERO -->|Triggered| ALERT_GEN
    end

subgraph TelegramInterface ["Telegram Bot Engine (aiogram / asyncio)"]
 direction TB
 subgraph PushService ["Proactive Notification Channel"]
 BROADCAST["Alert Broadcast High-priority budget breach pings"]
 DIGEST["Scheduled Briefings 08:00 Morning Standup & 00:00 Rollup"]
 end

subgraph InteractiveService ["Interactive Query Engine"]
 CMD_PARSER["Command Router <code>/today | /week | /campaigns</code>"]
 LIVE_STATS["Real-Time Query Formatter Top campaigns, burn rate, spend totals"]
 CMD_PARSER --> LIVE_STATS
 end

ALERT_GEN --> BROADCAST
 SCHED -.->|Trigger scheduled digest| DIGEST
 LIVE_STATS <-->|Read aggregations| PG
 end

subgraph Users ["Operations & Growth Team"]
 TELEGRAM_APP(["Telegram Mobile & Desktop App"])
 BROADCAST --> TELEGRAM_APP
 DIGEST --> TELEGRAM_APP
 TELEGRAM_APP <-->|User Commands / Queries| CMD_PARSER
 end
```

---

## 1. Robust Google Ads Ingestion with GAQL

Google Ads metrics are queried using the **Google Ads Query Language (GAQL)**. To avoid hitting strict API rate limits while maintaining accurate cost tracking, the service runs batched queries targeting yesterday and today's accumulated spend:

```python
# services/google_ads.py snippet: GAQL spend extraction
def fetch_campaign_costs(client, customer_id: str, date_range: str):
    ga_service = client.get_service("GoogleAdsService")
    query = f"""
        SELECT 
            campaign.id, 
            campaign.name, 
            metrics.cost_micros, 
            metrics.impressions, 
            metrics.clicks 
        FROM campaign 
        WHERE segments.date DURING {date_range}
    """
    response = ga_service.search_stream(customer_id=customer_id, query=query)
    
    records = []
    for batch in response:
        for row in batch.results:
            # Convert micros (1/1,000,000) to standard currency units
            cost = row.metrics.cost_micros / 1_000_000.0
            records.append({
                "campaign_id": row.campaign.id,
                "name": row.campaign.name,
                "cost": round(cost, 2),
                "clicks": row.metrics.clicks,
                "impressions": row.metrics.impressions,
            })
    return records
```

---

## 2. PostgreSQL Storage and Anomaly Detection

All incoming records are normalized and written to a PostgreSQL database managed with **Alembic** migrations. By maintaining clean relational history, the service compares current hourly run rates against trailing moving averages:

- **Runaway Detection:** If a campaign burns through 70% of its daily budget before noon, the alert engine triggers an immediate high-priority alert.
- **Zero-Spend Detection:** If an active campaign records zero impressions for 6 consecutive hours, the team is alerted to potential billing or creative disapproval issues.

---

## 3. Interactive Telegram Bot Interface

Rather than logging into the Google Ads console, team members can query live numbers on demand using concise Telegram commands:

```
User: /today
Bot:  📊 Google Ads Report (Today)
      ------------------------------
      Total Spend:    $142.50 / $200.00
      Total Clicks:   312
      Avg CPC:        $0.46
      
      Top Campaign:
      • Search_US_Tier1: $98.20 (210 clicks)
      • Retargeting:      $44.30 (102 clicks)
```

The bot also broadcasts scheduled daily rollups every morning at 08:00 and midnight, summarizing total burn, conversion costs, and day-over-day variance.

---

## Deployment & Reliability

The entire application is packaged with **Docker Compose** and configured for single-command deployment to cloud platforms like Render or generic Linux VPS instances:
- Environment variables isolate OAuth tokens, refresh tokens, and developer tokens securely.
- Automatic reconnect logic ensures Telegram webhook listeners recover seamlessly from transient network drops.

---

## Conclusion

Automating advertising surveillance frees developers and growth engineers from routine console checking. By combining GAQL queries, PostgreSQL time-series storage, and Telegram messaging, `google-ads-telegram-bot` turns reactive budget management into proactive peace of mind.