AdTech APIs, Data & Interactive REST Sandbox | AdTech Pilot

Santosh Mahanty

REST APIS • OPENAPI 3.0.3 • TELEMETRY 🔒 Production Read-Only Endpoints Active

AdTech APIs, Data Pipelines & Interactive Sandbox

Modern advertising technology is a distributed ecosystem connected by APIs, streaming protocols, databases, and automated telemetry. Explore how REST APIs, Python analytics, SQL discrepancy queries, and Model Context Protocol (MCP) AI agents integrate across ad serving, programmatic bidding, and attribution.

⚡ Live API Sandbox 🔄 Data Workflow 📊 SQL Query Library 🤖 MCP AI Integration

1. ENDPOINT: REST Path Selector

→

2. PARAMS: Query / JSON Body

→

3. EXECUTE: HTTPS Request • Live Latency

→

4. SNIPPETS: cURL & Fetch Generator

🌐 Hosted on Hostinger (Production Web Server)

PHP • MySQL • REST APIs • JavaScript

Powers this website, database-backed read-only JSON API endpoints (/api/v1/), and client-side interactive diagnostic sandboxes (OpenRTB Validator, GAM Simulator, VAST Inspector).

💻 External Engineering (GitHub / Separate Runtime)

Python • Pandas • MCP Server • AI Agents

External standalone Python clients, Pandas data analytics scripts, and Model Context Protocol (MCP) servers that query the PHP REST API over HTTPS. Python and MCP are not executed on Hostinger.

OpenAPI 3.0 Interactive Sandbox

Live AdTech Read-Only API Explorer

📄 Download openapi.json 🔒 Safe Read-Only Active

Test live production read-only endpoints and simulated ad-decisioning APIs. Inspect latency, parameter responses, and auto-generated cURL / Fetch commands:

SYSTEM HEALTH

CAMPAIGN MANAGEMENT

AUCTION & BIDDING

Demonstrated Concept: System heartbeat, API versioning, uptime metrics, and database connectivity health.

GET /api/v1/health.php

Response Payload (HTTP 200 OK • Latency: -- ms):

Loading API response...

1. The End-to-End AdTech Communication Chain

AdTech systems communicate via distinct synchronous auction protocols (OpenRTB/VAST) and asynchronous REST/JSON management APIs across 5 core tiers:

┌─────────────────────────┐ REST API / OAuth 2.0 ┌─────────────────────────┐ │ Advertiser / DSP UI │ ─────────────────────────────► │ Google Ad Manager 360 │ │ (Campaigns & Budgets) │ ◄───────────────────────────── │ (Inventory & Orders) │ └─────────────────────────┘ Sync Status & Yield └─────────────────────────┘ │ │ │ Programmatic OpenRTB (ms latency) │ Line Item Allocation ▼ ▼ ┌─────────────────────────┐ Real-Time S2S Auction ┌─────────────────────────┐ │ SSP / Ad Exchange │ ◄────────────────────────────► │ Prebid Server (PBS) │ │ (Floors, Deals, SChain) │ │ (Multi-Bidder Routing) │ └─────────────────────────┘ └─────────────────────────┘ │ │ │ VAST XML / SSAI Stitched Chunks │ Impression / Quartile Pings ▼ ▼ ┌─────────────────────────┐ Attribution Pull API ┌─────────────────────────┐ │ Smart TV / Mobile App │ ─────────────────────────────► │ Measurement (AppsFlyer) │ │ (Player & Impression) │ │ & Verification (IAS/DV)│ └─────────────────────────┘ └─────────────────────────┘ │ │ └─────────────────────────┬─────────────────────────────────┘ │ Log Transfer / S3 Export ▼ ┌───────────────────────────┐ │ Cloud Data Warehouse │ │ (BigQuery, Snowflake) │ │ Python / SQL / Dashboards│ └───────────────────────────┘

2. The Engineering Data Pipeline: API → Database → Python → SQL

Technical AdTech roles require transforming raw API logs and SQL tables into actionable business decisions using Python automation:

Step 1: Ingest Step 2: Store Step 3: Analyze Step 4: Action ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐ │ AdTech REST API │ ───────► │ SQL Database │ ──────► │ Python + Pandas │ ──────► │ Root-Cause (RCA) │ │ (GAM/SSP Logs) │ │ (Data Warehouse) │ │ (KPI Automation) │ │ & Floor Decision │ └──────────────────┘ └──────────────────┘ └──────────────────┘ └──────────────────┘

