Pipeline Intelligence Report with CRM Integration

The Problem

Pipeline reporting at most services companies is a weekly ritual of pulling HubSpot exports into spreadsheets, reformatting data, adding color commentary, and emailing a PDF to leadership. By the time the report reaches the board, the data is three days stale. Worse, the narrative context (why a deal slipped, what changed in a prospect's buying process, which rep needs coaching) lives in the sales director's head, not in any system.

The board wants predictability. They want to see pipeline by stage, weighted forecast, deal velocity, and risk indicators. They want it current. And they want the revenue leader's commentary attached to the data, not delivered separately in a meeting.

What I Built

I designed and deployed a Pipeline Intelligence Report on Cloudflare Workers that connects directly to the HubSpot CRM API and renders a live, interactive pipeline view with integrated director commentary.

The system pulls active deals from a specific HubSpot pipeline, excludes closed stages (won and lost), and renders them in a structured report with multiple views. Each deal shows company name, deal value, pipeline stage, close date, days until close, and an urgency indicator. Stages are visualized as a horizontal bar chart showing total value per stage, giving leadership an instant read on pipeline shape, whether deals are concentrated in early stages (risky) or late stages (healthy).

A weighted forecast section applies stage-specific win probabilities to calculate expected revenue. This replaces the manual spreadsheet forecast that took hours to assemble each week.

The commentary layer is the critical differentiator. The sales director can enter weekly observations (deal-level notes, pipeline-level themes, risk flags, and coaching notes) through a passcode-protected interface. These comments are stored in a D1 database, timestamped by week, and rendered alongside the pipeline data. The report becomes a living document that captures both the quantitative state and the qualitative narrative.

Technical Architecture

The Worker fetches deal data from HubSpot's CRM API using a private app token, with built-in rate limiting (5 attempts per 15-minute window) and response caching (60-minute TTL) to stay within API limits. All API responses are cached in the Worker's memory for the TTL window, reducing HubSpot API calls by roughly 90%.

Director commentary is stored in a Cloudflare D1 database, keyed by the Monday of each reporting week. This makes it trivial to retrieve the right commentary for any weekly view. Authentication for the commentary interface uses timing-safe passcode verification (SHA-256 hashed comparison) to prevent timing attacks.

The frontend uses a clean, minimal design: urgency dots (red/yellow/green) based on days to close, stage pills with color coding, probability bars per stage, and formatted currency values. The bar chart is pure CSS, no charting library needed.

Design Decisions

Several choices reflect lessons learned from building reporting tools that people actually use. The weekly keying of commentary (Monday of each week) means directors don't have to think about date ranges, they just write their notes and the system files them correctly. Caching HubSpot responses aggressively means the report loads in under two seconds even when HubSpot's API is slow. Excluding closed stages from the active view keeps the report focused on what leadership cares about, deals that need attention.

The urgency scoring is deliberately simple: red for overdue or closing within 14 days, yellow for closing within 30 days, green for 30+ days out. This surfaces deals that need immediate attention without requiring complex scoring algorithms.

Impact

The report replaced a weekly three-hour pipeline assembly process with a live URL that leadership checks daily. Pipeline visibility went from weekly snapshots to real-time. The commentary layer captured institutional knowledge that previously existed only in meetings, creating an auditable record of pipeline narrative that proved invaluable during board reviews and quarterly planning.