Developer Handover Pack

Developer Handover Pack

Marketing Attribution Reporting

Connects to Google Ads, Meta Ads Manager, Google Analytics 4, and HubSpot on a fixed schedule or on detection of a campaign end event. Handles OAuth token refresh, API pagination, rate-limit back-off, and date range alignment across all four platforms before writing structured raw data tables to the staging Google Sheet. This is the entry point of the entire automation; no downstream agent fires until this agent confirms a successful four-source write.

// Input
report_period: { start_date: 'YYYY-MM-DD', end_date: 'YYYY-MM-DD' }
platforms: ['google_ads', 'meta_ads', 'ga4', 'hubspot']
staging_sheet_id: '<GOOGLE_SHEETS_ID>'
staging_tab_prefix: 'raw_'

// Output (on success)
fetch_status: { google_ads: 'ok'|'error', meta_ads: 'ok'|'error', ga4: 'ok'|'error', hubspot: 'ok'|'error' }
rows_written: { google_ads: <int>, meta_ads: <int>, ga4: <int>, hubspot: <int> }
staging_tabs: ['raw_google_ads', 'raw_meta_ads', 'raw_ga4', 'raw_hubspot']
handoff_signal: 'DATA_FETCH_COMPLETE' | 'DATA_FETCH_PARTIAL' | 'DATA_FETCH_FAILED'

// On error
error_payload: { platform: string, error_code: int, message: string, retry_count: int }
alert_channel: '#marketing-ops-alerts'

Reads all four raw staging tabs written by the Data Fetch Agent, normalises UTM and source labels to a canonical taxonomy, merges the data into the master attribution tab in Google Sheets, and applies the configured attribution model to produce per-channel CPA and ROAS figures. This agent owns all formula logic and must be built against an agreed attribution model before deployment.

// Input
staging_sheet_id: '<GOOGLE_SHEETS_ID>'
staging_tabs: ['raw_google_ads', 'raw_meta_ads', 'raw_ga4', 'raw_hubspot']
attribution_model: 'last_touch' | 'first_touch' | 'linear'  // must be confirmed pre-build
utm_normalisation_map: { 'cpc': 'paid_search', 'paidsocial': 'paid_social', ... }
master_tab: 'attribution_output'

// Output
attribution_table: [
  { channel: string, spend: float, impressions: int, clicks: int,
    conversions: int, revenue: float, cpa: float, roas: float,
    attributed_weight: float, period: 'YYYY-MM-DD/YYYY-MM-DD' }
]
anomaly_flags: [{ channel: string, metric: string, value: float, threshold: float, direction: 'above'|'below' }]
merge_status: 'MERGE_COMPLETE' | 'MERGE_PARTIAL'
missing_channels: [string]  // populated when DATA_FETCH_PARTIAL was received

Reads the completed attribution table from the master Google Sheet, identifies the top-performing channel by ROAS, surfaces any anomaly flags raised by the Attribution and Merge Agent, and writes a short plain-language summary. Posts the Google Sheet link and the summary text to the designated Slack channel, tagging the marketing manager for review. This agent fires only after merge_status is confirmed.

// Input
attribution_table: [ { channel, spend, cpa, roas, conversions, revenue } ]
anomaly_flags: [ { channel, metric, value, threshold, direction } ]
merge_status: 'MERGE_COMPLETE' | 'MERGE_PARTIAL'
sheet_url: '<PUBLIC_OR_SHARED_GOOGLE_SHEET_URL>'
slack_channel_id: '<CHANNEL_ID>'  // e.g. C0123456789
mention_user_id: '<SLACK_USER_ID>'  // Marketing Manager

// Output
slack_message: {
  text: string,  // plain-language summary, max 500 characters
  blocks: [ section_block, context_block ]  // Block Kit layout
  attachments: [ { title: 'View Attribution Report', url: sheet_url } ]
}
delivery_status: 'DELIVERED' | 'SLACK_ERROR'

// On MERGE_PARTIAL
summary_includes_warning: true
missing_channels_listed: [string]  // named in the Slack message body

// ── TRIGGER ─────────────────────────────────────────────────────────────────
SCHEDULE_TRIGGER fires (cron: weekly Mon 06:00 | monthly 1st 06:00)
  OR campaign_end_event received from Google Ads / Meta webhook

payload_in: {
  report_period: { start_date, end_date },
  platforms: ['google_ads', 'meta_ads', 'ga4', 'hubspot'],
  staging_sheet_id,
  staging_tab_prefix: 'raw_'
}

// ── AGENT 1: DATA FETCH AGENT ────────────────────────────────────────────
// Step A: Google Ads API call
GoogleAds.query(GAQL) ->
  { campaign.name, campaign.id, metrics.clicks, metrics.impressions,
    metrics.cost_micros, metrics.conversions, segments.date }
  -> write to staging_tab: 'raw_google_ads'

