Developer Handover Pack

Everything needed to build the automation from scratch: current state, full agent specs, data flow, and the build stack.

4 pagesPDF · Technical

FS-DOC-04Technical

Developer Handover Pack

Sales Forecasting Automation

[YourCompany.com] · Sales Department · Prepared by FullSpec · [Today's Date]

This document is the complete technical reference for the Sales Forecasting automation build. It contains the current-state process map with bottleneck flags, full agent specifications including IO contracts and build constraints, the end-to-end data flow trace, and the recommended build stack with time estimates. FullSpec owns everything in this document end to end: the builds, integrations, tests, and deployment. Your team's responsibility is to confirm access credentials, validate the Google Sheets template structure, and provide approval during parallel-run testing.

01Process snapshot

Step

Name

Description

Export Pipeline Data from CRM

Sales manager logs into HubSpot and exports all open deals (stage, amount, close date, owner) as a CSV requiring manual cleaning. Time cost: 20 min.

2 BOTTLENECK

Chase Reps for Record Updates

Reps are messaged via Slack to update deals with missing amounts, stale close dates, or wrong stage. Replies trickle in over hours and block the forecast from starting accurately. Time cost: 45 min.

Clean and Organise Export Data

The raw CSV is opened in Google Sheets; duplicates, test deals, and blank fields are removed by hand and columns are renamed to match the template. Time cost: 30 min.

Apply Probability Weighting by Stage

Each deal amount is multiplied by the close probability for its pipeline stage and the weighted value is entered into the forecast model manually. Time cost: 25 min.

Compare Against Historical Conversion Rates

Historical conversion data from a separate tab or report is pulled in to benchmark whether the pipeline is tracking above or below historical norms. Time cost: 20 min.

6 BOTTLENECK

Build Weekly and Monthly Projection Tables

Weighted deal values are grouped by expected close week and month to produce near-term and 90-day views; charts are refreshed manually each cycle. Time cost: 30 min.

Review Forecast for Anomalies

Sales manager reads through projection tables, flags over-valued or at-risk deals, and adjusts weightings where something looks off. Time cost: 20 min. (RETAINED, human step)

Distribute Forecast to Stakeholders

Finished spreadsheet or PDF export is emailed to leadership via Gmail; a summary is pasted manually into Slack. Time cost: 15 min.

Update Tableau Dashboard Manually

Tableau data source is refreshed or a new data file is uploaded so charts reflect the latest forecast figures. Time cost: 15 min.

Time cost summary: Total manual time per cycle is 220 minutes (3 hours 40 min). At approximately 4 cycles per month (plus ad-hoc runs), this equates to roughly 6 hours of manual effort per week across the sales manager and sales ops. Steps 1, 2, 3, 4, 5, 6, 8, and 9 are replaced by automation. Step 7 (manager review and approval) is intentionally retained as a human gate before distribution.

Developer Handover PackPage 1 of 4

FS-DOC-04Technical

02Agent specifications

Data Pull and Completeness Agent

Connects to HubSpot on a fixed weekly schedule or on a stage-change event for any deal above the configured value threshold. Retrieves all open deal records via the HubSpot Deals API, evaluates each record for completeness and staleness against defined rules (missing amount, close date older than 30 days, no stage update in 14 days), writes a completeness flag back to HubSpot, and dispatches a targeted Slack direct message to each rep whose records are flagged. Once flagged records have been notified, the agent confirms the dataset is ready for handoff to the Forecast Calculation Agent.

Trigger

Weekly schedule (Monday 06:00 local time) OR HubSpot deal stage-change webhook for deals above the configured value threshold

Tools

HubSpot (Deals API), Slack (DM via Bot token)

Replaces steps

Steps 1 and 2 (Export Pipeline Data, Chase Reps for Record Updates)

Estimated build

12 hours — Moderate

// Input
trigger_type: 'schedule' | 'stage_change'
hubspot_deal_id?: string          // only present on stage-change trigger
value_threshold: number            // USD, configurable; default 5000

// HubSpot Deals API response fields consumed
deal.hs_object_id: string
deal.dealname: string
deal.dealstage: string             // must match configured stage map
deal.amount: number | null
deal.closedate: ISO8601 | null
deal.hubspot_owner_id: string
deal.hs_lastmodifieddate: ISO8601

// Output (passed to Forecast Calculation Agent)
dataset_ready: boolean
pull_timestamp: ISO8601
deals: Deal[]                       // full array of open deal records
flagged_deal_ids: string[]          // IDs with incomplete or stale data
slack_nudges_sent: number           // count of DMs dispatched

HubSpot API access requires a Private App token with scopes: crm.objects.deals.read, crm.objects.deals.write (for flagging), crm.objects.owners.read. Confirm the token is scoped correctly before build starts.
Pipeline stage names in HubSpot must be consistent and match the stage map configured in the agent. Any custom stage names must be provided by sales ops before the probability-weight map can be finalised.
Each pipeline stage must have a probability value set in HubSpot (0 to 100). Stages with no probability value will cause the Forecast Calculation Agent to fall back to 0, which will suppress those deals from the weighted total. This must be confirmed before build.
Staleness rules (close date older than 30 days, no stage update in 14 days) are configurable constants. The FullSpec team will set defaults; the process owner must confirm these thresholds are appropriate before go-live.
Slack nudges are sent as DMs from a dedicated bot account. The bot must be invited to the workspace and granted chat:write scope. Slack user IDs are mapped from HubSpot owner IDs via the owners API endpoint; if a rep has no Slack user linked, the nudge falls back to a named channel post.
Deduplication: on a stage-change trigger, the agent checks whether a full scheduled pull has already run within the past 2 hours and skips the full pull if so, processing only the changed deal instead.
The agent must be confirmed to have read/write access to HubSpot before any test run; a read-only token will cause the flag-write step to fail silently.

Forecast Calculation Agent

Triggered when the Data Pull and Completeness Agent signals that the deal dataset is ready. Retrieves the confirmed deal array, applies stage-based probability weights sourced from the configured stage map, aggregates weighted deal values into weekly and monthly projection buckets grouped by close date, compares output totals against stored historical conversion benchmarks, flags deals whose weighted value deviates materially from historical norm for that stage, and writes the full output (projection tables, weighted totals, variance flags, refreshed chart data) to the designated Google Sheets forecast template. Also triggers a Tableau data source refresh after the Sheets write completes.

Trigger

Data Pull and Completeness Agent emits dataset_ready: true

Tools

Google Sheets (Sheets API v4), Tableau (REST API)

Replaces steps

Steps 3, 4, 5, and 6 (Clean and Organise Data, Apply Probability Weighting, Compare Historical Rates, Build Projection Tables)

Estimated build

16 hours — Moderate

// Input (from Data Pull and Completeness Agent)
deals: Deal[]
pull_timestamp: ISO8601
stage_probability_map: Record<string, number>  // e.g. { 'Proposal Sent': 40, 'Negotiation': 70 }
historical_benchmarks: Record<string, number>  // weighted_total by month, last 12 cycles

// Computed fields per deal
deal.weighted_value: number           // amount * (stage_probability / 100)
deal.close_week: string               // ISO week string, e.g. '2025-W22'
deal.close_month: string              // e.g. '2025-06'
deal.variance_flag: boolean           // true if weighted_value deviates > 20% from stage norm

// Output written to Google Sheets
sheet: 'ForecastOutput'
range_weekly: 'B4:Z50'               // weekly projection table
range_monthly: 'B55:Z70'             // monthly projection table
range_variance: 'A75:F100'           // flagged deals with deviation detail
range_summary: 'B2:D3'              // total weighted pipeline, cycle timestamp

// Output: Tableau
tableau_datasource_id: string         // confirmed during build
refresh_triggered: boolean

The Google Sheets forecast template must be finalised before this agent is built. The write logic targets named ranges (ForecastOutput sheet, specific range addresses). If the template layout changes post-build, the write step will need updating.
Google Sheets API access requires a service account with Editor permission on the target spreadsheet. The service account JSON key must be stored in the shared credential store, not hardcoded.
Historical benchmark data must be pre-populated in a dedicated sheet tab (HistoricalBenchmarks) with columns: cycle_month (ISO), weighted_total (number), deals_count (integer). Sales ops must supply at least 6 months of back-data before QA testing begins.
Variance flagging threshold defaults to 20% deviation from the stage-level historical mean. This is a configurable constant the process owner must confirm.
Tableau refresh uses the Tableau REST API (POST /datasources/{id}/refresh). The datasource ID must be confirmed by sales ops. Licence tier must support API-triggered refreshes; if the account is on Tableau Creator with a published data source connected to Google Sheets, the refresh will succeed. If not, step 9 will remain partially manual and must be documented in the runbook.
Chart refresh in Google Sheets is automatic after the range write if charts are bound to named ranges. If charts are bound to static cell references, they will need to be updated to named ranges before build.
Deduplication: the agent writes a cycle ID (UUID + pull_timestamp) to a Sheets log tab on each run. If the same pull_timestamp is detected on a repeat trigger, the write step is skipped and an alert is logged.

Distribution Agent

Polls the Google Sheets review tab for the sales manager's approval flag on a 5-minute interval. When the approval cell is set to 'APPROVED', the agent reads the completed forecast summary from the ForecastOutput sheet, composes a formatted HTML email via Gmail, sends it to the configured leadership distribution list, and posts a structured pipeline snapshot message to the designated Slack channel. The agent logs the distribution event and timestamp to the run log tab in Google Sheets. If the approval cell is set to 'REJECTED' or remains unset after 48 hours, the agent posts a reminder to the manager's Slack DM and halts until the cell is updated.

Trigger

Approval cell in Google Sheets 'ReviewTab'!B2 is set to 'APPROVED' by the sales manager

Tools

Google Sheets (Sheets API v4), Gmail (Gmail API, OAuth2), Slack (Web API, chat.postMessage)

Replaces steps

Steps 8 and 9 (Distribute Forecast via Email, Update Tableau Dashboard)

Estimated build

10 hours — Moderate

// Input (polled from Google Sheets)
sheet: 'ReviewTab'
approval_cell: 'B2'                  // value: 'APPROVED' | 'REJECTED' | ''
reviewer_name_cell: 'B3'             // populated by manager before approving
approval_timestamp_cell: 'B4'        // written by agent on detection

// Forecast summary fields read from ForecastOutput
summary.total_weighted_pipeline: number
summary.cycle_month: string
summary.deals_in_forecast: number
summary.variance_flags_count: number
summary.weekly_table_range: string    // embedded in email as HTML table

// Output: Gmail
to: string[]                          // leadership distribution list, configured at build
subject: 'Sales Forecast: {cycle_month} | Approved by {reviewer_name}'
body_format: 'text/html'
attachment: null                      // no attachment; all data inline

// Output: Slack
channel: '#sales-forecast'            // configurable
message_format: 'block_kit'
fields: ['total_weighted_pipeline', 'deals_in_forecast', 'variance_flags_count', 'approval_timestamp']

// On approval
run_log_tab: 'RunLog'
log_fields: ['cycle_id', 'pull_timestamp', 'approval_timestamp', 'reviewer_name', 'gmail_message_id', 'slack_ts']

// On rejection or timeout (48 hrs)
fallback_action: 'slack_dm_to_manager'
fallback_message: 'Forecast for {cycle_month} is awaiting your approval in Google Sheets.'

Gmail API requires OAuth2 with scope mail.send. The sending account must be a real Google Workspace mailbox (not a service account alone). FullSpec will configure OAuth2 with offline access; the refresh token must be stored in the shared credential store.
The leadership distribution list is a configurable array set at build time. The process owner must supply the final recipient list before QA testing.
Slack bot requires chat:write and im:write scopes. The #sales-forecast channel must exist and the bot must be invited to it before testing.
The approval polling interval is 5 minutes by default. This is configurable and can be reduced to 1 minute if the process owner requires faster distribution after approval.
If the ReviewTab approval cell is set by anyone other than the authorised manager account, the agent still processes the approval. Access control for the review tab should be restricted at the Google Sheets level (protected range) before go-live. The FullSpec team will flag this as a setup step in the runbook.
The 48-hour timeout reminder is a safeguard only. If the sales manager does not approve within 48 hours, the forecast cycle is logged as 'pending' in the RunLog tab and a Slack DM reminder is sent. The cycle does not auto-expire; it must be manually approved or the run log entry must be marked 'cancelled'.
Gmail sending limits: the Gmail API allows 500 messages per day for Workspace accounts. The distribution list must stay below this limit per cycle, which is not a concern for typical SMB use.

Developer Handover PackPage 2 of 4

FS-DOC-04Technical

03End-to-end data flow

Full trigger-to-output data flow, Sales Forecasting Automation

// ─────────────────────────────────────────────────────────────────
// TRIGGER
// ─────────────────────────────────────────────────────────────────
TRIGGER:
  type: 'schedule'           // Monday 06:00 local time, weekly
  OR
  type: 'stage_change'       // HubSpot webhook: deal.stage updated
  payload.deal_id: string
  payload.new_stage: string
  payload.deal_amount: number
  condition: deal_amount >= value_threshold (default: $5,000)

// ─────────────────────────────────────────────────────────────────
// AGENT 1: Data Pull and Completeness Agent
// ─────────────────────────────────────────────────────────────────
AGENT_1_INPUT:
  trigger_type: string
  hubspot_deal_id?: string

STEP_1A: HubSpot Deals API (GET /crm/v3/objects/deals)
  filter: pipeline_stage != 'Closed Won' AND pipeline_stage != 'Closed Lost'
  properties: [hs_object_id, dealname, dealstage, amount, closedate,
               hubspot_owner_id, hs_lastmodifieddate]
  response -> deals[]

STEP_1B: Completeness check per deal
  flag if: amount == null
       OR: closedate < (now - 30 days)
       OR: hs_lastmodifieddate < (now - 14 days)
  flagged_deals[] = deals where any flag == true
  clean_deals[]   = deals where all flags == false

STEP_1C: HubSpot write-back (PATCH /crm/v3/objects/deals/{id})
  property: hs_tag_ids += 'stale_or_incomplete'
  applied to: flagged_deals[]

STEP_1D: Slack DM per flagged rep
  HubSpot owners API -> map hubspot_owner_id to slack_user_id
  POST /api/chat.postMessage
  channel: slack_user_id (DM) OR fallback: '#sales-updates'
  text: 'Hi {rep_name}, the following deals need updating before
         the forecast runs: {deal_list}. Please update by EOD.'

AGENT_1_OUTPUT -> AGENT_2:
  dataset_ready: true
  pull_timestamp: ISO8601
  deals: Deal[]              // all open deals, flagged field included
  flagged_deal_ids: string[]
  slack_nudges_sent: number

// ─────────────────────────────────────────────────────────────────
// AGENT 2: Forecast Calculation Agent
// ─────────────────────────────────────────────────────────────────
AGENT_2_INPUT:
  deals: Deal[]
  pull_timestamp: ISO8601
  stage_probability_map: { [stage_name: string]: number }
  historical_benchmarks: { [month: string]: number }

STEP_2A: Apply probability weights
  for each deal in deals[]:
    deal.weighted_value = deal.amount * (stage_probability_map[deal.dealstage] / 100)
    deal.close_week     = isoWeek(deal.closedate)
    deal.close_month    = isoMonth(deal.closedate)
    deal.variance_flag  = abs(deal.weighted_value - historical_stage_mean)
                          / historical_stage_mean > 0.20

STEP_2B: Aggregate into projection tables
  weekly_table[]  = GROUP BY close_week  -> SUM(weighted_value), COUNT(deals)
  monthly_table[] = GROUP BY close_month -> SUM(weighted_value), COUNT(deals)
  variance_list[] = deals where variance_flag == true

STEP_2C: Google Sheets write (Sheets API v4, batchUpdate)
  spreadsheet_id: <confirmed at build>
  writes:
    'ForecastOutput'!B4:Z50   <- weekly_table[]
    'ForecastOutput'!B55:Z70  <- monthly_table[]
    'ForecastOutput'!A75:F100 <- variance_list[]
    'ForecastOutput'!B2:D3    <- [total_weighted_pipeline, pull_timestamp, cycle_id]
    'ReviewTab'!B2            <- '' (reset approval cell for new cycle)
    'RunLog'!A:G              <- APPEND [cycle_id, pull_timestamp, 'pending', ...]

STEP_2D: Tableau refresh (POST /api/2.1/sites/{site_id}/datasources/{id}/refresh)
  auth: Tableau Personal Access Token
  datasource_id: <confirmed at build>
  response: job_id -> poll for completion (max 5 min)

AGENT_2_OUTPUT -> ReviewTab (human gate):
  Google Sheets 'ReviewTab' is now populated
  Sales manager receives Slack notification: 'Forecast ready for review.'
  'ReviewTab'!B2 awaits: 'APPROVED' | 'REJECTED'

// ─────────────────────────────────────────��───────────────────────
// HUMAN STEP: Sales Manager Review (Step 7, retained)
// ─────────────────────────────────────────────────────────────────
HUMAN_GATE:
  actor: Sales Manager
  action: Reviews 'ForecastOutput' and 'ReviewTab' in Google Sheets
  sets: 'ReviewTab'!B2 = 'APPROVED' (or 'REJECTED')
  sets: 'ReviewTab'!B3 = reviewer_name
  estimated_time: 15 minutes

// ─────────────────────────────────────────────────────────────────
// AGENT 3: Distribution Agent
// ─────────────────────────────────────────────────────────────────
AGENT_3_TRIGGER:
  poll: 'ReviewTab'!B2 every 5 minutes
  condition: value == 'APPROVED'

AGENT_3_INPUT:
  approval_cell_value: 'APPROVED'
  reviewer_name: string
  summary fields from 'ForecastOutput'!B2:D3
  weekly_table from 'ForecastOutput'!B4:Z50

STEP_3A: Gmail send (Gmail API, POST /gmail/v1/users/me/messages/send)
  to: [leadership_distribution_list]
  subject: 'Sales Forecast: {cycle_month} | Approved by {reviewer_name}'
  body: HTML template with inline weekly_table and summary KPIs
  response: gmail_message_id

STEP_3B: Slack post (POST /api/chat.postMessage)
  channel: '#sales-forecast'
  blocks: Block Kit card with:
    total_weighted_pipeline, deals_in_forecast,
    variance_flags_count, approval_timestamp, link to Sheets
  response: slack_ts

STEP_3C: Run log update (Sheets API v4)
  'RunLog' row for cycle_id -> UPDATE status: 'distributed'
  log fields: [cycle_id, pull_timestamp, approval_timestamp,
               reviewer_name, gmail_message_id, slack_ts]

// ─────────────────────────────────────────────────────────────────
// ON REJECTION or 48-hr TIMEOUT
// ─────────────────────────────────────────────────────────────────
FALLBACK:
  if 'ReviewTab'!B2 == 'REJECTED':
    Slack DM to manager: 'Forecast marked as rejected. Please review
                          and re-approve or contact the FullSpec team.'
    RunLog status -> 'rejected'
  if poll_elapsed > 48 hours AND 'ReviewTab'!B2 still == '':
    Slack DM to manager: 'Forecast for {cycle_month} awaiting approval.'
    RunLog status -> 'pending_reminder_sent'

Developer Handover PackPage 3 of 4

FS-DOC-04Technical

04Recommended build stack

Orchestration layer

A workflow automation platform configured with one workflow per agent (three workflows total): 'Agent 1: Data Pull and Completeness', 'Agent 2: Forecast Calculation', 'Agent 3: Distribution'. All credentials (HubSpot Private App token, Google service account JSON, Slack bot token, Gmail OAuth2 refresh token, Tableau PAT) are stored in a single shared credential store, never hardcoded in workflow nodes. Workflow-level error handlers are configured on every node to route failures to the error logging sink.

Webhook configuration

A single inbound webhook endpoint is registered in HubSpot to fire on deal.propertyChange for the dealstage property. The webhook payload is validated against a shared secret (HMAC-SHA256). The endpoint is exposed via the automation platform's built-in webhook receiver. For the schedule trigger, a cron expression of '0 6 * * 1' (Monday 06:00) drives the Agent 1 workflow on the same entry point. A value_threshold filter (default $5,000) is applied immediately after the webhook is received to discard low-value stage-change events.

Templating approach

Gmail email body is an HTML template stored as a workflow constant, with {{cycle_month}}, {{reviewer_name}}, {{total_weighted_pipeline}}, {{deals_in_forecast}}, and {{variance_flags_count}} as interpolation variables. The weekly projection table is rendered as an HTML table from the Google Sheets range read before dispatch. Slack Block Kit payloads are defined as JSON templates within the workflow node. Google Sheets write targets named ranges to decouple the build from cell-address changes; any template layout change requires only a named range update, not a workflow rebuild.

Error logging

All workflow-level errors are written to a Supabase table ('automation_errors') with fields: workflow_name, node_name, error_message, error_timestamp, cycle_id, payload_snapshot (JSON). A Supabase edge function triggers a Slack alert to a private '#automation-alerts' channel whenever a new row is inserted with severity >= 'error'. Warnings (e.g. Tableau refresh job took longer than 5 minutes) are logged to the same table with severity 'warn' and do not trigger a Slack alert. The FullSpec team monitors this channel during the parallel-run testing period.

Testing approach

All three agents are built and tested in a staging environment before any connection to the production HubSpot pipeline, live Google Sheets template, or live Slack channels. Sandbox HubSpot deals with known values are used to verify probability weighting calculations. A cloned Google Sheets template (prefixed 'STAGING_') is used for write testing. Gmail sends during staging go to a single internal test address only. Tableau refresh is tested against a non-production data source. Full parallel-run testing runs three live forecast cycles alongside the manual process to verify numerical accuracy before go-live.

Estimated total build time

Agent 1 (Data Pull and Completeness): 12 hours. Agent 2 (Forecast Calculation): 16 hours. Agent 3 (Distribution): 10 hours. Subtotal agent builds: 38 hours. End-to-end integration testing, parallel-run validation, error handling, and documentation: included in the 38-hour total as scoped. Total build effort: 38 hours across a 4 to 5 week delivery window.

Before build starts, the following must be confirmed: (1) HubSpot Private App token with correct scopes issued and stored. (2) All pipeline stages in HubSpot have probability values set and stage names are finalised. (3) Google Sheets forecast template layout is locked and named ranges are applied. (4) Historical benchmark data (minimum 6 months) is populated in the HistoricalBenchmarks tab. (5) Tableau licence tier confirmed to support API-triggered refreshes. (6) Leadership distribution list for Gmail confirmed by the process owner. Any of these items outstanding at build start will cause a dependency delay. Contact support@gofullspec.com if any item cannot be confirmed before the scheduled build date.

Developer Handover PackPage 4 of 4

Developer Handover Pack