Integration and API Spec

Integration and API Spec

Campaign Performance Reporting

The automation calls the Meta Marketing API (v18.0 or later) on a schedule to retrieve campaign-level and ad-set-level performance metrics for the reporting window. All requests are authenticated with a long-lived user access token that must be refreshed before the 60-day expiry.

// API endpoint
GET https://graph.facebook.com/v18.0/{act_META_AD_ACCOUNT_ID}/insights
// Key request parameters
fields=impressions,clicks,spend,cpc,ctr,actions,cost_per_action_type
time_range={since: YYYY-MM-DD, until: YYYY-MM-DD}
level=campaign
// Sample response fields written to staging sheet
campaign_id | campaign_name | impressions | clicks | spend | ctr | cpc | conversions | cost_per_conversion

The automation queries the Google Analytics Data API v1 using a service account to retrieve session counts, goal completions, and traffic source breakdowns for the reporting period, aligned to the same date window as the Meta pull.

// API endpoint
POST https://analyticsdata.googleapis.com/v1beta/properties/{GA_PROPERTY_ID}:runReport
// Request body (simplified)
{dimensions:[{name:sessionSource},{name:sessionMedium},{name:sessionCampaign}],
 metrics:[{name:sessions},{name:goalCompletionsAll}],
 dateRanges:[{startDate:REPORT_START_DATE,endDate:REPORT_END_DATE}]}
// Fields written to staging sheet
session_source | session_medium | campaign_name | sessions | goal_completions

The automation uses the HubSpot Contacts and Deals APIs to retrieve contacts and associated deals created within the reporting period, filtered by campaign source UTM values, and maps them to the ad spend records from Meta.

// API endpoint (contacts search)
POST https://api.hubapi.com/crm/v3/objects/contacts/search
// Filter: contacts created in reporting window with campaign source populated
{filterGroups:[{filters:[{propertyName:createdate,operator:BETWEEN,
  value:REPORT_START_EPOCH,highValue:REPORT_END_EPOCH},
  {propertyName:hs_analytics_source_data_2,operator:HAS_PROPERTY}]}],
 properties:[hs_analytics_source,hs_analytics_source_data_1,hs_analytics_source_data_2,
  email,createdate],limit:100,after:CURSOR}
// Fields written to staging sheet
contact_id | hs_source | hs_medium | hs_campaign | createdate | associated_deal_id | deal_amount

Google Sheets serves as the central staging and normalisation store. The Data Collection Agent writes raw outputs from all three platforms to a staging sheet. The Normalisation and Insight Agent reads from staging, transforms the data, and writes the clean normalised dataset and derived KPIs to the master sheet.

// Write raw data to staging (Data Collection Agent)
POST https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/{STAGING_SHEET_NAME}!A1:append
// Read staging and write normalised output (Normalisation Agent)
GET  https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/{STAGING_SHEET_NAME}!A:Z
PUT  https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/{MASTER_SHEET_NAME}!A1
// Set completion flag
PUT  https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/Staging!A1
// Body: {values:[[READY]]}

Looker Studio does not expose a direct refresh API. The dashboard is kept live by virtue of its connection to the Master tab in Google Sheets. When the Normalisation Agent writes updated data to the Master sheet, Looker Studio reflects the new values on the next viewer load or scheduled refresh.

// No direct API call. Distribution Agent confirmation check:
GET https://sheets.googleapis.com/v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/Master!A1
// Expected response value to proceed:
{values:[[UPDATED]]}
// Dashboard URL appended to Slack message:
LOOKER_DASHBOARD_URL = https://lookerstudio.google.com/reporting/{REPORT_ID}

The Distribution Agent posts the formatted report summary to a designated Slack channel using the Slack Web API. The message includes top-line metric callouts, any underperformance flags, and a hyperlink to the Looker Studio dashboard.

// API endpoint
POST https://slack.com/api/chat.postMessage
// Headers
Authorization: Bearer {SLACK_BOT_TOKEN}
Content-Type: application/json
// Payload (Block Kit, simplified)
{channel: SLACK_CHANNEL_ID,
 blocks: [
   {type:header, text:{type:plain_text, text:SLACK_REPORT_TITLE_PREFIX - report_period}},
   {type:section, fields:[
     {type:mrkdwn, text:*Total Spend:* total_spend},
     {type:mrkdwn, text:*Conversions:* total_conversions},
     {type:mrkdwn, text:*Cost per Lead:* cost_per_lead},
     {type:mrkdwn, text:*Top Performer:* top_performer}]},
   {type:section, text:{type:mrkdwn,
     text::warning: Underperformer: underperformer}},
   {type:actions, elements:[{type:button, text:{type:plain_text,
     text:View Dashboard}, url:dashboard_url}]}
 ]}

// CREDENTIAL STORE CONTENTS
// All values are encrypted at rest. Reference by key name only in workflow config.

META_ACCESS_TOKEN          = <long-lived user access token, refresh every 50 days>
META_APP_ID                = <Meta App ID from Business Manager>
META_APP_SECRET            = <Meta App Secret from Business Manager>
GA_SERVICE_ACCOUNT_JSON    = <full JSON key file content for Google service account>
HUBSPOT_PRIVATE_APP_TOKEN  = <HubSpot private app Bearer token>
SLACK_BOT_TOKEN            = <Slack Bot Token, format xoxb-...>

// ROTATION SCHEDULE
META_ACCESS_TOKEN          -> refresh every 50 days via token extend endpoint
GA_SERVICE_ACCOUNT_JSON    -> rotate key annually or on team member offboarding
HUBSPOT_PRIVATE_APP_TOKEN  -> does not expire; rotate on team member offboarding
SLACK_BOT_TOKEN            -> does not expire; rotate on team member offboarding

// NEVER store the following in workflow config or logs:
// META_APP_SECRET, GA_SERVICE_ACCOUNT_JSON private_key field, SLACK_BOT_TOKEN