Developer Handover Pack

Developer Handover Pack

Paid Ad Campaign Management

Connects to the Google Ads API and the Meta Marketing API on a daily schedule each morning. Authenticates via OAuth 2.0 for Google and a system user access token for Meta. Pulls spend, clicks, impressions, and conversions for all active campaigns on both platforms and merges the results into a single structured data object keyed by campaign ID and date. Writes each campaign row to the correct position in the Google Sheets master log using the Sheets API. No human intervention is required at any point in this agent's execution. If either API call fails, the agent retries up to three times before logging the error and halting without a partial write to avoid corrupt data in the master log.

// Input
trigger_time: ISO8601 timestamp from scheduler
google_ads_customer_id: string (MCC or individual account ID)
meta_ads_account_id: string (act_XXXXXXXXXX format)
sheet_id: string (Google Sheets document ID)
sheet_tab: string (e.g. 'Daily Log')
date_range: { start: 'YYYY-MM-DD', end: 'YYYY-MM-DD' }

// Output
campaigns[]: {
  campaign_id: string,
  platform: 'google' | 'meta',
  campaign_name: string,
  date: 'YYYY-MM-DD',
  spend_usd: float,
  clicks: integer,
  impressions: integer,
  conversions: integer,
  cpc_usd: float,
  ctr_pct: float,
  roas: float | null
}
write_status: 'success' | 'partial' | 'failed'
rows_written: integer
error_log[]: { step: string, message: string, timestamp: ISO8601 }

Fires immediately after the Ad Data Sync Agent confirms a successful write to the Google Sheets master log. Reads the freshly written rows, loads the budget and KPI threshold configuration from a dedicated config tab in the same sheet, and compares actual spend and conversion performance against those thresholds for each campaign. Scores each campaign as within range, approaching cap, or over cap. For any campaign flagged as anomalous, it composes and sends a structured Slack message to the configured marketing channel. It then writes conversion attribution data, matched by UTM parameters, to the corresponding HubSpot contact records via the HubSpot Contacts API. Finally, the Looker Studio dashboard refreshes automatically because it is connected directly to the Google Sheet as its data source and requires no separate API call from this agent.

// Input
sheet_id: string (same document as Ad Data Sync Agent)
data_tab: string (e.g. 'Daily Log')
config_tab: string (e.g. 'Thresholds')
slack_channel_id: string (e.g. 'C0XXXXXXXXX')
hubspot_portal_id: string
campaigns[]: { campaign_id, platform, date, spend_usd, conversions, cpc_usd, roas }

// Threshold config row structure (read from config_tab)
threshold_row: {
  campaign_id: string,
  monthly_budget_usd: float,
  daily_spend_cap_usd: float,
  max_cpc_usd: float,
  min_roas: float | null,
  alert_pct: integer (e.g. 80 = alert at 80% of budget consumed)
}

// Output
anomaly_flags[]: {
  campaign_id: string,
  campaign_name: string,
  flag_type: 'budget_cap' | 'cpc_spike' | 'roas_drop' | 'zero_spend',
  current_value: float,
  threshold_value: float,
  recommended_action: string
}
slack_messages_sent: integer
hubspot_records_updated: integer
hubspot_errors[]: { contact_id: string, error: string }

// On approval (manager acts on Slack alert)
manager_action: 'pause_campaign' | 'adjust_budget' | 'no_action'
actioned_by: string (Slack user display name, logged to sheet)
action_timestamp: ISO8601

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ────────────���────────────────────────────────────────────────────
scheduler.fire({ time: '09:00', date: 'YYYY-MM-DD' })
  OR threshold_monitor.breach({
    campaign_id: string,
    metric: 'spend_pct' | 'cpc_usd' | 'roas',
    current_value: float,
    threshold_value: float
  })

// ─────────────────────────────────────────────────────────────────
// AGENT 1: Ad Data Sync Agent
// ─────────────────────────────────────────────────────────────────

// Step 1 — Google Ads API query
google_ads.query({
  customer_id: google_ads_customer_id,
  query: "SELECT campaign.id, campaign.name, metrics.cost_micros,
           metrics.clicks, metrics.impressions, metrics.conversions,
           metrics.average_cpc, metrics.ctr
           FROM campaign
           WHERE segments.date = '{yesterday}'
           AND campaign.status = 'ENABLED'"
})
-> google_raw[]: {
     campaign_id, campaign_name, cost_micros,
     clicks, impressions, conversions, average_cpc, ctr
   }

// Step 2 — Normalise Google fields
google_normalised[]: {
  campaign_id: string,
  platform: 'google',
  campaign_name: string,
  date: 'YYYY-MM-DD',
  spend_usd: cost_micros / 1_000_000,
  clicks: integer,
  impressions: integer,
  conversions: integer,
  cpc_usd: average_cpc / 1_000_000,
  ctr_pct: ctr * 100,
  roas: null  // Google Ads ROAS requires separate revenue signal if not configured
}

