Automated Board Pack Reporting: Xero and HubSpot KPIs

A 40-person professional services firm's finance director would begin assembling the board pack on the 25th of every month. She pulled ARR from HubSpot, gross margin from Xero, headcount from a spreadsheet, and project utilisation from a Notion table. Then she formatted everything into PowerPoint, wrote the commentary, and emailed the draft to the managing director by the 29th — four working days, 14 hours of senior time. We built the replacement pipeline in three weeks. The first automated pack was ready on the 25th at 09:42, 40 seconds after the scheduled trigger fired. The finance director now reviews the document rather than assembles it. That is the actual value: not speed, but the return of senior attention to work that requires judgement.

What a UK SME board pack actually contains and where the 14 hours disappear

Most UK SME board packs follow a predictable structure that nobody documented and everyone assembles from memory. The sections below are typical for a 30–60-person professional services firm:

The 14 hours do not disappear into one long task. They fragment across four days, three tools, two people, and roughly 40 export-paste-format cycles. That fragmentation is exactly why automation helps: you are not replacing one long job, you are eliminating dozens of small handoffs.

This firm's specific problem was that every export step required a manual login, a filter adjustment, and a paste into a template built in 2021 with hard-coded cells. When the Xero chart of accounts changed — it did, twice in 18 months — the whole pack broke and nobody caught it until the commentary referenced numbers from the wrong period.

Data source mapping: connecting Xero, HubSpot, and Google Sheets to a single pipeline

The pipeline architecture for this build:

[Trigger: n8n Cron — 09:40 on the 25th]
         |
    ┌────┴────┐
    │  Fetch  │──→ Xero Accounting API   (P&L, cash, aged debtors)
    │  Layer  │──→ HubSpot CRM API       (pipeline, MRR, qualified leads)
    │         │──→ Google Sheets API     (headcount, utilisation)
    └────┬────┘
         │
  [Validate against KPI schema]
         │
  [LLM Commentary Node — Claude 3.5 Sonnet]
         │
  [Jinja2 HTML Template → WeasyPrint PDF]
         │
  [SendGrid → Finance Director + MD]

Each source has its own fetch-and-validate node. The Xero and HubSpot calls run in parallel and take two to four seconds each. The Google Sheets call is under a second. The validate step converts everything into a single typed KPI schema before the commentary or rendering nodes see any data. This separation is deliberate: if Xero's API returns an unexpected currency code or a null value where a number should be, the error is caught cleanly before the LLM receives half-formed data and produces confident-sounding nonsense about it.

KPI extraction from Xero: ARR, gross margin, cash position, and aged debtors via API

The Xero Accounting API exposes most of what a board pack needs, but the endpoints are not as obvious as exporting a report from the UI.

For gross margin and P&L data, call /Reports/ProfitAndLoss with fromDate and toDate. For aged debtors, use /Reports/AgedReceivablesByContact. Cash position comes from /BankSummary, or for more control, individual /Accounts filtered by type BANK.

Here is the Python node we run inside n8n's Code node to pull the monthly P&L:

import requests, os
from datetime import date

XERO_TOKEN = os.environ["XERO_ACCESS_TOKEN"]
TENANT_ID  = os.environ["XERO_TENANT_ID"]

today = date.today()
first_of_month = today.replace(day=1)

resp = requests.get(
    "https://api.xero.com/api.xro/2.0/Reports/ProfitAndLoss",
    headers={
        "Authorization": f"Bearer {XERO_TOKEN}",
        "Xero-tenant-id": TENANT_ID,
        "Accept": "application/json",
    },
    params={
        "fromDate": first_of_month.isoformat(),
        "toDate":   today.isoformat(),
        "standardLayout": "true",
    },
    timeout=10,
)
resp.raise_for_status()
report = resp.json()["Reports"][0]

The response nests rows inside row groups inside sections — it is not a clean flat object. Write a row-walker function that extracts values by label ("Gross Profit", "Total Income") rather than by array position, because Xero's report structure shifts when someone renames an account category. We found this out on month three when a client's accountant renamed their revenue account and the gross margin field silently returned zero.

One non-negotiable: the Xero access token expires every 30 minutes. Store the refresh token in n8n credentials, not in code, and add a token-refresh step at the top of every workflow that calls Xero.

HubSpot pipeline data: pulling MRR, pipeline value, and qualified leads into the report

The HubSpot CRM Deals API is the primary source for pipeline reporting. For a UK professional services firm, "ARR" typically maps to a custom property on the Deal object rather than a standard HubSpot field. Confirm the property name with the client before building — three of our last five clients had different ARR definitions stored in different places, and two of them had duplicates.

The specific queries a board pack needs:

• Pipeline value by stage: filter Deals by dealstage, sum the amount property per stage
• MRR: sum the monthly_revenue custom property on closed-won Deals from the past 12 months, divided by 12 — or however the client defines it
• Qualified leads this month: filter Contacts by lifecycle stage SALESQUALIFIEDLEAD created within the current calendar month

Use the Search endpoint (POST /crm/v3/objects/deals/search) rather than listing all deals and filtering in code. A client with 800 deals will exhaust pagination limits pulling everything locally. The Search endpoint supports filterGroups and returns only the records and properties you specify.

For a related data-assembly pattern applied to a different output document, see our post on automated proposal generation for UK professional services firms.

Report assembly: LLM-generated commentary on the numbers that doesn't sound generic