// Step B: Meta Marketing API call
MetaAds.get('/act_{AD_ACCOUNT_ID}/insights') ->
  { campaign_name, adset_name, spend, impressions, clicks,
    actions[purchase], date_start, date_stop }
  -> write to staging_tab: 'raw_meta_ads'

// Step C: GA4 Data API call
GA4.runReport({ dimensions: [sessionDefaultChannelGroup, sessionSource,
                sessionMedium], metrics: [sessions, conversions,
                totalRevenue], dateRange: { start_date, end_date } }) ->
  { channel_group, source, medium, sessions, conversions, revenue }
  -> write to staging_tab: 'raw_ga4'

// Step D: HubSpot Deals API call
HubSpot.crm.deals.search({ filter: closedate IN period, stage: closedwon }) ->
  { dealname, amount, hs_analytics_source, hs_analytics_source_data_1,
    createdate, closedate }
  -> write to staging_tab: 'raw_hubspot'

// Agent 1 emits handoff signal
handoff_signal: 'DATA_FETCH_COMPLETE' | 'DATA_FETCH_PARTIAL' | 'DATA_FETCH_FAILED'
fetch_status: { google_ads, meta_ads, ga4, hubspot } -> each: 'ok'|'error'
rows_written: { google_ads: <int>, meta_ads: <int>, ga4: <int>, hubspot: <int> }

// ── AGENT 2: ATTRIBUTION AND MERGE AGENT ────────────────────────────────
// Receives: handoff_signal IN ['DATA_FETCH_COMPLETE', 'DATA_FETCH_PARTIAL']

// Step E: Read all raw tabs
GoogleSheets.read('raw_google_ads') -> raw_google_ads_rows[]
GoogleSheets.read('raw_meta_ads')   -> raw_meta_ads_rows[]
GoogleSheets.read('raw_ga4')        -> raw_ga4_rows[]
GoogleSheets.read('raw_hubspot')    -> raw_hubspot_rows[]

// Step F: UTM normalisation
GoogleSheets.read('utm_config') -> utm_normalisation_map
FOR EACH row IN all_raw_rows:
  row.channel_normalised = utm_normalisation_map[row.source + '/' + row.medium]
    | 'other'  // fallback if no match found

// Step G: Merge and attribute
attribution_model = GoogleSheets.readCell('config', 'B2')
  // 'last_touch' | 'first_touch' | 'linear'

FOR EACH channel_normalised IN unique_channels:
  attributed_weight = applyModel(attribution_model, channel_touchpoints)
  spend  = SUM(google_ads.cost_micros/1e6) + SUM(meta_ads.spend) for channel
  conversions = SUM(ga4.conversions) * attributed_weight
  revenue = SUM(hubspot.amount) * attributed_weight
  cpa    = spend / conversions  // 0-guard applied
  roas   = revenue / spend      // 0-guard applied

  -> write row to master_tab: 'attribution_output'
     { channel_normalised, spend, impressions, clicks, conversions,
       revenue, cpa, roas, attributed_weight, period }

// Step H: Anomaly detection
FOR EACH channel IN attribution_output:
  IF roas_delta_wow < -0.30 -> anomaly_flags.push({ channel, 'roas', ... })
  IF spend_delta_wow > 0.50  -> anomaly_flags.push({ channel, 'spend', ... })
  IF channel MISSING vs prior_report -> anomaly_flags.push({ channel, 'missing', ... })

merge_status: 'MERGE_COMPLETE' | 'MERGE_PARTIAL'
missing_channels: [string]

// ── AGENT 3: SUMMARY AND DELIVERY AGENT ─────────────────────────────────
// Receives: merge_status IN ['MERGE_COMPLETE', 'MERGE_PARTIAL']

// Step I: Read attribution output
GoogleSheets.read('attribution_output') -> attribution_table[]
top_channel = MAX(attribution_table, key='roas').channel_normalised

// Step J: Build Slack message
summary_text = buildSummary({
  top_channel, total_spend, total_conversions, total_revenue,
  anomaly_flags, merge_status, missing_channels
})

slack_message = {
  text: summary_text,
  blocks: [ section(summary_text), context(period, merge_status) ],
  attachments: [{ title: 'View Attribution Report', url: sheet_url }]
}

// Step K: Post to Slack
Slack.chat.postMessage({
  channel: slack_channel_id,
  ...slack_message,
  mention: mention_user_id  // only on MERGE_COMPLETE or MERGE_PARTIAL
})

delivery_status: 'DELIVERED' | 'SLACK_ERROR'

// ── END OF FLOW ───────────────────────────────────────────────────────────
// On any SLACK_ERROR: write to Supabase error_log, post to #marketing-ops-alerts