Snapshot your HubSpot pipeline weekly in Google Sheets using Make

Prerequisites

Overview

Make (formerly Integromat) is well-suited for this workflow because its visual flow builder handles data aggregation natively — no code required. You'll use Make's HubSpot module to search deals, Numeric and Text Aggregators to compile metrics, and the Google Sheets module to append the snapshot row.

Step 1: Prepare the Google Sheet

Create a new Google Sheet with headers in the first row:

Date | Total Value | Deal Count | Appointment Scheduled (Count) | Appointment Scheduled (Value) | Qualified (Count) | Qualified (Value) | Proposal (Count) | Proposal (Value) | Closed Won (Count) | Closed Won (Value)

Adjust the stage columns to match your HubSpot pipeline stages.

Step 2: Create a scenario and schedule it

Create a new scenario in Make. Click the clock icon at the bottom-left to configure the schedule:

• Schedule type: At regular intervals
• Run scenario: Every week
• Day: Monday
• Time: 08:00
• Timezone: Select your team's timezone

On the Free plan, the minimum interval is 15 minutes. For weekly schedules, any plan works.

Step 3: Fetch pipeline stages

Add an HTTP module (Make an API Call) to fetch pipeline stage names:

• URL: https://api.hubapi.com/crm/v3/pipelines/deals
• Method: GET
• Headers: Use the HubSpot connection (Make adds the Authorization header automatically)

This gives you a mapping of stage IDs to labels and the display order.

Step 4: Search for deals

Add a HubSpot CRM module → Search CRM Objects:

• Object type: Deals
• Filter: Pipeline equals "default" (or your pipeline name)
• Properties to return: dealname, amount, dealstage, closedate, createdate
• Limit: Set to the maximum (Make handles pagination for you)

Step 5: Aggregate deals into metrics

This is where Make's visual builder shines. You'll use aggregators to collapse all deal bundles into summary numbers.

Total pipeline value

Add a Numeric Aggregator module:

• Source module: The HubSpot Search module
• Aggregate function: SUM
• Value: Map amount from the deal bundles

This outputs one bundle with the total pipeline value.

Deal count

Add a second Numeric Aggregator:

• Source module: The HubSpot Search module
• Aggregate function: COUNT

Per-stage breakdown

For each pipeline stage, add a Numeric Aggregator with a filter:

1. Add a Numeric Aggregator for the stage
2. Set Source module to the HubSpot Search module
3. Set Aggregate function to COUNT
4. Click the filter icon and add: dealstage equals appointmentscheduled (or whatever the stage ID is)
5. Add a second Numeric Aggregator for the same stage with SUM on amount
6. Repeat for each stage

Alternative: Use a single Code module

If you have many stages or want less visual clutter, replace the per-stage aggregators with a single Tools → Set Multiple Variables or Code module that processes all bundles at once:

• Language: JavaScript
• Code:

const bundles = $input;
const byStage = {};
 
for (const bundle of bundles) {
  const stage = bundle.dealstage || 'unknown';
  if (!byStage[stage]) byStage[stage] = { count: 0, value: 0 };
  byStage[stage].count += 1;
  byStage[stage].value += parseFloat(bundle.amount || '0');
}
 
return { byStage: JSON.stringify(byStage) };

Step 6: Append row to Google Sheets

Add a Google Sheets module → Add a Row:

• Connection: Your Google Sheets OAuth connection
• Spreadsheet: Select your snapshot spreadsheet
• Sheet: Select the sheet tab
• Map each column:
  • Date: {{formatDate(now; "YYYY-MM-DD")}}
  • Total Value: Map from the SUM aggregator
  • Deal Count: Map from the COUNT aggregator
  • Stage columns: Map from each per-stage aggregator (or parse from the Code module output)

Step 7: Add error handling

Add error handlers to critical modules:

1. On the HubSpot module: Add a Resume error handler with a 10-second delay (handles 429 rate limit responses)
2. On the Google Sheets module: Add a Break error handler that stores the incomplete execution and retries later
3. Go to Scenario settings → Error handling and enable Email notifications for failed executions

Step 8: Test and activate

1. Click Run once to execute the scenario manually
2. Inspect each module's output — click any module to see the bundles it processed
3. Open Google Sheets and verify a new row was appended with the correct date and metrics
4. Toggle the scenario to Active (the scheduling icon turns blue)

Cost and credits

• Free plan: 1,000 credits/month, 15-minute minimum interval. A weekly snapshot with ~50 deals and 5 stages uses approximately 15 credits per run (~60 credits/month). Plenty of room on the free plan.
• Core plan: $10.59/month for 10,000 credits. Required for more complex branching or higher volume.
• Credit calculation: 1 trigger + 1 HTTP + 50 deal bundles through search + aggregators + 1 Sheets append. Triggers count as 1 credit regardless of bundles returned.

Scenario execution limits

• Execution timeout: 40 minutes (hard limit). Not a concern for a pipeline snapshot.
• Maximum scenario data size: 6 MB per execution. Large pipelines with thousands of deals may need filters to reduce data volume.

Next steps

• Week-over-week comparison — use Make's Data Store module to persist last week's totals, then add delta columns to the Sheet
• Conditional alerts — add a Router with a Filter that sends a Slack message when total pipeline drops below a threshold
• Multiple pipelines — duplicate the search module for each pipeline and aggregate separately
• Chart embedding — create a Google Sheets chart on the snapshot data and share the Sheet link with stakeholders