// Step 3 — Meta Marketing API query (runs in parallel with Step 1)
meta_ads.insights({
  account_id: meta_ads_account_id,
  fields: ['campaign_id', 'campaign_name', 'spend', 'clicks',
           'impressions', 'actions', 'cpc', 'ctr', 'purchase_roas'],
  date_preset: 'yesterday',
  level: 'campaign'
})
-> meta_raw[]: {
     campaign_id, campaign_name, spend, clicks,
     impressions, actions[], cpc, ctr, purchase_roas[]
   }

// Step 4 — Normalise Meta fields
meta_normalised[]: {
  campaign_id: string,
  platform: 'meta',
  campaign_name: string,
  date: 'YYYY-MM-DD',
  spend_usd: parseFloat(spend),
  clicks: integer,
  impressions: integer,
  conversions: actions.find(a => a.action_type === 'lead').value | 0,
  cpc_usd: parseFloat(cpc),
  ctr_pct: parseFloat(ctr),
  roas: purchase_roas[0].value | null
}

// Step 5 — Merge and dedupe
merged_campaigns[]: [...google_normalised, ...meta_normalised]
// Dedupe check: sheet lookup by (campaign_id + date) before write
// If row exists: overwrite. If not: append.

// Step 6 — Write to Google Sheets master log
sheets.batchUpdate({
  sheet_id: sheet_id,
  tab: 'Daily Log',
  rows: merged_campaigns[]  // one row per campaign per date
})
-> write_status: 'success' | 'partial' | 'failed'
-> rows_written: integer

// ─────────────────────────────────────────────────────────────────
// AGENT HANDOFF: Ad Data Sync Agent -> Campaign Analysis and Alert Agent
// Condition: write_status === 'success'
// Payload: { sheet_id, data_tab, config_tab, campaigns: merged_campaigns[] }
// ─────────────────────────────────────────────────────────────────

// ─────────────────────────────────────────────────────────────────
// AGENT 2: Campaign Analysis and Alert Agent
// ─────────────────────────────────────────────────────────────────

// Step 7 — Load threshold config
sheets.read({ sheet_id: sheet_id, tab: 'Thresholds' })
-> thresholds[]: {
     campaign_id, monthly_budget_usd, daily_spend_cap_usd,
     max_cpc_usd, min_roas, alert_pct
   }

// Step 8 — Score each campaign
for each campaign in merged_campaigns[]:
  t = thresholds.find(t => t.campaign_id === campaign.campaign_id)
  flags = []
  if campaign.spend_usd > t.daily_spend_cap_usd:
    flags.push({ flag_type: 'budget_cap', current: spend_usd, threshold: daily_spend_cap_usd })
  if campaign.cpc_usd > t.max_cpc_usd:
    flags.push({ flag_type: 'cpc_spike', current: cpc_usd, threshold: max_cpc_usd })
  if t.min_roas != null AND campaign.roas < t.min_roas:
    flags.push({ flag_type: 'roas_drop', current: roas, threshold: min_roas })
  if campaign.impressions > 0 AND campaign.conversions === 0 AND campaign.spend_usd > 50:
    flags.push({ flag_type: 'zero_conversions' })

// Step 9 — Send Slack alerts for flagged campaigns
for each flagged campaign:
  slack.chat.postMessage({
    channel: slack_channel_id,
    text: ':warning: Campaign Alert: {campaign_name} ({platform})',
    blocks: [
      { flag_type, current_value, threshold_value,
        recommended_action, platform_deep_link }
    ]
  })
-> slack_messages_sent: integer

// Step 10 — HubSpot attribution write
// Source: conversion events matched by utm_campaign === campaign_name
for each campaign with conversions > 0:
  hubspot.contacts.search({
    filter: { property: 'hs_analytics_last_url', value contains utm_campaign }
  })
  -> matching_contacts[]: { contact_id, email, hs_analytics_last_url }
  for each matching_contact:
    hubspot.contacts.update({
      contact_id: contact_id,
      properties: {
        hs_latest_source: 'PAID_SEARCH' | 'PAID_SOCIAL',
        hs_latest_source_data_1: campaign_name,
        hs_latest_source_data_2: platform,
        last_attribution_date: date
      }
    })
  // Unmatched conversions -> append to 'Unmatched Conversions' sheet tab
-> hubspot_records_updated: integer
-> hubspot_errors[]: { contact_id, error }

// Step 11 — Looker Studio refresh (passive)
// Looker Studio reads directly from 'Daily Log' tab on each dashboard load.
// No API call required. Data is live as soon as the Sheets write completes.

// ─────────────────────────────────────────────────────────────────
// TERMINAL STATE
// ─────────────────────────────────────────────────────────────────
run_summary: {
  run_date: 'YYYY-MM-DD',
  campaigns_synced: integer,
  rows_written: integer,
  anomalies_flagged: integer,
  slack_messages_sent: integer,
  hubspot_records_updated: integer,
  errors: error_log[]
}
// run_summary written to 'Run Log' sheet tab and to Supabase error_log table