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

Client Reporting Automation

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

This document provides the complete technical specification for the Client Reporting Automation build. It covers the current-state step map with bottleneck identification, full agent specifications with IO definitions, the end-to-end data flow trace, and the recommended build stack. FullSpec handles all build, integration, and testing work described here. The process owner and account manager are referenced only where a human action remains in the automated flow.

01Process snapshot

Step

Name

Description

Identify Reports Due This Period

Operations Manager reviews a shared calendar or Google Sheets schedule to find which clients have reports due this week or month. Time cost: 15 min/cycle.

Pull Performance Data from CRM

BOTTLENECK: Team member logs into HubSpot and exports activity, pipeline, or campaign metrics for each client account. Error-prone and volume-sensitive. Time cost: 30 min/cycle.

Pull Supporting Metrics from Other Sources

BOTTLENECK: Additional figures (website traffic, ad spend, project status) are gathered from secondary tools and noted in a staging spreadsheet. High context-switching cost. Time cost: 25 min/cycle.

Populate Report Template

All collected figures are entered into a branded Looker Studio dashboard or slide template. Time cost: 35 min/cycle.

Write Client Commentary Section

Account Manager writes a short narrative explaining key changes from the prior period. Time cost: 20 min/cycle.

Internal Review of Draft Report

A second team member checks data accuracy, formatting, and tone before the report leaves the business. Time cost: 15 min/cycle.

Confirm Client Recipient Details

Sender checks HubSpot or a contacts list to confirm the correct name and email address for the report recipient. Time cost: 10 min/cycle.

Export and Format Final Report File

Finalised report is exported as a PDF and given a consistent file name matching the client and period. Time cost: 10 min/cycle.

Send Report to Client via Email

Operator manually composes an email, attaches or links the report, and sends it to the confirmed recipient via Gmail. Time cost: 10 min/cycle.

Log Delivery in CRM

A note or activity record is added to the client record in HubSpot confirming the report was sent and when. Time cost: 5 min/cycle.

Notify Team of Delivery

A short message is posted in the relevant Slack channel so the account manager knows the report has gone out. Time cost: 3 min/cycle.

Time cost summary: Total manual time per reporting cycle is 178 minutes (approximately 2 hrs 58 min). At 42 runs per month this equates to approximately 26 hours of manual work per month, or 6 hours per week. The automation replaces steps 1, 2, 3, 4, 7, 8, 9, 10, and 11 entirely for standard accounts. Steps 5 and 6 (commentary drafting and internal review) are replaced by an AI draft plus an optional human review gate retained for flagged accounts only.

Developer Handover PackPage 1 of 4

FS-DOC-04Technical

02Agent specifications

Report Data Collector

This agent fires on a scheduled trigger tied to each client's reporting date as configured in the Google Sheets master schedule. It authenticates with the HubSpot API using an OAuth 2.0 service token, queries the relevant contact, deal, and activity properties for the reporting period, and writes the results into the client's dedicated staging tab in Google Sheets. If any expected field values are absent or return null, the agent flags the row in the staging tab with a status value of MISSING and halts further processing for that client record, triggering an error log entry and a Slack alert so the issue can be resolved before the report is built. Estimated build time: 10 hours. Complexity: Moderate.

Trigger

Scheduled reporting date reached for a client account (date pulled from 'report_due_date' column in Google Sheets master schedule tab)

Tools

HubSpot (CRM data source via API), Google Sheets (staging tab write destination)

Replaces steps

Steps 1, 2, 3: Identify Reports Due; Pull Performance Data from CRM; Pull Supporting Metrics from Other Sources

Estimated build

10 hours / Moderate

// Input
trigger.client_id          // string: unique client identifier from Sheets schedule
trigger.report_period      // string: ISO date range e.g. '2024-04-01/2024-04-30'
trigger.report_template_id // string: Looker Studio template reference for this client
trigger.review_required    // boolean: true if account is flagged for human review

// HubSpot API query targets (confirmed per account during discovery)
hubspot.contact.properties  // e.g. email, firstname, lastname, company
hubspot.deal.properties     // e.g. dealname, amount, dealstage, closedate
hubspot.engagement.activity // e.g. calls, emails, meetings in period

// Output
sheets.staging_tab.client_id         // written to row matching client_id
sheets.staging_tab.period            // ISO date range string
sheets.staging_tab.metrics.*         // all mapped HubSpot field values
sheets.staging_tab.supplementary.*   // any pre-logged secondary metrics already in tab
sheets.staging_tab.data_status       // 'READY' or 'MISSING' per field
sheets.staging_tab.populated_at      // UTC timestamp of write completion

