AdInsight — Google Ads Campaign Analytics Platform

---

What This Is

AdInsight is an end-to-end analytics platform that ingests Google Ads-style campaign performance data, runs SQL-driven KPI analysis, automatically detects anomalies (CTR drops, cost spikes, tracking gaps, ad fatigue), and surfaces ranked budget-reallocation recommendations in a polished React dashboard — styled after Google Analytics / Looker Studio.

---

Why Synthetic Data

Real Google Ads API data requires an active, authenticated ad account — not available for a portfolio project. This project generates statistically realistic synthetic data using a documented, reproducible Python script. This is a standard practice in data engineering (testing pipelines against known data). The generator:

• Produces channel-specific CTR/CPC ranges (Search: 3–5.5%, Display: 0.3–0.9%, YouTube: 1–2.5%, Discovery: 0.7–1.8%)
• Injects weekday/weekend seasonality (B2B campaigns dip on weekends)
• Deliberately injects 4 documented anomaly types with an answer-key CSV (data/raw/anomaly_ground_truth.csv) to validate detector accuracy

This is not "fake data hiding a lack of skill" — it's a defensible, transparent design choice with explicit documentation.

---

Architecture

CSV Data (data/raw/)
      │
      ▼  python database/load_data.py
SQLite DB (adinsight.db)
      │
      ├─ SQL Views: v_campaign_daily_kpis, v_channel_summary, v_campaign_summary
      │
      ▼  python -m uvicorn backend.main:app
FastAPI Backend (:8000)
      │
      ├─ /api/metrics/summary, /api/metrics/timeseries
      ├─ /api/campaigns, /api/campaigns/{id}
      ├─ /api/channels/comparison
      ├─ /api/anomalies
      ├─ /api/recommendations
      └─ /api/data-quality
            │
            ▼  npm run dev
React Frontend (:5173)
      ├─ Overview Dashboard
      ├─ Campaign Deep-Dive
      ├─ Anomalies
      ├─ Recommendations
      └─ Data Quality

---

Quick Start

Prerequisites

• Python 3.11+
• Node.js 18+

Step 1 — Generate Data (already done if CSVs exist)

cd data_generation
pip install numpy
python generate_data.py --weeks 10 --seed 42 --outdir ../data/raw

Step 2 — Load Database

pip install fastapi uvicorn
python database/load_data.py --reset

Step 3 — Run Analysis Engines

The engines are auto-triggered via the frontend "Refresh Engines" button, or run manually:

python -c "
from backend.analysis.anomaly_detector import run_all_detectors
from backend.analysis.recommendation_engine import run_engines
run_all_detectors('adinsight.db')
run_engines('adinsight.db')
"

Step 4 — Start Backend

cd backend
pip install -r requirements.txt
uvicorn main:app --reload --host 0.0.0.0 --port 8000

Swagger docs: http://localhost:8000/docs

Step 5 — Start Frontend

cd frontend
npm install
npm run dev

Dashboard: http://localhost:5173

---

SQL Queries Demonstrated

sql/analysis_queries.sql contains 12 documented queries:

1. Account-level KPI scorecard
2. Week-over-week CTR change (window function: LAG)
3. Top 5 underperforming ad groups (CTE + ROW_NUMBER ranking)
4. Rolling 7-day ROAS trend (window function: SUM OVER ROWS)
5. Channel vs industry benchmark comparison
6. Period-over-period spend comparison
7. Daily spend stacked by channel
8. Budget utilization signal (capped vs underutilized campaigns)
9. Z-score cost anomaly detection in SQL
10. Data quality completeness check
11. Ad group ROAS ranking within campaigns (RANK window function)
12. Cumulative spend and conversions (running totals)

---

Anomaly Detection

Three detection methods — rule-based and statistical, deliberately not ML (explainable > black-box for this role):

Method	Threshold	Reasoning
Z-score cost spike	|z| > 2.5 vs 14-day trailing	~99th percentile; balances sensitivity vs false positives
CTR drop	|z| > 2.5 (downward only)	Same threshold applied to click-through rate
Tracking gap	Clicks ≥ 20 AND conversions == 0 for 2+ days	1 day is noise; 2 consecutive = likely broken tag
Ad fatigue	CTR declining each day for 3+ days, ≥10%/day	3 days filters noise; each day must decline individually

---

Folder Structure

adinsight/
├── data_generation/generate_data.py    # Synthetic data generator
├── data/raw/*.csv                       # Generated datasets
├── database/
│   ├── schema.sql                       # Full DDL + SQL views
│   └── load_data.py                     # ETL: CSV → SQLite
├── sql/analysis_queries.sql             # 12 documented analysis queries
├── backend/
│   ├── main.py                          # FastAPI application
│   ├── analysis/
│   │   ├── kpi_engine.py                # KPI computation (SQL-first)
│   │   ├── anomaly_detector.py          # Anomaly detection engine
│   │   └── recommendation_engine.py     # Budget reallocation engine
│   ├── requirements.txt
│   └── tests/                           # pytest unit tests
├── frontend/                            # React + Vite + Recharts
│   └── src/
│       ├── pages/                       # 5 dashboard pages
│       └── components/                  # Reusable UI components
├── adinsight.db                         # SQLite database
├── README.md
├── INSIGHTS_SUMMARY.md                  # 1-page stakeholder summary
└── PRD_Google_Ads_Campaign_Analytics.md

---

Running Tests

cd backend
pip install pytest
pytest tests/ -v

---