Introduction
In modern sales operations, forecasting expected revenue accurately is crucial for informed decision-making and resource allocation. HubSpot’s Deal Value Projection feature provides automated forecasting of potential deal revenues based on deal stages, close probabilities, and pipeline data. However, HubSpot’s pricing tiers can make this feature cost-prohibitive for startups and SMBs who want the same insights without the SaaS expense.
This article shows how to build a cost-effective, scalable alternative using n8n—an open-source automation tool—and Google Sheets. This solution automates deal data collection and revenue projection inside Google Sheets, providing real-time forecast updates without needing HubSpot’s premium features.
Who benefits from this:
– Startup CTOs reducing SaaS costs
– Automation engineers building custom revenue workflows
– Operations specialists wanting transparent, editable deal pipelines
What you need:
– n8n instance (cloud or self-hosted)
– HubSpot account with API access
– Google Sheets account
– Basic familiarity with APIs and workflow automation
Tools and Integrations
– HubSpot CRM API: Pull deal data (including deal amount, stage, close probability)
– Google Sheets API: Read & write forecast data
– n8n: Orchestrate the entire workflow (trigger, data transformation, writing output)
Workflow Overview
1. Trigger: Scheduled Cron trigger runs the workflow daily (or as per your needs)
2. HubSpot API Node: Fetch all deals with relevant properties (amount, stage, close probability)
3. Function Node: Calculate projected revenue per deal (Amount x Close Probability)
4. Google Sheets Node: Update or append deal data and projections into a designated spreadsheet
5. Optional: Slack or Email Node to send daily forecast reports
Step-by-Step Technical Tutorial
Step 1: Setting up the Google Sheet
Create a Google Sheet with columns such as:
– Deal ID
– Deal Name
– Deal Stage
– Amount
– Close Probability
– Projected Revenue
Create and save your Google Sheets API credentials and make sure n8n can authenticate with Google Sheets.
Step 2: Preparing HubSpot API Access
– Generate a private app or API key from HubSpot with permissions to read deals.
– Familiarize yourself with HubSpot’s GET /crm/v3/objects/deals endpoint.
– Use this endpoint to pull deal data, including custom properties such as amount and probability.
Step 3: Configure n8n Workflow
1. Cron Trigger Node:
– Set it to run once daily, e.g., at 7 AM.
2. HTTP Request Node (Get HubSpot Deals):
– Method: GET
– URL: https://api.hubapi.com/crm/v3/objects/deals
– Query Parameters: properties=dealname,amount,dealstage,hs_probability
– Headers: Authorization Bearer
– Pagination: Handle pagination if you have many deals.
3. Function Node (Calculate Projected Revenue):
– Extracts each deal’s amount and close probability (hs_probability).
– Calculates projected revenue = amount * (close probability / 100).
– Outputs an array with deal details and projected revenue for each.
Sample JavaScript code in Function Node:
“`javascript
return items.map(item => {
const amount = parseFloat(item.json.amount || 0);
const probability = parseFloat(item.json.hs_probability || 0);
const projectedRevenue = amount * (probability / 100);
return {
json: {
dealId: item.json.id,
dealName: item.json.dealname,
dealStage: item.json.dealstage,
amount,
probability,
projectedRevenue
}
};
});
“`
4. Google Sheets Node (Update Spreadsheet):
– Operation: Append or Update Rows
– Sheet Name: Your designated sheet
– Map input fields to sheet columns:
– Deal ID
– Deal Name
– Deal Stage
– Amount
– Close Probability
– Projected Revenue
To update rows instead of append, implement logic that searches for existing deal IDs to avoid duplicates.
5. (Optional) Slack or Email Node:
– Send a summary message or report with total projected revenue or major pipeline insights.
Common Errors and Tips
– Authentication Failures: Ensure API tokens and OAuth scopes are correct.
– API Rate Limits: Handle HubSpot API rate limitations by throttling or paginating requests.
– Data Types: Validate that numeric fields like amount and probability are not null or strings.
– Updating vs Appending: To avoid duplicate rows, keep track of deal IDs and use Google Sheets ‘Update’ mode properly.
– Scheduling Considerations: Run workflows during off-peak hours to reduce API load.
Scaling and Adaptation
– Add multiple pipelines by using filters on deal stages or teams.
– Extend functionality by integrating Salesforce, Pipedrive, or other CRMs.
– Include more KPIs like weighted forecast totals or rolling averages.
– Trigger the workflow on CRM webhooks for near real-time updates instead of scheduled runs.
– Implement error handling nodes to catch and alert on n8n execution failures.
Summary
By orchestrating HubSpot’s API with Google Sheets through n8n, you create a no-cost, highly customizable revenue projection system that replaces HubSpot’s premium Deal Value Projection feature. This automation empowers startups and operations teams to forecast sales pipeline value accurately while maintaining full control and flexibility.
Bonus Tip
Use Google Sheets’ built-in charts and pivot tables on your updated data to create dynamic visual sales funnel reports that automatically refresh with each n8n workflow run—turning raw data into actionable insights instantly.