HubSpot field names vary by account configuration: a field mapping session must be completed during discovery week before this agent is built. Do not hardcode property names; use a configuration object stored in the automation platform's credential/variable store.
The Google Sheets master schedule tab must contain the columns: client_id, report_due_date, report_template_id, review_required, and primary_contact_email. The FullSpec team will provide a template schema before build starts.
The staging tab for each client must be pre-created with the agreed column headers before the agent first runs. A one-time setup script will be provided to generate all staging tabs from the master schedule.
If the HubSpot API returns a 429 rate-limit response, the agent must implement exponential back-off with a maximum of three retries before logging a FAILED status and alerting via Slack.
Supplementary metrics (steps 3) are assumed to be pre-populated in the staging tab by the client team or a separate feed. The agent reads these as-is and does not attempt to fetch from secondary external platforms unless explicitly scoped in a later build phase.
Dedupe: if the trigger fires twice for the same client_id and report_period (e.g. due to a schedule misconfiguration), the agent checks for an existing READY row before writing. If found, it skips and logs a DUPLICATE_SKIPPED event.
Confirm before build: HubSpot API access tier (must support private app tokens with crm.objects.deals.read, crm.objects.contacts.read, and crm.objects.engagements.read scopes). Confirm with process owner that these scopes are available on the current HubSpot subscription.

Report Builder and Distributor

This agent triggers once the Report Data Collector has written a READY status to the staging tab for a given client. It initiates a Looker Studio data source refresh via the connected Google Sheets connector, then invokes an AI language model call to read the period-on-period figures and produce a short plain-English commentary section. If the client account carries a review_required flag, the commentary draft is written to a designated Google Drive review document and a Slack notification is sent to the Account Manager. The agent pauses at this point and waits for an approval signal (a cell value update in the staging tab or a webhook callback from a lightweight approval form) before proceeding. Once approved, or immediately for non-flagged accounts, the agent exports the Looker Studio report as a PDF to the client's Google Drive folder, sends the report email via Gmail using a pre-approved template, logs the delivery as a HubSpot engagement activity, and posts a delivery confirmation to the designated Slack channel. Estimated build time: 16 hours. Complexity: Complex.

Trigger

Google Sheets staging tab data_status field transitions to 'READY' for a given client_id and report_period (polled or webhook-triggered depending on orchestration platform capability)

Tools

Google Looker Studio (report refresh via Sheets connector), Google Drive (PDF export and archiving), Gmail (client email delivery), HubSpot (activity log), Slack (delivery notice and review alert)

Replaces steps

Steps 4, 7, 8, 9, 10, 11: Populate Report Template; Confirm Client Recipient Details; Export Report File; Send Report via Email; Log Delivery in CRM; Notify Team in Slack. Also generates the AI draft that replaces step 5 (Write Commentary) for non-flagged accounts.

Estimated build

16 hours / Complex

// Input
sheets.staging_tab.client_id         // string
sheets.staging_tab.period            // ISO date range
sheets.staging_tab.metrics.*         // all READY metric values
sheets.staging_tab.review_required   // boolean
sheets.staging_tab.primary_contact_email  // string: confirmed recipient
sheets.staging_tab.report_template_id     // string: Looker Studio report ID
drive.client_folder_id               // string: target Google Drive folder per client

// AI commentary generation call
ai.prompt.context    // current period metrics + prior period delta values
ai.prompt.tone       // 'professional, concise, client-facing'
ai.output.commentary // string: plain-English narrative, max 150 words

// On approval (flagged accounts only)
approval.signal      // 'APPROVED' value written to sheets.staging_tab.review_status
                     // OR webhook POST to /approve/{client_id}/{period}
approval.reviewer    // string: name of approving Account Manager
approval.timestamp   // UTC datetime of approval action

// Output
drive.pdf_file_id           // string: Google Drive file ID of archived PDF
drive.pdf_file_name         // string: '{client_code}_{period}_report.pdf'
gmail.sent_message_id       // string: Gmail message ID for audit
hubspot.activity.id         // string: HubSpot engagement ID of logged delivery
hubspot.activity.timestamp  // UTC datetime
hubspot.activity.note       // 'Report delivered. Drive link: {pdf_url}'
slack.message.ts            // Slack message timestamp for delivery notice
sheets.staging_tab.dispatch_status  // 'SENT' with timestamp written back

