Developer Handover Pack

Developer Handover Pack

Churn Risk Identification

This agent runs on a fixed daily schedule and is responsible for collecting raw customer signals from three source systems, Mixpanel for product usage events, Intercom for support ticket data, and HubSpot for CRM account health fields. It normalises all three data sets into a single account snapshot object per active customer, resolving identity across systems using the HubSpot company ID as the canonical key. The output is a structured JSON array passed directly to the Risk Scoring and Routing Agent. No scoring or routing logic lives in this agent; its only concern is data fidelity and completeness. Estimated build time: 10 hours. Complexity: Moderate.

// Input
trigger: { type: 'schedule', time: '06:00', timezone: 'account_local' }
source_accounts: HubSpot Companies API -> filter: { lifecycle_stage: 'customer', is_active: true }

// Per account: Mixpanel fetch
mixpanel.export({ event: ['Login', 'Feature Used', 'Session Start'],
  from_date: today-30d, to_date: today,
  where: 'properties["$company_id"] == account.hubspot_company_id' })
-> fields: { login_count_7d, login_count_30d, feature_events_30d, last_login_date }

// Per account: Intercom fetch
intercom.conversations.list({ company_id: account.intercom_company_id,
  filter: { state: 'open', created_at_gt: today-14d } })
-> fields: { open_ticket_count, tickets_gt_5d_old, total_tickets_30d, last_ticket_date }

// Per account: HubSpot fetch
hubspot.companies.get(account.hubspot_company_id)
-> fields: { hs_last_activity_date, hs_analytics_last_visit_timestamp,
             custom_health_score, csm_owner_id, account_name, mrr }

// Output (one object per account, passed to Risk Scoring Agent)
{
  hubspot_company_id: string,
  account_name: string,
  csm_owner_id: string,
  mrr: number,
  login_count_7d: number,
  login_count_30d: number,
  feature_events_30d: number,
  last_login_date: ISO8601,
  open_ticket_count: number,
  tickets_gt_5d_old: number,
  total_tickets_30d: number,
  last_ticket_date: ISO8601,
  hs_last_activity_date: ISO8601,
  custom_health_score: number | null,
  snapshot_timestamp: ISO8601
}

This agent receives the normalised account snapshot array from the Churn Signal Aggregator and applies a configurable weighted scoring model to assign each account a risk tier of High, Medium, or Low. For every account scored High, it drafts a personalised outreach email using account-specific field values, prepares a structured Slack alert payload, updates the HubSpot company record with the risk tier and triggering signals, and posts the Slack alert with the draft email body linked or embedded for CSM review. Once a CSM approves the draft via the Slack interactive button, the agent sends the email through Gmail and then logs the activity and creates a follow-up task in HubSpot. Medium and Low accounts receive only the HubSpot risk tier update; no email or Slack alert is generated for them. Estimated build time: 16 hours. Complexity: Moderate.

// Input
account_snapshot_array: AccountSnapshot[]  // from Churn Signal Aggregator

// Scoring model (configurable thresholds, confirm values before build)
score_signals: {
  login_drop_pct: (login_count_7d < login_count_30d * 0.4) -> +3,
  open_tickets_high: (open_ticket_count >= 3) -> +2,
  ticket_age_flag: (tickets_gt_5d_old >= 1) -> +2,
  no_recent_activity: (days_since(hs_last_activity_date) > 14) -> +2,
  health_score_low: (custom_health_score != null && custom_health_score < 40) -> +3
}
tier_thresholds: { high: score >= 5, medium: score 2-4, low: score <= 1 }

// Output: HubSpot update (all tiers)
hubspot.companies.update(hubspot_company_id, {
  churn_risk_tier: 'High' | 'Medium' | 'Low',
  churn_risk_signals: string[],
  churn_risk_score: number,
  churn_risk_last_updated: ISO8601
})

// Output: Slack alert (High tier only)
slack.chat.postMessage({
  channel: '#churn-risk-alerts',
  blocks: [
    { type: 'header', text: 'High-Risk Account: {account_name}' },
    { type: 'section', fields: [score, signals, mrr, csm_owner] },
    { type: 'actions', elements: [
      { type: 'button', text: 'Approve Email', action_id: 'approve_outreach' },
      { type: 'button', text: 'Edit Before Sending', action_id: 'edit_outreach' }
    ]}
  ],
  metadata: { hubspot_company_id, draft_email_body, csm_owner_id }
})

// On approval (Slack interactive callback -> action_id: 'approve_outreach')
gmail.users.messages.send({
  to: account.primary_contact_email,
  from: csm_owner_gmail_address,
  subject: 'Checking in, {account_name}',
  body: draft_email_body  // populated with account_name, csm_owner_name, signals summary
})
hubspot.engagements.create({
  type: 'EMAIL',
  hubspot_company_id,
  body: draft_email_body,
  timestamp: ISO8601
})
hubspot.tasks.create({
  hubspot_company_id,
  title: 'Follow up: churn risk outreach',
  due_date: today + (risk_tier == 'High' ? 3 : 7),
  owner_id: csm_owner_id
})

// ────────────────────���────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────
schedule.cron('06:00', timezone='account_local')
  -> emit: { run_id: uuid, triggered_at: ISO8601 }

// ─────────────────────────────────────────────────────────────
// AGENT 1: Churn Signal Aggregator
// ─────────────────────────────────────────────────────────────

// Step 1a: Fetch active customer accounts from HubSpot
HubSpot.Companies.list({ filter: { lifecycle_stage: 'customer', is_active: true } })
  -> accounts[]: { hubspot_company_id, account_name, csm_owner_id, mrr,
                   hs_last_activity_date, custom_health_score }

