Weekly HubSpot Pipeline Forecast Snapshot to Google Sheets + Slack

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.

Additional information

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.

Frequently asked questions

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.