Looker Studio does not expose a fully automated PDF export via a public REST API. The current best approach is a scheduled Apps Script or a third-party connector (e.g. a Looker Studio export service). This adds a small monthly tool cost estimated at $10 to $20/month and one additional setup step during week 3 of the build. Confirm the preferred export method with the process owner before committing to the implementation approach.
The AI commentary call must use a model with a context window sufficient to hold all metric fields for the largest client report variant. Confirm the expected maximum field count during discovery. The prompt template must be stored as an editable variable so the process owner can adjust tone instructions without a code change.
Gmail send must use a pre-approved email template stored in the automation platform's variable store. The template must include: client first name, reporting period label, a one-sentence summary sentence, the AI commentary block, and a closing line with the sender's name drawn from a configuration field, not hardcoded.
The HubSpot activity log step requires the client's HubSpot contact record ID (not just the email address) to associate the engagement correctly. This ID must be stored in the Google Sheets master schedule tab alongside the primary_contact_email field.
Review gate behaviour: if review_required is true and no approval signal is received within 48 hours, the agent sends a reminder Slack DM to the Account Manager and logs a REVIEW_OVERDUE event. It does not send the report without approval.
Slack channel name for delivery notices must be confirmed and stored as a configuration variable. Do not hardcode channel IDs. The channel for review alerts and the channel for delivery notices may differ and should be stored as separate variables.
Confirm before build: Gmail OAuth scope requirements (gmail.send is the minimum; confirm whether gmail.compose is also needed). Confirm that the sending Gmail account is a shared team inbox rather than a personal account to avoid authentication issues if the account owner changes.

Developer Handover PackPage 2 of 4

FS-DOC-04Technical

03End-to-end data flow

Full trigger-to-output data flow trace with field names at each agent handoff

// ============================================================
// CLIENT REPORTING AUTOMATION: END-TO-END DATA FLOW
// ============================================================

// TRIGGER
// Scheduled trigger fires on client reporting date
trigger.source          = 'schedule'
trigger.client_id       = sheets.master_schedule['client_id']        // e.g. 'CLT-042'
trigger.report_period   = sheets.master_schedule['report_due_date']  // e.g. '2024-04-01/2024-04-30'
trigger.template_id     = sheets.master_schedule['report_template_id']
trigger.review_required = sheets.master_schedule['review_required']  // boolean

// ============================================================
// AGENT HANDOFF 1: SCHEDULE -> REPORT DATA COLLECTOR
// ============================================================

// Step 1 (replaces manual step 1): Identify client and period
rdc.input.client_id     = trigger.client_id
rdc.input.period        = trigger.report_period

// Step 2 (replaces manual step 2): HubSpot API call
hubspot.api.endpoint    = 'GET /crm/v3/objects/contacts/{id}/properties'
hubspot.api.endpoint   += 'GET /crm/v3/objects/deals?filters=[owner,period]'
hubspot.api.endpoint   += 'GET /crm/v3/objects/engagements?filters=[period]'
hubspot.auth            = 'Bearer {HUBSPOT_PRIVATE_APP_TOKEN}'
hubspot.scopes          = ['crm.objects.contacts.read', 'crm.objects.deals.read', 'crm.objects.engagements.read']

rdc.mapped_fields.contact_name    = hubspot.response.properties['firstname'] + ' ' + hubspot.response.properties['lastname']
rdc.mapped_fields.deal_value      = hubspot.response.deal['amount']
rdc.mapped_fields.deal_stage      = hubspot.response.deal['dealstage']
rdc.mapped_fields.close_date      = hubspot.response.deal['closedate']
rdc.mapped_fields.calls_made      = hubspot.response.engagements.count['CALL']
rdc.mapped_fields.emails_sent     = hubspot.response.engagements.count['EMAIL']
rdc.mapped_fields.meetings_held   = hubspot.response.engagements.count['MEETING']

// Step 3 (replaces manual step 3): Read supplementary metrics from staging tab
rdc.supplementary.*     = sheets.staging_tab[client_id]['supplementary_*']  // pre-logged by client team

// Write to staging tab
sheets.staging_tab.write.client_id       = rdc.input.client_id
sheets.staging_tab.write.period          = rdc.input.period
sheets.staging_tab.write.metrics.*       = rdc.mapped_fields.*
sheets.staging_tab.write.supplementary.* = rdc.supplementary.*
sheets.staging_tab.write.data_status     = (nullFields.length > 0) ? 'MISSING' : 'READY'
sheets.staging_tab.write.populated_at    = utcNow()