// Step 1b: For each account, fetch Mixpanel usage signals (parallel)
Mixpanel.DataExport.query({
  event: ['Login', 'Feature Used', 'Session Start'],
  from_date: today-30d, to_date: today,
  where: 'properties["$company_id"] == hubspot_company_id'
})
  -> usage: { login_count_7d, login_count_30d, feature_events_30d, last_login_date }

// Step 1c: For each account, fetch Intercom ticket signals (parallel)
Intercom.Conversations.list({
  company_id: intercom_company_id,
  filter: { state: 'open', created_at_gt: today-14d }
})
  -> tickets: { open_ticket_count, tickets_gt_5d_old, total_tickets_30d, last_ticket_date }

// Step 1d: Merge and normalise into account snapshot
merge(accounts[], usage, tickets)
  -> snapshot: AccountSnapshot {
       hubspot_company_id, account_name, csm_owner_id, mrr,
       login_count_7d, login_count_30d, feature_events_30d, last_login_date,
       open_ticket_count, tickets_gt_5d_old, total_tickets_30d, last_ticket_date,
       hs_last_activity_date, custom_health_score, snapshot_timestamp
     }

// Exception: if cross-system ID missing -> write to error_log, skip account
// Exception: if Mixpanel returns 0 events -> login_count_7d = 0 (valid signal)
// Exception: if duplicate run detected (snapshot_timestamp = today) -> halt

// ── HANDOFF: Agent 1 -> Agent 2 ──────────────────────────────
emit: { run_id, snapshots: AccountSnapshot[], account_count: number }

// ─────────────────────────────────────────────────────────────
// AGENT 2: Risk Scoring and Routing Agent
// ─────────────────────────────────────────────────────────────

// Step 2a: Score each account
for each snapshot in snapshots:
  score = 0
  signals = []
  if (login_count_7d < login_count_30d * 0.4):  score += 3; signals.push('login_drop')
  if (open_ticket_count >= 3):                   score += 2; signals.push('high_ticket_volume')
  if (tickets_gt_5d_old >= 1):                   score += 2; signals.push('aged_ticket')
  if (days_since(hs_last_activity_date) > 14):   score += 2; signals.push('no_crm_activity')
  if (custom_health_score != null
      && custom_health_score < 40):               score += 3; signals.push('low_health_score')
  tier = score >= 5 ? 'High' : score >= 2 ? 'Medium' : 'Low'
  -> scored_account: { ...snapshot, score, signals, tier }

// Step 2b: Write risk tier to HubSpot (all tiers)
HubSpot.Companies.update(hubspot_company_id, {
  churn_risk_tier: tier,
  churn_risk_signals: signals[],
  churn_risk_score: score,
  churn_risk_last_updated: ISO8601
})
  -> hubspot_update_status: 'success' | 'error'

// Step 2c: High-tier accounts only — draft outreach email
if tier == 'High':
  draft_email_body = template.render('churn_outreach_v1', {
    account_name, csm_owner_name, signals_summary, mrr
  })
  primary_contact_email = HubSpot.Contacts.getPrimary(hubspot_company_id)
    -> { contact_email, contact_first_name }

// Step 2d: Post Slack alert with approval buttons (High tier only)
  Slack.chat.postMessage({
    channel: '#churn-risk-alerts',
    blocks: [header, signal_summary, draft_preview, approve_button, edit_button],
    metadata: { hubspot_company_id, draft_email_body,
                csm_owner_id, contact_email, run_id }
  })
  -> slack_message_ts: string  // stored for thread reply on approval

// ── WAIT STATE: Slack interactive callback ────────────────────
// Webhook receives POST from Slack: { action_id, payload.metadata }
// Timeout: 48h -> log 'pending_approval_timeout', re-post reminder

// Step 2e: On approval (action_id == 'approve_outreach')
Gmail.users.messages.send({
  userId: csm_owner_gmail_address,
  to: contact_email,
  subject: 'Checking in, {account_name}',
  body: draft_email_body
})
  -> gmail_message_id: string

// Step 2f: Log email activity in HubSpot
HubSpot.Engagements.create({
  type: 'EMAIL',
  associations: [{ type: 'COMPANY', id: hubspot_company_id }],
  metadata: { subject, body: draft_email_body, from: csm_owner_gmail_address,
               to: contact_email, timestamp: ISO8601 }
})

// Step 2g: Create follow-up task in HubSpot
HubSpot.Tasks.create({
  title: 'Follow up: churn risk outreach',
  associations: [{ type: 'COMPANY', id: hubspot_company_id }],
  owner_id: csm_owner_id,
  due_date: today + 3,  // 3 days for High tier
  priority: 'HIGH'
})

// Step 2h: Post completion reply to Slack thread
Slack.chat.postMessage({
  channel: '#churn-risk-alerts',
  thread_ts: slack_message_ts,
  text: 'Email sent to {contact_email}. Follow-up task created in HubSpot.'
})

// ─────────────────────────────────────────────────────────────
// TERMINAL STATE
// ─────────────────────────────────────────────────────────────
// All accounts processed: risk tiers written to HubSpot
// High-risk accounts: Slack alert posted, email sent on approval,
//   HubSpot engagement logged, follow-up task created
// Errors: written to error_log table with run_id, account_id, error_type
// Run summary: written to run_log table { run_id, triggered_at, account_count,
//   high_count, medium_count, low_count, error_count, completed_at }