Developer Handover Pack

Developer Handover Pack

Campaign Performance Reporting

Connects to Meta Ads Manager, Google Analytics, and HubSpot on a schedule and retrieves all campaign metrics for the reporting period without any manual exports or logins. The agent fires at the scheduled trigger time, authenticates with each platform via OAuth or API key, fetches raw metric data for the configured date window, and writes a structured staging dataset to a designated tab in the master Google Sheet. It must handle partial API failures gracefully, logging which source failed and retrying before alerting.

// Input
report_type: 'weekly' | 'monthly'
date_range: { start: ISO8601, end: ISO8601 }
campaign_ids: string[]   // pulled from config sheet

// Output (written to Google Sheets staging tab 'raw_data')
meta_rows: [{ campaign_id, campaign_name, impressions, clicks, spend_usd, conversions, date_range }]
ga_rows:   [{ session_source, sessions, goal_completions, bounce_rate, utm_campaign, date_range }]
hs_rows:   [{ contact_id, deal_id, campaign_source, lifecycle_stage, create_date, deal_value_usd }]
collection_status: { meta: 'ok'|'error', ga: 'ok'|'error', hs: 'ok'|'error' }
collected_at: ISO8601

Takes the raw multi-source dataset written by the Data Collection Agent, resolves column name mismatches, standardises date formats, deduplicates any overlapping rows, calculates derived KPIs, flags campaigns that are underperforming against configured targets, and drafts a plain-English summary paragraph for the report. The draft commentary identifies the top performer, the biggest underperformer, and one recommended action. The agent then presents the draft to the marketing manager for review before the Distribution Agent fires.

// Input (read from Google Sheets tab 'raw_data')
meta_rows: [{ campaign_id, campaign_name, impressions, clicks, spend_usd, conversions, date_range }]
ga_rows:   [{ session_source, sessions, goal_completions, bounce_rate, utm_campaign, date_range }]
hs_rows:   [{ contact_id, deal_id, campaign_source, lifecycle_stage, create_date, deal_value_usd }]
target_thresholds: { cpl_target_usd, roas_target, ctr_target_pct }  // from 'agent_config' tab

// Output (written to Google Sheets tab 'normalised_report')
normalised_rows: [{ campaign_id, campaign_name, impressions, clicks, spend_usd, conversions,
                    cpl_usd, ctr_pct, conversion_rate_pct, roas,
                    hs_leads, hs_deals, hs_deal_value_usd,
                    utm_campaign, underperformance_flag: boolean,
                    underperformance_reason: string | null }]
draft_commentary: string   // plain-English paragraph, max 120 words
top_performer: { campaign_id, campaign_name, metric: string, value: number }
biggest_underperformer: { campaign_id, campaign_name, metric: string, value: number }
recommended_action: string
normalisation_status: 'awaiting_review' | 'approved' | 'edited'
normalised_at: ISO8601

// On approval (manager sets normalisation_status to 'approved' or 'edited' in 'agent_config' tab B3)
Distribution Agent trigger fires

Formats the final approved summary into a structured Slack message with key metric callouts, underperformance flags highlighted in the message body, and a direct link to the Looker Studio dashboard. Posts the message to the designated Slack channel and records the delivery timestamp. This agent fires only after the marketing manager has approved or edited the draft commentary, ensuring no report is distributed without human sign-off on the narrative.

// Input
normalised_rows: [{ campaign_id, campaign_name, spend_usd, cpl_usd, roas, ctr_pct, underperformance_flag, underperformance_reason }]
draft_commentary: string   // approved or edited version from 'agent_config' tab B4
top_performer: { campaign_id, campaign_name, metric, value }
biggest_underperformer: { campaign_id, campaign_name, metric, value }
looker_dashboard_url: string   // stored in 'agent_config' tab B5
slack_channel_id: string       // stored in 'agent_config' tab B6
report_type: 'weekly' | 'monthly'

// Output
slack_message_ts: string   // Slack message timestamp, stored in 'normalised_report' tab column N
delivery_status: 'sent' | 'failed'
delivered_at: ISO8601

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────
cron_schedule: 'weekly' | 'monthly'
  -> emit: { report_type, date_range: { start: ISO8601, end: ISO8601 } }
  -> read: campaign_ids[]        from GoogleSheets['agent_config'].B10:B50
  -> read: target_thresholds     from GoogleSheets['agent_config'].C2:C4

// ─────────────────────────────────────────────────────────────────
// AGENT 1: Data Collection Agent
// ─────────────────────────────────────────────────────────────────
MetaAdsManager.GET /v18.0/act_{ad_account_id}/insights
  params: { date_preset: custom, time_range: {since, until},
            fields: 'campaign_id,campaign_name,impressions,clicks,spend,actions',
            level: 'campaign' }
  -> meta_rows[]: { campaign_id, campaign_name, impressions,
                    clicks, spend_usd, conversions, date_range }