// If MISSING: log error, alert Slack, halt this client branch
if (data_status == 'MISSING') {
  error_log.write({ client_id, period, missing_fields, timestamp })
  slack.post({ channel: config.alert_channel, text: 'Data missing for {client_id} / {period}' })
  halt()
}

// ============================================================
// AGENT HANDOFF 2: REPORT DATA COLLECTOR -> REPORT BUILDER AND DISTRIBUTOR
// ============================================================

// Trigger condition: staging_tab.data_status == 'READY'
rbd.input.client_id             = sheets.staging_tab['client_id']
rbd.input.period                = sheets.staging_tab['period']
rbd.input.metrics.*             = sheets.staging_tab['metrics.*']
rbd.input.supplementary.*       = sheets.staging_tab['supplementary.*']
rbd.input.review_required       = trigger.review_required
rbd.input.primary_contact_email = sheets.master_schedule['primary_contact_email']
rbd.input.hubspot_contact_id    = sheets.master_schedule['hubspot_contact_id']
rbd.input.drive_folder_id       = sheets.master_schedule['drive_folder_id']

// Step 4 (replaces manual step 4): Refresh Looker Studio
looker_studio.connector         = 'Google Sheets data source'
looker_studio.report_id         = rbd.input.template_id
looker_studio.refresh.trigger   = 'Apps Script HTTP trigger or third-party export connector'
looker_studio.refresh.confirmed = boolean  // must return true before PDF export proceeds

// AI commentary draft (replaces manual step 5 for standard accounts)
ai.prompt.metrics_current       = rbd.input.metrics.*
ai.prompt.metrics_prior         = sheets.prior_period_tab[client_id]  // previous cycle row
ai.prompt.instruction           = config.commentary_prompt_template    // editable variable
ai.output.commentary            = string  // max 150 words, plain English

// Review gate decision
if (rbd.input.review_required == true) {
  drive.review_doc.write({ client_id, period, commentary: ai.output.commentary })
  slack.post({ channel: config.review_channel, text: 'Review required: {client_id} / {period}. Doc: {review_doc_url}' })
  // Pause and await approval signal
  approval.signal   = sheets.staging_tab['review_status']  // poll for 'APPROVED'
  approval.reviewer = sheets.staging_tab['reviewer_name']
  approval.timestamp = sheets.staging_tab['approved_at']
  // On 48hr timeout: send reminder DM, log REVIEW_OVERDUE, continue waiting
}

// Step 8 (replaces manual step 8): Export PDF to Google Drive
drive.pdf.file_name  = '{client_code}_{period}_report.pdf'
drive.pdf.folder_id  = rbd.input.drive_folder_id
drive.pdf.file_id    = drive.upload(pdf_bytes)  // returned file ID
drive.pdf.url        = drive.getShareableLink(drive.pdf.file_id)

// Step 7+9 (replaces manual steps 7 and 9): Send Gmail
gmail.send.to        = rbd.input.primary_contact_email
gmail.send.from      = config.sender_gmail_account
gmail.send.subject   = config.email_subject_template.replace('{period}', rbd.input.period)
gmail.send.body      = config.email_body_template
                       // placeholders: {client_name}, {period}, {commentary}, {sender_name}
gmail.send.attachment = drive.pdf.file_id
gmail.auth            = 'OAuth2: gmail.send scope'
gmail.output.message_id = string  // stored for audit

// Step 10 (replaces manual step 10): Log HubSpot activity
hubspot.engagement.create.contact_id = rbd.input.hubspot_contact_id
hubspot.engagement.create.type       = 'NOTE'
hubspot.engagement.create.body       = 'Report delivered for period {period}. Drive: {pdf_url}'
hubspot.engagement.create.timestamp  = utcNow()
hubspot.engagement.output.id         = string

// Step 11 (replaces manual step 11): Slack delivery notice
slack.delivery.channel  = config.delivery_channel
slack.delivery.text     = 'Report sent: {client_id} / {period} / {utcNow()}'
slack.delivery.output.ts = string

// Write-back to staging tab
sheets.staging_tab.write.dispatch_status = 'SENT'
sheets.staging_tab.write.dispatched_at   = utcNow()
sheets.staging_tab.write.gmail_message_id = gmail.output.message_id
sheets.staging_tab.write.drive_file_id    = drive.pdf.file_id
sheets.staging_tab.write.hubspot_activity_id = hubspot.engagement.output.id

