Integration and API Spec

Integration and API Spec

Marketing Attribution Reporting

The Google Ads API (v16+) is queried by the Data Fetch Agent to retrieve campaign, ad group, and conversion data for the reporting date range. Pagination is handled via page tokens. All requests use GAQL (Google Ads Query Language).

// GAQL query example (Data Fetch Agent)
SELECT campaign.name, campaign.id,
       metrics.cost_micros, metrics.clicks,
       metrics.impressions, metrics.conversions,
       metrics.conversions_value, segments.date
FROM campaign
WHERE segments.date BETWEEN '{{period_start}}' AND '{{period_end}}'
  AND campaign.status = 'ENABLED'
ORDER BY segments.date ASC

// Output written to Google Sheets staging tab: gads_raw

The Meta Marketing API (v19.0+) is queried by the Data Fetch Agent to retrieve campaign-level spend, impressions, clicks, and conversions for the reporting period. Results are paginated using cursor-based pagination.

// Meta Insights API call (Data Fetch Agent)
GET /{ad_account_id}/insights
  ?fields=campaign_name,campaign_id,spend,impressions,
          clicks,actions,action_values,date_start,date_stop
  &time_range={"since":"{{period_start}}","until":"{{period_end}}"}
  &level=campaign
  &time_increment=1
  &limit=500

// Output written to Google Sheets staging tab: meta_raw

The Google Analytics Data API (GA4) is queried by the Data Fetch Agent to retrieve session counts, source/medium breakdown, and key event (conversion) totals for the reporting period.

// GA4 Data API runReport body (Data Fetch Agent)
{
  "dateRanges": [{"startDate": "{{period_start}}", "endDate": "{{period_end}}"}],
  "dimensions": [
    {"name": "sessionDefaultChannelGrouping"},
    {"name": "sessionSourceMedium"},
    {"name": "date"}
  ],
  "metrics": [
    {"name": "sessions"},
    {"name": "keyEvents"},
    {"name": "totalRevenue"}
  ]
}

// Output written to Google Sheets staging tab: ga4_raw

The HubSpot CRM API is queried by the Data Fetch Agent to retrieve closed deals and lead source data for the reporting period, providing the bottom-of-funnel revenue layer for attribution.

// HubSpot deal search request body (Data Fetch Agent)
POST /crm/v3/objects/deals/search
{
  "filterGroups": [{
    "filters": [
      {"propertyName": "closedate", "operator": "BETWEEN",
       "value": "{{period_start_epoch_ms}}", "highValue": "{{period_end_epoch_ms}}"},
      {"propertyName": "dealstage", "operator": "EQ",
       "value": "{{hubspot_closed_won_stage_id}}"}
    ]
  }],
  "properties": ["dealname","closedate","amount",
    "hs_analytics_source","hs_analytics_source_data_1",
    "hs_analytics_source_data_2"],
  "limit": 100,
  "after": "{{pagination_cursor}}"
}

// Output written to Google Sheets staging tab: hubspot_raw

Google Sheets serves as both the staging area for raw fetched data (written by the Data Fetch Agent) and the master attribution output (written and read by the Attribution and Merge Agent, then read by the Summary and Delivery Agent). A single spreadsheet with named tabs is used.

// Sheets tab: control_flags schema
| agent_name              | status     | timestamp            | row_count |
|-------------------------|------------|----------------------|-----------|
| data_fetch_agent        | complete   | 2024-05-12T08:03:11Z | 142       |
| attribution_merge_agent | complete   | 2024-05-12T08:07:44Z | 5         |
| summary_delivery_agent  | in_progress| 2024-05-12T08:08:01Z | -         |

// Attribution and Merge Agent reads: gads_raw, meta_raw, ga4_raw, hubspot_raw
// Attribution and Merge Agent writes: attribution_output, control_flags
// Summary and Delivery Agent reads: attribution_output, control_flags

Slack receives the final formatted report message and Google Sheets link from the Summary and Delivery Agent via an incoming webhook or bot token post to the configured channel.

// Slack chat.postMessage payload (Summary and Delivery Agent)
POST https://slack.com/api/chat.postMessage
{
  "channel": "{{slack_channel_id}}",
  "text": "Attribution report ready for {{period_label}}",
  "blocks": [
    {"type": "section", "text": {"type": "mrkdwn",
      "text": "*Attribution Report: {{period_label}}*\n{{summary_text}}"}},
    {"type": "section", "text": {"type": "mrkdwn",
      "text": "<{{sheets_report_url}}|Open full report in Google Sheets>"}}
  ]
}

// Credential store -- full key list
// Google Ads
GADS_CLIENT_ID              = "<oauth_client_id>"
GADS_CLIENT_SECRET          = "<oauth_client_secret>"
GADS_REFRESH_TOKEN          = "<oauth_refresh_token>"
GADS_DEVELOPER_TOKEN        = "<developer_token>"
GADS_CUSTOMER_ID            = "<customer_id_without_dashes>"

// Meta Ads Manager
META_APP_ID                 = "<meta_app_id>"
META_APP_SECRET             = "<meta_app_secret>"
META_SYSTEM_USER_TOKEN      = "<long_lived_system_user_access_token>"
META_AD_ACCOUNT_ID          = "act_XXXXXXXXXX"
META_ATTRIBUTION_WINDOW     = "7d_click"

// Google Analytics 4
GA4_SERVICE_ACCOUNT_EMAIL   = "<service_account>@<project>.iam.gserviceaccount.com"
GA4_SERVICE_ACCOUNT_KEY     = "<private_key_pem_string>"
GA4_PROPERTY_ID             = "<numeric_property_id>"

// HubSpot
HUBSPOT_PRIVATE_APP_TOKEN   = "pat-na1-XXXXXXXXXXXXXXXXXXXXXXXX"
HUBSPOT_PORTAL_ID           = "<portal_id>"
HUBSPOT_PIPELINE_ID         = "<pipeline_id>"
HUBSPOT_CLOSED_WON_STAGE_ID = "closedwon"

// Google Sheets
SHEETS_SERVICE_ACCOUNT_EMAIL = "<same_as_GA4_or_separate>"
SHEETS_SERVICE_ACCOUNT_KEY   = "<private_key_pem_string>"
SHEETS_SPREADSHEET_ID        = "<spreadsheet_id_from_url>"
SHEETS_REPORT_URL            = "https://docs.google.com/spreadsheets/d/<id>/edit"

// Slack
SLACK_BOT_TOKEN              = "xoxb-XXXXXXXXXXXX-XXXXXXXXXXXXX-XXXXXXXXXXXXXXXXXXXXXXXX"
SLACK_CHANNEL_ID             = "C0XXXXXXXXX"

// Staging equivalents (prefix STAGING_ on all keys above)
// e.g. STAGING_GADS_CUSTOMER_ID, STAGING_SLACK_CHANNEL_ID, etc.