GoogleAnalytics.POST /v1beta/{property_id}:runReport
  body: { dateRanges: [{startDate, endDate}],
          dimensions: ['sessionDefaultChannelGroup','sessionCampaignName'],
          metrics: ['sessions','goalCompletionsAll','bounceRate'] }
  -> ga_rows[]: { session_source, sessions, goal_completions,
                  bounce_rate, utm_campaign, date_range }

HubSpot.GET /crm/v3/objects/contacts
  params: { filterGroups: [{ filters: [{ propertyName: 'hs_analytics_source',
            operator: 'EQ', value: 'PAID_SOCIAL' }] }],
            properties: 'hs_campaign_source,lifecycle_stage,createdate',
            after: epoch_ms(date_range.start) }
HubSpot.GET /crm/v3/objects/deals
  params: { properties: 'dealname,campaign_source,amount,createdate' }
  -> hs_rows[]: { contact_id, deal_id, campaign_source,
                  lifecycle_stage, create_date, deal_value_usd }

// Write raw output to staging
GoogleSheets.WRITE tab='raw_data'
  fields: meta_rows, ga_rows, hs_rows, collection_status, collected_at
GoogleSheets.SET 'agent_config'.B2 = 'collection_complete'

// ── HANDOFF: Data Collection Agent -> Normalisation and Insight Agent ──
// Trigger: 'agent_config'.B2 == 'collection_complete'

// ─────────────────────────────────────────────────────────────────
// AGENT 2: Normalisation and Insight Agent
// ─────────────────────────────────────────────────────────────────
GoogleSheets.READ tab='raw_data'
  -> resolve column_name_map:
     meta:'campaign_name' = ga:'sessionCampaignName' = hs:'campaign_source'
  -> normalise date formats to ISO8601 YYYY-MM-DD
  -> deduplicate on (campaign_id, date_range)
  -> join on: campaign_name (fuzzy match, Levenshtein distance <= 2)

// Derived KPI calculations
cpl_usd            = spend_usd / hs_leads
ctr_pct            = (clicks / impressions) * 100
conversion_rate_pct = (conversions / clicks) * 100
roas               = hs_deal_value_usd / spend_usd

// Underperformance flagging
underperformance_flag = (cpl_usd > target_thresholds.cpl_target_usd)
                      | (roas < target_thresholds.roas_target)
                      | (ctr_pct < target_thresholds.ctr_target_pct)

// Commentary generation
LLM.call prompt_template(top_performer, biggest_underperformer,
                          recommended_action, underperforming_campaigns)
  -> draft_commentary: string (max 120 words)

// Write normalised output
GoogleSheets.WRITE tab='normalised_report'
  fields: normalised_rows[], draft_commentary, top_performer,
          biggest_underperformer, recommended_action, normalised_at
GoogleSheets.SET 'agent_config'.B3 = 'awaiting_review'
GoogleSheets.SET 'agent_config'.B4 = draft_commentary

// Human review gate
POLL 'agent_config'.B3 every 5 min
  -> if B3 == 'approved' | 'edited': proceed
  -> if 4 hours elapsed with no approval: send Slack DM reminder

// ── HANDOFF: Normalisation and Insight Agent -> Distribution Agent ──
// Trigger: 'agent_config'.B3 set to 'approved' or 'edited'

// ─────────────────────────────────────────────────────────────────
// AGENT 3: Distribution Agent
// ─────────────────────────────────────────────────────────────────
GoogleSheets.READ tab='normalised_report'
  fields: normalised_rows[], top_performer, biggest_underperformer
GoogleSheets.READ 'agent_config'.B4  // approved commentary
GoogleSheets.READ 'agent_config'.B5  // looker_dashboard_url
GoogleSheets.READ 'agent_config'.B6  // slack_channel_id

// Format Slack Block Kit message
SlackBlock.header:  report_type + date_range
SlackBlock.fields:  spend_usd | cpl_usd | roas | ctr_pct
SlackBlock.section: approved_commentary
SlackBlock.section: underperforming_campaigns[] (if any flagged)
SlackBlock.button:  'View Full Dashboard' -> looker_dashboard_url

Slack.POST /api/chat.postMessage
  params: { channel: slack_channel_id, blocks: SlackBlocks }
  -> slack_message_ts: string

// Write delivery confirmation
GoogleSheets.SET 'normalised_report'.N{row} = slack_message_ts
GoogleSheets.SET 'normalised_report'.O{row} = delivered_at (ISO8601)

// ─────────────────────────────────────────────────────────────────
// ERROR PATH (any agent)
// ─────────────────────────────────────────────────────────────────
ON error:
  Supabase.INSERT table='automation_errors'
    { agent_name, error_code, error_message, payload_snapshot, occurred_at }
  Slack.POST channel=ops_channel_id
    { text: 'Reporting automation error: {agent_name} - {error_message}' }