// ============================================================
// END OF FLOW FOR THIS CLIENT / PERIOD
// ============================================================

Developer Handover PackPage 3 of 4

FS-DOC-04Technical

04Recommended build stack

Orchestration layer

A workflow automation platform handling one workflow per agent (two primary workflows: Report Data Collector and Report Builder and Distributor) plus one auxiliary error-handling workflow. All credentials (HubSpot private app token, Gmail OAuth2 refresh token, Google Sheets service account key, Slack bot token) stored in the platform's shared credential store and referenced by variable name, not hardcoded. Configuration variables (channel names, prompt template, email subject template, sender address, 48-hour review timeout) stored as environment-level variables editable without touching workflow logic.

Webhook configuration

An inbound webhook endpoint is registered in the orchestration platform to receive the approval callback for the review gate (POST /approve/{client_id}/{period} with a bearer token for authentication). The endpoint updates the Google Sheets staging tab review_status field to APPROVED and passes control back to the Report Builder and Distributor workflow. A second webhook or polling interval (recommended: 5-minute poll on staging tab data_status) triggers the Report Builder and Distributor when the Report Data Collector writes a READY status. Use a dedicated staging tab column as the state flag rather than relying solely on webhook delivery to ensure idempotency.

Templating approach

Email body and subject line stored as plain-text templates with named placeholders (curly-brace syntax: {client_name}, {period}, {commentary}, {sender_name}). Substitution performed in the orchestration layer before the Gmail send step. The AI commentary prompt template stored as a separate editable variable with the full system instruction and a structured output requirement (max 150 words, three sentences minimum). Looker Studio report template linked to the Google Sheets staging tab as its data source; one template per report variant (identified by report_template_id in the master schedule). PDF file naming convention: {client_code}_{YYYY-MM}_report.pdf.

Error logging

All error events (MISSING data, HubSpot API failures, Gmail send failures, Looker Studio refresh timeouts, REVIEW_OVERDUE) written to a dedicated error log table (recommended: a reserved tab in the Google Sheets master file titled 'error_log', or a Supabase table if the team has an existing instance). Each error row contains: client_id, period, error_type, error_detail, timestamp, resolved (boolean). A Slack alert to config.alert_channel is sent for every new error row. The FullSpec team will configure alert deduplication so repeated failures on the same client_id and period do not flood the channel.

Testing approach

All agent workflows are built and validated in a sandbox environment first using a test client record (client_id: TEST-001) with synthetic HubSpot data. The Report Data Collector is tested against a stubbed HubSpot API response before live API credentials are connected. The Report Builder and Distributor is tested with a dummy Gmail recipient (an internal team inbox) and a test Looker Studio report before any client-facing sends occur. PDF export is validated for formatting, file naming, and Drive folder placement across at least three report template variants. The review gate is tested by setting review_required to true on the test record and confirming the pause, Slack alert, 48-hour timeout reminder, and approval-trigger resume all function correctly. Full parallel run (automated alongside manual) for one complete reporting cycle before go-live.

Estimated total build time

Report Data Collector: 10 hours. Report Builder and Distributor: 16 hours. End-to-end integration testing, error handling, and parallel run validation: 2 hours. Total: 28 hours. This aligns with the confirmed build effort of 28 hours from the process snapshot. Discovery and field mapping (week 1) is a prerequisite and is not included in the 28-hour build figure.

Looker Studio PDF export is the highest-risk step in the build. There is no officially supported REST API endpoint for automated PDF export from Looker Studio as of this document date. The recommended approach is a Google Apps Script deployed as a web app that uses the Looker Studio Connectors API or a browser-automation export step via a third-party service. This must be prototyped and confirmed working during week 2 before the full Report Builder and Distributor build proceeds. If the export approach is not viable for the client's Looker Studio configuration, an alternative PDF generation step using a Google Slides or Docs template will be scoped as a fallback.

Before the build begins, the FullSpec team requires the following confirmed from the process owner: (1) HubSpot private app token with the three CRM read scopes listed in the agent spec; (2) the complete list of HubSpot property names to be pulled per client tier; (3) the Gmail account to be used as the sending address and confirmation it is a shared team inbox; (4) the Looker Studio report IDs for each template variant in use; (5) the Google Drive folder structure and folder IDs for client report archives; (6) the Slack channel names for delivery notices and review alerts; and (7) the list of client accounts that carry the review_required flag at go-live. Contact support@gofullspec.com to submit these details.

Developer Handover PackPage 4 of 4

Developer Handover Pack