Weekly HubSpot Pipeline Forecast Snapshot to Google Sheets + Slack

PromptCreate

Build me a code workflow that runs every Monday at 7:00am America/New_York and produces a deterministic weekly pipeline forecast snapshot. It should be reproducible, no LLM judgement needed.

Step 1: Use HubSpot List Pipelines to fetch all active deal pipelines and their stages, including each stage's win probability.

Step 2: Use HubSpot Search Deals to pull every open deal across those pipelines, paginating through all pages of results. Request the properties dealname, amount, dealstage, pipeline, and closedate. Treat any deal whose stage is not closed-won and not closed-lost as open.

Step 3: In code, bucket each deal by (pipeline, stage). For each bucket compute deal_count, total_amount (sum of amount), and weighted_forecast (sum of amount times the stage's win probability). Use the stage probability from HubSpot by default.

Step 4: Use Google Sheets Append Values to append one row per (week, pipeline, stage) to a tracking tab. The week key should be the ISO week start date in America/New_York. Columns: week_start, pipeline_name, stage_name, deal_count, total_amount, weighted_forecast, week_over_week_delta. Compute week_over_week_delta by reading the most recent prior row for the same pipeline and stage and subtracting its weighted_forecast from this week's.

Step 5: Use Slack Send a Message to post a compact summary to the revenue channel. Include the total weighted forecast across all pipelines, the change vs last week (absolute and percent), and the top three (pipeline, stage) buckets that contributed the largest absolute swing. Format it tightly with bold headers and a short bullet list.

Make the spreadsheet ID, tracking tab name, Slack channel, and timezone configurable inputs. Log a final summary line with total deals processed, pipelines covered, and total weighted forecast so it's easy to verify the run.

What does this prompt do?

Pulls every open deal from HubSpot across your active pipelines on a Monday 7am ET cron.
Buckets deals by stage and multiplies amount by each stage's win probability to compute a weighted forecast.
Appends one row per (week, pipeline, stage) to a tracking tab in Google Sheets, including deal count, total amount, weighted forecast, and week over week delta.
Posts a compact Slack summary to your revenue channel with total weighted forecast, change vs last week, and the top three stages driving the swing.

What do I need to use this?

A HubSpot account connected with permission to read deals and pipelines.
A Google Sheets account connected with edit access to the tracking spreadsheet.
A Slack workspace connected with permission to post in your revenue channel.
The spreadsheet ID and tab name where weekly snapshots should be appended.
The Slack channel ID or name where the summary should be posted.

How can I customize it?

Change the cron to a different day or timezone (for example Friday 5pm ET for an end-of-week snapshot).
Filter to a single pipeline, a deal owner, or a minimum amount threshold instead of all open deals.
Adjust the Slack summary format, swap in different highlight metrics, or route to a different channel.
Use HubSpot's stage probabilities or override them with your own forecast model in the workflow code.

FAQs

How is the weighted forecast calculated?

For each open deal, the workflow takes the deal amount and multiplies it by the win probability of the deal's current stage. It sums those weighted amounts per pipeline and per stage, and rolls them up into a total.

Where does the win probability come from?

By default it uses the probability configured on each stage in HubSpot. You can override this in the workflow code if you maintain your own forecast model in a sheet or constants file.

How is week over week delta computed?

When the workflow appends new rows to the tracking tab, it reads the most recent prior row for the same pipeline and stage and writes the difference into a delta column. The Slack summary surfaces the top three contributors.

What if I have more than 10,000 open deals?

HubSpot's Search Deals operation caps at 10,000 results. The workflow paginates through results, and if you exceed that you can split the search by pipeline or by close date window so each query stays under the cap.

Can I run this on demand instead of weekly?

Yes. The cron trigger is just one option. You can swap it for a manual trigger or change the schedule without changing the rest of the workflow.

Related templates

Review desk for portal forms your team still fills in by hand

Stage a batch of filings overnight, then approve each completed form from a screenshot before anything is ever submitted.

App

Client-by-client cold email pipeline review for agencies

Pick a client and a date range to see sent, replies, meetings booked and the real deal value your cold email produced, campaign by campaign.

App

Partner tier review board for quarterly promotion calls

Work every partner tier decision from one board, with the revenue and commission numbers, clear promotion thresholds, and AI review briefs.

App

Reconcile partner commissions between HubSpot and Introw

Open one board at month end to find every closed deal missing a commission, every commission with no deal behind it, and every amount that does not add up.

App

Audit what Intercom's Fin AI actually resolved before you pay

Review every conversation Fin closed as resolved, judge which ones actually stuck, and see what the gap is worth against your bill.