The commentary section is where most automated board packs either fail or embarrass their owners. Generic output — "Revenue increased this month" — is worse than no commentary because it signals that no senior person looked at the numbers.

The approach that produces usable commentary:

1. Pass the full KPI JSON to the LLM with a system prompt specifying the firm's sector, the previous month's actuals, and three variance thresholds. Ours are: flag any month-on-month movement above 10%, any cashflow figure below £50k, and any aged debtors total above 90 days.
2. Instruct the model to comment only on figures that crossed a threshold. Silence on stable numbers is correct behaviour.
3. Ground every number in the prompt: inject the values, ask the model to write about them. A prompt like "Gross margin moved from 64.2% to 61.8% this month. Write two sentences on this movement. Do not infer causes unless the data includes a breakdown by service line" produces reliable output. Asking the model what gross margin is this month produces hallucination.

The ICAEW's guidance on AI in financial reporting flags automated commentary presented without human review as a material misstatement risk. Every pack we ship includes a "Generated on [date] — please review before circulation" footer and a sign-off window before board distribution.

One hard rule: do not ask the LLM to do arithmetic. Calculate every variance and percentage in code, then pass the result into the prompt.

PDF rendering with brand consistency: the template layer that makes automated reports boardroom-ready

The PDF layer uses a Jinja2 HTML template rendered to PDF via WeasyPrint. HTML and CSS keep the template in a format a designer can modify without touching the pipeline code.

Key decisions in the template layer:

• Logo and brand colours: injected as CSS variables loaded from a per-client config file at render time
• Page headers and footers: WeasyPrint supports the CSS @page rule for running headers, footers, and page numbers — use it; PDF generators that do not support @page produce documents that look like long screenshots
• Charts: pre-rendered in Python with matplotlib, converted to base64-encoded PNG, embedded inline in the HTML. WeasyPrint's SVG support is inconsistent across library versions; base64 PNG embeds are reliable
• Tables: standard HTML <table> with alternating row colours and explicit column widths set in px rather than % to avoid Xero data with long account names breaking column layout

This firm's original PowerPoint template used 14 font size variations. The automated version uses four. Nobody on the board noticed. Our invoice OCR pipeline case study covers the WeasyPrint rendering approach including multi-page layouts and dynamic table heights.

Scheduling and delivery: monthly triggers and error handling when source data is missing

The pipeline runs on an n8n cron trigger: 40 9 25 * * — 09:40 on the 25th of every month. A date-check node at the top of the workflow detects whether the 25th falls on a weekend and shifts the trigger date to the following Monday.

Error handling is more consequential than the happy path. Failures we have hit in production:

• Xero token expired mid-run (30-minute window; now auto-refreshed at workflow start)
• HubSpot returning 429 Too Many Requests on the second API call within the same minute
• Google Sheets row count changed because a department head added a row mid-month without telling anyone
• Xero P&L returning zero values because the accountant had not yet posted month-end journals

For each failure mode, the workflow sends a Slack alert to the ops lead naming the specific failure and including a one-click retry button implemented as an n8n webhook trigger. The board pack is not sent if any source fetch returns an error or produces a value outside expected bounds (e.g., total revenue of zero when last month was £180k). Sending a pack with missing data is worse than sending nothing.

The retry window runs from 09:40 to 14:00. A second Slack alert fires at 14:00 if no successful pack has been delivered, flagging that manual assembly may be needed — enough time for the ops lead to respond before close of business. For related error-handling patterns, see our post on invoice data extraction pipelines for UK SMEs.

What changed in 2025–2026: Xero Analytics Plus and HubSpot Breeze revenue intelligence

Two platform developments in 2025–2026 change the calculus for new builds.

Xero Analytics Plus expanded to all UK Established plan subscribers in 2025. The API now includes a /AnalyticsPlusCashflow endpoint returning a 30-day cashflow forecast without you needing to build a forecasting model. For board packs, this is genuinely useful: you can include a forward-looking cashflow figure sourced directly from Xero rather than building your own projection from bank transaction history. The caveat is data quality: the forecast accuracy depends on how consistently the client categorises transactions, which varies more than clients expect.

HubSpot Breeze, launched Q3 2024 and iterated through 2025, includes an AI-generated revenue intelligence layer that produces deal-level commentary inside HubSpot. Some clients ask whether Breeze makes this pipeline redundant. It does not — Breeze is scoped to HubSpot data only and does not incorporate Xero figures, headcount, or utilisation. A board pack requires a cross-source narrative that no single platform generates. That said, check the HubSpot Breeze Intelligence documentation before building the HubSpot side from scratch; some deal-stage commentary Breeze now ships natively.

Good / Bad / Ugly: three board pack automation approaches and their failure at month-end

The export-and-parse approach is what we see most often when clients attempt this themselves with Make or Zapier. It looks simple: download the monthly export, parse the P&L column. In practice, Xero's CSV export column order changes when Xero updates the report UI, without announcement. We have seen clients run on broken packs for three months without noticing because the numbers looked plausible — they were just from the wrong date range.

For a counterpoint on maintaining manual controls even inside automated financial reporting, the ICAEW Technology Thought Leadership series on AI adoption makes a case for human gates worth reading before presenting this to any board with accountants on it. Their argument — that automation removes errors of effort but introduces errors of assumption — is correct, and it is why the sign-off window exists in every pack we ship.

The document RAG case study covers the same validate-before-render pattern applied to internal knowledge retrieval.