Python Client Script scripts/adtech_api_client.py

The standalone Python client executes authenticated HTTP GET requests with exponential backoff, parses the JSON payload into a Pandas DataFrame, and detects campaigns with low CTR (< 0.5%) or high CPA:

import urllib.request, json, time
import pandas as pd

class AdTechApiClient:
    def __init__(self, base_url="https://adtechpilot.fun/api/v1"):
        self.base_url = base_url
        self.headers = {"Accept": "application/json", "X-API-Key": "client_token"}

    def fetch_campaigns(self):
        req = urllib.request.Request(f"{self.base_url}/campaigns.php", headers=self.headers)
        with urllib.request.urlopen(req, timeout=5) as resp:
            return json.loads(resp.read().decode("utf-8"))["data"]

# Transform to Pandas DataFrame for KPI Analysis
client = AdTechApiClient()
df = pd.DataFrame(client.fetch_campaigns())
underperforming = df[df["ctr_percent"] < 0.5]
print(f"[ALERT] Flagged {len(underperforming)} underperforming campaigns for floor review.")

3. AdTech SQL Query Library (MySQL 8.0)

Standard MySQL 8.0 Syntax

How SQL is used to solve real-world AdTech operational challenges, from aggregated KPI calculations to multi-partner impression discrepancy reconciliation:

Executable Demo SQL Table: campaigns

Campaign Channel Aggregations & eCPM

Business Question: What is the total spend, aggregate CTR, and average eCPM across active campaigns grouped by delivery channel?

-- Executable MySQL Query
SELECT 
    channel,
    COUNT(id) AS total_campaigns,
    SUM(spend) AS total_spend_usd,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    ROUND((SUM(clicks) / NULLIF(SUM(impressions), 0)) * 100, 3) AS aggregate_ctr_pct,
    ROUND((SUM(spend) / NULLIF(SUM(impressions), 0)) * 1000, 2) AS aggregate_ecpm_usd
FROM campaigns
WHERE status = 'active'
GROUP BY channel
ORDER BY total_spend_usd DESC;

Expected Output: Rows for CTV ($42.1k spend, $22.78 eCPM), Mobile Display ($18.4k spend, $5.00 eCPM), etc.

Illustrative SQL Example Tables: gam_logs, dsp_logs

GAM vs. DSP Hourly Discrepancy Reconciliation

Business Question: Where does the billing variance between publisher ad server impressions and DSP billed logs exceed 15%?

-- Illustrative MySQL 8.0 CTE Query
WITH hourly_metrics AS (
    SELECT 
        DATE_FORMAT(g.event_time, '%Y-%m-%d %H:00:00') AS log_hour,
        g.ad_unit_id,
        COUNT(g.impression_id) AS gam_impressions,
        COUNT(d.dsp_impression_id) AS dsp_impressions
    FROM gam_data_transfer g
    LEFT JOIN dsp_billing_logs d 
      ON g.auction_id = d.auction_id
    WHERE g.event_time >= NOW() - INTERVAL 24 HOUR
    GROUP BY log_hour, g.ad_unit_id
)
SELECT 
    log_hour,
    ad_unit_id,
    gam_impressions,
    dsp_impressions,
    ROUND(((gam_impressions - dsp_impressions) / CAST(gam_impressions AS DECIMAL(10,2))) * 100, 2) AS discrepancy_pct
FROM hourly_metrics
WHERE gam_impressions > 0 
  AND ((gam_impressions - dsp_impressions) / CAST(gam_impressions AS DECIMAL(10,2))) > 0.15
ORDER BY discrepancy_pct DESC;

Expected Output: Log hours with discrepancy > 15% flagged for VAST render abort or tracking beacon drop triage.

Advanced Integration Protocol

Model Context Protocol (MCP) in AdTech

AI Agent ↔ Data Integration Standard

Protocol Distinction: OpenRTB is an advertising auction protocol; VAST is a video delivery protocol. Model Context Protocol (MCP) is an open integration standard developed by Anthropic that connects AI agents securely to tools, databases, and enterprise APIs.

Hosting & Runtime Architecture Separation:
• Production Website (Hostinger): Runs the lightweight PHP + MySQL + JavaScript stack serving HTML and secure read-only JSON REST API endpoints.
• External Data & AI Layer (GitHub / Local Runtime): The Python client, Pandas analytics, and MCP Server run in separate external environments, querying the Hostinger PHP API over HTTPS. Python and MCP are NOT executed on the Hostinger shared server.